VBA Best Practices That I’ll Never Do

I don’t know if these are really “best practices” or if I’m just making them up. I know I heard them somewhere, but that doesn’t make them universal by any means. Also, I generally agree with all of these best practices, although I have no intention of changing my ways.

Best Practice:Never use lowercase ‘i’ or lowercase ‘l’ as variables.

Reasoning:Both look too much like the number 1 and makes the code very difficult to debug.

Justification:I’ve been bitten by the 1 vs. i problem myself. But i, j, k will always be my For Next control variables. I’ve accepted the fact that I’m never going to change that.

Best Practice:Don’t reuse variables

Reasoning:When you reuse variables, you have to be careful to reinitialize the variable and you won’t be as careful as you think. Also, how could you have a well-named variable that you use in two places?

Justification:If I’m coding two loops in a procedure, I reuse the control variable. I think that’s a reasonable exception because it’s a control variable and it will initialize itself in the loop. However, I’m probably also guilty of using a variable like lCnt in two places. That’s one of those generically named variables that should really identify what it’s counting. If it was lWorkbookCnt, then it would be a lot harder to justify reusing it later when counting worksheets.

Best Practice:Align data types in Dim statements. Like

Reasoning:The block of declared variables is easier to read.

Justification:I can’t imagine a worse fate than having to adjust all of my Dim statements when I add a new variable whose name is longer than the rest.

Best Practice:Declare each variable on its own line

Reasoning:It prevents the problem of not assigning a data type. A variable declaration like

is really declaring sFile as a Variant (because omitted data types are Variants) and sPath as a String. By giving each variable its own line, you are less likely to make that mistake.

Justification:Bah. I’m a careful programmer. Not unusually careful, just averagely careful, and I don’t make that mistake. The only people who make that mistake are people that don’t understand that you have to declare a data type for every variable on the same line. Once you know how it works, it’s just not a mistake you’re going to make. Most of my variables are on their own line, but closely related variables often end up sharing a line. In my code you’ll often see

(although let’s hope I don’t have three nested For Each loop too often)

Best Practice:Comment, comment, comment

Reasoning:You and everyone else that reads your code will find it easier to understand if you include comments.

Justification:Most comments suck. If 25% of your code lines are comments, you and everyone else that reads your code will skip over them anyway (the VBE makes them a different color so that it’s super easy to skip over them). This best practice stems from the fact that most people don’t comment enough. In reality, most people don’t write readable enough code, which is far superior to commenting. When I’m writing code to teach a beginner, I comment every line. But teaching is different. If you feel you need a comment, consider rewriting your code. If you consider it and still need a comment, you probably really need a comment.

Best Practice:Avoid Exit For and Exit Do in loops

Reasoning:Exit For and Exit Do are just like GoTo. And we all know that GoTo is the spawn of Satan.

Justification:I abhor GoTo so deeply that I want to be on the other side of this argument. But I can’t justify continuing a loop when I’ve found what I need. Here’s a crappy example of what I mean.

Of course I would never hardcode a string or have domain specific data in a variable name, but you get the point. The main differences between Exit For/Do and GoTo is that the Exit statements only flow in one direction (only forward, while GoTo can go forward or backward) and the Exit statement flow to a specific place (the line below Next or Loop, while GoTo can go wherever you choose).

Best Practice:Use named parameters

Reasoning:Named parameters make the code more readable and don’t enforce an order to the parameters, reducing bugs.

Justification:I hate named arguments. They just clutter everything up and make it ugly. I want my code to be pretty. I don’t absolutely value prettiness over readability, but I don’t absolutely discount it either. I don’t need to write code for a complete beginner to read (unless I know that’s my audience). If you are reading my code, you should have a pretty good working knowledge of the Find method and what it’s arguments are. If you’re reading this post and don’t have the exact order of the parameters memorized, you probably could figure it out by the constants used. And failing that, you could just look it up. Which is what you should do if you don’t know. Don’t write code for your non-programmer supervisor to read – he won’t get it even with named arguments and copious comments.

If there is a best practice you knowingly avoid, leave a comment. You don’t have to justify it. There is no justification for using ‘i’ as a variable, but I still do it.

Posted in VBA

