Data type prefixing in Excel VBA

I hinted at this during the Excel user conf the other week when I asked who used a naming convention in their Exccel VBA.

I am being very specific about what I am talking about – VBA code that sits in a spreadsheet or add-in in some business type application. Not C systems code.

Some authors suggest using a 3 letter prefix to signify the data type of a variable

so

Dim x as integer becomes

Dim intx as integer

I used to do this because I thought it made my code look ‘more professional’, harder to read, but ‘more professional looking’. Then I read this, which totally cleared up why I didn’t like data type prefixing.

And then I read Code Complete by Steve McConnell.

Around the turn of the century Microsoft released their naming convention advice for .net (ie – business applications), which built on advice from MFC era in the ’90’s. (note the advice ‘Do not use Hungarian notation’ here)

My advice is simple, and here (near the bottom) – basically just choose a good meaningful name. That presupposes other things like general design, procedure length etc are sensible.

I think, like in many things, if something works for you, then great, use it, if it doesn’t then don’t.

Just in case you didn’t know the VBAIDE has had the locals window feature since the last millennium:

Locals Window - clear full data type info

Locals Window - clear full data type info

(You will notice I havent discussed scope prefixes – I find them quite useful if I have to use non proc level variables)

As it happens I do use frm, qry etc in Access, but hey maybe thats just another habit I need to kick. Well, and hands up, I still often use a single letter data prefix, (s, l, d, r, etc).

So, do you use data type prefixes? If so why?

Do you use ‘Original Hungarian’ – where the prefix indicates the usage, rather than the somewhat less valuable underlying datatype?

Do you still use the ‘my’ prefix so beloved in MS VBA documentation (myString, myVariant, etc)? (‘my’ as opposed to whose??)

Do you use carefully thought out, clear unambigous names?

Do you just type any old stuff in to get the thing to run?

cheers

simon

(I do all of the above!)

Advertisements

