Vba Exercises

The problem:

A programmer wants to learn how to program in Microsoft Visual Basic for Applications.

The context:

The programmer has seen Visual Basic and VBA code, but is not very familiar with the syntax, and is not very familiar with the objects that are commonly used in VBA code. The programmer has seen some TestFirst code, but has not learned how to test her own code first. The programmer is not familiar with VBA's environment for trying out individual lines of code, and does not have much practice stepping through VBA code in the debugger. The programmer does have experience identifying a problem, identifying what a solution to the problem should be capable of, and verifying that the solution matches the problem requirements -- just in other contexts. The programmer has reference material for VBA (including VBA's help, the help within each Microsoft Office application, Google, and a very thick reference tome). The programmer has access to the Refactoring book, which unfortunately only has Java examples. The programmer has access to an experienced VB and VBA programmer, whom she can ask questions of. The programmer has access to MicrosoftOffice, including MicrosoftWord, MicrosoftExcel, and MicrosoftAccess.

What this page tries to do:

This page will present a series of programming exercises. Each exercise will allow the programmer to become familiar with one feature of VBA. By the time the programmer is done with the exercise, the programmer will be able to explain the concept, when it is useful, when it is not useful, and what some common problems are with it. The programmer will have used the concept to solve a problem of her own devising. The exercises will build on each other, and let the programmer continually refresh and cement her knowledge of the earlier concepts. In other words, it will use the SaxonMath? approach, not the CramStudying? approach. This page will not present the answers to the problems -- those are left as exercises for the programmer. (Per the context, the programmer can get the information she needs to solve the problems.)

Recommended Coding Conventions:

In each of the exercises, it is a very good idea to follow these guidelines:

• Always use Option Explicit
• Always explicitly state whether a Sub, Function, or Type is Public or Private.
• Always explicitly state whether an argument to a Sub or Function is ByRef or ByVal.
• Use the line continuation character to explicitly wrap long lines. This lets you see all of your code in the IDE, and see it the same way when you print out.
• Whenever possible, declare variables with a datatype; avoid implicitly declaring them as Variant. If a variable needs to be a Variant, know why it needs to be a Variant.

Exercise 1: The Excel VBE

• In Excel, launch the VisualBasicEditor? (VBE).
• Configure the VBE so that any new modules you create start with Option Explicit.
• In the VBE, show these features:
  • The Project Pane
  • The Immediate Window
  • The Locals Window
  • The Code Window
• Write a line of code in the Immediate Window that computes something. For example, if you have 3 spoons and 4 forks, how many pieces of flatware do you have in all? Make VBE show you the result in the Immediate Window.
• Optionally, exit VBE.
• Optionally, exit Excel.

• Questions about Exercise 1:
  • What is the purpose of the Project Pane?
  • What is the purpose of the Immediate Window?
  • What is the purpose of the Locals Window?
  • What is the purpose of the Code Window?
  • What are the two ways you can tell the Immediate Window to show you an answer in the Immediate Window?

Exercise 2: The Access VBE

Perform Exercise 1 in Access.

• Questions about Exercise 2:
  • How are the contents of the Project Pane different in Access than in Excel?

Exercise 3: "OK" Message Boxes

• In a VBE, write a line of code in the Immediate Window that computes something. For example, if you have 3 spoons and 4 forks, how many pieces of flatware do you have in all?
• Make VBE show you the result in a message box.
• Change the title of the message box. (It is often a good idea to have the title briefly indicate the name and version number of the program you are writing.)
• Add one of the standard icons to the message box, such as a question mark, an information icon, a serious warning icon, or no icon.

• Questions about Exercise 3:
  • What argument(s) to the MsgBox command do you have to use?
  • What argument(s) are optional?
  • Are there any argument(s) that you did not use in this exercise?
  • What does each argument do?
  • In the programs you expect to write, which arguments to you expect to use often? Why?
  • In the programs you expect to write, which arguments to you rarely expect to use? Why?
  • What are the "magic numbers" for each kind of standard icon?
  • Does VBA have any global constants that correspond to these "magic numbers"?
    • If so, what are they?
    • If so, which is easier for you to look up or remember?
    • If so, which is easier for you to understand when you are reading code?

Exercise 4: Trivial Functions

• In Excel's VBE, write a function that takes one input (of type Variant), and returns the exact same value.
• Make the function a Public Function.
• Make sure to indent the code inside of the function.
• Call the function from cell(s) in Excel.
• Try out the function with several kinds of data (such as strings or numbers).
• Save your work.

