Web Development

Train users to follow best practices when writing VBA code

Visual Basic for Applications (VBA) lets you automate tasks and provide functionality that you can't get via the regular features in Microsoft Office. Susan Harkins discusses seven programming best practices that you should focus on when training users to write efficient VBA code.

If you train users to do a little coding on their own, they may not hit you up with as many development requests. I think that teaching users Visual Basic for Applications (VBA) is a good place to start because it's easy to learn. VBA lets you automate tasks and provide functionality that you can't get via the regular features in Microsoft Office.

Here are seven programming best practices that you should focus on when training users to write efficient VBA code.

Add an Option Explicit statement to each module

Even if you're a veteran developer, some typos will still slip through. The technical solution is to add an Option Explicit statement to every module.

A quick example will show users how Option Explicit forces VBA to check referenced variables against declared variables. When VBA finds an undeclared variable, it's very specific about the problem, as shown in Figure A. VBA highlights the variable and tells you exactly what's wrong. In this case, the referenced variable has a couple of transposed characters. Figure A

Figure A

Figure A: Option Explicit finds undeclared variables before they can cause a problem.
Without Option Explicit, users might find it impossible to identify the problem when the function fails to return the expected results; or, even worse, users might not realize there's a mistake. As you can see in Figure B, with Option Explicit commented out, VBA returns an erroneous value. In this case, the result is 0 because a Long variable defaults to 0. This kind of mistake is hard to find -- even for an experienced developer. Figure B

Figure B

Figure B: With Option Explicit disabled, an undeclared variable doesn't return an error, which you might expect.

Since Microsoft Office 97, Option Explicit is enabled by default. If the feature has been disabled, here's how to enable it:

  1. In the Visual Basic Editor (VBE), choose Options from the Tools menu.
  2. Click the Editor tab.
  3. Check the Require Variable Declaration option in the Code Settings section.
  4. Click OK.

Teach users to keep Option Explicit enabled, as well as how to use Option Explicit to prevent erroneous data and hard-to-debug typos.

Pick a naming convention and stick with it

A naming convention is a set of rules for naming objects and variables. Since this article is about VBA, I'll focus on the variables. Variable names should identify the data type and purpose.

Users may want to dismiss a naming convention as a nuisance and unimportant, but inform them that consistent naming practices make code easier to work with for the following reasons:

  • You don't have to memorize every little detail about the code because the variable's name exposes a great deal about it.
  • It makes it easier for others to decipher and modify their code.
  • It's easier to modify code months down the road because consistently named variables are self documenting.
  • It's easier to identify problems if they use meaningful variable names.

For example, a variable named Variable1 or value2 doesn't offer a hint at the variable's purpose. In contrast, FirstName, LastName, and StreetName are descriptive and meaningful names. Once the variable's purpose is apparent, denote the variable's data type. Most VBA developers prefer a three-character prefix, such as obj, str, lng, int, and so on. FirstName and LastName become strFirstName and strLastName. It might seem redundant in this case because names are string values, but encourage users to include the data type prefix. Notice that the final names use a mixture of upper and lower case, and each prominent word begins with an uppercase character -- this is known as camel style, which improves readability. You should also advise users to avoid using abbreviations, even if they seem obvious.

There are many naming conventions. If there are no corporate rules in place about naming conventions, users can create a simple rule of their own. The rules aren't as important as convincing users that they need to consistently apply a naming convention.

Microsoft offers more details about naming conventions and VBA prefixes.

Comment appropriately

Comments are invaluable when, months down the road, you forget why the code was written. For instance, this simple comment does a good job of defining the function's purpose:

' This function reduces product price by a predetermined discount. 

Without this comment, you would waste time trying to decipher the code.

