Excel VBA repeated change of a control cell hogs memory

2
votes

I have searched on the web quite extensively, but have not managed to find any similar experiences. Any ideas?

I have a simple subroutine in VBA that changes a control cell. This control cell is used by the formulae in another sheet. As the code changes the control values, Excel uses increasingly more and more ram to the point that excel comes to a halt.

Basically I have a sheet setup that has 3000 rows and 330 columns. In each cell of the sheet the same formula below is populated:

=sum(sheet1!F8:INDEX(sheet1!F8:F$3000,Control!$D$1))

So in cells A1 you would have the formula above and in cell say B1 you would have:

=sum(sheet1!F**9**:INDEX(sheet1!F9:F$3000,Control!$D$1))

The control value that the code changes is Control!$D$1 and therefore changing control say from 2 to 4 will result in calculating running Sums from 2 to 4 consecutive rows in sheet1. Note the code starts by setting a high value in the control cell (200) and works its way down to 2. The memory usage increase is therefore really baffling me.

The code I have is:

For i = 200 To 1 Step -1
    Application.Calculation = xlCalculationManual
    ClearClipboard 'sets cutcopymode to false
    Range("Control!d1").Value = i
    Application.Calculation = xlCalculationAutomatic
Next i

Finally I have tried the following alternatives and none of them suited me:

  • Doing all calculations in VBA arrays: Since VBA is not multithreaded, this is painfully slow (the point is to take advantage of my cpu cores using formulae in excel worksheets)
  • Setting screenupdating = false, enablevents = false, cutcopymode = false have no significant improvements
  • Converting formulae to values and reentering formulae by VBA: It again slows down the calculations
  • Manually reducing the number of processors: Defeats the purpose of my method as I need fast calculations

Is this an excel bug?

1
vbaexcelmemory
I've never had a reason to update a formula precedent repeatedly without halting, and given the size of your calculation matrix together with the referential formulas being used, I'm not really surprised at the impact. perhaps you could outline what you're trying to achieve and we could think about another less intensive approach to try? – Tim
Thanks Tim. Firstly, it is surprising that the memory used by excel actually increases when progressively referencing fewer cells from 200 down to 2(from the index function); this should require fewer resources, not more. Initially when the control cell is set to 200, it uses around 1GB of ram, but this increases to 7GB as the control cell value is reduced (same if increased as well). In answer to your question about the objective, I need these sums to go into a sqlite database which is used for analysis. All these sums are needed for averaging, and sqlite does not do running sums or averages. – Ali
hrm, your first post indicates 300x arrays of 3000 cells, which are returned into each function x200. I'm not 100% on the theory behind it, but your experiment in reverse seems to support the hunch. what happens if you experiment with much smaller arrays? – Tim
Each cell holds one value but refers to a range, so we are dealing with 3000*300 formulae. Quite large indeed. I tried a two tests with one control cell and one formula cell only and changed the control cell value repeatedly via VBA. The first test is simply a sum(A1:A10000) and the second was like sum(A1:index(A1:Ai)). Using the index function, no matter whether variable i starts being large and reduced progressively, or the other way around, the memory consumed by excel keeps growing and stays that way, even if the formula is deleted! I'm guessing a bug with Index... – Ali
the variable is not important, but the array size is. if you can find a way to reduce the number of matrices being processed I think you'll see the overhead drop off as much as it builds currently – Tim

1 Answers

0
votes

I have done some more research on your problem. The way your running sum function is built a lot of intermediate ranges have to be created:

=sum(sheet1!F8:INDEX(sheet1!F8:F$3000,Control!$D$1))

First, sheet1!F8:F$3000, second the result of the INDEX() function, third the argument to SUM(). Instead, I propose to only construct one range (the bare minimum) using the OFFSET() function. It lends itself to the task as the control parameter is a scalar and OFFSET() creates a new range from a range and scalars. The running sum then is

=SUM(OFFSET(sheet1!$A$1;ROW()-1;COLUMN()-1;Control!$D$1;1))

where the control value merely sets the size of the range to be summed. In my tests with 2000 x 14 cells and 200 loops there was no increase in memory consumption to be observed. As both methods (INDEX() and OFFSET()) were quite fast for a sheet that size I cannot make any assumptions about calculation time (but see below for hints).

I have added the ROW() and COLUMN() functions to make the formula self-adjusting - the start address for the sum will be the identical address of the cell the formula is in. This way, all formulas on the sheet are exactly identical and do not have to be changed depending on their location.

As to the runtime, I would suggest you leave changing the Calculation Mode out of the loop (i.e. as automatic). I have noticed a huge overhead of several seconds for each change of mode. After changing the control cell all dependent cells have to be calculated once, and setting the mode to manual will not change that.

Lastly, if you try this formula with your original data, test whether leaving out the ClearClipboard command will have any effect. Ultimately it will have to call a Windows function thus leaving the Excel process environment, and I cannot see why it is needed here anyway except for cosmetic purposes.