CODEWITHSUNDEEP
vbaexcelexcel-2010
bakeson
bakeson

Reputation: 91

Cut down on If statements VBA

So right now I'm making an interactive graph in excel, where the user can select a variety of options via drop down menus. For example, if the user wants to change the color of one of the charts to red, they select the drop down menu for that chart, select red, and then the chart turns red. I've written macros to do this, but it composes of multiple if statements for each color. Is there a good way to cut down on all of these if statements? Current code works and is below. F16 is the where the drop down menu is in excel that indicates what color will change. also am using excel 2010

If Target = Range("F16") Then
    'Checks to see if the color is being changed for the Elevation Graph
    If Worksheets("Reference_Sheet").Range("H9").Value = "Black" Then
        Call Black_Line_ELE
    ElseIf Worksheets("Reference_Sheet").Range("H9").Value = "Tan" Then
        Call Tan_ELE
    ElseIf Worksheets("Reference_Sheet").Range("H9").Value = "Dark Blue" Then
        Call Dark_Blue_ELE
    ElseIf Worksheets("Reference_Sheet").Range("H9").Value = "Yellow" Then
        Call Yellow_ELE
    ElseIf Worksheets("Reference_Sheet").Range("H9").Value = "Olive Green" Then
        Call Olive_Green_ELE
    ElseIf Worksheets("Reference_Sheet").Range("H9").Value = "Light Green" Then
        Call Light_Green_ELE
    ElseIf Worksheets("Reference_Sheet").Range("H9").Value = "Green" Then
        Call Green_ELE
    ElseIf Worksheets("Reference_Sheet").Range("H9").Value = "Light Blue" Then
     Call Light_Blue_ELE
    ElseIf Worksheets("Reference_Sheet").Range("H9").Value = "Aqua" Then
        Call Aqua_ELE
    ElseIf Worksheets("Reference_Sheet").Range("H9").Value = "Theme Orange" Then
        Call Theme_Orange_ELE
    ElseIf Worksheets("Reference_Sheet").Range("H9").Value = "Standard Orange" Then
        Call Standard_Orange_ELE
    ElseIf Worksheets("Reference_Sheet").Range("H9").Value = "Standard Purple" Then
        Call Standard_Purple_ELE
    ElseIf Worksheets("Reference_Sheet").Range("H9").Value = "Theme Purple" Then
        Call Theme_Purple_ELE
    ElseIf Worksheets("Reference_Sheet").Range("H9").Value = "Theme Blue" Then
        Call Theme_Blue_ELE
    ElseIf Worksheets("Reference_Sheet").Range("H9").Value = "Standard Blue" Then
        Call Standard_Blue_ELE
    ElseIf Worksheets("Reference_Sheet").Range("H9").Value = "Standard Red" Then
        Call Standard_Red_ELE
    ElseIf Worksheets("Reference_Sheet").Range("H9").Value = "Theme Red" Then
        Call Theme_Red_ELE
        End if
End if

Upvotes: 2

Views: 130

Answers (3)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

Time for some mapping! Reference the VBScript runtime and create a new Scripting.Dictionary object.

Dim colorActions As Dictionary
Set colorActions = New Dictionary

Or, don't reference the VBScript runtime and use late-binding to create the dictionary instead:

Dim colorActions As Object
Set colorActions = CreateObject("Scripting.Dictionary")

Next, map each value to a string representing the name of the procedure you want to run:

With colorActions
    .Add "Black", "Black_Line_ELE"
    .Add "Tan", "Tan_ELE"
    .Add "Dark Blue", "Dark_Blue_ELE"
    '...
    .Add "Theme Red", "Theme_Red_ELE"
End With

I would make the colorActions dictionary live at module-level, and then put the code to populate it at startup - doing that in the SheetChanged handler would be doing much more work than needed, for no reason: you don't need to recreate it everytime F16 changes!

Now assuming these methods are all public subs, you can use Application.Run to execute the named procedure mapped to the value of H9:

Dim key As String
key = Worksheets("Reference_Sheet").Range("H9").Value

If colorActions.Exists(key) Then
    Application.Run colorActions(key)
Else
    MsgBox "Not supported."
End If

This will scale much better than a Select Case block, and makes only 1 single place that actually executes something. And when you need to add a supported color/method, you just add a dictionary entry and you're done.

That said Call is useless clutter, you can safely omit it.

I also suspect that all these separate methods are really all doing the same thing and could all be removed and replaced with a single parameterized version - but there's no way of telling for sure without seeing your actual code... if you really want to clean up your code, bring it to Code Review and give as much context code as possible!

Upvotes: 6

Benno Grimm
Benno Grimm

Reputation: 530

If Target = Range("F16") Then
    Select Case Worksheets("Reference_Sheet").Range("H9").Value
         Case "Black"
             Call Black_Line_ELE
         Case "Tan"
             Call Tan_ELE
         Case 'Do this for all cases
    End Select
End If

Look here for more details

Should make the structure of your code clearer.

Upvotes: 2

Gary Evans
Gary Evans

Reputation: 1890

As @findwindow has stated in the comments, you may benefit from a Select statement. Consider the example below: -

If Target = Range("F16") Then
   'Checks to see if the color is being changed for the Elevation Graph
   Select Case Worksheets("Reference_Sheet").Range("H9").Value 

       Case "Black" 
           Call Black_Line_ELE

       Case "Tan" 
            Call Tan_ELE

        '... [Your other options] ...

    End Select
End if

Upvotes: 2

Related Questions