The following guidelines will help users write efficient and meaningful comments:

  • Add a comment to the beginning of most procedures (both functions and subs) to explain their general purpose. The comment only needs to be a quick synopsis. If a procedure only has one or two lines, a comment usually isn't necessary.
  • Don't comment every statement in a procedure. Most functions and expressions are self documenting.
  • Add comments to explain decisions that defy general standards.
  • Use proper punctuation and grammar in comments. A phrase that seems clear to you might confuse someone else.
  • If a procedure is long, some people find it helpful to comment a series of steps when the code changes its purpose, but this isn't strictly necessary.
  • Add comments to document modifications and enhancements, including the modifier's name and the date modified.

Write easy-to-read code

One of the simplest ways to enhance your code's readability is to indent multi-line statements. You can space or use tab to indent. The VBE default tab comprises four spaces. To alter the amount of spaces in a tab, choose Options from the Tools menu and click the Editor tab (which is the default choice). Enter the appropriate number of characters in the Tab Width control, as shown in Figure C. Figure C

Figure C

Figure C: Determine the number of spaces when indenting code using a tab.

When to indent is mostly up to the author, but the following guidelines should help:

  • Indent paired statements such as If...End, Select Case, With, For...Next, and so on.
  • Indent related operations such as BeginTran...CommitTrans, AddNew, Update, and so on.
  • Indent the first and subsequent lines to isolate each function, making them easier to discern from other procedures in the same module.

The following cmdPrint sub procedure indents all the code to separate it from the function's stub lines and to distinguish the If statement's action clauses.

Private Sub cmdPrint_Click()

  'Open filtered report.

  Dim frm As Form

  Set frm = Forms!frmReconcilePayments

  If intfilterType = acApplyFilter Then

    DoCmd.OpenReport "rptOnFilterTest",

acViewPreview, , frm.Filter

  Else

    DoCmd.OpenReport "rptOnFiltertest", acViewPreview

  End If

End Sub

VBA doesn't require indents, but indents help distinguish the code's flow and make it more readable. Following the indents makes it easier to pinpoint specific pieces of code.

Another readability issue is finding a specific block of code among numerous lines of uninterrupted code. Inserting a blank line to separate tasks and changes in logic can help. Teach users to think of their code like a book. When the code seems to start a new paragraph, insert a blank line. In other words, add a blank line when the tasks or thought changes. If that's too difficult for users to grasp, teach them to insert a blank line before each comment. Initially, users won't use this tip much because their procedures will be short; as they progress, you may need to remind them.

Improve efficiency with constants

Users new to VBA aren't ready for constants. As they improve their skills, you should emphasize efficiency, and constants definitely fall into that category.

A constant stores a value that seldom changes. In fact, you can't programmatically change the value of a constant -- you must manually update the value. For that reason, the first lesson you must stress is not to declare and define a constant unless the value is stable.

Teach advanced users how to set stable values apart as a constant; that way, when and if the value needs updating, they have only one statement to modify -- the constant's declaration statement. For example, you might declare a generic discount value as an Integer data type, as follows:

Const conDiscount As Double = .01

Dropping the reference conDiscount into an expression or function is the same as hard coding the value .01. The best part is, users only have one statement to update when the discount amount changes.

Validate data with data types

Users familiar with Excel and Access probably know that a data type defines data by limiting it. For users who aren't as familiar with Excel and Access, a quick example will clarify what this means.

The simple procedure in Figure D shows what can happen when VBA tries to evaluate inappropriate data. Because the code passes Variant values, there's no way for VBA to determine if the passed values will work as expected. Figure E illustrates how using a numeric data type (Integer) prevents an error when a text value slips through the cracks. Users need to know how to handle this error when it occurs. Figure D

Figure D

Figure D: Variant data types leave data wide open for mistakes.
Figure E

Figure E

Figure E: Appropriate data types help reject inappropriate values before code tries to evaluate them.

In addition, teach users to choose the smallest data type that will get the job done. If you expect only Boolean (Bit or YesNo) values, use a Boolean data type and handle the error that occurs when a non-Boolean value slips through.

VBA's Variant data type accepts any value. A Variant data type should be used sparingly and only when users must accommodate unknown values. Keep in mind that VBA automatically converts data types when necessary using a set of internal rules -- there's no way to control how VBA converts a Variant unless you explicitly convert the data type.

