☰

VBA で整数を期待している関数が #VALUE エラーを返す

tag-icon • tag-icon microsoft-excel vba
VBA で整数を期待している関数が #VALUE エラーを返す

別のブックのセルからいくつかのパラメータが与えられた、閉じたブックをループする関数が必要です。目的は、1 か月を考慮して、いくつかの場所に存在する人の数をカウントすることです。#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 を揮発性関数呼び出しの一部として呼び出さないようにしてください。揮発性関数呼び出しは、常に再計算を行ってワークブックの速度を低下させます。

または、たとえば「外部ワークブックから更新」というボタンにリンクされたサブ/マクロから実行するようにコードを書き直します。

関連情報