Speed up looping?

[alee001]

I tried to use method A instead of method B to speed up the loop process in function, but in the massive fx calculation did not significantly accelerate, do you know why?

Dim lot_now As Long

Method A:
   Dim lot_chk As Long
   lot_chk = 0
rtn_chk:
   If lot_now > 10 Then
     step = 10
   Else
     step = 2
   End If
   If amt - lot_now * rate < min_cost And lot_now > 0 Then
     If lot_now = lot_chk Then
       lot_now = lot_now - 1
       GoTo fx_exit  'exit loop
     End If
     lot_now = lot_now - step
     GoTo rtn_chk
   Else
     lot_now = lot_now + 1
     lot_chk = lot_now
     GoTo rtn_chk
   End If

Method B:
Do While amt - lot_now * rate < min_cost And lot_now > 0
   lot_now = lot_now - 1
Loop

 

[dougmarkham]

I tried to use method A instead of method B to speed up the loop process in function, but in the massive fx calculation did not significantly accelerate, do you know why?

Dim lot_now As Long

Method A:
   Dim lot_chk As Long
   lot_chk = 0
rtn_chk:
   If lot_now > 10 Then
     step = 10
   Else
     step = 2
   End If
   If amt - lot_now * rate < min_cost And lot_now > 0 Then
     If lot_now = lot_chk Then
       lot_now = lot_now - 1
       GoTo fx_exit  'exit loop
     End If
     lot_now = lot_now - step
     GoTo rtn_chk
   Else
     lot_now = lot_now + 1
     lot_chk = lot_now
     GoTo rtn_chk
   End If

Method B:
Do While amt - lot_now * rate < min_cost And lot_now > 0
   lot_now = lot_now - 1
Loop

Hi alee001

I am not familiar with your function per say; however, I have some general info about this which might help.

In general, there are two ways to speed any macro up that inputs formulas into cells or does calculations row by row:
1) Add the below code at the start and end of macro sub's / functions:

Sub xyz()

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlManual

Dim x as 'etc

'Enter code here

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlAutomatic

End sub

2) a - If you use a Sub to insert the formula and/or to call the function, stipulate the precise range that the formula will be inserted into within the Sub. Some people will add 'IF statements' to a formula they insert into cells e.g., =IF(A2="","",Index(..... etc. This would mean that you'd see nothing in the formula column if another column had no data; however, if you didn't stipulate the range for inserting the formula (just the column), you could get it inserted in all cells in a column, thus taking ages. I always create a dynamic table when inserting formulas, and make sure that the dynamic table is adjusted in size to match the CurrentRegion, thus getting around that issue.

b - If you are using the function in a limited number of cells within an excel formula, the only ideas I have are one's that speed up excel:
e.g., Set the Excel.exe process to high priority; in Performance management, set visual effects to 'best performance'.

Usually, if formulas are used on large data-sets (hundreds of thousands of lines) and those formulas call VBA functions, then it's going to take a long time regardless.
More advanced VBA programmers will no doubt have better suggestions.

Kind regards,

Doug.

 

[alee001]

Hi alee001

I am not familiar with your function per say; however, I have some general info about this which might help.

In general, there are two ways to speed any macro up that inputs formulas into cells or does calculations row by row:
1) Add the below code at the start and end of macro sub's / functions:

Sub xyz()

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlManual

Dim x as 'etc

'Enter code here

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlAutomatic

End sub

2) a - If you use a Sub to insert the formula and/or to call the function, stipulate the precise range that the formula will be inserted into within the Sub. Some people will add 'IF statements' to a formula they insert into cells e.g., =IF(A2="","",Index(..... etc. This would mean that you'd see nothing in the formula column if another column had no data; however, if you didn't stipulate the range for inserting the formula (just the column), you could get it inserted in all cells in a column, thus taking ages. I always create a dynamic table when inserting formulas, and make sure that the dynamic table is adjusted in size to match the CurrentRegion, thus getting around that issue.

b - If you are using the function in a limited number of cells within an excel formula, the only ideas I have are one's that speed up excel:
e.g., Set the Excel.exe process to high priority; in Performance management, set visual effects to 'best performance'.

Usually, if formulas are used on large data-sets (hundreds of thousands of lines) and those formulas call VBA functions, then it's going to take a long time regardless.
More advanced VBA programmers will no doubt have better suggestions.

Kind regards,

Doug.

Thanks for your advice. I had used to 3 application on my code. I only want to improve looping search on defined function using adjust to step (>30K cell fx formula). I don't know whether method A is better than method B or not? Have you any idea to improve looping?(e.g. bi-section method)

 

[jkpieterse]

If you want proper advice I advise you to post the full function and explain what it is supposed to do and how the function is called (from a worksheet cell?) and also show the function call.

 

[alee001]

This is original code of method B, lot_now be calculated form other code then input on following loop.

Do While amt - c0 - c1 - c2 _
   - Application.max(amt * Range("bkg"), Range("bkg_min")) _
   - amt * (Range("lvy") + Range("stp") + Range("tra")) _
   - lot_now * Range("qty") * prt - Application.max(lot_now * Range("qty") * prt * Range("bkg"), Range("bkg_min")) _
   - lot_now * Range("qty") * prt * (Range("lv") + Range("stp") + Range("tra")) _
   - Application.Min(Application.max(lot_now * Range("qty") * prt * Range("stm"), Range("stm_min")), Range("stm_max")) _< Range("cty") * 12 And lot_now > 0
    lot_now = lot_now - 1
Loop

 

[jkpieterse]

Given that you are reading the same ranges over and over again you can speed this up significantly by assigning their values to variables outside of the loop.

 

[jkpieterse]

Same goes for any calculated values which remain the same

 

[alee001]

Same goes for any calculated values which remain the same

Even if I replace range (??) to variable but not significantly accelerated

 

[jkpieterse]

Please show your optimized version

 

[alee001]

I found that when the lot_now got a target closer to the ceiling (search loop range<30%), time difference between the method A and B is nearly 0 or no different, but if the target close to the floor (search range>70%), which larger time difference between two methods, especially in case on the large lot_now value with the target close to the lower limit that time difference is more obvious.