Using VBA to avoid repeat complex calculations

[GrahamPears]

Quite a long question, but must be commonplace?
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I am pretty experienced with Excel, but new to VBA (pored through lots of books, but not much hands-on yet). I’m using Excel 2003. I want to produce a quite complex budgeting and cashflow model. I have a draft in Excel, but I always knew I would have to tear it down and rebuild it to work with VBA, because using Excel alone produces a model which becomes big, inflexible and very cumbersome.
<o></o>
I (think) I want to be able to get VBA to select a value from a range on a spreadsheet, feed that into an Excel calculation, take the calculation result, write to a schedule on Excel, then cycle through the whole range of hundreds/thousands of cells, repeating the process for each input value in turn, to generate a large output schedule containing calculated values.
<o></o>
Here is an illustration of my problem. My model allows 20 stock lines, and the stock values for each line can be computed in various ways dependent upon user-entered and model-calculated variables. The result is that this stock calculation takes up 90 rows on my spreadsheet, and since I want to perform the calculation for each of 24 months it takes up 24 columns. That is fine. But the whole calculation has to be repeated for each stock line. That means I have twenty blocks of calculations, each block being 90 rows deep and 24 columns wide. That is a lot of calculations, but worse it is inflexible – suppose I want to double the number of stock items, and extend the model to say 48 months, then I have a huge amount of formula copying to do; and to automate that is almost impossible because of the referencing in complex formulae.
<o></o>
This is just one corner of the complexity of my model, but it illustrates the problem I want to solve. I ought to be able to have just one instance of the calculation in Excel and re-use it, rather than having 20 x 24 = 480 repetitions of the same calculation (or worse, 40 x 48 = 1,920 repetitions!!).
<o></o>
So here is what I want to do.
<o></o>
I will have a data input area, say 20 rows by 24 columns. I want to produce VBA code to take the input value from the first cell of that input range, feed it into the complex stock calculation in my spreadsheet, allow the spreadsheet to calculate, then take the result of that calculation and write it to an output area (also a 20 x 24 grid); then the macro should move to the next cell of the input grid, feed that value into the Excel calculation, write the result to the next cell in the output grid, and so on.
<o></o>
In this way the macro loops through the same calculation 480 times and writes the result of each calculation to a unique cell in an output grid.
<o></o>
When the macro has run its course I will have a 480-cell grid of input values, and a corresponding 480-cell grid of calculated values, but I will only have to set out the calculation once in the spreadsheet (and re-use it) rather than setting it out 480 times.
<o></o>
If the stock calculation were simple, and if I were proficient at VBA, I suspect the best way to proceed would be to create the calculation itself within the VBA code. – but that is out of the question, because the calculation is full of nested conditions (IF), mixed references (part absolute, part relative) and other Excel functions.
<o></o>
I have some ideas about how to proceed, and I’m pretty confident that I will get there in the end – even if it takes me a few weeks to figure it out. But I suppose what I am looking for now is the reassurance that I am setting off on the right track.
<o></o>
Loads of books tell you how to manipulate data and objects, but I can find very little reference to this type of problem – where variables are selected from a range by VBA, passed into a complex calculation on Excel, results passed back to VBA which finally writes the result to Excel, and then repeats.
<o></o>
As far as I see it there are two approaches.
<o></o>
One is to loop through the ranges on Excel - read, compute, write, read, compute, write etc. I think this could be unacceptably slow, especially if there were several thousand repetitions. (Excel recalculates almost instantly, but I suspect code could take a couple of minutes or more).
<o></o>
The other approach is similar, but would involve reading the input range into an array, performing the calculations in Excel and writing to a second array, and then finally, when all calculations had been completed writing from the second array to a results range on the spreadsheet.
<o></o>
However, I have no idea how to code for: “write from input range to array – read from array – Excel calculation – write to array – read next from array – Excel calculation – write to array – [ repeat hundreds of times ] – then finally write from output array to results range.
<o></o>
Can you put me on the right track?
<o></o>
Surely the kind of problem I have must be commonplace? Surely sophisticated spreadsheets don’t have the same complex calculation laid out thousands of times on the spreadsheet, with all the attendant problems of absolute and relative referencing when replicating the various formulae used in the calculations (for example, when additional columns or rows of input data are required)?
<o></o>
There must be a simpler way than just creating thousands of versions of the same calculation??
<o></o>
I am not asking for a complete solution – but just some guidelines.
<o></o>
Thanks so much.

 