• Questions about Exercise 4:
  • What is the difference between a Function and a Sub?
  • Why can't you use a Sub to solve this problem?
  • What is the difference between a Public and a Private routine?
  • Why can't you use a Private routine to solve this problem?
  • If you do not specify Public or Private, what does VBA default to?
  • Why should we always explicitly specify Public or Private?
  • How do you tell VBA what value should be returned by a function?

Exercise 5: Calling Functions from Excel

• In Excel, call the function you wrote in Exercise 4 using a hard-coded parameter value.
• In Excel, call the function you wrote in Exercise 4 using the contents of another cell as the parameter value.
• In Excel, call the function you wrote in Exercise 4 using the results of a function.

• Questions about Exercise 5:
  • Are there any differences between the Public Function you wrote in Exercise 4, and a built-in Excel function?

Exercise 6: If / Then / Else

• Come up with a choice that needs one of two results, based on a condition.
• In Excel, write a VBA function that performs this choice.
  • Use the If Then Else feature.
  • Make sure to indent your code so that it is easy to understand.
• Try out the function in cell(s) of the spreadsheet.
• Save your work.

Exercise 7: More Complicated If / Then / Else

• Come up with a choice that is based on several conditions.
• In Excel, write a VBA function that performs this choice. Use the If Then Else feature.
• Try out the function in cell(s) of the spreadsheet.
• Save your work.

Exercise 8: If / Then / ElseIf / Else

• In Excel, write a VBA function that performs the choice in Exercise 7.
  • Use the If Then ElseIf Else feature.
  • Make sure to indent your code so that it is easy to understand.
• Try out the function in cell(s) of the spreadsheet.
• Save your work.

• Questions about Exercise 8:
  • Is this code easier to understand than the code in Exercise 7?

Exercise 9: Select Case

• In Excel, write a VBA function that performs the choice in Exercise 7.
  • Use the Select Case feature.
  • Note that Visual Basic's Select Case feature is very powerful. It can handle numbers, strings, and ranges of values. It can also handle expressions. Other common languages' Case statements have many fewer options.
  • If appropriate, include a Case Else statement.
  • It is often a good idea to put comments in some of the Case statements, especially the Case Else statement.
  • Make sure to indent your code so that it is easy to understand. Two examples are on the SelectCaseExamples page.
• Try out the function in cell(s) of the spreadsheet.
• Save your work.

• Questions about Exercise 9:
  • Is this code easier to understand than the code in Exercises 7 and 8?

Exercise 10: Guard Clauses

• In Excel, write a VBA function that performs the choice in Exercise 7.
  • Use GuardClauses, as explained in MartinFowler's Refactoring book.
• Try out the function in cell(s) of the spreadsheet.
• Save your work.

• Questions about Exercise 10:
  • How does the Exit Function statement work in VBA?
  • How does the Exit Sub statement work in VBA?
  • Is this code easier to understand than the code in Exercises 7, 8, and 9?
  • In what situations does it make sense to use each approach?
  • In what situations does it make sense to use a combination of approaches?

Exercise 11: Make a Button in Excel

• Show the "Toolbox".
• Make a button in Excel.
• Write a subroutine that is called by the button.
  • For example, show a message box.
• Save your work.

Exercise 12: String Concatenation

• Modify the message box text in Exercise 11, so that the message text is the result of combining two or more strings.
• Save your work.

Exercise 13: Let VBA See an Cell Value from Excel

• Modify the message box text in Exercise 12, so that the message text includes the value of a cell in the spreadsheet.
• Save your work.

Exercise 14: Have Excel do something when a cell value is changed

• See http://www.cpearson.com/excel/Events.aspx
• In an Excel workbook, choose a cell.
• Show a message box when this cell is changed.
• Save your work.

Exercise 15: Factoring out shared code

• Note: This is the ExtractMethod refactoring.
• Choose a cell in another worksheet of the same workbook (as Exercise 14).
• Show the same message box (as in Exercise 14) when the second cell is changed.
• Move the message box code to a new subroutine or function.
• Inside each Worksheet_Change() event, call the new subroutine or function, instead of having the details in both Worksheet_Change() events.
• Save your work.

Exercise 16: Static Variables

Exercise 17: Using Guard Clauses to Avoid Infinite Recursion

