ArrayList / Add causing out of Memory errors - not large/complicated Excel file

[srp]

I'm trying to generate an array of worksheets and I'm getting an out of memory error when I try to add to the array - well, on another person's machine. My machine works just fine. The excel file is not large (it's 4mb), nor is it overly complicated. It has 10 worksheets and the array has 7 members once going through the following code:

Dim wsArray As New ArrayList

For wsi = 1 To Worksheets.Count
MsgBox "Checking " + Worksheets(wsi).Name
If IsNumeric(Left(Worksheets(wsi).Name, 2)) = True Then
MsgBox "Adding " & wsi & " - " + Worksheets(wsi).Name
'wsArray.Add Worksheets(wsi).Name
End If
Next

-- Background

I was doing a Set wsArray = CreateObject("System.Collections.ArrayList") but that was throwing an out of memory error and so now I've loaded the .dll on the other machine and we're now erroring out on the add line/for loop.

Thoughts?

the comment on the add line - that's uncommented out when I'm running it. Just troubleshooting things and is a copy/paste error.

 

[johnnyL]

How about:

    Dim wsArray As Object
'
    Set wsArray = CreateObject("System.Collections.ArrayList")
'
    For wsi = 1 To Worksheets.Count
        MsgBox "Checking " + Worksheets(wsi).Name
'
        If IsNumeric(Left(Worksheets(wsi).Name, 2)) = True Then
            MsgBox "Adding " & wsi & " - " + Worksheets(wsi).Name
            wsArray.Add Worksheets(wsi).Name
        End If
    Next

 

[srp]

I'm not using late binding, I've added the dll and am doing earlybinding now. (and I think that's all I see for changes in the above from my original code?)

I've traced it to the line:

wsArray.Add Worksheets(wsi).Name

That causes the out of memory error.

 

[johnnyL]

The code is not the cause of memory error. It may trigger that error, but it is not the cause. Try restarting the computer and try it again. If that doesn't work then you may have to terminate some other programs that are running to free up some memory.

 

[srp]

I created a work around - instead of using the ArrayList command, I just manually Redim'd (with Preserve) and created my own index. Hacky af, but it works.

    Dim wsArray, wsArrayindex
    wsArrayindex = 0
    ReDim wsArray(1)
    
    For wsi = 1 To Worksheets.Count
        If IsNumeric(Left(Worksheets(wsi).Name, 2)) = True Then
            ReDim Preserve wsArray(wsArrayindex + 1)
            wsArray(wsArrayindex) = Worksheets(wsi).Name
            wsArrayindex = wsArrayindex + 1
        End If
    Next

 

[johnnyL]

Redim outside of loop:

    Dim wsArrayindex    As Long
    Dim wsArray         As Variant
'
    ReDim wsArray(1 To Worksheets.Count)
'
    For Each Worksheet In Worksheets
        If IsNumeric(Left(Worksheet.Name, 2)) = True Then
            wsArrayindex = wsArrayindex + 1
            wsArray(wsArrayindex) = Worksheet.Name
        End If
    Next
'
    ReDim Preserve wsArray(1 To wsArrayindex)