☰

Excel VBA 自定义函数在 Debug.Print 中有效，但在工作表中无效（给出错误答案）

tag-icon • tag-icon microsoft-excel worksheet-function vba
Excel VBA 自定义函数在 Debug.Print 中有效，但在工作表中无效（给出错误答案）

我有一个包含多张工作表的工作簿，每张工作表都具有相同的表格/布局。定期添加新工作表并删除旧工作表。

我想计算特定字符串在特定列（始终是同一列字母）中出现的次数，全部工作表的总数。使用 COUNTIFS + COUNTIFS + 等可以轻松完成此操作，但我不想每次添加或删除工作表时都必须不断更新 COUNTIFS 公式。我编写了一个函数来检查名称存储在数组中的所有工作表，因此我所要做的就是更新该数组。

函数前声明的公共项：

' Used by function checkDuplicates
' Array to hold names of tabs in use within workbook
' NOTE: Need to update within the function when sheets are added/deleted
    Public yearArr As Variant
' Location of column to check
    Const myCol As String = "H:H"

函数本身：

Function checkDuplicates(myString As String) As Integer

' Update this when sheets are added or deleted
yearArr = Array("2018", "2019", "2020", "2021")

' Year referenced by worksheet tabs 
' Will reference the contents of yearArr
Dim yearSheet As Variant

' Integers to hold our running totals
Dim bigTotal As Integer
Dim loopTotal As Integer
' Reset them at the start of each function run (probably not necessary but hey)
bigTotal = 0
loopTotal = 0

' Loop through all sheets and count

For Each yearSheet In yearArr
    ' Select a specific sheet
    Debug.Print "This yearSheet is " & yearSheet
    Sheets(yearSheet).Select
    ' Count how many instances of 'myString' in 'myCol' column
    loopTotal = Application.WorksheetFunction.CountIf(Range(myCol), myString)
    Debug.Print "loopTotal is now " & loopTotal
    ' Add the total from this loop onto the running total
    Debug.Print "bigTotal is now " & bigTotal
    bigTotal = bigTotal + loopTotal
    Debug.Print "At the end of loop " & yearSheet & ", bigTotal is finally " & bigTotal
    
Next yearSheet

' Output final total
checkDuplicates = bigTotal
Debug.Print "Final output is " & checkDuplicates

End Function

来自立即窗口的示例输出：

This yearSheet is 2018
loopTotal is now 40
logTotal is now 0
At the end of loop 2018, logTotal is finally 40
This yearSheet is 2019
loopTotal is now 9
logTotal is now 40
At the end of loop 2019, logTotal is finally 49
This yearSheet is 2020
loopTotal is now 19
logTotal is now 49
At the end of loop 2020, logTotal is finally 68
This yearSheet is 2021
loopTotal is now 0
logTotal is now 68
At the end of loop 2021, logTotal is finally 68
Final output is 68

效果很好！但是，在单元格中使用 =functionCheckDuplicates("blah") 返回的是 0，而不是 68！我尝试使用另一个应该返回 2（在即时窗口中也确实如此）的“blah”，但函数在单元格中返回的是 4！在我看来，它甚至不是始终正确的。

我到底做了什么蠢事？

答案1

我找到了一个基于公式的答案！我制作了一个表格，其中包含我想要检查的所有其他表格名称的列表，然后将该列表保存为命名范围。现在我可以将此公式粘贴到单元格中：

=SUMPRODUCT(COUNTIF(INDIRECT(listActive&"[specific column name]"),"Blah"))

其中“listActive”是包含所有表名称的命名范围。

本页有帮助：https://www.extendoffice.com/documents/excel/2541-excel-countif-across-multiple-worksheets.html

我在最初的问题上想得太多了。我仍然希望有其他答案/想法可以增加我对自定义函数及其能做什么和不能做什么的了解。

相关内容