• Note: This is a variation on the SingletonPattern.
• In an Excel workbook, choose two cells that you want to always have the same value.
• Make it so that updating the first cell automatically updates the second cell. (This is trivial -- just have a formula in the second cell that gets the value from the first cell.)
• Make it so that updating the second cell automatically updates the first cell. (This is trivial, if you are willing to give up the ability to manually change the first cell -- just have a formula in the first cell that gets the value from the second cell.)
• Create Worksheet_Change() event(s) for each cell.
  • Have each Worksheet_Change() event call a subroutine -- but do not make the subroutine set the value of the other cell yet. (If you do, you risk an infinite loop).
  • Use a static variable inside the subroutine to make sure that the guts of the subroutine do not get called (indirectly) by the subroutine. You will need to use a GuardClause, as discussed in Exercise 10.
  • Now it is safe for the guts of the subroutine to set the values of both cells.
• Save your work.

Exercise 18: Text Box Events

• In an Access database, create a form.
• On the form, create two text boxes.
• When the text in the first text box changes, update the second text box.
• Save your work.

• Questions about Exercise 18:
  • What are the differences between using macros and code to perform this update?
  • What events can you use to trigger this update?
  • When do these events occur?
  • What order do these events occur in?
  • Does triggering one of these events require triggering another event?
    • For example, single-click versus double-click.
    • For example, leaving one control and entering another control.
  • Can you think of scenarios where you might want to use some of these events?
  • Are there any events where it is hard to think of scenarios that should use them?
  • Do any of your scenarios result in infinite loops?

Exercise 19: Syncing Two Controls

• Add to and/or modify your code or macros in Exercise 18, so that when either text box changes, the other changes to match it. Use the techniques from Exercise 17 to avoid infinite loops.
• Save your work.

• Questions about Exercise 19:
  • Does Access let you refer to the Text property of a textbox?
    • What does it mean for a control to "have the focus"?
    • Does Access let you refer to the Text property of a textbox that has the focus?
    • Does Access let you refer to the Text property of a textbox that does not have the focus?
  • What is the "Value" property of a textbox?
  • Does Access let you refer to the Value property of a textbox that does not have the focus?
  • Can you refer to the "Value" property of a textbox without explicitly coding ".Value"?

Exercise 20: TBD

Exercise 101: Ask Simple Questions in Message Boxes

• Come up with a problem that requires the user to make a "Yes / No" or "OK / Cancel" choice.
• Write a VBA function that asks this question of the user, and returns a value based on the user's answer.
• Try out your function.
• Unfortunately, Microsoft gives you very few choices about what text is on each button. If needed, edit your message so that it is very easy for the user to understand which button they should press.
• See what happens if the user closes the message box, instead of pressing one of the buttons.
• See what happens if the user tries to do something else in the program while the message box is open.
• See what happens if the user tries to use another program while the message box is open.
• Save your work.

• Questions about Exercise 101:
  • What argument(s) to the MsgBox command do you have to use?
  • What are the "magic numbers" for each kind of choice?
  • Does VBA have any global constants that correspond to these "magic numbers"?
    • If so, what are they?
    • If so, which is easier for you to look up or remember?
    • If so, which is easier for you to understand when you are reading code?

Exercise 102: Combining Features of Message Boxes

• Modify your function from Exercise 101, so that it has a custom title.
• Modify your function from Exercise 101, so that it has a standard icon, such as a question mark, an information icon, a serious warning icon, or no icon.
• Try out your function.
• Save your work.

• Questions about Exercise 102:
  • How do you tell VBA to use both a custom icon and have more than just an "OK" button?
  • Does this mean that you need new "magic numbers"?
  • If you wanted new "magic numbers", how would you declare them?

Exercise 103: Default Buttons in Message Boxes

• If the user makes a mistake, think about which button is the safer choice.
• Modify your function from Exercise 102, to make the safer choice the default button.
• Try out your function.

• Questions about Exercise 103:
  • What does it mean for a button to be the "default" button on a dialog box?
  • How do you tell the MsgBox command which button should be the default choice?
  • If you do not tell the MsgBox command which button should be the default choice, which button is the default choice?
  • What are the "magic numbers" for choosing the default button?

Exercise 104: Ask Complicated Questions in Message Boxes

Perform Exercises 101 - 103, but use an "Abort / Retry / Cancel" dialog box.

• Questions about Exercise 104:
  • When would you want to use an "Abort / Retry / Cancel" dialog box?
  • Is it easy for the user to understand these choices? Why or why not?
  • What ways are there around this problem?
  • What should the program do if the user closes the dialog box from the dialog box' toolbar?
  • What does the program do if the user closes the dialog box from the dialog box' toolbar?

---

