title: Efficient UDF to check formulae consistency in Excel
body: <p>For financial modelling we often come across with the problem that we need to check array of formulas for being consistent (i.e. nobody put hard-coded values into array of formulas or adjusted formula for specific periods). Using array functions is not an option in our working environment.</p>
  
  <p>I have tried to come up with a user-defined function that would check the consistency. However it is very slow and makes working in a 1-2 thousand line financial model simply impossible due to lag caused.</p>
  
  <p>Can you please help me with ideas how to check the consistency efficiently or highlight inefficiencies in my code?</p>
  
  <pre><code>Public Function isConsistent(ByRef range_to_check As Range, Optional ByVal R1C1 As Boolean = False)
  '
  ' Check if all formulae in range_to_check are consistent
  ' To check R1C1 formula, pass True as second parameter
  '
  '
  
      ' Get array of formulas: either normal formula or R1C1 depending on inputs
      If R1C1 = True Then
          formulae = range_to_check.FormulaR1C1
      Else
          formulae = range_to_check.Formula
      End If
  
      ' Check every formula in array
      ' If at least one formula in array does not match, return False
      If IsArray(formulae) Then
          firstFormula = formulae(1, 1)
          For Each f In formulae
              If f &lt;&gt; firstFormula Then
                  isConsistent = False
                  Exit Function
              End If
          Next f
      End If
  
      ' If test passed, return True
      isConsistent = True
  
  End Function
  </code></pre>