Aprroach and speeding up code

[kuda25]

I am trying to develop a model that calculates the commission under various scenarios(assumptions). I have developed something that using loops (quite a few loops) to generate all the sceanrios. However I am finding this to be quite slow at the moment and the time taken may be restrictive to it's use. Current model may take days to complete the task. I would be very grateful for any suggestion on how I could speed up the code or if there is a better way to do this using vba. I have attached the spreadsheet for you to review.

I also concerned the size of the output file and restrictions that excel may have on the number of scenarios I can run. On a full run hundreds if thousands of scenarios could be produced. Is there a way around this? any suggestions

 

[SkipVought]

Hi,

Many of us are restricted by company security.

If you want someone to review your entire application, you ought to spend the money to hire a reputable consultant.

However, if you post your code with specific questions, you will get lot of good tips.

In general, assign ScreenUpdating FALSE, do NOT use the SELECT or ACTIVATE methods to manipulate your objects, use the With...End With construct to minimize object node resolution calculations, for a start.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

You have several embedded loops, if each of them contains more than two steps the whole time will be in practice infinite.
I'd analyse and try:
- are the agents in dependent? If so, calculate them separately and mix results in faster loops.
- avoid VLOOKUPs in the sheet. They significantly slow down recalculation.
- if possible, calculate everything with vba. One can pick and set values of a range in one go, using variant arrays (to pick: varArray=rngArray, to set: rngArray=varArray).
- if there is a need to interact with the sheet, avoid activating and selecting. If possible, calculate necessary part of the workbook.

combo

 

[SkipVought]

Also, turn automatic calculate OFF. Calculate individual ranges as required.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

if possible, calculate everything with vba

Not sure I'd agree with that - I agree that you shouldn't leave calcs on the sheet but I have read in several places that the formuale in excel are optimised better than trying to do the same through code - my normal practice with large groups of formulae is to use VBA to enter the formaule into cells, do the calc then copy / paste special as values once done

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MajP]

xlbo,
You are completely correct. For the majority of excel functions there is absolutely no way you can calculate in vb more efficiently than the native functions.

 

[combo]

I have not analysed the logic of linked worksheet detaily, but either it seems to be too flexible or contains a lot of inefficient references (esp. LOOKUPS). It's the main reason that slows down calculations. I've seen a worksheet with thousands of lookups, every recalculation lasted minutes.
Here's a link to some useful tips to speed up excel sheet:

http://www.decisionmodels.com/fxluserg.htm

Concerning excel functions, yes, they are faster than vba. To get benefits of it, there should be calculation blocks with clear input and output, that are recalculated on demand. The output table could be filled with vba and passed whole to the sheet, it's faster than filling cell by cell.

combo

 

[electricpete]

Let's say the application resides primarily in vba (excel is just input/output at the end which we presume contributes a small portion of the total computing time).

Then there is no doubt that using vba functions is almost always faster than trying to borrow an excel function.

Now if your calculation resides primarily in worksheet, it may be faster to use the excel function rather than the excel function. I don't believe that to be the case for the original post.

 

[electricpete]

I should back up and say I haven't even looked at the sheet. So maybe I am wrong in the assumption of the first statement of my previous post.

 

[MajP]

Can you give a possible example?

Then there is no doubt that using vba functions is almost always faster than trying to borrow an excel function.
{/quote]
Because I can list hundreds of examples where there is no way you can even come close. Try coding any sort algorithm, and guarantee you cannot get close to the speed of excel in sorting an array or range of data. Try to code any statistical function and guarantee you cannot get close.

 

[electricpete]

Majp - 3 examples come to mind:
1 – Atan2
2 – fft
3 – mmult
I have timed all of them and seen big advantage for vba udf over the excel function. I haven't tried any sorting... of course I am talking about operations on vba variables (including arrays) as you know from the first sentence of the post that you quoted... I didn't know there was an excel sort function that will work on vba variables or arrays. Could be... I just haven't done much sorting. Most of my stuff is number-crunching.

 

[electricpete]

Whoops, I dug up the mmult comparison and I was wrong. Mmult is indeed faster than udf function to multiply arrays.
Results were reported in my 25 May 10 13:21 post here:

http://www.eng-tips.com/viewthread.cfm?qid=272153&page=1

My apologies. I'll see if I can dig up my other examples when I get a chance.

 

[electricpete]

Here is a comparision of homemade atan2 udf vs excel worksheet function atan2

http://newtonexcelbach.wordpress.com/2008/02/23/worksheetfunction-vs-udf/

The vba udf was 3-10 times faster

 

[electricpete]

For fft, look at the files referenced in my post 24 Aug 10 22:44 here:

http://www.eng-tips.com/viewthread.cfm?qid=279742&page=1

The difference between these and the analysis tookpak FFT is easily noticeable... don't need a timer to see it. I would guess factor of 10.

 

[HughLerwill]

It strikes me that a couple of different things may be being compared above.

1. the speed at which Excel updates the value of a cell containing a formula which can then be read by vba code; and

2. the speed of vba code using vba functions vs calling very similar functions out of Excel using Application.WorksheetFunction calls.

 

[electricpete]

I'm not sure to whom you are referring.

If to me:

The comparison of Atan2 timing takes place completely within vba and the udf was faster.

The comparision between mmult was done completely within vba although in that case the excel function was faster.

I didn't do formal timing comparison fft but I am fairly certain of the results.