VBA and Excel for Engineers and Scientists – Part 2

Flow Control

This is one of the more important aspects of VBA or any programming language for that matter. You will inevitably need to add a little logic to your VBA code. So flow control is the next thing to discuss. Some elements of flow control are fairly self explanatory, so the simplest way to learn them is to show some examples.

Listing 9: Flow control examples

'If Statement If X > 5 Then    'Execute code End If  'Else Statement  If blnCheck = True Then    'Execute True code Else    'Execute False code End If  'More complicated If/Then Statements If X < 0 Then    'Negative X ElseIf X < 10    'X is between 0 and 10 Else    'X is 10 or greater End If  'You can use the And keyword to evaluate more than one condition If X < 5 And Y >= 6 And blnYes Then    'Code goes here End If  'The Or keyword works similarly. Here we are using parenthesis to group conditions If X = 5 Or (X < 5 and Y > 12) Then    'Code goes here End If  'For - Next Loop For X = 1 To 5 'Perform some loop Next  'Do - While/Until Loop Do While X <= 20    'Perform some loop Loop  Do Until blnWeAreFinished = True    'Perform the loop Loop 

Warning: Watch out for infinite loops! Particularly with the Do While/Until loops.

Calling Subs and Functions

A fundamental concept of programming is to eliminate redundant code. If you find yourself repeating certain code over and over, try putting it in a subroutine or function and then just call it whenever you need it. If you need a return value, use a Function. A call to a function usually involves assigning the resulting value of a function to a variable:

Listing 10: Calling a function

myVariable = SomeFunction(Arg1, Arg2)

A subroutine looks similar, but you would usually call a subroutine if you didn’t need any value back from the procedure call. Note that you use the "Call” keyword to call a subroutine (almost makes sense, huh?).

Listing 11: Calling a subroutine

Call SomeSubName(Arg1, Arg2, etc) Call AnotherSubName() 'You don't always have to have arguments 

Info: It’s not entirely true that you can’t return values from a Sub. The easiest way is to have "global” variables, or variables that have their scope defined by using the "Public” keyword at the top of a code module. Then your subroutine can assign values to those variables and all other functions and subroutines will be able to access the values. You can also read up on the ByRef keyword in VBA.