30 Responses to “Data type prefixing in Excel VBA”

  1. Greg Says:

    I got away from using hungarian notation for the most part, but I still find it helpful to prefix g_ or m_ for global variables. I do tend to use prefixes for GUI elements such as lbl or txt. I find that it makes it easier for me to follow my code when creating forms. I can also type “txt” and then ctrl space to get a list of text boxes and take advantage of the auto complete.

  2. Harlan Grove Says:

    Suppose this is an age-related issue. I learned programming more than a decade before Hungarian notation was introduced. I had used prefixes for structure types in C code, and I even used p as prefix for pointers and a or b for arrays or buffers (so pointers to compile-time memory allocation). I could see the usefulness for this sort of thing in C.

    As for type prefixes, sng, dbl, lng, int, I never saw the point. I have enough FORTRAN background that I tend to use certain groups of letters for certain types (e.g., i-n for integers, w-z for reals). In small procedures, ones where the variable declarations would appear in the samescreen as the rest of the body of the procedure, I consider single letter variable names to be more readable. Of course, I’ll admit that I may suffer from a numeric programming bias – code should look as much as possible like equations in a math textbook.

  3. Bob Phillips Says:

    I am with you Simon. Do whatever suits you.

    What suits me is to use a naming convnetion that studiously avoids a prefix that signifies the data type (Linus Torvalds: Encoding the type of a function into the name (so-called Hungarian notation) is brain damaged, the compiler knows the types anyway and can check those, and it only confuses the programmer.). Have these VBA programmers never met Shift-F2?

    The only variable prefixes I include is something to designate its scope.

  4. Dick Kusleika Says:

    I prefix on scope and data type. I would like to stop prefixing on data type, but there is one really nice advantage that I can’t overcome. I have more flexibility with short variable names when I include a single character prefix than if I don’t.

    I can have a variable rInvoiceNum that points to a range that contains an invoice number. And I can have a variable sInvoiceNum that contains the string. How do you do that without a prefix?

    I can also use reserved words as variables like rNext and sCase.

  5. dougaj4 Says:

    I pretty much agree with the concensus – it depends on what you find useful, and who you are writing the program for. The main thing is to make it readable and meaninful. Most of my work is related to engineering analysis design, which means I’ll be using the abreviation used in a code or specification as often as not.

    The only identification I usually use is an upper case A on the end of names of arrays.

    I can see some benefit in prefixing on scope, maybe I’ll start doing that.

  6. jonpeltier Says:

    Whatever works. I don’t do the Hungarian thing, though I try to use some kind of descriptive prefix, such as g and m for scope, and other things that combine type and usage. It’s just mnemonics.

  7. BIGGUS DICKUS Says:

    I use a strange mix of L-R syntax and my wn home-grown descriptive names. I am frankly embarassed that I am not more disciplined but after all these years naming things becomes pretty F***ing tedious….

    Dick
    p.s. SUSAN BOYLE ROCKS !!!

  8. Dennis Wallentin Says:

    I got my own naming convention with VBA and classic VB. But as for .NET it is suggested to not doing it at all so I guess I will sooner or later drop it.

    Kind regards,
    Dennis

  9. geoffness Says:

    Scope, type and usage, but not religiously, more out of habit. I agree that data type prefixing is largely unnecessary for code safety, but I also agree with Dick’s point about type prefixes allowing re-use of the same underlying (and meaningful) name. I quite frequently use them for just this reason.

  10. Mike Staunton Says:

    Partly, it depends on how many different types that you use – since I use predominantly just doubles and longs (typically stating with i-n) with occasional variants – the only additional thing that I do is to give one-dimensional arrays a name that includes vec and two-dimensional arrays a name that includes Mat

  11. Bob Phillips Says:

    I like that last idea Mike. I have always struggled with arrays, I don’t like the ary prefix but it is useful to know that they are arrays, so prefixing (or even suffixing) with Vec and Mat is good. I am going to adopt that.

  12. Ross Says:

    It’s one of those things where there’s no right answer.

    I’ve tried loads of different things in VBA, for I,J,K, I don’t bother, I don’t use ^F2, but I do have a button on the menu bar is needed in the IDE.

    I guess if I looked back at some code I’ll have used s, str, st, all over the place for string and like wise l, lng etc for long. – I guess you have to use 3 letters really if your going to do it this way.

    In .Net I was struggling with controls, there’s so may, so I looked for a naming system. The problem was that there was no way I could remember them all.

    It’s all a bit much now, so I just try and name things with 1/2 decent name, statbarProgBar, tbOpenFile (tb = toolbar) it’s not pretty, but I think it makes sense, the problem is that it needs the 2 bits, because tb could also mean text box etc., etc, ect. I have kinda said to myself, look, you’ll be smart enough to work out what you meant when you come back to it – which I guess is a very dangerous thing to do!!

    I do always use g_ or gs_ etc for global, it makes them easy to get to.

    “Dim i as $” anyone?

  13. Doug Glancy Says:

    I’ve arrived at pretty much the same place as Dick, for the same reasons. I use three-letter prefixes though, or in the case of “bool”, four. For some reason the prefixes also help me focus a bit on the overall structure of my code. Global prefixes too of course.
    Another thing I’ve started doing lately is prefixing my data types with “Excel” (or “Word”, etc.) as in Dim rngAllOverThePlace as Excel.Range.

  14. Charlie Hall Says:

    Great topic!

    When introduced to the concept of prefixes for data type (probably PED), I thought about how inefficient several characters of a prefix would be when depending on intellisense to narrow down the possible variable names – I guess I am just lazy and I like long meaningful variable names, and thought this would mean I would have to type even more characters (eg – typing rng for a range, does get you much closer to the variable name).

    So I used suffixes instead. I liked how I can read a variable without first reading the data type, but that the data type is there for the purposes of catching bad code.

    However, lately I have migrated to be less strigent in my suffix use – I now use it for scope, and usually for variables that have unexpected data types (objects, arrays, ranges). And for constants. I do like the original concept of using a convention to hihglight the kind of variable as opposed to the type. I guess I have already being doing that with suffixes of rw, and col but I could use it more.

  15. Harlan Grove Says:

    Long meaningful variable names are fine for items with wider scope than the current procedure or references to OS or application objects. Otherwise arguable benefit at best. For example, seldom any point to lngRowIndex and lngColIndex vs i and j.

    A(i, j)

    to

    arrMatrixA(lngRowIndex, lngColIndex)

    To me, the former is far more readable AND EASIER TO SPOT ERRORS than the latter.

    IMO, in BASIC dialects the greater problem is the syntactic ambiguity between array indexing and function calls.

  16. Harlan Grove Says:

    As for scope, I follow a C-like convention. Module scope variable names are upper case, procedure scope variable names are lower case, and I never use project scope (global) variables – I use defined names instead.

  17. Doug Glancy Says:

    Charlie, your post made me realize I have the opposite experience you described, i.e., one of the reasons I like prefixes is that if I’ve forgotten the name but know it’s a range once I type in the “rn” intellisense has narrowed down my choices to the possible ones.

  18. Bane Says:

    I have a similar style to Charlie – but I only use _m or _g suffixes for scope… and that’s it. Local variables need no suffixes, of course. I find it more efficient to have intellisense on a meaningful name rather than a data type.

  19. Marcus from London Says:

    I heavily use UDTs (particularly in classes or for module level variables) for several reasons. The first is it makes remembering variable names a no-brainer – just remember one variable and intellisense takes care of the rest. The second is for passing parameters between functions which now only require a single argument.

    For local variables I use a Hungarian notation except for simple variables such as integer counters – I use x not, intX. The other exception is parameter names where I don’t use any notation. Instead I make parameter names meaning and always assign a data type which shows up in the tooltip when calling a function.

  20. Stan Scott Says:

    Just FYI, Microsoft’s latest word on the subject was a reversal of it’s previous position. It now suggests that you NOT use prefixes.

    The guidelines were directed to .NET developers — sorry for not posting the link, but Google should provide it, and if people are interested, I’ll try and dig it up.

    Not too long ago, people were posting that “Long” was VB’s “native” setting, though I don’t believe there’s anything definitive for VBA. Almost all of my variables are now either Long or String.

    I’ve retained one prefix. Putting “p” before Public variables is a good practice, I think. With a lot of long, complex code, this can sometimes be hard to determine.

  21. Simon Says:

    Stan
    I think MS has always discouraged datatype prefixing for business apps, certainly that has always been their view for .net (link in post), and to some extent MFC before that.

    Long is the native type of the 32 bit world, so its the same for VBA, thats pretty much all I use too, longs, strings, and doubles and booleans.

  22. Dick Kusleika Says:

    I can see how people might have differing opinions on their use of prefixes, but why would MS encourage or discourage anything when it comes to variable names? Why would they care what I call my variables? Do they also care how long my names are, what case they are?

  23. Simon Says:

    Lots of people look to MS for guidance in any and every thing to do with computing. Most large orgs will have a coding convention based off some Microsoft consulting docs, unless they couldn’t afford MCS and had to use accenture.

    If you are coding in Visual studio then who better than the authors (in theory) for advice?

    I never read WHY MS say don’t use hungarian, but Joel covered it well enough for me.

    I dont’t think they could claim to care about variable names when the vba help is full of myvar, myint etc names.

  24. Stan Scott Says:

    Microsoft DID recommend the use of Hungarian some years ago, though I’m not sure who this was addressed to.

    MSDN posts helpful Microsoft articles, including clean coding. It was in this context that MS made its recommendations.

  25. Stan Scott Says:

    The MSDN article is here http://msdn.microsoft.com/en-us/library/ms229045.aspx

  26. Simon Says:

    Stan that link is in the post – so we are definitely talking about the same thing:-)

    I think MSDN is one of the shining lights of the MS ecosystem, and something wouldbe competitors have to work hard to match. mind you you have to use google to find what you want in it.

  27. Dick Kusleika Says:

    Thanks for the link. OK, I can see how they can make that recommendation in that context – giving personal preference to people who can’t come up with their own. But I still don’t like it and I’d like to know why they say no to Hungarian.

  28. Simon Says:

    yep me too, its in there like ‘don’t microwave your cat’ – as if its bleedin’ obvious

  29. Gordon Says:

    @Simon
    Agree about MSDN being handy, but I found out something the other day that makes MSDN much more useable: loband. MS have created an de-crufted version of every page, makes load times much quicker and removes the nasty treemap and other nonsense. Also handy for viewing from a mobile device.

    http://blogs.msdn.com/innovation/archive/2009/03/26/launching-low-bandwidth-loband-beta-for-long-haul.aspx

    You can view the loband version of any page by inserting (loband) just before the .aspx, or (better) it can be set as the default view for all MSDN pages, even those reached via Google.

    Apologies if this is already common knowledge, but this is something that needs be be shared!

  30. Stan Scott Says:

    Gordon, thanks so much for this! It makes MSDN so much better! Faster to load, less cluttered. Wow.

    Bringing developers like you together is another reason why blogs like this are so valuable. The Excel material is excellent, and nuggets like this improve all of my MS-related development.

    Thanks again,

    Stan

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: