Speed up looping?

alee001

Board Regular
Joined
Sep 9, 2014
Messages
76
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
 

Some videos you may like

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
Joined
Jul 19, 2016
Messages
165
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

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:

VBA Code:
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

Board Regular
Joined
Sep 9, 2014
Messages
76
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:

VBA Code:
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

Well-known Member
Joined
Dec 3, 2007
Messages
1,002
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.
 

Forum statistics

Threads
1,089,220
Messages
5,406,927
Members
403,113
Latest member
ms_excel_recal_or_die

This Week's Hot Topics

Top