Handle errors

After writing a few successful procedures, users might be ready to paint a big S for Super Coder on their shirts. Despite your users' confidence, their code will contain errors. Reassure them that bugless code is an admirable yet unachievable goal.

The three categories of errors are:

  • Design-time: These are the simplest to resolve. The VBE alerts you when it doesn't like something.
  • Logic: These insidious monsters give no warning and are much harder for users to find. The code runs -- it just doesn't get the job done.
  • Runtime: These occur when VBA can't process the code because something the code needs isn't as expected (e.g., a file that doesn't exist in the specified folder, there's no CD in the drive, and so on). Users can plan for and handle runtime errors, but they usually can't prevent them. When runtime errors occur, VBA displays an error number and description and then exits the procedure. Users can click Debug to open the module and study the statement that's causing the problem; this is helpful when debugging, but it's a nuisance during normal use. Show users how to include a generic error handling routine, as follows:
Sub|Function name()

  On Error GoTo errHandler

  Code that does something

  Exit Sub|Function

errHandler:

  MsgBox Err.Number & ": " & Err.Description,

vbOKOnly, "Error"

End Sub|Function

This routine does little more than VBA does on its own, but it does keep VBA from disrupting work. There are more complex error handling routines, but this is a good start for beginners. As users become more adept, show them how to test for errors and add appropriate code to the error handling routine.

Let 'em rip!

When you're teaching users VBA basics, be sure to educate them about good coding practices along the way. Not everyone will appreciate the advice and some will even ignore you, but the few who take the experience seriously, will thank you some day.

Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is "Mastering Microsoft SQL Server 2005 Express," with Mike Gunderloy, published by Sybex. Other collaborations with Mike Gunderloy are "Automating Microsoft Access 2003 with VBA," "Upgrader's Guide to Microsoft Office System 2003," "ICDL Exam Cram 2," and "Absolute Beginner's Guide to Microsoft Access 2003" all by Que. Currently, Susan volunteers as the Publications Director for Database Advisors at http://www.databaseadvisors.com/. You can reach her at ssharkins@gmail.com.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

30 comments
cactus
cactus

It only takes a little experience with VB(A) to learn that a Variant fits should you need to carry a Null value, as VB(A) doesn't sport nullable data types. So nothing bad about a Variant - you just need to know when to use it and when not. /gustav

Jaqui
Jaqui

is don't use VBA. use a STANDARDS COMPLIANT language instead. you will have far fewer headaches, and get better code than is possible with VBA.

Justin James
Justin James

