0

I have a VBA macro, where I want to write out an array to an Excel sheet. I'm getting an "Out of memory" runtime error on some machines. I can run it easily on my development PC, but my client has issues with it.

Here I define my Values array: 

Dim Values()
Dim idx As Long
idx = 0

Then I have a for cycle where I dynamically redim the array, and add my values to it: 

for cycle...
    ReDim Preserve Values(16, 0 To idx)
    Values(0, idx) = "some text"
    Values(1, idx) = "some other text"
    ....
    Values(15, idx) = "last values for this row"
    idx = idx + 1
next

Then here is where my code fails:

With ws
    .Range(.Cells(1, 1), .Cells(1+ idx - 1, 16)).value = TransP(Values)
End With

Here's the TransP transposing function:

Public Function TransP(var As Variant) As Variant
    Dim outP() As Variant, i As Long, j As Long
    ReDim outP(LBound(var, 2) To UBound(var, 2), LBound(var, 1) To UBound(var, 1))
    For i = LBound(outP) To UBound(outP)
      For j = LBound(var) To UBound(var)
        outP(i, j) = var(j, i)
      Next
    Next
    TransP = outP
End Function

As I said, I can run the macro, and get something like 108770 rows. The same 108770 rows don't work on my clients PC.

I expect that the TransP function gives up on his PC, so should I split up the array into multiple smaller chunks, and write them 1 by 1? Or my data model is not good?

Improve this question
5
  • 1
    Just checking: your client doesn't use MS Excel 2003? Commented Mar 1, 2019 at 9:17
  • Note, they use Excel 2013 and up. Commented Mar 1, 2019 at 9:43
  • What is idxUA? Commented Mar 1, 2019 at 10:02
  • @Tom sorry for that, I actually made some small corrections to the code when I posted it, and I renamed idxUA to idx here on stackoverflow. I didn't notice that I didn't change it in all places. I corrected the code example now. Commented Mar 1, 2019 at 10:36
  • @Laureant That's fine - I was just wondering whether idxUA could be 0 which could cause an error. I'd also try and avoid using Values as a variable name - it is very close to actual syntax and could lead to erroneous mistype errors. Commented Mar 1, 2019 at 10:41

4 Answers 4

Reset to default
2

Your method of appending elements to Values is inefficient because every time an element is added a new array is created and the values of the existing one copied to it. During this time twice the memory is in use, and if a large array is copied in this way multiple times in quick succession only heaven can know what demands are put on the RAM management.

The better way is to dimension the array larger than will be required (once), count the number of elements written to it and use Redim Preserve to reduce its size (once) when you are done.

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

9 Comments

The problem with that is that in the beginning I have no idea how big the array will be.
Surely you will be able to estimate 100,000 or 1 million. If even that isn't possible consider expanding the array only after the million has been reached, by another million, etc. Compare this effort with that of copying a million elements a million times. A blank element takes up very little memory.
Just ReDim the array to the number of rows your have in your For loop and use a counter for the array position. Then after the For loop ReDim Preserve the array to the value of your counter
@Tom there's a complex object hierarchy, so there's not just ONE for loop. There are multiple ones. I just wanted to get the code examples as basic as possible. That's why I can't specify the exact (or even an estimate) number of how many rows I'll have.
@Laureant - Well then you know your loop criteria. Add the max of each together before starting. In terms of maximum size of an array take a look at this answer link
|
2

You could also create a loop to write your output array row by row, it will take more time but you will most likely don't get out of memory error.

In the past when I've got out of memory issues with an arrays I just tried to perform actions using regular excel commands, in this case you could just copy range and than paste transposed values:

.PasteSpecial Paste:=xlPasteValues, Transpose:=True
Improve this answer

Comments

0

I suspect this might be due to you not specifying the dimension in your TransP function. You are also defining your loops with both arrays. You've sized them identically (although with the dimensions switched) - Just use the same one to define your For loop

Public Function TransP(var As Variant) As Variant
    Dim outP() As Variant, i As Long, j As Long
    ReDim outP(LBound(var, 2) To UBound(var, 2), LBound(var, 1) To UBound(var, 1))
    For i = LBound(outP, 1) To UBound(outP, 1)
      For j = LBound(outP, 2) To UBound(outP, 2)
        outP(i, j) = var(j, i)
      Next
    Next
    TransP = outP
End Function
Improve this answer

Comments

0

I don't think it's the TransP function, since that is already handling everything in a loop. I experienced the same sort of issue and there this error occured when I tried to transfer a large multidimensional array to a range.

My solution was to create a loop and do about a 1000 rows each time, but that depends on the clients pc I guess with how many rows you can do.

To take into account that var will not end on a certain step size, you could do a while loop:

i = 0
with ws
Do while i < Ubound(var)
   max = application.worksheetfunction.max(1000,Ubound(var) - i) 'At some point, you could have less than 1000 left
  .range(.cells(i+1,1), .cells(i+max)) = TransP(var,i,max)
  i = i + 1000
Loop

In TransP you now use the lower- and upperbound of var, but if you add two variables from the loop, you can use them to only take a piece of the array.

Improve this answer

4 Comments

This seems like a good candidate...how would I split the large array into smaller ones, without going through the elements and copying them one-by-one?
I changed the answer to include a way of doing it, because it was too messy in a comment.
How would I rewrite the TransP function to accept the additional two parameters?
You create the outP array based on the lowerbound and upperbound of var. You could instead use the values from i and max from the main function.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.