VBA 错误处理仅适用于第一次通过

VBA Error Handling only works on first pass

提问by Derek

My code is:

我的代码是：

Sub zaa()
'code to find another open file that has the defined name "data" 
'    - useful where the name changes each month or week

For Each wb In Workbooks
  On Error GoTo abcd
  x = wb.Name
  Workbooks(x).Activate
  If Range("Data").Address <> "" Then y = wb.Name

  Exit Sub

abcd:
Next wb

End Sub

Basic goal to find an Excel file with a specific named range when I know it exists but don't know the file name as it changes each week or month. Aim is to find the file and exit the sub at that point (or then do other code on that file and exit rather than going to other files.)

基本目标是找到具有特定命名范围的 Excel 文件，当我知道它存在但不知道文件名时，它每周或每月都在变化。目标是找到该文件并在该点退出子（或者然后对该文件执行其他代码并退出而不是转到其他文件。）

I find it works okay if I only have two files open but not if have more (unless the target one is second in line). Whilst I can run with what I have I thought others may benefit from what I have and I can have a more robust solution.

我发现如果我只打开了两个文件，但如果打开更多则不行（除非目标文件排在第二位）。虽然我可以运行我拥有的东西，但我认为其他人可能会从我拥有的东西中受益，并且我可以拥有更强大的解决方案。

UPDATE: Thanks to all those who have responded & to Mitch for putting original post in readable format! (I have since learnt how to correct that issue and to be able to copy code directly - which I have indicated in some comments below that I was having trouble with.) I have had varying degrees of success - code from PaulStock & Reafidy both worked for me originally but PaulStock code has stopped working for me. Responses & code from Jean-Fran?ois Corbett and Chris Neilsen have been helpful to me but presently when I run the code (as is) it appears to do nothing - ie doesn't leave the sheet I run it from (and that's not where the range name data appears). I was originally running the code in Excel 2007 in a sheet with other macros but to try and get different results have moved them to a stand alone sheet with no other macro files open. Have also tried running them from a file saved as '97-'03 format. Neither yielded different results. Others with more experience than I (see the errors I made evidenced in comments discussion with Reafidy & bear in mind that my original posted code was result of material found through google and modified by me for specific task & application and implication that I am not savvy enough to have come up with it on my own) may find other solutions better for them but for now:

更新：感谢所有回复并感谢米奇将原始帖子置于可读格式的人！（此后我学会了如何纠正该问题并能够直接复制代码 - 我在下面的一些评论中指出我遇到了问题。）我取得了不同程度的成功 - 来自 PaulStock 和 Reafidy 的代码都有效最初对我而言，但 PaulStock 代码已停止对我来说有效。Jean-Fran?ois Corbett 和 Chris Neilsen 的响应和代码对我很有帮助，但目前当我运行代码（按原样）时，它似乎什么也没做 - 即不离开我运行它的工作表（这不是范围名称数据出现的地方）。我最初是在带有其他宏的工作表中运行 Excel 2007 中的代码，但为了尝试获得不同的结果，已将它们移至独立工作表，没有打开其他宏文件。还尝试从保存为 '97-'03 格式的文件中运行它们。两者都没有产生不同的结果。其他比我更有经验的人（请参阅我在与 Refidy 的评论讨论中证明的错误并记住，我最初发布的代码是通过谷歌找到的材料的结果，并由我针对特定任务和应用程序进行修改，暗示我不精明足以让我自己想出它）可能会为他们找到更好的其他解决方案，但现在：

I am more than happy as Reafidy's code works for me.

我很高兴 Refidy 的代码对我有用。

