我想要一个函数,它可以循环遍历已关闭的工作簿,其中包含来自另一个工作簿的单元格的一些参数。目的是计算一个月内某些地点的人数。我#VALUE
在工作表中传递参数时出错。
Option Explicit
Public Function count(collega As Range, luogo As Range, mese As Range) As Integer
Dim nomeMese As String
Dim nomeLuogo As String
Dim nomeCollega As String
Dim rangeLuogo As String
Dim stringaMese As String
Dim file As Variant
Dim wb As Workbook
'Dim count As Integer
count = 0
nomeMese = mese
nomeLuogo = luogo
nomeCollega = collega
Select Case True
Case nomeLuogo = "ponte milvio"
rangeLuogo = "$A$2:$B$2"
Case Else
rangeLuogo = "null"
End Select
Select Case True
Case nomeMese = "Gennaio"
stringaMese = "-01-2022"
Case Else
stringaMese = "null"
End Select
file = Dir("C:\Users\sbalo\Desktop\Test\*.xlsx") 'la funzione Dir permette di looppare senza specificare...
While (file <> "")
If InStr(file, stringaMese) > 0 Then 'cerca nella directory il file con il match mese-anno
Set wb = Workbooks.Open("C:\Users\sbalo\Desktop\Test\" & file)
count = count + Application.CountIf(wb.Sheets("Foglio1").Range(rangeLuogo), nomeCollega)
wb.Close SaveChanges:=False
End If
file = Dir
Wend
End Function
答案1
VBA 中的 UDF 受到某些限制,例如无法修改另一个单元格、打开工作簿等。
看看这里
https://stackoverflow.com/questions/46782402/can-excel-vba-function-open-a-file
和这里
https://stackoverflow.com/questions/7693530/excel-vba-cant-open-workbook
唯一的解决方法是在 UDF 中打开另一个 Excel 实例,这可能会耗费大量资源(请参阅链接)。确保关闭并退出所有工作簿,并且不要将此 UDF 作为易失性函数调用的一部分来调用,因为该函数调用会一直重新计算并降低工作簿的速度。
或者,重写您的代码,以便从链接到按钮的子/宏执行,例如“从外部工作簿刷新”。