vba - Excel VBA 数组循环故障排除 : Using Redim and UBound, 一维和二维数组

Question

我从 Stackover Topic 来到这里做更快的宏。我得到了答案，但这段代码不起作用，我问你，(我试图修复)

   Sub Faster_Method()

Dim objIE As InternetExplorer
Dim Prc1 As String
Set objIE = New InternetExplorer
Dim Search_Terms() As Variant
Dim CopiedData() As Variant

objIE.Visible = True


Search_Terms() = ActiveSheet.Range("A1:A121").Value
ReDim CopiedData(1 To UBound(Search_Terms) + 1)

For a = 1 To UBound(Search_Terms) + 1

objIE.navigate "https://opskins.com/?loc=shop_search&app=578080_2&sort=lh&search_item=" & Search_Terms(a)
Do: DoEvents: Loop Until objIE.readyState = 4
Prc1 = objIE.document.getElementsByClassName("item-amount")(0).innerText
CopiedData(a) = Prc1

Next

ActiveSheet.Range(Cells(1, 2), Cells(UBound(CopiedData), 2)).Value = CopiedData

objIE.Quit

End Sub

错误是: run time error '9' subscript out of range 调试是: objIE.navigate "https://opskins.com/?loc=shop_search&app=578080_2&sort=lh&search_item=" & Search_Terms(a)

固定

   Sub Faster_Method()

Dim objIE As InternetExplorer
Dim Prc1 As String
Set objIE = New InternetExplorer
Dim Search_Terms() As Variant
Dim CopiedData() As Variant


Dim y As Integer
objIE.Visible = True


Search_Terms = Application.Transpose(ActiveSheet.Range("A1:A121").Value)

ReDim CopiedData(LBound(Search_Terms) To UBound(Search_Terms))


y = 1


For a = LBound(Search_Terms) To UBound(Search_Terms)
objIE.navigate "https://opskins.com/?loc=shop_search&app=578080_2&sort=lh&search_item=" & Search_Terms(a)
Do: DoEvents: Loop Until objIE.readyState = 4
Prc1 = objIE.document.getElementsByClassName("item-amount")(0).innerText
Sheets("Sheet1").Range("B" & y).Value = Prc1

y = y + 1

Next

ActiveSheet.Range(Cells(1, 2), Cells(UBound(CopiedData), 2)) = Application.Transpose(CopiedData)

objIE.Quit

End Sub

Answer 1

有几个问题。

Search_Terms() = ActiveSheet.Range("A1:A121").Value

上面的代码创建了一个 Search_Terms(1 到 121、1 到 1)的二维数组。这可以通过以下方式验证，

debug.print lbound(Search_Terms, 1) & " to " & ubound(Search_Terms, 1)
debug.print lbound(Search_Terms, 2) & " to " & ubound(Search_Terms, 2)

接下来，您尝试使用以下方法 reshape 一个新数组，

ReDim CopiedData(1 To UBound(Search_Terms) + 1)

这会将二维数组转换为 CopiedData(1 到 122)的新空白一维数组。

现在你进入一个循环。

For a = 1 To UBound(Search_Terms) + 1

这里的 ubound 是第一个等级(例如 1 到 121)。你不能去 Search_Terms(122, 1) 因为它不存在。因此，当 a 变为 122 时，以下崩溃会导致运行时错误“9”下标超出范围。

objIE.navigate "https://opskins.com/?loc=shop_search&app=578080_2&sort=lh&search_item=" & Search_Terms(a)

可能的解决方案

Search_Terms = application.transpose(ActiveSheet.Range("A1:A121").Value)

创建 Search_Terms(1 到 121)的一维数组。

ReDim CopiedData(LBound(Search_Terms) To UBound(Search_Terms))

将目标数组 reshape 为相同的尺寸。

For a = LBound(Search_Terms) To UBound(Search_Terms)

不要走出下界或上界。

ActiveSheet.Range(Cells(1, 2), Cells(UBound(CopiedData), 2)) = application.transpose(CopiedData)

您将 A1:A121 转置为一维数组。有意义的是，您需要转置一个相同的一维数组以将其放回 B1:B121。