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:

Exercise 1: The Excel VBE

Exercise 2: The Access VBE

Perform Exercise 1 in Access.

Exercise 3: "OK" Message Boxes

Exercise 4: Trivial Functions Exercise 5: Calling Functions from Excel Exercise 6: If / Then / Else Exercise 7: More Complicated If / Then / Else Exercise 8: If / Then / ElseIf / Else Exercise 9: Select Case Exercise 10: Guard Clauses Exercise 11: Make a Button in Excel Exercise 12: String Concatenation Exercise 13: Let VBA See an Cell Value from Excel Exercise 14: Have Excel do something when a cell value is changed Exercise 15: Factoring out shared code Exercise 16: Static Variables

Exercise 17: Using Guard Clauses to Avoid Infinite Recursion Exercise 18: Text Box Events Exercise 19: Syncing Two Controls Exercise 20: TBD

Exercise 101: Ask Simple Questions in Message Boxes

Exercise 102: Combining Features of Message Boxes Exercise 103: Default Buttons in Message Boxes Exercise 104: Ask Complicated Questions in Message Boxes

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

Requests for new topics


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:

EditText of this page (last edited April 12, 2012) or FindPage with title or text search