The worst I ever saw was a guy who learned to write VBA by recording macros, and then modifying the code to his needs, putting variables where the macro recording used hardcoded variables. The result? He write VBA code that essentially imitated a user with insanely fast mouse and keyboard skills. The result? Touching any input device while the code was running (sometimes it would take an hour or two) would derail it, because it was all based on selecting cells and doing copy/paste operations. When I re-wrote the code the way a *programmer* would do it (instead of doing cells.select, selection.copy, cell.select, selection.paste (or whatever it was), and doing range1.value = range2.value not only could you use the computer for other tasks, but it tended to run many times faster, and was more reliable and easier to troubleshoot. The only good thing about using macro recorder is that it let you quickly figure out what commands to use for certain tasks. J.Ja

Tony Hopkinson
Tony Hopkinson

followed these rules. I think it would be nice if once wrote a macro along side your MBA didn't qualify you as a programmer. I like and work well with power users, with one proviso. If what they are doing could end up needing integrating into the main stream system, talk to some one first! I've lost count of the tmes I've been given the task of distributing Fred's data about X when X's identifier wasn't present or was different.

ssharkins
ssharkins

I understand what you're saying. FWIW, I don't have the frustrations many of you have. I don't clean up other people's mistakes. I do think that training is the key. Not everyone's going to become a developer, but if you can help someone help themselves, I'm all for it.

alex.a
alex.a

I hate it when someone tells me they're just writing a "quick and dirty little fix" for something and so don't feel they have to bother with best practices. If a programmer really knows his stuff, clean coding should be second nature to him. It should be as abhorrent to a real programmer to write a line of dirty code as it would be to a pianist to play a Beethoven sonata with his elbows. "Quick and dirty little fixes" have a way of morphing into major patches, and of hanging around long after the so-called fix was supposed to so-called work.

Tony Hopkinson
Tony Hopkinson

Well I'll admit if you know enough about what you are doing and you are careful and you can afford the overhead and you understand the sort of things that they'll let you get away with. Then they may have some utility. However seeing as the were designed so people with none of the above could write some logic where brain straining concepts such as 1 "1" might intefere, there is everything wrong with them.

ssharkins
ssharkins

Thanks Gustav -- that's good advice.

ssharkins
ssharkins

VBA is easy to learn and quick to implement. Which language would you like MS Office users to use, instead of VBA, to create custom solutions?

Tony Hopkinson
Tony Hopkinson

and many do. I've turned power users in to decent developers in my time, mainly out of self defence. I've done the same for junior developers as well... Also out of self defence. When it all goes bad, it's not Ghostbusters who gets the call is it?

Richard O
Richard O

Thanks for a good summary of VBA best practices, although one could almost write a book about VBA Best Practices. Sometimes we, as programmers, forget that not all MS Office (and Access) users are seasoned programming pros. Everybody has to start with the first grade before moving to the twelfth. I'd like to see more articles about using OOP/classes with Access. Thanks.

Tony Hopkinson
Tony Hopkinson

is to work on bad code. Preferably your own from six months ago, seeing your own name at the top of a pile of unintelligible flakey crap, drives the lesson home. Namimng conventions etc, OK, but you have to be careful about what you call bad. It might be bad now, but was it then?

Tony Hopkinson
Tony Hopkinson

Which is far more ofetn than I'd like. Quick and dirty fixes usually need fixing. Generally they need fixing urgently which means you write a quick and dirty fix for the mess the last one left behind. While I find this sort of thing abhorrent, management find change caption on form X will take six man weeks really really abhorrent. The fact that it will take six weeks because the last quick and dirty fix was done because it would take four which was because the time before that it would take two, because the orginal code was written by the MD's nephew whe he needed to get some experience on his resume, is of course immaterial. Far more of us do the best we are allowed to rather than the best we can. That's just IT meets business, and certainly isn't programmer specific. By the way when are you going to reroute that temporary patch cable across the doorway?. :p

cactus
cactus

Right, but if debugging 1 "1" represents a challenge, other discussions than "to variant or not" pop forward. /gustav

Jaqui
Jaqui

none. end users and coding are a bad mix, you will wind up giving out data that is required to be kept confidential.. like the login information to the company bank account. coding should only be done by a programmer, not an end user. The other problem, no one should be using MS Office, since the file formats violate H.I.P.P.A, Sarbanes-Oxley, and every other bit of legislation requiring you to keep data confidential.

Tony Hopkinson
Tony Hopkinson

solve a lot of problems. :p Python would probably be more practical though.

ssharkins
ssharkins

You know that some of the people you support are going to try to teach themselves -- you might as well give them the best tools you can. Besides, occasionally, you're going to run into one that turns out to be good at it.

ssharkins
ssharkins

It's always good to hear that an article's helpful. I'll certainly consider some OOP articles -- do you have something specific in mind?

Locrian_Lyric
Locrian_Lyric

Are consistant. PERIOD. it doesn't matter if you're using hungarian, or what have you. if I name an integer used to track a column as iCol, document it at the beginning, and use it consistantly, it's a good naming convention. I would argue that a variable name like grazxcvnyin could be a good name if it means the same to anyone in the shop. CONSISTANCY is the key,.

ssharkins
ssharkins

Oh Tony, tell me it isn't true - you never really wrote flakey crap, did you???? ;)

ssharkins
ssharkins

If your system is that vulnerable, don't blame your users.

Tony Hopkinson
Tony Hopkinson

Thought up by a moron, promoted by idiots and used by muppets. Variants well I can't even be that polite about them.

Justin James
Justin James

You know, it is possible that at a low level, it could be using variants. I always am a total hardcase about it, and never tried to push it (ie: I always declare my variables with a type, and stick to my types), so it is entirely possible that I missed that internal kludge. I know that you are quite welcome to declare & type. But I'm the type of person to throw auto declaration out the window, I find that saving the 0.023 seconds of typing to get the headache 2 days later during debugging is never worth it. J.Ja

Tony Hopkinson
Tony Hopkinson

looked dynamically typed (if you squinted carefully) but in fact used variants??? I might be out of date of course, I normally reverse engineer the result of that sort of thing, than translate it. Appropos of nothing. I translated some very badly written delphi to C# last night, it was a rush job so now I have some badly written C#. :( :( Refactoring note entered against both code bases, will probably never happen though.

Justin James
Justin James

... VBA shares much in common with Python & Perl. It too is a strongly typed (with the option of implicit typecasting) interpreted language. Where it differs from them is that it is whitespace delimited (and if you are going to say "Python", then that is obviously not a valid objection). I spent nearly 2 years working in VBA not so long ago. I really did not mind it that much. My biggest problem with it was that its collection object was miserable, there was no way to check for the existence of a key or value without iterating through the entire collect (in other words, no "exists" or "contains" mechanism). Not that syntactically, Python, Perl, and VBA are anything alike. But the same traits they share in common make them all suited to be scripting or macro languages (the same stuff that lets ECMAScript be a scripting languge, or Lisp [in emacs and GIMP]): implicit typecasting (more forgiving on the newcomer), strong typing, interpreted languages. The VBA syntax, in and of itself, is easy to learn. Definitely easier to learn than Perl's. VBA is so rediculously verbose, it is impossible to write code as unreadable/unmaintainable as Perl is capable of. To be honest, I think that ALL of these systems are typically too much for the average user, and the vast majority of "power users" too. There's a reason why the IT department usually gets dragged into writing these things. Which is why I would much rather see the .Net CLR used in conjuction with IronPython (like Silverlight does), or maybe IronRuby for internal Office automation. As it is, as many people as possible have been turning the Office automation issue inside out, manipulating Office from outside of the system with .Net code, it is simply much easier, particularly on the debugging. The VBA editor in Office is the worse GUI IDE I've ever encountered... NOTE: Sice I have never actually written Python, I may be off the mark on a statement or two about it, which is why I keep any statements regarding Python at a very high , general level. J.Ja

Jaqui
Jaqui

but yup, python or perl would be good solutions.

Richard O
Richard O

MS Access has the ability to use class modules, but we seldom see articles or books on the subject of using Object Oriented Programming. It would be very helpful to read more about creating and using class modules (other than form and report classes) to encapsulate program logic and data validation. Perhaps start with OOP concepts, then walk through practical examples. Thanks !

Tony Hopkinson
Tony Hopkinson

Hungarian is well iffy, can't say I'm mad keen exctpt when it does ad information as opposed to mislead or constrain. strInvoiceDate for the converted DateTime is good. dtInvoiceDate is a waste of time. grazxcvnyin could indeed be a good name if attached to some documentation that de-jargonises it for those not overly familiar with the domain. I still want to look at implementing a data dictionary. The number of times you see the same name used for two completely different things or as bad two different names for the same thing is really f'ing annoying. Like all these things though. You have to start it, evolve it, maintain it and police it, otherwise you are back to puzzling out what the original developer meant. Nobody say comments, I'm not in a good mood.

ssharkins
ssharkins

Tony, I hear your lament -- I sing it myself occasionally. Personally, I am no longer challenged by the changing technology -- I wish things would slow down! :)

Tony Hopkinson
Tony Hopkinson

I'm still learning , things are still changing, the environment I work in is still evolving. There was always a reason why I did (or is that an excuse?) but the evidence is inarguable. It at least now, doesn't always do what it should, and is real hard to fix without starting again.