title: Custom Excel VBA formula generates #VALUE! only on automatic calculate
body: <p>I have a cell "A" with a complicated formula involving 2 custom functions / formula.</p>
  
  <p>I understand that when a cell changes value, Excel automatically recalculates all its dependant cells. When cell "A" gets updated from this mechanism, it gives #VALUE!.</p>
  
  <p>Without doing anything else, I select cell "A" > hit F2 (edit) > then hit enter (exit edit), the value of cell "A" gets recalculated and gives correct value.</p>
  
  <p><strong>EDIT 1</strong>: Out of these <a href="http://office.microsoft.com/en-ca/excel-help/change-formula-recalculation-iteration-or-precision-HP010054149.aspx#BMrecalculate_a_worksheet_or_workbook_m" rel="nofollow">keyboard shortcuts</a> only CTRL+ALT+SHIFT+F9 gives correct value. All others give #VALUE! if the function does not have <code>Application.Volatile</code>.</p>
  
  <p>The formula:</p>
  
  <pre><code>=HLOOKUPRANGE(arr(CB43,CC43,CE43),Q!$CD$3:$DG$28,CG43)
  </code></pre>
  
  <p>VBA functions in question:</p>
  
  <pre><code>Public Function arr(ParamArray fields() As Variant)
      arr = fields
  End Function
  
  Public Function HLOOKUPRANGE(headers() As Variant, lookup_range As Range, row_index As Integer) As Variant
      If lookup_range.Columns.Count = 1 Then
          HLOOKUPRANGE = lookup_range(row_index, 1)
      Else
          ' look at the first row in the range for the header
          For colStart = 1 To lookup_range.Columns.Count Step 1
              col = lookup_range.Cells(1, colStart).Value
              If col = headers(1) Then
                  colEnd = colStart + lookup_range.Cells(1, colStart).MergeArea.Columns.Count - 1
                  Exit For
              End If
          Next
  
          ' set the new smaller range
          First = lookup_range.Cells(2, colStart).Address
          Last = lookup_range.Cells(lookup_range.Rows.Count, colEnd).Address
          Dim szRange As String
          szRange = First &amp; ":" &amp; Last
  
          ' set the new headers array
          Dim header_next() As Variant
          If UBound(headers) &gt; 1 Then
              ReDim header_next(1 To UBound(headers) - 1)
              For i = LBound(headers) + 1 To UBound(headers) Step 1
                  header_next(i - 1) = headers(i)
              Next
          End If
  
          HLOOKUPRANGE = HLOOKUPRANGE(header_next, Range(szRange), row_index)
      End If
  End Function 
  </code></pre>
  
  <p>The combined work of the functions is to navigate a lookup table from the headers in a top down fashion and return a value in the table based on the string headers defined in arr().</p>
  
  <p><strong>EDIT 1</strong>: When checking the Calculation Steps, arr() evalutes correctly into HLOOKRANGE({"hdr1","hdr2","hdr3"},Q!$CD$3:$DG$28,CG43), but this result evaluates into #VALUE!</p>