下面是构建属性然后将它们打印为 xml 文件的代码。每次我运行它，它都说下标超出范围，突出显示 attributes2。第 40-41 列应在定义的范围内。

我认为问题可能是因为我不应该使用“ElseIf”。当我仅将它作为 attributes1 和 attributes2 运行时，当我使用“Else”语句时它工作正常。也许我错误地定义了我的数组，无论哪种方式我都无法找到答案并且需要一些新的眼光。

Sub ILikeFruits()

Dim headers(), data(), attributes1(), attributes2(), attributes3(), _
attributes4(), attributes5(), attributes6(), attributes7(), attributes8(), attr$, r&, c&

' load the headers and data to an array '

headers = Cells(1, 1).Resize(1, 104).Value
data = Cells(2, 1).Resize(10, 104).Value


' set the size for the attributes '
ReDim attributes1(1 To 39)
ReDim attributes2(40 To 41)
ReDim attributes3(42 To 51)
ReDim attributes4(52 To 65)
ReDim attributes5(66 To 69)
ReDim attributes6(70 To 89)
ReDim attributes7(90 To 97)
ReDim attributes8(98 To 104)

' open file and print the header '
Open "C:\desktop\XML Update\Simulation\XML tests (Attributes)\DataTest.xml" For Output As #1
Print #1, "<Fruits>"
Print #1, "  <Tasty_Fruits>"

' iterate each row '
For r = 2 To UBound(data)

  ' iterate each column '
  For c = 1 To UBound(data, 2)

    ' build each attribute '
    attr = headers(1, c) & "=""" & data(r, c) & """"
    If c <= 39 Then
      attributes1(c) = attr
    ElseIf 40 <= c <= 41 Then 'Subscript out of range
      attributes2(c) = attr
    ElseIf 42 <= c <= 51 Then
      attributes3(c) = attr
    ElseIf 52 <= c <= 65 Then
      attributes4(c) = attr
    ElseIf 66 <= c <= 69 Then
      attributes5(c) = attr
    ElseIf 70 <= c <= 89 Then
      attributes6(c) = attr
    ElseIf 90 <= c <= 97 Then
      attributes7(c) = attr
    ElseIf 98 <= c <= 104 Then
      attributes8(c) = attr
      End If
  Next

  ' print the row '
  Print #1, "    <Fruits_By_Color " & Join(attributes1, " ") & " >"
  Print #1, "      <Small_Red_Fruits>"
  Print #1, "        <Cranberry " & Join(attributes2, " ") & " />"
  Print #1, "      </Small_Red_Fruits>"
  Print #1, "      <Big_Red_Fruits>"
  Print #1, "        <Apple " & Join(attributes3, " ") & " />"
  Print #1, "        <Pomegranate " & Join(attributes4, " ") & " />"
  Print #1, "        <Tomato " & Join(attributes5, " ") & " />"
  Print #1, "      </Big_Red_Fruits>"
  Print #1, "      <Yellow_Fruits>"
  Print #1, "       <Small_Yellow_Fruits " & Join(attributes6, " ") & " >"
  Print #1, "        <Banana " & Join(attributes7, " ") & " />"
  Print #1, "        <Lemon " & Join(attributes8, " ") & " />"
  Print #1, "       </Small_Yellow_Fruits>"
  Print #1, "      </Yellow_Fruits>"
  Print #1, "    </Fruits_By_Color>"

Next

' print the footer and close '
Print #1, "  </Tasty_Fruits>"
Print #1, "</Fruits>"
Close #1

End Sub

最佳答案

问题出在你的If上语句 - 您不能像 VBA 中那样链接条件。在 VBA 中，它们将从左到右计算，所以这...

40 <= 50 <= 41

...变成这样:

(40 <= 50) <= 41

表达式40 <= 50返回 True , 变成...

True <= 41

...并且 True 在 VBA 中强制转换为 -1 的数值，所以您最终得到...

If -1 <= 41 Then

...这恰好是 True以及超出范围的索引。

所以，放一个And在你的测试中:

ElseIf 40 <= c And c <= 41 Then

关于xml - 在 Excel VBA 中构建数组时下标超出范围？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/36323305/