Optimize VBA Speed with many ElseIf Statements

Question

I'm running a code in VBA which takes way too long to run. It needs to check the value in a cell, determine what the value is and if the value is accepted then increase up to one out of 4 corresponding counters with + 1. The value in the cell can be any of 64 predefined words. They way I made it work comes down to the following.

     Sub Overview

        Application.ScreenUpdating = False 'turns off screen updating to increase macro running speed
        Application.Calculation = xlCalculationManual 'Turns off automatic cell calculation to increase macro running speed

        Dim CountCondition1, SACountCondition1, WBDACountCondition1, StatusCountCondition1 As Long         'Defines counters for condition 1
        Dim CountCondition2, SACountCondition2, WBDACountCondition2, StatusCountCondition2 As Long         'Defines counters for condition 1
        'And so on for all 64 different values

        Dim UnitOverview As Range   'Defines range to use loop
        Dim Dataget As Long         'Defines counter to walk through loop

        ThisWorkbook.Sheets("Datadump").Range("E23").Value = Application.WorksheetFunction.CountA _
        (ThisWorkbook.Sheets("Status").Range("C:C")) - 1 
        'Displays total data amount in file, numbering about 132000 cells in column C

        For Each UnitOverview In ThisWorkbook.Sheets("Status").Range _
        ("A2:A" & ThisWorkbook.Sheets("Datadump").Range("E23").Value + 1) 
        'Create loop to check completion status for entire document. Column A contains the cells which have any of the 64 predefined values, these values are text based

        DataGetCompletion = (DataGetCompletion + 1)

        ThisWorkbook.Sheets("Datadump").Range("E17").Value = DataGetCompletion + 1 
        'Used to move cell reading range for each new loop cycle

        'The following code section counts the number of cells related to each plant
                If UnitOverview.Value = "Condition1" Then         
                   CountCondition1 = CountCondition + 1
                ElseIf UnitOverview.Value = "Condition2" Then
                   CountCondition2 = CountCondition2 + 1
        'This continues for all 64 different variables

        'The following section of code checks SA Terms for each value
        If UnitOverview.Value = "Condition1" And ThisWorkbook.Sheets("Status").Range _
        ("D" & ThisWorkbook.Sheets("Datadump").Range("E17").Value).Value = "Yes" Then
        SACondition1 = SACountCondition1 + 1
        ElseIf UnitOverview.Value = "Condition2" And ThisWorkbook.Sheets("Status").Range
        ("D" & ThisWorkbook.Sheets("Datadump").Range("E17").Value).Value = "Yes" Then
        SACountCondition2 = SACountCondition + 1
        ' Again this continues for all 64 different variables
        ' The same is then being done for the "WBDACOuntCondtion" terms and the "StatusCountCondition" Terms. 

        Next UnitOverview

        'Finally after the loop completes, all the different counters are assigend to cells in order to read them out and do whatever you like with 'em.

        Application.ScreenUpdating = True 'Turns screen updating back on
        Application.Calculation = xlCalculationAutomatic 'Turns automatic cell calculations back on

        End Sub

Running this code over all 132000 cells in column A takes about 15 minutes, which is way to long for my liking. I made it this way because it was the only way I knew how. Any help would be much appreciated. I thought about making a list to check the variables against, but wouldn't how to properly set it up, or if it would be any faster than my current solution.

Iterating over ranges is slower than iterating over VBA arrays. One thing to try is to dump your data to arrays and use these instead. Also, you may want to use a [dictionary object](https://stackoverflow.com/questions/915317/does-vba-have-dictionary-structure) to [avoid checking 64 conditions](https://stackoverflow.com/questions/18311320/excel-vba-using-select-case-but-now-need-an-array/18311783#18311783). — Ioannis, Feb 16 '18 at 10:59

score 2 · Answer 1 · answered Feb 16 '18 at 10:59

2

I would recommend using the Excel COUNTIF function, rather than doing the calculations in the VBA. For example, to count the number of cells in the range A2:A30 that contain "Condition1", add a new cell with the formula:

=COUNTIF(A2:A30,"Condition1")

This value will be calculated when the spreadsheet is populated, so you can just retrieve the value from the cell in the VBA.

answered Feb 16 '18 at 10:59

Tevildo

340
2
11

Thanks, that will certainly decrease part of the workload! – Yellow Feb 16 '18 at 11:10
+1 When it comes to Excel and "how do I make this faster in VBA?" - I love answers that actually solve it mostly in Excel formulae. Nice. At the end of the day: use the tool. It's powerful. – andrew Feb 16 '18 at 19:11

score 1 · Accepted Answer · answered Feb 16 '18 at 11:01

You appear to have 64*4 counters which you have defined explicitly - i.e. 256 explicitly defined variables. I'd start by reconsidering that and instead have 4 arrays of 64 elements, each of which is the counter value.

Dim CountCondition(1 to 64) As Long
Dim SACountCondition(1 to 64) As Long
Dim WBDACountCondition(1 to 64) As Long
Dim StatusCountCondition(1 to 64) As Long

(You can index the array using 0 to 63 if that's your preference - this is just an example.)

Then just evaluate the 4 cases and increment the appropriate element of the array.

You also repeat expensive range evaluations within the loop even though they are the same:

Instead of:

ThisWorkbook.Sheets("Status").Range("D" & ThisWorkbook.Sheets("Datadump").Range("E17").Value).Value = "Yes"

...at every test, extract that condition (true/false) into a variable outside/before the loop:

Dim status_D_is_yes As Boolean
status_D_is_yes = ThisWorkbook.Sheets("Status").Range("D" & ThisWorkbook.Sheets("Datadump").Range("E17").Value).Value = "Yes"

...then use that boolean variable in the checks.

Having said that, if you do the first suggestion, then the second probably ends up being redundant. I would do the second suggestion first (sorry!) because it is easier to do and will give a performance boost straight away.

Thanks, I can follow the boolean part, however the first part I cant quite follow. How would I define the CountCondition (1 to 64) As Long? As I said, the actual values are text base, so I essentially have 64 different words that need to be evaluated. — Yellow, Feb 16 '18 at 11:12
@Yellow - You'll need to write a simple function that gives you the array index given the string. This can be something like: `Function ArrayIndex(ByVal code As String) As Integer Dim index As Integer Select Case code Case "Condition1" index = 1 Case "Condition2" index = 2 ' etc End Select ArrayIndex = index End Function` — Tevildo, Feb 16 '18 at 11:32
If I understood correctly, your "64 different words" are actually `"condition" & word` where `word` is one of 64 values. The idea is to extract doing something 64 times into a sub-routine that you can call 64 times, and then handle that 4 times (your 4 different cases) by calling each of the 4 over the 64 elements. Frankly, ill be honest im a little confused! At least @Tevildo is on the ball. :) — andrew, Feb 16 '18 at 19:05

Optimize VBA Speed with many ElseIf Statements

2 Answers2