VBA loop efficiency

Question

I've used the answers found in the site a TON of times, but this is my first post.

Is it more efficient to perform a for loop inside a function or sub or to just send the incremented value of the loop into the function or sub as an argument?

I am importing some text data files into Excel, parsing out the various fields, and then splitting some of the fields into individual characters. One file I am using is a list of doctors. I have name, address, phone, DEA number, NPI, etc.

When checking the DEA number, I have a sub that receives the line number to be checked that splits the DEA into its individual digits, perform checking on these digits one at a time and then modify another field with the status of that DEA. This status cell will be colored red if it contains anything but the word "GOOD". Also, I am coloring the individual digit that is bad, if applicable.

This one sub is doing a lot and I could probably break it up a little, but there aren't any other places in the doctor file that I am performing this exact step, so I figured I should keep it like it is.

Anyways, the real question is whether I should send the line number into the sub or should I just call the sub and have the sub calculate the number of lines and do the checking. In the first case, I will call the sub a number of times equal to the number of lines in the doctor file. In the second, I will call the sub once and the sub contains the for loop for each line. Which is usually more efficient.

Apologies if I seem redundant. I train some complex software and that sort of thing leaks into other areas of life sometimes.

EDIT: I tried to add this into a comment but have insufficient experience posting here. Apologies if I violate some rule for this...

Here is the code I use currently to call the sub:

'Use the Doctor Last Name as the number of rows count
Dim numRows As Integer
numRows = Application.CountA(Sheets("DoctorDEA").Range("m:m"))

'lineCtr is the Line Counter used to iterate the FOR loops
Dim lineCtr As Integer
    lineCtr = 1

'Call DEACHecking and DisplayIssues Subs
For lineCtr = 1 To numRows - 1
    DEAChecking (lineCtr)
    DisplayIssues (lineCtr)
Next lineCtr

My questions is this: Would it be better to just call DEAChecking with no arguments and just have DEAChecking calculate the line numbers and then use the FOR loop or to leave it as is?

If you repeated call a sub with new criteria, the sub has to set up the environment (e.g. With Sheets("mySheet") ... End With or Set ws = Sheets("mySheet"), last_row = ..., etc) to process the procedure. If the loop is within the sub, the environment is only set once. — user4039065
– user4039065, Commented Jan 14, 2015 at 18:35
Performance is a non-issue, reading the file from the disk or the network takes much longer. Focus on writing logical and maintainable code. — Hans Passant
– Hans Passant, Commented Jan 14, 2015 at 19:46
Hans, I guess this is the essence of my question. I know it will work either way. I just don't know which preferred. — Stanley Law
– Stanley Law, Commented Jan 14, 2015 at 19:55

John Alexiou · Accepted Answer · 2015-01-14 18:15:37Z

1

This question is too broad right now to be answered effectively. So am just offering a small insight that might help you structure your program.

Typically the most efficient code is the one where all the variables are as local as possible. If inside a loop you are using globals, or calling other functions it is going to be much worse than performing all the calculation with local variables.

answered Jan 14, 2015 at 18:15

John Alexiou

29.4k12 gold badges83 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

peege Over a year ago

Even if in the same module? Outside the scope of the sub?

John Alexiou Over a year ago

It all has to do with the L1/L2 cache of the processor. The registers are × times faster than nearby memory access so if your program keeps things within the scope of the loop it is going to be much faster since the compiler is going to translate this into registry access commands vs. pointer memory access.

Community · Accepted Answer · 2017-05-23 11:50:56Z

0

If you want to test each, and time them, you can use a timer. If you have a major gap, you will be able to catch it. If not, you will have your answer with no significant difference as far as processing time.

You can either use this and call your sub from TimerTest, or simply Call TimerStart at the beginning of your code and TimerStop at the end.
Run some code with the timer
Log the result
Repeat and compare
HH:MM:SS:00 format

Timer Code:

Public strStartTime As String
Public strEndTime As String
Public startTime As Date
Public endTime As Date


Sub timeTest()

    Call TimerStart
        'INSERT CALL TO YOUR SUB HERE
    Call TimerStop   

End Sub

Sub TimerStart()

    startTime = Now

End Sub

Sub TimerStop()

    endTime = Now

'Waited until the timer stopped to perform any additional code, such as formatting the time

    Dim TotalTime As String

    strStartTime = Format(startTime, "hh:mm:ss:" & Right(Format(Timer, "#0.00"), 2))  
    strEndTime = Format(endTime, "hh:mm:ss:" & Right(Format(Timer, "#0.00"), 2))
    TotalTime = Format(endTime - startTime, "hh:mm:ss:" & Right(Format(Timer, "#0.00"), 2))

    MsgBox ("      Start: " & strStartTime & vbNewLine & _
            "        End: " & strEndTime & vbNewLine & _
            "Total Time : " & TotalTime)               
End Sub

Credit: @Nick Dandoulakis for timer formatting in his answer here: Providing this solution to show clock time with accuracy of less than a second.

edited May 23, 2017 at 11:50

CommunityBot

11 silver badge

answered Jan 14, 2015 at 19:39

peege

2,4771 gold badge13 silver badges24 bronze badges

2 Comments

Stanley Law Over a year ago

Interesting. I hadn't even thought about attempting to run some sort of timer code. I will look at this. Thank you.

peege Over a year ago

Thanks for asking the question. I've been wanting to make a timer for this for a while, but never really got around to it until this. In your particular example, it might not be noticeable, but this can be applied to any procedures, including comparing WorksheetFunctions vs VBA functions, or anything. It would probably be most useful on large DataSets.

Collectives™ on Stack Overflow

VBA loop efficiency

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related