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

srp

New Member
Joined
Jan 14, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited by a moderator:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
How about:

VBA Code:
    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
 
Upvote 0
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:

VBA Code:
wsArray.Add Worksheets(wsi).Name

That causes the out of memory error.
 
Upvote 0
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.
 
Upvote 0
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.

VBA Code:
    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
 
Upvote 0
Solution
Redim outside of loop: ;)

VBA Code:
    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)
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top