CODEWITHSUNDEEP
vbaexcel
Yellow
Yellow

Reputation: 148

Optimize VBA Speed with many ElseIf Statements

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.

Upvotes: 0

Views: 384

Answers (2)

andrew
andrew

Reputation: 1816

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.

Upvotes: 1

Tevildo
Tevildo

Reputation: 340

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.

Upvotes: 2

Related Questions