[BrianB]

There are dozens of possibilities depending on what you want to do.
Your basic question seems to be about how to get values from a worksheet and process them.

Custom Functions are OK, but have limitations, so I show a way of using subroutines. Normally a small amount of code like this would not not need it, but it is often convenient to be able to control which routines run during testing, as well as keeping the code for each routine clearly separated. Please bear in mind that although I put 'answers' into the sheet the latest variable values set are still available globally until changed by the code.

I have also started declaring explicit worksheet variables in case more than one will be required.

Hope this helps.

'========================================================================================
'- BASIC EXCEL REPEAT CALCULATION EXAMPLE
'========================================================================================
'- Declarations here available to all subroutines
Dim ws As Worksheet
Dim FromRow As Long
Dim MyValue As Double
Dim Answer1 As Double
Dim Answer2 As Double
'========================================================================================
'- MAIN ROUTINE
'========================================================================================
Sub test()
    Set ws = Worksheets("Sheet1")
    '------------------------------------------------------------------------------------
    '- loop cells
    For FromRow = 1 To 10
        MyValue = ws.Cells(FromRow, 1).Value
        CALCULATE1
        CALCULATE2
        ' CALCULATE3        ' not used yet
        '-------------------------------------------------------------------------------
        '- DATA TO WORKSHEET
        ws.Cells(FromRow, 2).Value = Answer1
        ws.Cells(FromRow, 3).Value = Answer2
    Next
    '------------------------------------------------------------------------------------
End Sub
'========================================================================================
'========================================================================================
'- CALCULATION 1  SUBROUTINE CALLED FROM ABOVE
'========================================================================================
Private Sub CALCULATE1()
    '------------------------------------------------------------------------------------
    '- this code skips the calculation while testing
    Answer1 = 40
    Exit Sub
    '----------------------------------------------------------------------------------
    '- normal code
    Answer1 = MyValue * 2
End Sub
'----------------------------------------------------------------------------------------
'========================================================================================
'- CALCULATION 2 SUBROUTINE CALLED FROM ABOVE
'========================================================================================
Private Sub CALCULATE2()
    Answer2 = Answer1 + MyValue / 4
End Sub
'----------------------------------------------------------------------------------------

 

[GrahamPears]

Brian - Thanks very much for all that. As I said, I did not expect anyone to provide code - but I'm glad you did. As is so often the case, it seems that an appropriate solution evolves as you hammer away at the problem. I think that what you have shown me - which you maybe didn't expect - is that I'm wrong to assume I can't do the calculations in VBA. What you have demonstrated is that it is not difficult to break any calculation into steps in VBA just as you would in Excel itself - and that puts a new perspective on things.

I have since found a way of looping through the cells, using Excel to calculate, and writing the results back to Excel - but as expected that is slow, but much slower than I ever expected - I really thought my PC had frozen! This demonstrates that my problem definitley is speed.

So at least one of the paths to pursue is to read the input range/s into one or more arrays, do the calculations in VBA, write the results to an array, and write that back to the output range.

Another possibility is to only recalculate those cells which have been changed, e.g. in a data entry process.

A challenge I now foresee (rightly or mistakenly - my inexperience is showing) is how to control the flow of dependent calculations when I take some of the calculating away from Excel and put it into VBA - especially where the results of one calculation flow into another, then into another and so on through a complex model. I think some careful thought / design is needed here.

Still, I'm sure perseverence and trial and error will pay off.

I notice that you have written nearly 8,000 posts! That is quite extraordinary. You must have helped an awful lot of people in your time.

Thanks again.

Graham Pears

 

[BrianB]

Glad to help. Some peope do crossword puzzles.