excel - Excel VBA：为什么 Application.Volatile 将单元格重置为零

Question

在我的工作簿中，当我更改使用此函数的工作表或使用 F9 时，宏会生成所需的值。当我在工作簿中的另一个工作表上进行更改时，宏使用此函数将工作表上的值重新计算为零。而且，当我保存或打开工作簿时，重新计算也会使用函数零重置所有单元格。如果我使使用此函数的工作表处于活动状态，然后按 F9，则宏将计算所需的值，并使用所需的值重新填充活动工作表和工作簿中其他位置的单元格。

我不明白为什么这个功能会这样工作。我需要始终重新计算其值，而不是重置为零。非常感谢您的帮助！

谢谢你。

Public Function LastMoneyValue(rng As Range) As Single
    Dim v As Variant
    Dim count As Integer
    Dim i As Variant
    Dim row As Long
    Dim column As Long
    Dim aMoneyValues() As Variant
    Dim vLastMoneyValue As Variant
    Dim money As Single
    Dim counter As Long
    
    Application.Volatile
    
    count = rng.Cells.count
    row = rng.row
    column = rng.column
    counter = column       'use counter to increment through the array. Hold the column variable constant.
    
    'Note ranges should be an even number. In this case 26 representing 13 months of the POP.
    ' money = count / 2
    money = count
    ReDim aMoneyValues(money)
    
    'fill the money array with money. Increment the starting column by 1 the first time as the money is in the adjacent cell to the hours.
     For i = 0 To money - 1
        aMoneyValues(i) = Cells(row, counter).value
        'counter = counter + 2
        counter = counter + 1
    Next i
    
    'evaluate the money array. Reset the counter i.
    i = 0
    For i = LBound(aMoneyValues) To UBound(aMoneyValues)
        v = aMoneyValues(i)
        If v > 0 Or v < 0 Then
            vLastMoneyValue = aMoneyValues(i)
        End If
    Next i
    LastMoneyValue = vLastMoneyValue
End Function

标签： excelvbafunction