excel - 为什么所有这些变体都在正确的公式上？

Question

我制作了一个将三个报告合二为一的宏。
我首先通过查看打开的工作簿以找到匹配的名称来找到动态名称

For Each wk In Workbooks
    If Left(wk.Name, 14) = "PayrollSummary" Then
        Set wbpay = Workbooks(wk.Name)
    End If
    If Left(wk.Name, 12) = "PunchedHours" Then
        Set wbpun = Workbooks(wk.Name)
    End If
Next

从一开始，这条线就起作用了（ws 是它正在处理的报告）。

ws.Range("K5").Formula = "=IFERROR(VLOOKUP(A5,['" & wbpay.Name & "']payrollsummary!$B:$B,1,FALSE),""Fel"")"

然后那条线开始起作用，这奏效了：

ws.Range("K5").Formula = "=IFERROR(VLOOKUP(A5,[" & wbpay.Name & "]payrollsummary!$B:$B,1,FALSE),""Fel"")"

现在我添加了第三个：

On Error Resume Next
ws.Range("K5").Formula = "=IFERROR(VLOOKUP(A5,['" & wbpay.Name & "']payrollsummary!$B:$B,1,FALSE),""Fel"")"
ws.Range("K5").Formula = "=IFERROR(VLOOKUP(A5,[" & wbpay.Name & "]payrollsummary!$B:$B,1,FALSE),""Fel"")"
ws.Range("K5").Formula = "=IFERROR(VLOOKUP(A5,'[" & wbpay.Name & "]payrollsummary'!$B:$B,1,FALSE),""Fel"")"
On Error GoTo 0

因为今天只有第三行有效。
下面是 Excel 中的公式示例：

工作簿名称将始终为ParollSummary_DateFrom_DateTo_SomeRandomStuff.xlsx.
看着图像，我似乎不小心下载了两次文件 (1)。
但无论哪种方式，我仍然看不出为什么三行不同的代码随机地（我的印象）与不同的文件一起工作的原因。
有什么方法可以确保它始终有效，这样我就不需要找出下周的正确方法是什么？

标签： excelvba

Answer 1

很抱歉在这里发布“答案”，但讨论空间不足。让我们详细看看你的代码。

For Each wk In Workbooks
    If Left(wk.Name, 14) = "PayrollSummary" Then
        Set wbpay = Workbooks(wk.Name)
    End If
    If Left(wk.Name, 12) = "PunchedHours" Then
        Set wbpun = Workbooks(wk.Name)
    End If
Next

目前尚不清楚为什么应检查以“PayrollSummary”开头的工作簿名称是否也以“PunchedHours”开头。两者是互斥的。当两者都被找到时，搜索应该停止，而当其中一个没有找到时，你的宏的其余部分不应该被执行。您上面的代码可能会发生这些事情中的任何一个，从而导致稍后出现错误。下面的代码不会有刚才描述的错误。

Sub Trial()

    Dim WbPay       As Workbook
    Dim WbPun       As Workbook
    
    If GetWorkbook(WbPay, "PayrollSummary") Then
        If Not GetWorkbook(WbPun, "PunchedHours") Then Exit Sub
        
        ' continue your code here
        Debug.Print WbPay.Name
        Debug.Print WbPun.Name
    End If
End Sub

Private Function GetWorkbook(Wb As Workbook, _
                             WbName As String) As Boolean

    For Each Wb In Workbooks
        If InStr(1, Wb.Name, WbName, vbTextCompare) = 1 Then
        GetWorkbook = True
        Exit For
    Next Wb
End Function

现在我们知道其余代码不会因为找不到工作簿之一而失败。WbPay和WbPun实际上都存在并且是开放的。

这就引出了为什么我们需要使用工作表函数来访问它们的问题。既然他们所有的内容都可以访问，为什么不直接获取呢？但你想要这个： -

=IFERROR(VLOOKUP(A5,['ParollSummary_DateFrom_DateTo_SomeRandomStuff.xlsx']payrollsummary!$B:$B,1,FALSE),"Fel")

这个需求附带了三个问题。

1. 公式在哪个工作簿中？请记住，A5它位于该工作簿的 ActiveSheet 上。如果此时输入公式的工作表未处于活动状态，会发生什么情况？我不知道，但如果 Excel 会尝试在这种情况下执行公式，则必须发生错误 - 可能是错误 1004。
2. 'ParollSummary_DateFrom_DateTo_SomeRandomStuff.xlsx' 应该是WbPay.Name. 为什么不使用这个定义？它将保证引用的工作簿确实存在并且是打开的。我们不知道“ParollSummary_DateFrom_DateTo_SomeRandomStuff.xlsx”。事实上，即使在这里，该名称也包含拼写错误（从您发布的代码中提取）。
3. 为什么要从查找数组的列（1）返回一个值？这将与您在 A5 中的值相同。并不是说这本身会导致错误，而是会增加一般的混乱。

因此，结论是您的计划风险高，精度低。解决方案必须是降低风险并提高精度。