vba - IsNA 无法识别封闭工作簿引用中的 #N/A

Question

我正在尝试测试关闭的外部工作簿中的单元格是否不适用。在下面的代码中，引用的工作簿中的单元格“G5”绝对不适用，但是当使用下面的 IsNA 函数引用它时，它会返回“Good to go!”当其意图是返回“嘿!”在消息框中。

Sub TestTest()

    'Declaring variables [BD]
    Dim sFilePath As String
    Dim sFileName As String
    Dim sSourceSheet As String
    Dim sSourceCell As String

    sFileName = "0306-0312 Margin Master.xlsx"
    sFilePath = "\\store\GroupDrives\Pricing\_Deli_\Deli Fresh Shift\Margin Master\"
    sSourceSheet = "Bakery"
    sSourceCell = "G5"

    If Application.WorksheetFunction.IsNA("'" & sFilePath & "[" & sFileName & "]" & sSourceSheet & "'!" & _
    Range("A1").Range(sSourceCell).Address(, , xlR1C1)) Then
        MsgBox "Hay!"
    Else
        MsgBox "Good to go!"
    End If

End Sub

Answer 1

尝试使用ExecuteExcel4Macro:

Sub TestTest()

    'Declaring variables [BD]
    Dim sFilePath As String
    Dim sFileName As String
    Dim sSourceSheet As String
    Dim sSourceCell As String
    dim externalValue As Variant

    sFileName = "0306-0312 Margin Master.xlsx"
    sFilePath = "\\store\GroupDrives\Pricing\_Deli_\Deli Fresh Shift\Margin Master\"
    sSourceSheet = "Bakery"
    sSourceCell = "G5"

    externalValue = ExecuteExcel4Macro("'" & sFilePath & "[" & sFileName & "]" & sSourceSheet & "'!" & _
        Range("A1").Range(sSourceCell).Address(, , xlR1C1)) 
    If Application.IsNa(externalValue) Then
        MsgBox "Hay!"
    ElseIf IsError(externalValue) Then
        MsgBox "May not work"
    Else
        MsgBox "Good to go! (value is '" & externalValue & "')"
    End If

End Sub

---

注意:Range("A1").Range(sSourceCell).Address(, , xlR1C1) 可能可以缩写为 Range(sSourceCell).Address(, , xlR1C1) 如果您只是使用单元格引用(例如“G5”)作为 sSourceCell 的值。