VBA iterative calculation

[acecard]

Hi all,

It's my first post here; I have used solutions from this forum and others for various Excel issues over the years but this one I cannot seem to crack or find the answer to! Please help!!

My general Excel skills are pretty good, but I have been unable to solve this issue using formulas. I think it calls for VBA, which I am very poor at, so hoping for some help! I am trying to make my spreadsheet constantly monitor and compare two values, one in A1 and one in A2; A1 is fixed, but A2 is generated by some iterative calculations based on other equations which are continually changing. Things get quite complex because the value of A2 is a function of A4, and A4 is the value that I wish to be iterated as per below:

I would like the spreadsheet to iteratively reduce the value in A4, starting from a reference value held in A3, by increments of 0.01 in order to keep A2 < A1, always looking for the HIGHEST value that will keep A2 < A1; A4 must remain > 0, so essentially the required value will be somewhere between the reference value in A3 and 0.01. If A2 cannot be made < A1 by the time the value reaches 0.01, then it will simply start the sequence from the value in A3 again and keep searching. This should carry on over and over for as long as the spreadsheet is open.

I hope this makes sense! I really don't know where to start with this!

Many thanks
Ace

 

[Eric W]

Welcome to the forum.

You're probably better off with a formula. Formulas will calculate automatically when the rest of the sheet changes. Adding VBA adds another level of complexity. It depends on your formulas in A1, A2, A3, and A4. If you can algebraically invert your formulas, that's by far the best solution. But without seeing your formulas, I can't tell you if that's viable.

If it's a complicated formula, then you can use Goal Seek or Solver, but these don't update as the sheet does, you'd need to invoke them.

If you want it to auto-calculate, you could have an OnCalculate event handler, but that adds overhead and can be complicated, and we'd need to know the formulas as well to look at it.

A final option could be a UDF, with the Application.Volatile option. This could also iteratively figure out your result.

But to even evaluate these options, you'll need to provide some samples of your data, formulas, and expected outputs.

 

[acecard]

Hi,

Thanks for the response! Yes, I understand the added complexity that is likely to result from the VBA addition. I don't think re-arranging the formulas works as they are iterative and so things start to get very complicated!

I stayed up pretty much all night and sort of came up with a work around: I essentially copied the cell boxes into a mini version of my spreadsheet to form a sort of look up table with different values of A4. Thus, each time a value in my spreadsheet changes, it does the same calc. for every iteration that I require. I limited A3 to 1 so it basically replicates my spreadsheet 100 times. For each instance, it then compares A1 with what A2 would be with the respective A4 variable, makes a comparison with some simple Excel INDEX and MATCH formulas, and then returns the closest, highest value for A4. Then using an IF function, I can implement that into the other iterative calcs!!! Very long-winded but it works. My only issue is that in doing so, it is having to carry out the iterative calculations SO MANY times; 100 times more than before, so it takes a few seconds to work everything out. I was hoping this wouldn't happen. I have tested this out on my 4 core laptop as well as my 8 core desktop and its slow on both machines!!! Multi-threading is enabled in Excel. Any ideas on how I might be able to speed this up? Even when using only 300 iterations for my calcs, it is still slow!

Thanks

 

[Eric W]

First, well done on finding a solution.

Next, with what I have, I can't see a way to speed it up. You're just doing a LOT of calculations. Two potential options. First, analyze your formulas and model. You have a lot of iterative formulas, maybe there's a way to simplify. I really can't say. Look for patterns. Redundant elements. Second, VBA can be faster if it takes all the data off the worksheet and performs the calculations in memory. So if you have an algorithm, you could do your 100 iterations faster. But that requires knowledge of VBA and your model.

So good luck, I'm not sure where you should go from here.