循环中的vba错误处理 [英] vba error handling in loop

问题描述

vba 新手，尝试错误转到"，但是，我不断收到错误索引超出范围".

New to vba, trying an 'on error goto' but, I keep getting errors 'index out of range'.

我只想制作一个由包含查询表的工作表名称填充的组合框.

I just want to make a combo box that is populated by the names of worksheets which contain a querytable.

    For Each oSheet In ActiveWorkbook.Sheets
        On Error GoTo NextSheet:
         Set qry = oSheet.ListObjects(1).QueryTable
         oCmbBox.AddItem oSheet.Name

NextSheet:
    Next oSheet

我不确定问题是否与在循环中嵌套 On Error GoTo 或如何避免使用循环有关.

I'm not sure whether the problem is related to nesting the On Error GoTo inside a loop, or how to avoid using the loop.

推荐答案

问题可能是你没有从第一个错误中恢复.您不能从错误处理程序中抛出错误.您应该添加一个 resume 语句，类似于以下内容，以便 VBA 不再认为您在错误处理程序中:

The problem is probably that you haven't resumed from the first error. You can't throw an error from within an error handler. You should add in a resume statement, something like the following, so VBA no longer thinks you are inside the error handler:

For Each oSheet In ActiveWorkbook.Sheets
    On Error GoTo NextSheet:
     Set qry = oSheet.ListObjects(1).QueryTable
     oCmbBox.AddItem oSheet.Name
NextSheet:
    Resume NextSheet2
NextSheet2:
Next oSheet

这篇关于循环中的vba错误处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！