# Speed up looping?

#### alee001

##### Board Regular
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?
VBA Code:
``````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``````

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### dougmarkham

##### Board Regular
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?
VBA Code:
``````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

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:

VBA Code:
``````Sub xyz()

Application.ScreenUpdating = False
Application.Calculation = xlManual

Dim x as 'etc

'Enter code here

Application.ScreenUpdating = 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

##### Board Regular
Hi alee001

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:

VBA Code:
``````Sub xyz()

Application.ScreenUpdating = False
Application.Calculation = xlManual

Dim x as 'etc

'Enter code here

Application.ScreenUpdating = 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

##### Well-known Member
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.