Subscript out of range when building arrays in Excel VBA?

Question

Below is code that builds up attributes and then prints them as an xml file. Everytime I run it, it says that the subscript is out of range, highlighting attributes2. Columns 40-41 should be within the range defined.

I assume the issue may be because I shouldn't use 'ElseIf'. When I run it as only attributes1 and attributes2, it works fine when I use 'Else' statement. Perhaps I defined my arrays wrong, either way I am unable to find an answer and need some fresh eyes.

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

You are asking ElseIf 40 <= CBool(c <= 41). Perhaps you should be asking ElseIf 40 <= c And c <= 41) ...? — user4039065
– user4039065, Commented Mar 31, 2016 at 3:06

Comintern · Accepted Answer · 2016-03-31 03:12:11Z

3

The problem is with your If statements - you can't chain conditionals like that in VBA. In VBA, they'll be evaluated left to right, so this...

40 <= 50 <= 41

... turns into this:

(40 <= 50) <= 41

The expression 40 <= 50 returns True, which becomes...

True <= 41

...and True casts to a numeric value of -1 in VBA, so you end up with...

If -1 <= 41 Then

...which happens to be both True and an index that is out of range.

So, put an And in your tests:

ElseIf 40 <= c And c <= 41 Then

edited Mar 31, 2016 at 3:12

answered Mar 31, 2016 at 3:06

Comintern

22.3k6 gold badges36 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

J.H Over a year ago

Holy...if this is the answer I will cut. Update you when I have time to test this out.

leowyn · Accepted Answer · 2016-03-31 03:13:58Z

1

The previous answer is correct, but as a stylistic thing, this is a perfect place to use a Select .. Case statement:

Select Case c
    Case 1 To 39
        attributes1(c) = attr
    Case 40 To 41
        attributes2(c) = attr
    Case 42 To 51
        attributes3(c) = attr
    ... ' Fill in the other  possibilities
End Select

answered Mar 31, 2016 at 3:13

leowyn

1285 bronze badges

1 Comment

J.H Over a year ago

Thank you for reminding me of this, I knew the solution was extremely obvious. Sometimes you just need to take a walk and come back with a fresh perspective...

Collectives™ on Stack Overflow

Subscript out of range when building arrays in Excel VBA?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related