Excel 2003 VBA运行时错误'9'：下标越界求助

Hey there, let's figure out why your refresh code works in Excel 2010 but throws a "Subscript out of range" error in 2003!

The Root Cause #

The key issue here is Excel version compatibility:

• Excel 2007 and later (including 2010) use the ListObjects collection to refer to what we call "Tables" in the UI.
• Excel 2003 doesn't support ListObjects—back then, these structured data ranges were called "Lists" and accessed via the Lists collection instead.

Your line Sheets("Tape and Reel").ListObjects(1).QueryTable.Refresh fails in 2003 because the ListObjects collection either doesn't exist at all, or there's no item at index 1 (since your table gets converted to a "List" when opened in 2003).

Solutions #

1. Excel 2003-Specific Fix #

If you only need to run this macro in Excel 2003, replace your problematic line with:

Sheets("Tape and Reel").Lists(1).QueryTable.Refresh

2. Cross-Compatibility (Works in 2003 and 2010+) #

If you want your code to work across both versions, add a version check to handle both collection types:

Dim targetSheet As Worksheet
Set targetSheet = Sheets("Tape and Reel")

' Excel 2007 is version 12.0—first version to support ListObjects
If Val(Application.Version) >= 12 Then
    targetSheet.ListObjects(1).QueryTable.Refresh
Else
    targetSheet.Lists(1).QueryTable.Refresh
End If

Quick Checks to Avoid Future Errors #

Before running the code in 2003, double-check:

• The sheet name "Tape and Reel" is spelled exactly as it appears in Excel (VBA ignores case, but typos will break things)
• There’s at least one List/QueryTable on that sheet (so Lists(1) or ListObjects(1) has a valid target)

内容的提问来源于stack exchange，提问作者lazybug