As I am not registered (I did try but it didn't work) and don't have enough reputation points I can not vote but I have put a check mark next to Reafidy's solution.

由于我没有注册（我确实尝试过但没有用）并且没有足够的声望点我不能投票，但我在 Refidy 的解决方案旁边打了一个复选标记。

FURTHER UPDATE: I have now discovered that PaulStock & Jean-Fran?ois Corbett's code wasn't working for me due to a very minor discrepancy on my part - the code contained "Data" whilst my named range was "data". Making this change (to allow for case sensitivity) in their code means that both of their solutions now work for me and hence I have attempted to add a tick to their solutions! Unfortunately I have found that only one solution can have a tick.

进一步更新：我现在发现 PaulStock 和 Jean-Fran?ois Corbett 的代码对我不起作用，因为我有一个非常小的差异 - 代码包含“数据”，而我的命名范围是“数据”。在他们的代码中进行此更改（以允许区分大小写）意味着他们的两个解决方案现在都适用于我，因此我试图在他们的解决方案中添加一个勾号！不幸的是，我发现只有一种解决方案可以打勾。

I have also learnt that I took Chris' code too literally. In an effort to test each code 'as is' that is what I did. A simple additon of 'wb.activate' in the section where he has 'do stuff' makes the code do what I want.

我还了解到我从字面上理解了 Chris 的代码。为了“按原样”测试每个代码，这就是我所做的。在他有“做事”的部分中添加了一个简单的“wb.activate”，使代码做我想要的。

THANKS again for all four contributions.

再次感谢所有四个贡献。

回答by Jean-Fran?ois Corbett

You're doing a few convoluted things in your code, and I think that's what's confusing the issue.

您在代码中做了一些令人费解的事情，我认为这就是混淆问题的原因。

Having you error handler (abcd:) start in the middle of a For...Nextloop is incredibly bad practice and can only lead to confusion. I can't think of a reason why this should ever be done.

让错误处理程序 ( abcd:) 在For...Next循环中间开始是非常糟糕的做法，只会导致混乱。我想不出为什么要这样做的原因。

Anyhow, there's no need to use error handling for this task. I understand that there are exceptional cases where bad VBA design forces us to use error handling instead of what should be built-in VBA functionality (e.g. testing whether a non-Variant array has been allocated), but this is not one of those cases. There's a standard way to do it (.Namescollection of workbook object), and using error handling instead of this is messy and convoluted and asking for trouble.

无论如何，没有必要为此任务使用错误处理。我知道在某些特殊情况下，糟糕的 VBA 设计迫使我们使用错误处理而不是应该使用的内置 VBA 功能（例如测试是否已分配非 Variant数组），但这不是这些情况之一。有一种标准的方法来做到这一点（.Names工作簿对象的集合），而使用错误处理而不是这种方法是混乱和令人费解的，并且会自找麻烦。

Also, why say

还有，为什么说

x = wb.Name
Workbooks(x).Activate

when you can just say wb.Activate? You're not using xfor anything. Or y, for that matter.

你wb.Activate什么时候可以说？你没有x任何用途。或者y，就此而言。

The following works and is both simplified and optimised, relative to your original code as well as to the other answers that have been given up to now:

相对于您的原始代码以及迄今为止已给出的其他答案，以下内容有效并且既简化又优化：

Sub zaa2()
    Dim wb As Workbook
    Dim nm As Name

    For Each wb In Workbooks
        For Each nm In wb.Names
           If nm.Name = "Data" Then
                wb.Activate
                Exit Sub
           End If
        Next
    Next wb
End Sub
' A workbook containing a range named "Data" is now activated 
' (if one is found amongst the open workbooks). 
' Note that there may be more than one, but only the first found is activated...

EDIT:In your comment, you mention you had trouble due to confusion between uppercase "Data"and lowercase "data". To guard against this in the future, one possibility is to ignore case. This could be done as follows:

编辑：在您的评论中，您提到由于大写"Data"和小写之间的混淆而遇到了麻烦"data"。为了防止将来出现这种情况，一种可能性是忽略大小写。这可以按如下方式完成：

If StrComp(nm.Name, "data", vbTextCompare) = 0 Then

or

或者

If LCase(nm.Name) = "data" Then

Both will return Trueif nm.Nameis "Data", "data", "dATa", etc.

True如果nm.Name是, , 等"Data"，两者都会返回。"data""dATa"

回答by Reafidy

You can't use error handling like that. Either move the error handling out of the loop or reset the error handler each time it occurs, so use error handling like this:

你不能像那样使用错误处理。将错误处理移出循环或在每次发生时重置错误处理程序，因此请使用如下错误处理：

A much preferred alternative would be:

一个更受欢迎的替代方案是：

Sub Test()

    For Each wb In Workbooks
        x = wb.Name
        Workbooks(x).Activate            

        If RangeExists("Data") Then
            y = wb.Name
            Exit Sub
        End If

    Next wb

End Sub
Function RangeExists(s As String) As Boolean
    On Error Resume Next
    RangeExists = Range(s).Count > 0
End Function

EDIT:

编辑：

@Jean-Fran?ois Corbett, I have to say you a very quick to jump on the down vote button. The first solution I posted was because I made the assumption that the OP was not posting his entire code hence why I did not attempt to simplify it or "clean it up" like I usually do. I agree I did not word my answer well, but with regard to the first solution I was trying to demonstrate that he needed to reset the error handler. Unfortunately I should have said a "prefered alternative would be".

@Jean-Fran?ois Corbett，我不得不说你很快就按下了否决按钮。我发布的第一个解决方案是因为我假设 OP 没有发布他的整个代码，因此我没有像往常一样尝试简化它或“清理它”。我同意我没有很好地表达我的答案，但关于第一个解决方案，我试图证明他需要重置错误处理程序。不幸的是，我应该说“首选的替代方案是”。

@Derek, Sorry I was unable to answer your further questions in time. Obviously you are free to choose whatever method you like. In my opinion the multiple loop solution provided by others which digs into the workbook name collection is unnecessary and long winded. Now more importantly the name collection can contain names which refer to a constant, formula, or a range. I presume you want to only check if the defined name is specifically a named range which means the looping method provided by others would need to be adjusted to be reliable.

@Derek，抱歉，我无法及时回答您的进一步问题。显然，您可以自由选择您喜欢的任何方法。在我看来，其他人提供的深入工作簿名称集合的多循环解决方案是不必要的，而且冗长乏味。现在更重要的是，名称集合可以包含引用常量、公式或范围的名称。我想您只想检查定义的名称是否专门是一个命名范围，这意味着需要调整其他人提供的循环方法以使其可靠。

I agree with comments made by others that error handling should be avoided BUT unnecessary looping in excel can be as much of an evil as using error handling and personally I avoid it like the plague.

我同意其他人的评论，即应该避免错误处理，但在 excel 中不必要的循环可能与使用错误处理一样邪恶，我个人像避免瘟疫一样避免它。

The function above can be placed in its own module and be reused as much as you like. It is quick, reliable, avoids unnecessary looping, checks SPECIFICALLY for a named range in a workbook and is the most widely accepted/used method for checking if a named range exists within the excel vba community (by this I mean using a function and error handling over looping through the name collection). Do a google search for "Check If Named Range Exists" if you don't believe me. Or ask at www.ozgrid.com/forum if you want other excel vba experts opinion's.

上面的函数可以放在自己的模块中，可以随意复用。它快速、可靠、避免不必要的循环、特别检查工作簿中的命名范围，并且是检查 excel vba 社区中是否存在命名范围的最广泛接受/使用的方法（我的意思是使用函数和错误处理遍历名称集合）。如果您不相信我，请在谷歌上搜索“检查命名范围是否存在”。或者在 www.ozgrid.com/forum 上询问是否需要其他 excel vba 专家的意见。

Now that I know you have posted your entire code and that you did not intend to activate every workbook, you could use this code which will activate the first workbook found with the named range "data":

现在我知道您已经发布了您的整个代码并且您不打算激活每个工作簿，您可以使用此代码来激活在命名范围“数据”中找到的第一个工作簿：

Sub Test3()
    Dim wbLoop As Workbook

    For Each wbLoop In Workbooks
        If RangeExists("data", wbLoop) Then
            wbLoop.Activate
            Exit Sub
        End If
    Next wbLoop

End Sub
Function RangeExists(s As String, wb As Workbook) As Boolean
   On Error Resume Next
   RangeExists = wb.Names(s).RefersToRange.Count > 0
End Function

I completely understand the need for positive Criticism and I believe in the down vote system if it is used correctly. However, with two down votes for what I believe was a reasonable solution and along with my help with the ops formatting issues - unfortunately I cant help but feel like I want to distance myself from this forum.

我完全理解积极批评的必要性，如果正确使用，我相信反对票制度。但是，对于我认为合理的解决方案以及我对操作格式问题的帮助，我投了两次反对票 - 不幸的是，我不禁觉得我想与这个论坛保持距离。

回答by chris neilsen

Heres an alternative method without getting fancy with the error handler

这是一种替代方法，无需考虑错误处理程序

Sub zaa()
    Dim wb As Workbook
    Dim CheckForNamedRange As Boolean
    Dim nm As Name

    On Error GoTo EH

    For Each wb In Workbooks
        CheckForNamedRange = True
        Set nm = wb.Names("data")
        If CheckForNamedRange Then
            ' Name found
            ' do stuff

            Exit For
        End If
    Next
Exit Sub
EH:
    If CheckForNamedRange Then
        ' Name not found
        Err.Clear
        CheckForNamedRange = False
        Resume Next
    Else
        ' Some other error occured, so handle it
        '...
    End If
End Sub

回答by PaulStock

Try this code. Shouldn't have to worry about getting errors.

试试这个代码。不必担心出错。

Sub zaa()
    For Each wb In Workbooks
        x = wb.Name
        Workbooks(x).Activate
        For Each n In Workbooks(x).Names
           If n.Name = "Data" Then
                y = wb.Name
                Exit Sub
           End If
        Next

    Next wb
End Sub