☰

在 VBA 中期望整數的函數傳回 #VALUE 錯誤

tag-icon • tag-icon microsoft-excel vba
在 VBA 中期望整數的函數傳回 #VALUE 錯誤

我想要一個循環遍歷封閉工作簿的函數，其中一些參數是從另一個工作簿的儲存格給出的。目的是統計一個月內某些地點的人數。#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 呼叫作為易失性函數呼叫的一部分，該函數呼叫將一直重新計算並減慢工作簿的速度。

或重寫您的程式碼以從連結到按鈕的子/巨集執行，例如「從外部工作簿刷新」。

相關內容