Print Array result from For Each function VBA getting subscript out of range error

Question

I continuously receive a subscript out of range error on the following and I can't understand why. I'm no VBA guru, I know how I would do this with JS, but I make my way VBA through projects when I have to. In this case, I need to create an array from my for each statement and print it to a file. I would just leave out the array and print from within the for each loop (which works) but I need to be able to add to the contents of the file and then also access the array later from a different function (a question for another time).

Hopefully someone can help with what I am doing wrong here.

Public Function Orders()
    Dim ItemDescription As String, Counter As Integer, Range As Range, sArray() As Variant
    ReDim sArray(1 To 1) As Variant
    length = Cells(Rows.Count, 1).End(xlUp).Row
    Set Range = ActiveSheet.Range("A2:A" & length)
    IntFile = FreeFile
    StrFile = "C:\Projects\Test.txt"
    Open StrFile For Output As #IntFile
    For Each Cell In Range
        ItemDescription = Cell.Offset(0, 19).Value
            If (Cell.Value = Cell.Offset(1, 0).Value And Cell.Value <> Cell.Offset(-1, 0).Value) Or (Cell.Value <> Cell.Offset(-1, 0).Value And Cell.Value <> Cell.Offset(1, 0).Value) Then
                Counter = 1
            ElseIf (Cell.Value = Cell.Offset(1, 0).Value And Cell.Value = Cell.Offset(-1, 0).Value) Or (Cell.Value <> Cell.Offset(1, 0).Value And Cell.Value = Cell.Offset(-1, 0).Value) Then
                Counter = Counter + 1
            End If
            If Counter = 1 Then
                OlStr = ItemDescription
            ElseIf Counter > 1 Then
                 OlStr = ItemDescription & " " & "your count =" & " " & Counter
            End If
            sArray(UBound(sArray)) = OlStr
            ReDim Preserve sArray(1 To UBound(sArray) + 1) As Variant
    Next Cell
    Print #IntFile, sArray(1, UBound(sArray))
    Close #IntFile
End Function

The issue occurs at the "Print #IntFile, sArray(1, UBound(sArray))" line, 3rd from the bottom. I understand that this means I am trying to reference an item outside the dimension of the array, but I can't seem to figure out why. If I put the Print line inside the For Each I still get the error. Here's the error:

As a note, it looks like the UBound is getting created correctly. I have 2537 lines in my worksheet, though it does have a header.

Any help is greatly appreciated!!!

You have a one dimensional array so you can only specify one index value. sArray(1) and sArray(UBound(sArray)) would be value but not sArray(1, UBound(sArray)). — DecimalTurn
If you just want to print all the cell values, you could put Print #IntFile, sArray(UBound(sArray)) inside the For Each loop before the Redim Statement. Or, you could just bypass the array an do Print #IntFile, OlStr. — DecimalTurn
@DecimalTurn I was able to do #IntFile, OlStr without issue, looks like I can print the array in the for each loop as well (thanks for the 1D v 2D array lesson). Part of this is that I need to be able to store the array for use later as well. In JS I would just return it in the function then call the function later as a variable. I thought I could do the same in VBA, but declaring the function As String, or Variant, or whatever, then trying to store it as a variable and call in later doesn't seem to be working. — PhilT41
Yeah, the scope of variable and array is quite different in JS vs VBA. If you want to reuse an array across procedures, you can always declare it as public. Feel free to ask a separate question about that if that's something you would like to have a proper answer to. — DecimalTurn

DecimalTurn DecimalTurn · Accepted Answer · 2020-08-13T02:44:58

As per my comments:

You have a one dimensional array so you can only specify one index value. sArray(1) and sArray(UBound(sArray)) would be valid but not sArray(1, UBound(sArray))

If you just want to print all the cell values, you could put Print #IntFile, sArray(UBound(sArray)) inside the For Each loop before the Redim Statement. Or, you could just bypass the array an do Print #IntFile, OlStr.

Print Array result from For Each function VBA getting subscript out of range error

1 Answers