76 thoughts on “VBA Best Practices That I’ll Never Do

  1. Thanks this is a great article and good refresher on some practices I’m guilty of.

    I recently got on the habit of using Option Explicit and making sure to compile the code before distributing it. Would you consider option explicit a best practice? Are there instances when it should not be used?

    What do you use in place of exit for and goto for loops? Do you use a do until loop?

  2. Option Explicit. Allways. Period.

    I also re-use control variables, but I am careful with that.

    I’d like to add a best practise: Never use variable names that coincide with existing object names:

    Dim Sheet As Worksheet
    For Each Sheet In Worksheets

  3. Error handling- I handle only the most obvious and destructive errors the dunderhead users can come up with. The rest I tell them to click debug, take a screenshot and send it to me. I think it is alot more productive then trying to think of every obscure error and how to handle it. I still feel guilty sometimes though.

  4. “Avoid Exit For and Exit Do in loops” being a ‘best practice’ is a new one on me. Really? If that’s the case then I’ve been (and will continue to be) a very naughty boy.

  5. Yes, Option Explicit 100% of the time.

    I use For Each, For Next, and Do Loop loops. If I can shortcut it with an Exit statement, I do, otherwise they run their course.

    I compile every few seconds. Before I distribute, I use CodeCleaner.

  6. Best practice I don’t follow: always qualify the Value property of Range object.
    However, if you don’t qualify it, you pass an object instead a Variant-Value data type and might get an error in a statement like Sheets(Range(“A1”)).Select

  7. “Code Complete” says to write the comments first for the flow of you program and then make the program. For complex programs I do this but for simple programs I don’t, and sometimes I don’t just because I get lazy.

    “Professional Excel Development” says to use type naming for your variables (like iInteger, lLong, strString, etc.) I tend not to for control loop variables. I just use i, j, k also, but I have tried to use actual names with my control variables, but most of the time it doesn’t make sense.

    I’ll add a “Best Practice” for Excel. Have a function for getting returning the range values. It’s the worst to expect a 2D array and get a string or double, best just to role your own function that will always return the 2D variable and only use that for any time you get values from a sheet.

  8. Nice article Dick. Interesting topic.

    Regarding commenting. Most people get confused when they are told “Alway comment you code”. They write “in words” what the code does. This is seldom necessary, since most “well written code” is easy to understand. But on the contrary, from time to time I find my self wasting time trying to find out what is going on (also in my own code). Then an extra comment would have been in place.

    I’ll say, keep it short (nobody reads looong comments), and alway write why(!) if something is solved in a not ordinary way. And perhaps what(!), if code is written very compact or if techniques not used elsewhere in the project is used. And give a short description right above all non-trivial functions and subs. A good name can be self-explaining, but often the context needs to be described. And alway(!) remember to keep comments updated. Outdated comments are far worse than no comments.

  9. What about the “Always destroy your objects”-recommendation?

    I think I recall that some (very few) objects should be destroyed, like new created Workbook, but otherwise, the garbage collector should take care of all clean up work. Right?

  10. I don’t have an example right on the tops of my fingers, but say you write a Class that creates and contain a workbook object, then when that class is destroyed, the workbook object is “hanging”, if you don’t destroy the Workbook in the Class_Terminate procedure. Or perhaps I’m thinking of something else.

  11. Unit tests are the best comments you can leave a developer or your future self.

  12. I like my loop counter variables named like iCount, iRow, iChoose, you get the idea.

  13. I don’t use type prefixes on variables. Nearly always just clutter – do I really want 17 variables all starting with “str”? am I likely to be unclear that ‘wscount’ is an Integer? If I do need to clarify – say between a Range and its Value, then I’d indicate this in the name: rng, rngval, or whatever.

    Comments: No to inline ‘pseudocode’ comments – as you say, write clearer code. For big If clauses, I do quite often put a comment on the Else. A big Yes for comments giving a routine’s Preconditions (i.e. at the start of the routine) – e.g. worksheet X exists, cell Y is non-empty, i > 0, etc, etc. Postconditions too, in some cases – good for writing test code.

  14. Isn’t there a “best practice” for having a single exit point? I don’t follow that one all the time.

    Actually, I think a lot of these “best” practices should be considered more like what you’d call guidelines (http://youtu.be/b6kgS_AwuH0) – good for the inexperienced to follow until they understand the underlying intention and know when there’s benefit to be had from deviation.

    The biggest problem with one-letter variable names is when you later decide to make them meaningful. Without a rename-capable IDE, find-and-replace on “i” doesn’t work too well…

    Regarding variable type declaration alignment: if there are so many variables that you feel benefit from aligning, then your routine is probably too large and should be broken up until there are few enough variables that it doesn’t matter. A similar argument might be made for the one-variable-declaration-pre-line thing. (Although I’ll do that from time to time and I don’t hate myself for it.)

    I use Exit For/Do when I consider them more expressive than re-writing as a Do..Loop While/Until. But then again I’ll also use Go To in certain specific situations, almost entirely guard clauses where I need a single exit point.

  15. I do use comments a lot. I try to make the comments say WHY the code is doing this and/or a high-level description of what a block of code should be doing if the block of code has not been refactored into a sub/function with a meaningful name

    I also use Exit For and Exit Do all the time

    I use GoTo Cleanup to maintain a single exit point that does, well , Cleanup (for example maintains the call stack if I am using one)

    I try to avoid public variables except fot constants and other initialisation variables (just spent a horrendous time unscrambling 5KLOC where almost every variable was declared as public)

  16. Exiting a for/next loop early saves time, especially if you’re searching a very large array. Reminds me of the question “Why is it when you’re looking for something you always find it in the last place you look?”…. Why keep looking WHEN YOU FOUND IT!

  17. Awesome comments.

    I like the function-that-returns-a-2d-array idea. I can’t say it’s ever bitten me, but I guess it could have.

    The only reason I still prefix most variables is so I can use the variable name I want. I can’t call a variable Input, but I can call it sInput or lInput. That’s probably not a very good reason to do it, but I still do.

    All the advice about good commenting (say ‘why’, keep them up-to-date, etc.) is the same as the reasons I don’t comment (they don’t say ‘why’, they’re not kept up-to-date). To be fair, I have at least a dozen comments in every project. I’m just very frugal with them (probably too frugal).

    I do sometimes start by writing high level comments to get the flow down, but they always get deleted because they’re too obvious and never indented properly.

    Unit tests = comments. That’s the best thing I’ve ever heard.

    Is late binding a best practice? Yeah, I never do that so I can add that to my list of things I’ll never do. When the VBE gets updated so I can code early and ship late automatically, I’ll ship late. Otherwise it’s a pain in the ass.

  18. I also use i, j, k, l as loop control variables, and short-range or intermediate result variables. Very rarely does it cause me a problem. Also use x, y, z for short-range or intermediate string variables. Meaningful names I only use for meaningful data.

    If you want to convert a meaningless name to a meaningful name, all you have to do is remove the DIM statement, and the compiler will find all places you’ve used it. Assuming, of course, that you’ve used Option Explicit, which I do, always. That’s one best practice that I adhere to religiously.

    Exit from Do and For, absolutely. Staying in a loop after you’ve accomplished what you entered the loop for is just stupid. There is nothing confusing about an Exit statement. It goes to exactly one precisely defined place, under precisely defined conditions. It’s no more confusing than a GoSub, or function/subroutine call.

    Destroy your object – I do it, although it shouldn’t be necessary. The run-time system should take care of it. I think it’s a holdover from days when OO code was new and quite buggy. Any decent language nowadays should deal with that as a matter of course. I don’t test variables after changing their values, to be sure that the value really changed. Nor do I initialize variables. Strings should come to life empty, numbers should come to life as zero, Booleans as false, objects as nothing and so on. If you can’t trust the language to do what specs say it will do, you shouldn’t use it.

    Variable prefaces – no. It’s extraneous garbage, and rarely is it necessary when examining code to constantly have that shoved in your face. Usually it’s obvious from context what the data is like, and if it’s both unobvious AND you need to know it for some reason, a glance at the Dim statement will clear it up. All prefaces do is make the code harder to read.

    Named parameters – sometimes. Not for simple functions, like Instr, but usually for home-brewed routines, or calls to complex objects. I find it much more readable than a series of commas for the unused parameters.

    Error handling – only when absolutely necessary. I much prefer to test and make a decision based on the results, than to try something, let the system maybe throw an error and then recover from it. Not always is that possible, but I use error handling only as a last resort, when I can’t figure out any other way to do it.

    Comments – minimally, usually after going back to something and having a little trouble reconstructing the logic. Usually only a line or two regarding the general purpose of a larger block, then a few words here and there if I find myself doing something I suspect later will look obscure.

    Multiple variable declarations on one line – all the time. Multiple lines only if there start to be too many to be clear, maybe all unspecified objects on one line, strings on one line, query/recordset/table variable together if they all address the same dataset. Stringing out one variable on each line just makes code longer and harder to read. I also use type declaration characters whenever possible – again, much more compact and readable.

    Align data types in Dim statements – hell, no! Even if I did use one line per variable, I wouldn’t do that. It’s pointless, and a lot of extra work if you suddenly have a longer name.

  19. Great topic

    I like clean readable code and so I usually handle the exceptions on single lines – I know they are exceptions, and not the main logic, so I don’t want them overloading (visually) the main logic. As such I will even code several statements in the same line with the “:” between just to get the exception onto a single line. For example,

    IF unexpected condition THEN MsgBox “user message” : Goto CleanUp

    I have worse examples but this is my most common.

  20. Professional Excel Development recommend early binding. Well I do both, but early binding whenever possible because I find it easy both on the short and the long run.

    The commenting recommendation is good I think. When I get my hands on an old (or new) project I never complain about over-commenting. Many comments are quickly ignored I agree, but often I complain about missing comments and most of time like “why this solution” and “what is the meaning behind this”. Things that are intuitive to you might be very hard to understand for less skilled and even for more skilled professionals, maybe because it’s not “well codes”.

    So I’d definitely recommend commenting, no matter what level you think you are on. A single comment can potentially safe lots of time. If not, it can often be ignored quickly. But it’s a hard discipline. It’s a discipline that calls for discipline as well. I’ve reviewed lots and lots of code, and read lots of bad comments. Be consistent. Keep it short and precise. Describe why more often than what. And when in doubt, place an extra comment.

    I think commenting is underestimated. It’s the most powerful way to document your work I think. The key to succes is to learn why and what to comment, and then produce short and precise comment.

    That’s my religious view :)

  21. @Dick, this guy seems to think so :) :

    “To get the most out of the VBA development environment and still write robust code, you should write the code early bound, but change it to late bound before distributing it. Even if you write it for personal use only, it makes sense to convert it to late bound. Someday you will have a different computer or send it to your brother and it won’t work because they will have an earlier version. If you’re a die-hard procrastinator like me, you will be cursing yourself for not converting to late bound sooner.”

  22. 1. Define a consistent naming convention and stick with it. If you’re working with someone else on the same code, you may have to write it up, and you may have to be an assh0le about enforcement.

    1.a. Never ever use variable names that look like keywords (as JKP mentioned).

    1.b. Meaningful prefixes are good.

    2. Use Exit For/Do. Exit Do is more readable and understandable than Do While and Do Until.

    3. GoTo ExitProcedure with the label ExitProcedure that cleans up and exits all the time is an acceptable use of GoTo.

    4. Reuse of control variables is fine. They are reset at the beginning of the next For Next loop. But just for fun, why not reset them explicitly after using them (i=0, Set ws = Nothing, etc.).

    5. Skip lines between procedures. Don’t rely on the line placed by the IDE, because it isn’t there if you print the code or paste it into another editor (yay, Notepad++).

    6. Minimize use of global variables and even module-level variables (except in classes).

    7. Combining code onto a single line using : saves lines but adds to reading & comprehension time.

    8. Declaring multiple variables on the same line should be avoided except for closely related variables of the same type.

    9. Don’t use public variables in classes. Use properties instead.

    … and many more.

  23. Nice post. Regarding Exit For and Exit Do, see if you can refactor your code to use a Do While loop instead. That would eliminate the need for “Exit Do”-type statements, at least some of the time.

    Regarding named parameters, the only use for this I can see is that you can rearrange the order of parameters in your function calls, which you alluded to in your comment.

  24. Named parameters are useful, and often clearer. Which of the followong would you say is more readable?

    oDoc.open(“abc.doc”, , , , , , , , , , False, , , , , , , True)


    oDoc.Open(Filename:=”abc.doc”, ReadOnly:=False, ConvertOnInput:=True)

    Trying to count all the commas and keep their positions straight can be a headache. With named parameters, it’s clear right in the call what information you’re passing and what the object or routine will do with it.

  25. I always preface my variables with a prefix, str int bool etc. It’s simple and prevents the problem mentioned above about calling your variable the same name as a keyword and makes search and replace ops easy – not like the ‘i’ variable name mentioned previously. Also, it reduces comments – why comment on a flag if the variable has bool prefix and the rest of the name makes it obvious what the flag is for.
    I find I rarely have counters but use intCount when I do – I only use i,j,k with arrays.
    Best practice is to use each variable once and give it a good name. I don’t do that for objects. For instance all my worksheets are called wks – just the prefix. All my recordsets are called rs. And I reuse the same variable many times in the same sub (also supposedly bad, but saves on declarations and I find less confusing) If I need two wks variables at the same time, I frequently use wks and wks2 – very bad, but it’s very seldom the distinction is important to understanding the code – when it is I will give one a full name.
    On comments, I am in the ‘use sparingly’ group. Good variable names will often remove the need for comments – so long variable/function/sub names ok by me. Auto complete make them easy to type anyway. I comment for unusual circumstances not much more.
    One best practice I recommend – have mixed case variable names but always type them with all lower case – makes typos immediately leap out at you.
    You are also supposed to have a comment for each parameter. I’m bad there too, but why bother with a parameter called wks – I am not adding any info I don’t already know. I only have comments for unusual params. Non object params usually have a good long name with a preix – no comment required. I always comment if I am using byref for a non-object, which is rare. I always declare byref and byval and rarely use byref for non-objects – which is a best practice I follow religiously.
    I always exit at the same point, almost always use error handling – only exceptions are simple functions, especially those functions relying on an error.

  26. Don’t drink and code. Which I often fail, as the deepening clink of my whiskey bottle will attest to as I put it down between lines will attest to.

  27. Create a code library (xla, xlam) and store commonly used routines there.
    This requires discipline and some extra time, but wow, what a huge difference it can make in reliability.
    I hate to re-code the same functionality for every new project….so I bring along my code lib, make a reference to it and viola….
    it’s all in there…string routines, pivot table routines, data table routines, etc.

  28. Here something I try and stick by. What ever I do, do it consistently.

    Best practice comes and goes, is partly fashion, its sometimes OTT, sometimes slack, sometimes style, sometimes true or important, sometimes not so much. But I find it hard enough just to make sure that in any project of size I’m using the same conventions etc.

    I comment as much as I can, even if its obvious, and i re-factor as much and as often as i can.

  29. In VBA Editor, I try to remember to close all open modules before saving. I find it’s much nicer to have a pristine session the next time through. This can be automated if you’re inclined.

    Taking it a step further, there’s a Stack Overflow post somewhere by an emacs developer who imposts all modules on workbook open and exports them all on workbook close in order to to facilitate integration with version control.

  30. Ribbon programming:

    [1] I keep all event handlers in a module named “Ribbon” and explicitly reference the module in the XML callbacks.

    [2] I noticed that some Microsoft samples name the event handlers themselves using the same “id_eventName” conventions as ordinary event handlers do. IMO this is helpful for readability and for having a naming convention rule that’s consistent and easy to remember, so I’ve adopted that practice also.

  31. File I/O

    Sometimes we need to interact with the file system to do folder management and file I/O. I use the “Microsoft Scripting Runtime” FileSystemObject instead of any VBA features because it’s more modern, organized, readable, and enables intellisense. The object model also aligns well with the .NET library. VB(A)’s built-in file I/O facilities are arcane and obsolete by comparison. This choice does create a dependency between the workbook and an ActiveX DLL, but it’s worth it. (I believe there is a 64-bit version of this component as well. I’ve seen the file the 64-bit area but have not tried to use it.)

  32. w.r.t. the OP:

    i,j,k are fine. These names for the index variable are age-old, and learning them is a rite of passage. Best to just accept that. But truth-be-told I don’t care for index loops at all, let alone nested ones (never, ever got to “k”), so the issue doesn’t arise much for me.

    For Each is just so much cleaner. I recommend using it wherever possible. I will even go so far as to construct Array()s to facilitate the use of For Each. Silly example:
    Dim x
    For Each x in Array(10, “hello”, 30)

    Useful example (where obj1,obj2,obj3 are of different classes that each implement ISomething):
    dim x as ISomething
    For Each x In Array(obj1, obj2, obj3)

  33. @Stephen: +1 for On Error Resume Next. For me the shorter the routine, the handier it is.

  34. I agree with your “Named Parameters” part. It’s useful if you want have a big, hairy sub/func that requires tons of optional parameters, and you only want to pass it two instead of putting tons of commas


    Ugly = SomeFunc(,,,”Blah”,,,,True)
    Nice = SomeFunc(MyName:=”Blah”,Really:=True)

    Looks a bit better, and folks can easily tell which optional parameters you’re passing. However, for small proc’s, since most folks are coding in the VBA IDE with intellisense, naming all paramters is just ridiculous. That’s what intellisense is for.

    For re-using var’s, I will do that only within the scope of my procedure. EG: I may have a generic var “s” string that I first use to load a filename into, then use to load the sql into from that file, then use to store a msgbox message to the end user. If I have a quick-n-dirty proc, I will re-use that “s” string. In the long-run, though, I should probably break that proc up into smaller, modular proc’s. The temptation is to have a “global” string var that all the sub-procs can use, and that’s where you can get bit in the rear. If each proc is doing something independantly with a string, I’ll just make each proc have it’s own string var. I learned this the hard way when I thought I’d be clever and have some global vars lots of little procs could use. Then I’m sitting there wondering why my loop in one proc is hosing up. It’s b/c another proc that got called also uses that var and switched the value mid-stream. Oops!

  35. @Stephen …

    On Error Resume Next is great for really small, useful functions where you’re doing a throw-away action or turning a “test” of something into a boolean true/false.

    eg1: I use VBA/ADO via MS Access to make connections to SQL Servers around the company and pull in data. (Why? b/c I’m not in the IS department, so as a sales/marketing reporting analyst they won’t give me access to play with the big-boy toys. So, I have to make due with what I have). It’s useful to have a function like:

    Sub Close_Conn(conn As Connection)
    On Error Resume Next 'if conn already closed or nothing, ignore error
    Set conn = Nothing
    End Sub

    Another situation is that in Excel almost all of my functions / subs require a workbook & worksheet to be open. Instead of kicking off each func/sub with its own On Error to trap wb/ws not open, I just made a quickie Boolean test function to do so.

    Function ActiveWindow_Visible() As Boolean
    ' if no wb or ws open, returns false in order to exit out of subs that work on wb/ws
    On Error Resume Next
    ActiveWindow_Visible = ActiveWindow.Visible
    End Function

    This lets me quickly test and exit/keep going as needed in those func/subs:

    Function SQL_Clean_Excel_Paste(r As Range)
    ' when pasting in sql comments to excel it likes to convert -- to =--
    ' this causes #NAME? error, so find "=--" and replace with "'--"
    If Not ActiveWindow_Visible Then Exit Function
    r.Replace "=--", "'--"
    End Function

  36. On really large projects I will use a Module named “_Notes”
    ‘Option explicit
    Option Private Module
    #If False Then ‘Prevent compiling

    ‘Use the Declarations Dropdown for navigation

    Sub About_This_Project()

    ‘This section of comments reads like the sales pitch on many web sites
    End Sub

    Sub Program_Flow()

    End Sub

    Sub About_The_Globals_Module()

    ‘Variable X is set in Module1.initialize and used by Module2.DoSomething
    ‘Constant Y is used by Module2.SomethingElse, Module3.YetAnotherSub, and etc.
    End Sub

    Sub YetMoreInformation()

    End Sub

    #End If
    ‘End of Module

    There is really only 1 rule for naming conventions: Have one and stick to it.

    Option Base 0 :)

  37. I’d be interested to hear what folk think about using On Error Resume Next to test for existance of something vs iterating through the collection and looking for it.

    Is iterating through a collection vs testing directly for its existance wise or wasteful? e.g. is this:

    …any more or less preferable to this:

    Does it really matter if a ‘Testing’ function uses On Error Resume Next?

    That said, I’ve seen a few such functions where they don’t clear the error with either an err.clear or an On Error Goto 0 at the end of the test, meaning the error number is still raised when flow returns to the calling procedure. Which is a problem if your error handlers are like mine:

    If err.number 0 then…

  38. Hey Jeff,

    I guess it depends on what you’re iterating through. If, for an extreme example, you had a workbook with thousands of sheets and the sheet you were testing for was one of the last sheets in the workbook, then you could be waiting a bit for it to be found through iteration. Obviously, it’s down to the individual programmer’s savvy whether or not they clear the error (or reset the handler) but assuming (haha) that they do then I’d tend to go for the direct approach and use your first example.

    But then I’m always thinking that the workbooks that I program will be scaled up at some point and try to avoid having to rework code that I can barely understand (even though I’ve written it)!

  39. That’s why I prefer:

  40. Hi Jon. Oftentimes I iterate through the names collection quite a bit, which is what prompted my comment as there can be quite a few names in an application that makes heavy use of the grid as presentation layer and VBA as the engine, and I might have to check a lot of names quite a few times over the course of a session. Just wanted to make sure there was no downside before I switch to either using something like snb suggests or a generic function such as Jan Karel’s IsIn function at http://www.jkp-ads.com/Articles/buildexceladdin02.asp

  41. I think it’s very strange that a collection doesn’t contian an ‘exists’ method like the Dictionary has.
    Dependent of how often you need to test it, it might be worthwhile to use the dictionary library:

  42. I like snb’s dictionary.exists suggestion. I much prefer using a solution that doesn’t require an error handler.

    It’s a bizarre thing, the collection item. So much potential, I guess like a lot of things in vba. I suppose, if there weren’t as many items with so much potential, then we may have learned all of what we have!!

  43. Interesting read. I’ve got one, that interestingly I don’t think anyone has mentioned.

    I always explicitly declare Sub and Function parameters with ByVal or ByRef.

    Two reasons for this.
    1) The default parameter setting is ByVal in VB, but ByRef in VBA, so avoids confusion.
    2) Makes understanding the code more straight-forward for others.

  44. I am not an expert, but I do not understand why everyone seems to agree that Option Explicit is such a good practice. It seems insane to me to have to go to the top of my program every time I write an assignment statement. Half the time, new variables I define are just for testing and debugging purposes. I have never once used Option Explicit, and I never once will.

  45. Very nice never do and best practices with VBA programing. He one nothing I will never always do is to declare too many Public variables. Best practice to insert error handlers in every function

  46. And you’re telling us you’re lazy and sloppy why?

    Some of us are disgusted with mediocre code that we have cleanup. Why are you promoting that?

  47. “most people don’t write readable enough code, which is far superior to commenting”

    – Based on this article, you’re one of those people.

  48. “I abhor GoTo so deeply”

    Based on the rest of this article, i’m shocked to hear you say that. Just because GoTo can be abused, that doesn’t mean it can’t be used judiciously. It doesn’t have to go backward. GoTo is one of the cardinal sins that i think has usefulness. A seasoned programmer follows her judgement, not arbitrary rules.

    On Error GoTo …

  49. “I don’t need to write code for a complete beginner”
    – Someone who’s never seen your code is a complete beginner with your code. Readable code isn’t about teaching new programmers, it’s about helping people who aren’t familiar with your code.

    I only use named args when a proc has a crap-ton of optional parameters, and i just want the param near the end of the list. This isn’t pretty:

    DoSomething ,,,,,,,,,,,,,,,,Param

  50. “If you’re reading this post and don’t have the exact order of the parameters memorized, you probably could figure it out by the constants used. And failing that, you could just look it up”

    My first go-to (1o1*) is called “Auto Quick Info” in the IDE Options.


  51. “Never use variable names that coincide with existing object names”

    i do that sometimes. Consider that there are a crap-ton of functions, especially if you have many ref’s. It’s not realistic to ALWAYS avoid using a fx name that some lib you referenced isn’t also using. Shell32 and Office libs both have something called “Folders” and something called “Shell”. Excel and Office both have something called “Execute”. Context, man, context. Qualify when necessary. Rely on the compiler.

  52. ““Never use variable names that coincide with existing object names”

    One situation where this is fine is when you’re extending a built-in object. I once created a class called MyPivotTable, which provided some additional properties and methods for pivot tables. i wanted to make it seamless to use MyPivotTable as a plugin replacement for the built-in pivottable object, with all the same methods and properties of the built-in object. That’s achieved by setting the actual pivottable as the default Get property of the class.


  53. @David, APRIL 2, 2019 AT 12:13 PM
    “It seems insane to me to have to go to the top of my program every time I write an assignment statement.”

    Declaring all your variables at the top of your program isn’t the most-maintainable and easiest place to declare variables.

    As described in the superb Code Complete, published in 1993 by Steve McConnell, it’s best to declare your variable exactly where you’re using them — not at the top of the program. Then you don’t have to go to the top of your program every time you declare a variable, and you don’t have to go searching if you want to delete the variable.

    i hope by “top of my program” you don’t mean global variables. Globals are high-risk and should be avoided when possible.

    Sub Demo()
    Dim sName as String
    sName = Get_Name()
    ‘ do stuff with name

    Dim lAge as Long
    lAge = Get_Age()
    ‘ do stuff with age
    End Sub


  54. @David, APRIL 2, 2019 AT 12:13 PM

    i hope by “top of my program” you’re not writing massive, multipage procedures. Very long procedures are a nightmare to troubleshoot, debug, and understand. When procedures are extremely short and single purpose, then it’s much easier to find and fix bugs, and also those procs can be re-used.

  55. @Thomas Ellebæk, JULY 2, 2013 AT 4:58 PM
    “Most people get confused when they are told “Alway comment you code”. They write “in words” what the code does. This is seldom necessary.”

    100%. A comment that says “Loop through the array” is completely useless — we can see the code is looping through an array! A meaningful comment, for the exact same code, would say something like “Update all the user-accounts.”

  56. @Chris R, JULY 2, 2013 AT 9:16 AM
    “dunderhead users can come up with. The rest I tell them to click debug, take a screenshot and send it to me.”

    Very unprofessional. Users aren’t dunderheads just because they aren’t programmers. Your bugs aren’t their fault. Our job as programmers is to make their lives easier. Professional code should back out of errors gracefully, with friendly error messages, and log the details of the error in a text file or other location accessible to the dunderhead programmer who’s bad code caused the error.

  57. jon, JULY 2, 2013 AT 4:24 PM
    “for control loop variables. I just use i, j, k also, but I have tried to use actual names with my control variables, but most of the time it doesn’t make sense.”

    i, j, k doesn’t make sense. Your code isn’t looping on a letter, it’s looping on a thing, such as “user”, or “account”, or “product”, or “file”, or “folder”. Do you know what thing you are looping on? Because a future coder who has to fix your code might not, at first glance. Understanding your code shouldn’t be painful for other people.

  58. @Thomas Ellebæk, JULY 2, 2013 AT 5:04 PM
    “What about the “Always destroy your objects”-recommendation? (very few) objects should be destroyed, like new created Workbook”

    i always destroy all objects. It seems you’re aware of the problem of phantom workbooks in the IDE after the workbook was closed, due to the variable not getting destroyed. How do we know that doesn’t cause other kinds of phantom objects? I always destroy objects as a habit, and i think it helps keep my projects quick and lean. The easy rule-of-thumb is: be aware of anyplace you use the Set keyword. At the end of the proc, make sure you Set it to Nothing.

  59. @Mitranim, JULY 3, 2013 AT 12:25 AM
    “I like my loop counter variables named like iCount, iRow, iChoose, you get the idea.”

    Great naming habit!, but those sound like Int’s. Long’s use less memory than Int’s, so i always use Long instead of Int.

  60. @Doug Glancy, JULY 3, 2013 AT 7:26 AM
    “I will admit that I don’t always late bind, even when I know I should.”

    Why should you? i always early bind. I like intellisense and auto code-complete.

  61. @Doug Glancy, JULY 3, 2013 AT 4:39 PM
    “you should write the code early bound, but change it to late bound before distributing it. Even if you write it for personal use only, it makes sense to convert it to late bound. Someday you will have a different computer or send it to your brother and it won’t work because they will have an earlier version”

    In my 25 years of pro VBA coding, i always early-bind, never convert to late-bound, and have never had a problem. When Microsoft rolls out new versions, they adhere to the old function signatures because they are a professional organization. If they have to, they’ll create a new-named fx to replace the old one, and keep the old fx as-is.

  62. @Jon Peltier, JULY 4, 2013 AT 9:13 AM
    “Don’t use public variables in classes. Use properties instead.”

    i agree with most of your comments, but not that one. What’s to be gained from a property which does nothing but return the module-level variable? A property should be use only when some kind of PROCESSING is required, or arguments to pass. Otherwise it’s pointless. The calling dot-syntax is identical with public variables. In a class which is instantiated, those public variables will NOT behave like public variables in a module — they will only be available in the context of that class instance. They won’t be globally visible.

  63. @JP, JULY 4, 2013 AT 7:28 PM
    “Regarding Exit For and Exit Do, see if you can refactor your code to use a Do While loop instead. That would eliminate the need for “Exit Do”-type statements”

    And would create the need to declare and increment an index. The whole point of For is that it can enumerate an object for you, without you having to use an index. Do..While throws out the benefits of enumeration.

  64. @Roy MacLean, JULY 3, 2013 AT 1:59 AM
    “do I really want 17 variables all starting with “str”?”

    Why the heck not?

    Prefixes help me simplify my code. I can use:
    sBook = workbook name
    0Book = workbook object
    lBook = workbook index

    Systematic and self-documenting, not “clutter”.

  65. @WGB, JULY 9, 2013 AT 5:20 PM
    “I reuse the same variable many times in the same sub”
    If your subs contain so much processing that you’re reusing variables, then your subs are prolly too long. Subs are easiest to debug, understand, and upgrade when simple, short, and single-purpose.

    “I frequently use wks and wks2 – very bad, but it’s very seldom the distinction is important to understanding the code”
    Maybe not for the next person.

    “have mixed case variable names but always type them with all lower case – makes typos immediately leap out at you.”
    Excellent, me too.

    “You are also supposed to have a comment for each parameter. I’m bad there too, but why bother with a parameter called wks”
    That means “weeks”, right? I would use “shtExpenses”. I think you can tell that doesn’t mean “weeks”.

    “I always declare byref and byval and rarely use byref”
    Wow, i’m impressed. Finally a best practice that i DON’T do :D

  66. @M Simms, JULY 11, 2013 AT 6:53 AM
    “viola…. it’s all in there…string routines”

    How about violins? Cellos? Just strings tho’? No percussion?

    I’m trying to get my whole company to use my lib.

  67. @steve j, JULY 15, 2013 AT 12:32 PM
    “imports all modules on workbook open and exports them all on workbook close in order to to facilitate integration with version control.”

    Impressive! Yeah, VBA version-control is basically non-existent.

  68. @Stephen Bullen, JULY 17, 2013 AT 7:27 AM
    “My guilty secret is frequent use of On Error Resume Next”

    OMG, THE Stephen Bullen comes clean! I will quote you next time someone hassles me about this practice. :D

  69. @snb, MAY 29, 2015 AT 3:52 AM
    “I prefer:
    Sub M_snb()
    c00 =”

    Excellent example of horrendously awful naming. No idea what the heck is “M_snb” or “c00”.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.