excel - 自定义 Excel VBA 公式生成 #VALUE!仅在自动计算时

Question

我有一个包含 2 个自定义函数/公式的复杂公式的单元格“A”。

我了解当单元格更改值时，Excel 会自动重新计算其所有相关单元格。当单元格“A”从此机制更新时，它会给出#VALUE!。

没有做任何其他事情，我选择单元格“A”>点击F2(编辑)>然后点击回车(退出编辑)，单元格“A”的值被重新计算并给出正确的值。

编辑 1 : 其中keyboard shortcuts只有 CTRL+ALT+SHIFT+F9 给出正确的值。所有其他人都给#VALUE!如果函数没有 Application.Volatile .

公式:

=HLOOKUPRANGE(arr(CB43,CC43,CE43),Q!$CD$3:$DG$28,CG43)

有问题的 VBA 函数:

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 & ":" & Last

        ' set the new headers array
        Dim header_next() As Variant
        If UBound(headers) > 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 

这些函数的组合工作是以自上而下的方式从标题中导航查找表，并根据 arr() 中定义的字符串标题在表中返回一个值。

编辑 1 :检查计算步骤时，arr() 正确计算为 HLOOKRANGE({"hdr1","hdr2","hdr3"},Q!$CD$3:$DG$28,CG43)，但此结果计算为 #VALUE!

Answer 1

似乎 Range() 函数在调用时具有 Application.ActiveSheet范围而不是包含相同函数的单元格公式的工作表的范围。

因此，为了解决我的问题，我需要从正确的工作表中显式调用 Range()，例如

Dim rangeWorksheet as String
rangeWorksheet = lookup_range.Cells(2, colStart).Parent.Name
Dim curws As Worksheet
Set curws = Worksheets(rangeWorksheet)
curws.Range(...)