如何自动更新使用特定 VBA 代码的 Excel 中的单元格？

亚历山大·威廉

我在 Excel 中有一个工作表，其中包含使用此 VBA 宏的单元格（使用两个参数，它计算所有工作表中 Arg#2 范围内 arg#1 的出现次数，并返回总数）：

Public Function WBCountString(SearchFor As String, InRange As Range) As Long
Dim wbcs As Long, rng As Range, addr As String
For Each s In Worksheets
    addr = InRange.Address
    Set rng = s.Range(addr)

    wbcs = wbcs + Application.WorksheetFunction.CountIf(rng, SearchFor)
Next s
WBCountString = wbcs
End Function

该宏用作单元格中的函数，如下所示，它使用位于 A:A 中的单元格作为参数：

当我更改 A:A 单元格值时，使用脚本的单元格的值不会更改，我必须单击公式并按“Enter”才能更新值。

我尝试在“ThisWorkbook”中使用此功能，但收到“更改”消息框，但 F:F 中的单元格不会更新或更改值：

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
 ByVal Source As Range)
 ' runs when a sheet is changed
    MsgBox "Change."
    Application.Calculate
End Sub

我怎样才能使使用此宏的单元格在每次更新工作簿任何工作表上的任何单元格 A:A 时更新？

里卡多·迪亚兹

我重构了一些代码，使变量更容易理解

这里的关键是添加 Application.Volatile 以便在每次工作簿更改时重新计算它。

请注意，如果工作簿中的公式很重（这种方法会减慢您的工作簿计算速度）

代码：

Public Function WBCountString(SearchFor As String, InRange As Range) As Long
    Dim targetSheet As Worksheet
    Dim lookupRange As Range

    Dim counter As Long

    Dim lookupAddress As String

    For Each targetSheet In Worksheets
        lookupAddress = InRange.Address
        Set lookupRange = targetSheet.Range(lookupAddress)

        counter = counter + Application.WorksheetFunction.CountIf(lookupRange, SearchFor)

    Next targetSheet

    WBCountString = counter

    ' Recalculate with every change in workbook
    Application.Volatile

End Function

让我知道它是否有效

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。