Requests for new topics

• Indenting code
  • Sub...End Sub, Function...End Function, If...Then...Else, For...Next, While...Wend, Do...While, Do...Until
  • Select...Case...End Select has four common indentation styles
    • Same line Case and action is good for short actions
    • Case statements can have either a half-indent or a full indent
  • IDE options for how many spaces 1 tab equals (the default is 1 tab = 4 spaces)
  • The VBA IDE lets you add or remove tabs easily, by selecting one or more lines, and pressing Tab or Shift-Tab. This does not delete the highlighted text.

• Hiding rows in Excel, based on whether the row's cell in a particular column has the value of "hide"
• Deciding which row is the last row (that is actually used) in an Excel worksheet
• Deciding which rows are in the Print Area of an Excel worksheet
• Showing all the rows in an Excel worksheet
• Hiding the rows in an Excel worksheet that have "hide" in a particular column

• Ways to handle error conditions
  • Break into the debugger
  • On Error GoTo 0
  • On Error GoTo (label)
  • Putting the line of code that is likely to have an error in a subroutine of its own
  • Using if statements (and similar code) to avoid having the error

• How to use the same code in multiple worksheets in the same Excel workbook
  • Copy the code into each worksheet's code page
  • Create a shared code module
  • Using the Worksheets() collection to get the worksheet object that has a particular name
  • Ways to tell a subroutine to update a particular worksheet
    • Passing the worksheet object
    • Passing the name of the worksheet
    • Passing the number of the worksheet in the collection (not recommended)

• Optimizations
  • Reducing the flashing of the code that hides rows

• String manipulation -- Left(), Right(), Mid(), Len(), InStr?(), Trim()
• How to avoid errors when using these functions
• Newer string functions
  • Reversing strings
  • Searching backwards in a string
  • The Like operator

• Common data types
  • When to use Int or Long
  • When to use Float or Double
  • When to use String
  • When you have to use Variant
    • Inputs from Excel that might be either a number or a string
    • Inputs from Access that might be Null
    • Passing objects (such as worksheets or cell ranges)
    • Passing pointers to/from Windows API calls

• Switch statements in Access' SQL view
• Iff statements in Excel's formula bar

• Ways to find things in the help system
  • F1 is sometimes context-sensitive
  • The Language Reference
    • What is a collection or an object?
    • The differences between methods, properties, events, and functions
    • What is an enumeration?
  • Finding things in the help
  • Google

• Good habits when you work with someone else's code
  • Giving credit for code found on the Internet

• Good naming habits
  • Name objects based on what they are
  • Name functions and subroutines based on what they do. If you were to yell at the computer to do something, what would you yell?
  • We are in the VowelGeneration -- it is good to be able to pronounce your variable names, functions, and subroutines.

• Optionally:
  • Either consistently use VB's simplified HungarianNotation
  • Or consistently do not use HungarianNotation

---

---

Discussion:

Re: "Whenever possible, declare variables with a datatype" + Re: "Always explicitly state [...] Public and Private"

This is the "heavy typing" view of things rather than the scriptish "keep code short and clean" philosophy. Perhaps the verbose approach is good during the learning phase, but dynamic coding is a useful and powerful skill and this should be pointed out. (This doesn't apply to "option explicit", which MS screwed up badly on.) --top

The "verbose approach" is also good when the requirements prioritize static TypeSafety over terseness.

Well, okay. I just think the beginner should know about both styles and that they both have their place. Even if you personally "hate" one or the other, a beginner has to at least be ready for the different expectations and requests of the marketplace.

True, but I'd encourage beginners to especially avoid implicit Variant variables in order to discourage questionable practices, such as using the same variable for multiple, differently-typed purposes in the same code block. Arguably, "scriptish" approaches using dynamic implicit typing should be considered an advanced programming technique -- a sharp cutting tool for the expert, rather than a shortcut for the beginner. New VBA programmers are all too likely to cause run-time type errors due to careless or naïve "beginners" coding. Manifest static typing may help reduce the likelihood of this, whilst encouraging discipline and focusing the developer's mind on the type and purpose of a given variable.

Students should be exposed to dynamic typing at some point. Perhaps VBx is not the best language for such because of its messy rules and limiting declaration options.

---

See also:

• VbClassic, VisualBasicForApplications, VbClassicSurvivalGuide,
• MicrosoftOffice, MicrosoftAccess, MicrosoftExcel, TeachMeToSmoke
• MsgBox, GuardClause, SingletonPattern, SelectCaseExamples