May I take this moment to briefly step off topic and express my sympathy and condolences to the many people who have given so much for freedom’s sake since the last issue of ABC.  My hat goes off to all the men and women heroes who are striving daily for the well being of all humanity world-wide.  As I’m finishing up this edition I have the TV tuned to “America: A Tribute to Heroes.”  Who would have ever thought that Willy Nelson would bring tears to my eyes.  God bless the USA and those who serve her!

Chad

Well, we are finally going to start delving deeper into VBA.  The last couple of issues have addressed the Macro Recorder.  It is a wonderful tool, but let’s face it, the recorder cannot record all of the things available in VBA.  What if you only want to execute a piece of code if certain conditions are met?  How do you repeat a piece of code more than once?  Trust me, there are better ways than just running the macro over and over. 

Let’s examine what is known as the If statement.  The examples used this month are written in VBA for Word, but keep in mind that the same structures work in any VBA application.  The structure of the If statement (for what it’s worth) is: 

If condition Then

[statements]

[ElseIf condition-n Then

[else if statements]...

[Else

[else statements]]

End If

The condition represents any statement that returns a True or False.  If the condition is true then all statements between the first line and the first ElseIf, Else or End If are executed.  If the condition is false, the program will skip over all statements in the True part of the structure. If there is an ElseIf statement another condition will be tested.  If that condition is True, the statements between that ElseIf and the next ElseIf, Else or End If is executed.  If none of the conditions are met then the statements between the Else and the End If (if the Else exists) are executed. 

Wow, that was a big confusing paragraph.  Let’s look at some examples to see how an If statement really works.  Here is a very basic If statement.  It contains only the bare minimum:  If, condition, Then, statement and End If.

If  x = 1 Then

    x = 2

End If

That seems pretty simple, right?  If x equals 1 then set x equal to 2.  If x does not equal 1 then nothing happens.  Now let’s look at a little bigger If statement and work through it:

Sub IfStructure()

    If Selection.Font.ColorIndex = wdBlue Then

        MsgBox "This text is Blue"

    ElseIf Selection.Font.ColorIndex = wdBrightGreen Then

        MsgBox "This text is Bright Green"

    ElseIf Selection.Font.ColorIndex = wdDarkBlue Then

        MsgBox "This text is Dark Blue"

    ElseIf Selection.Font.ColorIndex = wdDarkRed Then

        MsgBox "This text is Dark Red"

    Else

        MsgBox "This text is not blue, bright green, dark blue or dark red"

    End If

End Sub

This If statement contains four conditions using three ElseIf parts.  Remember if the first condition isn’t met it will skip to the first ElseIf.  If that condition isn’t met, it skips to the second ElseIf, and so on.  If none of the conditions are met, the Else part of the structure is executed. 

As you can imagine, this structure can quickly get big.  An easier way to manage multiple conditions is with the Case structure. The Case structure is laid out as follows:

Select Case test expression

[Case expression list-n

[statements-n]]…

[Case Else

[else statements]]

End Select

We can rewrite the if structure above to:

Sub CaseStructure()

    Select Case Selection.Font.ColorIndex

        Case Is = wdBlue

            MsgBox "This text is Blue"

        Case Is = wdBrightGreen

            MsgBox "This text is Bright Green"

        Case Is = wdDarkBlue

            MsgBox "This text is Dark Blue"

        Case Is = wdDarkRed

            MsgBox "This text is Dark Red"

        Case Else

            MsgBox "This text is not blue, bright green, dark blue or dark red"

    End Select

End Sub

You’ll notice in the If statement all of the conditions compare the font color.  The first line of the Case structure also checks the font color.  Then each possible Case checks the other half of the conditions in the If statement.  The lines between the Cases are executed if the particular Case is True.  If none of the Cases are True then the statements following the Case Else are executed. 

If and Case structures have many uses.  Put one or two of those in your macros that you record (or write from scratch), and you are beginning to program like a pro! 

Good Luck and Happy Coding!
Chad

Chad K. Welch works as a technician/enabler in Utah.  He is available for consulting or application programming with Microsoft Office and VBA.  Do you have a question or tip you’d like to have Chad address in this column?  Send an email to linda@personal-computer-tutor.com or contact him directly at chad@welchkins.com for information