Web Development

Five tips for writing better VBA code

You can produce efficient, reliable, and easy-to-maintain code by following a few best practices. Susan Harkins reviews some essential aspects of good VBA code writing.

Writing dependable and efficient code is the goal of every competent developer. You want code that performs well and is easy to maintain. Most VBA developers have a bag of tricks or best practices they employ to reach that goal. Here are five of my favorites.

1: Use the most appropriate data type

Variable data types are a 101 topic -- part of the basic foundation of good coding. It's also an area where some developers cut corners: They don't really matter anymore, so I'll make everything a Variant. Whether from ignorance or a lack of initiative, this decision couldn't be more wrong because:

  • VBA automatically converts data types following an internal set of rules you can't control. That means you can't guarantee the converted data type will be what you need.
  • Variants consume more storage than data types that are more specific.
  • Variants don't reject invalid data -- there is no such thing to a Variant.

It's true that declaring data types to conserve resources isn't as important as it once was, but data types are bigger than the memory they consume. They also ensure the validity of your data. For instance, an Integer data type will accept only a valid integer value. If you pass a decimal or an alpha character, VBA will reject it. That's the first step to protecting the validity of your data. Specifying a data type won't eliminate all errors, but it will ensure that the defined value is the right type.

2: Use constants

A constant stores a value that seldom or never changes. You can't change the value of a constant while code is executing; you can change it only during design time. That protects the validity of the value. In addition, constants are easier to maintain, even if the value occurs only a few times throughout your code -- you know exactly where to go to update the value. For instance, if you refer to a discount value of 10 percent throughout a dozen or more procedures, you might create the following constant:

Const conDiscount As Single = 0.1

Then, when the discount value changes, you have only one update to make. You open the module and change the Const statement. That's it. And it's certainly easier than finding and updating several variables throughout your modules. What if you miss one? Use constants to protect and maintain stable values.

3: Comment your code

Commenting your code won't make it run any better, but it will make maintaining it much easier. A simple, grammatically correct comment can mean the difference between a quick and easy update and a hair-pulling Why did I do that???? update. When you're writing the code, all the factors are current in your mind. Months later, you won't recall the small detail that sent you in a particular direction.

