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

问题描述

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

 对于每个oSheet在ActiveWorkbook.Sheets 
错误转到NextSheet：
设置qry = oSheet.ListObjects（1）.QueryTable 
 oCmbBox.AddItem oSheet.Name 
 
 NextSheet：
下一个oSheet 
  

我不知道问题是否与在循环中嵌套On Error GoTo相关，或者如何避免使用循环

解决方案

问题可能是您没有从第一个错误中恢复。您不能在错误处理程序中抛出错误。您应该添加一个简历语句，如下所示，所以VBA不再认为你在错误处理程序内：

  For每个oSheet在ActiveWorkbook.Sheets 
错误GoTo NextSheet：
设置qry = oSheet.ListObjects（1）.QueryTable 
 oCmbBox.AddItem oSheet.Name 
 NextSheet：
恢复NextSheet2 
 NextSheet2：
下一个oSheet 
  




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

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.

解决方案

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屋！