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

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

TrackBack URI for this entry

Comments (1)

Subscribe to this comment's feed

Ted said:

Very nice. I have avoided vba and macros up to this point but I think I'll give it a try.

Monday June 16, 2008 10:13AM

Show/Hide comment form

Name

Email

Title

Comment

Write the displayed characters

Add Comment