Comments only seem like a nuisance... until you need a reminder. For competent commenting, follow these simple guidelines:

  • Add a descriptive and meaningful, but brief, comment to the beginning of each procedure. (Be reasonable though, if a procedure is short and self-documenting, it probably doesn't need a comment.)
  • Don't try to explain every single statement, as many are self-documenting.
  • Include comments to explain decisions that aren't apparent.
  • Include comments that identify and explain anomalies.
  • Use complete, grammatically correct sentences and punctuation.

4: Control the flow

Some VBA statements, such as Select Case and If...Then...Else, make decisions. When writing these types of statements, be sure to control all the possibilities, not just the ones you anticipate. For instance, the following Select Case example allows for three possibilities:

Select Case val
  Case 1
    ...
  Case 2
    ...
  Case 3
    ...
End Select

What happens if val equals 4, 10, or xyz? Nothing happens. Depending on your code, that could be (and most likely will be) a big problem. To avoid loss of control, always use the Else component as follows:

Select Case val
  Case 1
    ...
  Case 2
    ...
  Case 3
    ...
  Case Else
    MsgBox "Can't evaluate the current value:  " & val, vbOkOnly
End Select

In this case, the message displayed is just a generic response so you can follow the flow. You'll probably want to exert a bit more control or offer more help. Of course, you could avoid the problem altogether by ensuring that only the values 1, 2, and 3 are passed to val, but don't bypass the Else component, regardless. A bit of extra protection can't hurt, and including Else produces the most comprehensive solution.

5: Keep it simple

When writing code, less is often more. In other words, keep your code as simple as possible. It will be easier to write and maintain. VBA's If...Then...Else statement is a good example of this strategy. This statement evaluates a value or expression and responds accordingly. If you're evaluating a single-statement for a simple true or false condition, use the simpler IIF() function instead. The following If...Then...Else statement illustrates this point -- it's just overkill for the job at hand:

If val = 1 Then
  i = 1
Else
  i = 0
End if

Using IIF() for such a simple comparison is more efficient, easier to write, and certainly easier to maintain:

i = IIF(val = 1, 1, 0)
If you need to evaluate only one statement, IIF() will probably be adequate. Always rely on the simplest solution possible.


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.

36 comments
anupriyadevi
anupriyadevi

hai, m an actuarial science student. Im finding difficulties in writing VBA code for the following question. Can you help me? Question: Simulation of stochastic short rate that follows vasicek or CIR model for pricing of interest rate over 3 years. Hints given: Milstein and Euler test,, control variate method,, boyle modification,, variance reduction,, antithetic method,, swap,, one floor one cap with given parameter value.

bart001fr
bart001fr

Break it all down into subroutines and keep the goal of each one simple so you can come back to it later, like 3 months later, and still understand it. And, of course, do NOT forget the comments. They will save you hours of anguish. It all comes down to Keep It Simple S*****!

redelapena
redelapena

About tip # 5: Keep it simple, for the example I prefer to use this code: i = 0 If val = 1 Then i = 1 This way i is initialized, then if condition is met i is changed.

rgray
rgray

This is the best tip I found - Always code as if the person who will maintain your code is a maniac serial killer that knows where you live.

Slayer_
Slayer_

An inline if is one line of code, and is compiled as such. Both the true and false are executed when the line is run. If the true or false is a function or requires a function, the function is run. If your If statement is to check if it is safe to run this function, you can get errors as the function will run, even though you don't want the return value. This will also hurt performance (not that VBA is a high performer anyways) Usually I use them in mid and instr statements for length checks.

Slayer_
Slayer_

Here is some sample data for you. You can try these in an immediate window if you want. ?"true" = true (true) ?"TRUE" = true (true) ?"tRUe" = true (true) ?"#true#" = true (error) ?"#TRUE#" = true (true) ?"1" = true (true) ?"0" = true (false) ?"-1" = true (true) ?1 = true (false) 'Seriously ?0 = true (false) ?-1 = true (true) ?abs(true) = true (false) ?cstr(abs(true)) = true (true) In short, converting from string, it makes decisions different then if you start with an int. This will really confuse you in VBS which is all variants. in VBS, you need to be sure, before doing any decisions, to convert the variable before testing it. in VBA/VB6. Double and triple check your data types

Slayer_
Slayer_

One of the best part of the VBA language is the case statement and just how smart it is. Unlike other languages that can only do a switch on primitive types, VBA (and VB6) can switch on anything, even objects. My favorite however is the multi true case statement. Where any one scenerio can be right, but only one. Its is basically a clean way of writing [b]elseif[/b]'s Select Case True Case strFirstName = strLastName call msgbox("Hey, cool name") case ucase$(strFirstName) = "MARIO" and ucase$(strLastName) = "MARIO" call msgbox("OMG it's Mario") case msgbox("Should I keep checking the statement?") = vbno 'This will only run if the first 2 were false, gives the ability to break out of the case statement without using gotos or breaks 'empty case len(strFirstName) > 15 call msgbox("You have a long name") case else call msgbox("I got nothing to say") end select You can also ask it to validate ranges of values Select Case intInput Case 1 to 10 call msgbox("Its between 1 and 10") case 11 to 20 call msgbox("Its between 11 and 20") case > 20 call msgbox("Its higher than 20") case < 0 call msgbox("its less than 0") end select And many more uses.

Slayer_
Slayer_

First year computer science covers these...

jacobso1
jacobso1

. ident your code . use meaningful names ie 'N' says nothing about its use but 'NumberOfStudents' is much better . add something to characterize the type ie 'I_NumberOfStudents' or 'NumberOfStudents_I' is integer . comment all routines, functions, ... .. input .. control if any .. output .. error conditions & return .. some words on functionality & method .. history, authors & reason for change . don't make your code 'too smart' . have some test inputs (also with errors) . users will not behave, so write a user-proof code . test it and let it be tested by others (users and also a 'candid' one) . look for ease to use (ergonomy) . be consistent in menus, colors, ... . have some fun too

ssharkins
ssharkins

I agree -- I wish I'd said that! :)

Tony Hopkinson
Tony Hopkinson

if (A=0) and (C=1) with fast on would never check C if (A != 0) Personally I think full evaluation makes for a lot of messy code but I can cope with it. Now when fast is the default and some plank turns it off (sometimes a compile time option), I tend to get... irritated.

Tony Hopkinson
Tony Hopkinson

masquerading as dynamic.... Brings back many memories. Bad ones...

access
access

You've overlooked the real fact that a lot of people who have never had a computer science class can now write code. And many people, even experienced programmers, don't document at all (or not nearly enough). The excuses are "I'm the only one who will look at this, and I now what it does" or "The code is self-evident". That "tip" about using IIF would certainly not have made my top 5. I would have put in a tip about using Error Handling.

Tony Hopkinson
Tony Hopkinson

Some of the f'kers might remember them then. Another novel idea might be getting marked down for not getting the basics right all the way through the course...

_Mr_West_
_Mr_West_

Actually, I think I learned this at secondary school.

Slayer_
Slayer_

VBA would compile the sub/function when it is used, whether or not it is actually needed. And I know for certain VB6 has no such option, cause its annoyed me before. You can't do something like this in VB6 If (not fsoFile is nothing) and (fsoFile.name "keep out") Then 'Stuff End if The If statement will runtime error if fsoFile is nothing, empty or null, even though you are checking for it first. You have to do nested if statements. Which execute faster anyways, but makes for slightly less simple code. That was an unlikely example, I had this happen more often when working with XML in VBA. Best way to figure out if your node select worked was to check if it was not nothing. But then you had to check if it was an empty node or not.

Tony Hopkinson
Tony Hopkinson

of any decent developer, comment where you can't, not write obfuscated bollocks and then try to esplain it in english. If a developer says to you I'm the only one who will look at this, they are an impostor. They don't have clue one, and shouldn't be allowed near any code ever. You learn to write comprehensible code, after you fail to understand your own six months later. They don't teach that in CS. I've done one or three classes in CS. It' a one liner. You should comment your code. That's it! No how, no when, no why and you don't get marked up for doing it, or even more imporant marked down for not. Same for meaningful names, standards, avoiding global variables, functions that do more than one thing and side effects. All fundamentals in the practical aspects of writing code in an environment where change is a given. By the way the resource for documenting is factored in to your plans of course, and if so doesn't get reprioritised in favour of writing some more code.....

Slayer_
Slayer_

If they are not commenting, they are shitty programmers and should not be in the profession... shoot them.

Slayer_
Slayer_

The only thing I hate is when people use the :. Why bother putting more than one line of code, on a line... Does nothing but hurt readability. 'Forms List 551.25 lblServiceForms.Left = lblFrame.Left + 15: lblServiceForms.Top = lblFrame.Top + 9 cmdServiceForms.Left = lblFrame.Left + 326: cmdServiceForms.Top = lblFrame.Top + 315 '355 cmdDelete.Left = lblFrame.Left + 326: cmdDelete.Top = lblFrame.Top + 315 '355 cmdEmail.Left = lblFrame.Left + 214: cmdEmail.Top = lblFrame.Top + 315 lstServiceForms.Width = 350: lstServiceForms.Height = 269 '309 lstServiceForms.Left = lblFrame.Left + 100: lstServiceForms.Top = lblFrame.Top + 33.75 lstTabForms.Width = 350: lstTabForms.Height = 269 '309 lstTabForms.Left = lblFrame.Left + 100: lstTabForms.Top = lblFrame.Top + 33.75 lstAllForms.Width = 350: lstAllForms.Height = 269 '309 lstAllForms.Left = lblFrame.Left + 100: lstAllForms.Top = lblFrame.Top + 33.75 I'm sorry, thats very unpleasant to read

yeoman
yeoman

The point is, people still don't follow good practices taught in school! Agreed, it is sad. I think it is worth reminding us.

Slayer_
Slayer_

Stupid Stupid VB.... If I remember correctly Nothing means there is no pointer Empty means there is a pointer, but there is nothing there Null is an actual value of an object or primitive. I am guessing it is just the null termination in memory??? So to cover your bases, you got to write this much.... If not oObject is nothing Then If isEmpty(oObject) = false and isNull(oObject) = false Then End If End If Stupid stupid stupid... For some of my code, I just wrote a function to cover these checks. Put simply, the original blog does not cover anything interesting, the original poster should take our comments and make a new blog.

Tony Hopkinson
Tony Hopkinson

if ((MyObject != null) && (MyObject.Flag) { MyObject.DoSomeOperation(); } It's real killer when you've got used to writing like this and then you switch to a language without the feature, tripped me up no end of times after a decade of Delphi was followed by a wee foray into VB6. Consistency is the key though, someone did the turning off trick on me in Delphi and during a version upgrade, the compile flag got lost. Best of it was he did that so he could put in some really nice side effects, like creating a record in the database, to update later on. Course fast eval got turned on and none of that sh1t happened. Took me ages to figure out what had gone wrong, wasn't clever enough to think of it, you see.....

Tony Hopkinson
Tony Hopkinson

on occasion. :p Exceptions prove rules not make them. It's the same as viewing comments as a faulure to write readable code, or comming up with an accurate name. Your description made sense to you at the time, and havng been doing this for a while you try not to shoot yourelf in the foot and make the description make sense to you and others later. These aren't really rules, more like guidelines, following in some baked in standard and making your code less comprehensible is moronic, usually imposed by an MBA manager with delusions of competence. Another one from my lengthy past. This guy had this idea (reducing cyclomatic complexity?) where there should only be one return point in a function and it should be the last intruction. To do that he used flag variables, and infinite loop and then break after they were set.... So you'd see stuff like find one item flag = false While True ... ... If x then globalvariable = 10 flag = false break; endwhile return flag Course everybody unfamiliar with this "standard", is stumbling all over the code with a WTF expression on their face. I wish I'd been there when he was, I'd have ripped him a new one.

Slayer_
Slayer_

If its a new user If the user has saved if the user tried to save if there is co users are the co users existing now loop thru existing co users loop thru new co users, add them Is the service saved? Id they try to savE? if saved, whats its status case status pending, updated Run restore routine case status approved, commmited request approval (run approval screens) if approval success, run restore routine. The restore routine is a massive sub procedure that basically contains 35k lines of code, each one is the exact same function call, but with different paremeters. Thank god for spreadsheets and fill down :). I just don't see how you could sub that out. lots of requirements, each with else statements and error handling and idiot user handling. Plus, the data might not be complete, since what I am writing is a crash recovery.

Tony Hopkinson
Tony Hopkinson

and their own aesthetics. For instance , if I have to indent by more than three levels I bust the inner code to another function, or use a switch statement. So your habit would have me foaming at the mouth. There again I've generally been blessed with decent compilers, which will just in line compile that sort of thing. If they didn't the number of stack frames it had to create could get expensive. Programmers, eh. The only people who can have a heated debate about how many spaces (or tabs !) you should indent by... :D

Tony Hopkinson
Tony Hopkinson

It probably would have been clearer. You just know some sales twonk had something to do with that comiler design. More than two chars should have thrown up a syntax error, the way that worked is a feature no real programmer would have thought of. Comments as special code was a regular trick back then. Still is in webpages... Can't remember which one it was, but one let you colour code your source by inserting screen colour codes in REM statements.

Slayer_
Slayer_

Or explain the reasoning behind something odd. Or, occasionally, to denote the end of an if statement or a loop if there is heavy embedding. I restate the conditions of the If statement in a comment at the end. I tend to get comment heavy when I get condition happy. But I haven't heard anyone complain when they read my code.

Tony Hopkinson
Tony Hopkinson

but what it the purpose of PrintAllInvoices... Now if there's a bit more to it than the name implies, by all means add // Do Print and if successful, mark batch as done and email requester. Aside from nipplehead comments like the one you quoted and this is where intent can go badly wrong if you detail the logic. N = N - 1 'increment N by 1 Guaranteed to make you or the next guy looking for bug, wonderif you didn't do what you said or didn't say what you did. The best comments are the ones where you look at the code and you can't remember why you wrote it like that. We aren't going to get stuck on why we used for when we wanted a loop. Something like this though MyObject.Property = 1; MyObject.Property = 0; .... It's even worse aftre someone fixes MyObject so it fires a state change event on instantiation instead of only when Property changes value... Every one thinks you are some sort of arse... :( I think the real pointer to why the comment everything boys are wrong, (not saying you two are !) just count the number of times a comment really helped, compared to the number it was no use whatsovere or where it made you question your own sanity, never mind the berk who wrote it. Here'a good one from my past, what comment would you use to make this comprehensible if(!Belly_Banded.IsNotBellyBanded != 'Y') I sh*t you not.

DelbertPGH
DelbertPGH

My first job in programming was civil engineering programs to run on an Apple IIe. The boss splurged and got the deluxe, fully-equipped version: not 32k, not 48k, but 64 kilobytes of RAM! If you put comments in your code, you used up RAM. In fact, each variable name consumed RAM, so if you used "self documenting" variable names, you would quickly run out of memory. And, actually, only the first two letters of a variable name actually designated anything: PURCHASES and PUNCH both mapped to the same memory location as PU. I had a utility that would count the instance of variable names, and allow you to swap them out for shorter strings. I'd find out which 26 variables were used most often, and give them the names A through Z. And of course, there wasn't a single comment.

Slayer_
Slayer_

You could use comments. It had a commenting character ("//") but if you compiled to EXE, the comments would attempt to get executed.

access
access

I agree, too many people put in superfluous comments, like this: N = N + 1 'increment N by 1 I was talking about putting in comments that describe the logic (or at least the intent) of a block of code. So when I look at it 6 mos from now (or 2 years), I won't have to ask "What was I doing here, and why?"!

Tony Hopkinson
Tony Hopkinson

Making the code comprehensible or littering the code with useless obvious and soon to be misleading drivel. Shoot pople who write unreadable code, by all means, mow the b'stards flat. Comments don't necessarily make code readable though and they will double your maintenance burden, unless you leave the old ones in.... In interpreted languages such as VBA they also add a parsing overhead... Write readable code, and annotate when you fail. Not write unreadable code and then annotate if you remember and you've got the spare time. Develop the habit and then when your boss decides that commenting the code can wait, because they have something with more priority, at least you've got some clues as to what it does. Oh and lets be clear about this, sometimes why cannot be coded, so it should be documented. Purpose statements are not comments, they just have // before them, or better still /// Thinks like jargon busting should be done with an external document. I keep trying to bring back data dictionaries.

Slayer_
Slayer_

Forgot about those. Been long time since I touched C++. (Too long :( ) Now code em all on one line, and mix a few of them together, to really screw with your code. Throw some substring functions in there as well, maybe a few string searches based on the incrementing A variable, incremented each time it is called.

Tony Hopkinson
Tony Hopkinson

A = A - 2; Damn my fingers are tired, it's a lot of typing.....

Slayer_
Slayer_

I am not familiar with that one, whats it mean?

Tony Hopkinson
Tony Hopkinson

I haven't seen one language feature that promotes terseness, that aids readbility. Language designers, there's a clue in there somewhere if you think about it. Did we need A -=2; or int C=D=E=9; Don't even mention that f'ing With statement.

Editor's Picks