Constants in Code

I was recently looking at some VBA code (not mine) that was accessing lots of different Word templates. The code contained many full absolute paths to the template files, as literal strings: “H:\foo\bar\…\…\Templates\Report.dot”. These happened to be on a network drive, not a local one. It struck me that this is a maintenance problem waiting to happen: if the drive or the path change, there are many occurrences of the literal to change. Also, anyone writing new code needs to be aware of the correct value for this path.

The Golden Rule here is: never have more than one occurrence of a literal value. (We can make exceptions for 0, 1 and immutable values such as the number of months in a year).

Within a project (i.e. template), the simple solution is to have a module that contains only constants: let’s call it ‘Constants’. This contains const declarations for all literal strings:

Const drive As String = "C:"
Const temppath As String = "\...\...\My Documents\Temp"
Const dotpath As String = "\...\...\...\Microsoft\Templates"

Now, we’re often going to want to construct a full file path from these strings plus a file name. So the module can also contain ‘constructor’ functions for different types of file:

Function TempFile(name As String) As String
      TempFile = drive + temppath + "\" + name
End Function

In other modules, we can then write things like:

ActiveDocument.SaveAs (TempFile("Working.doc"))

All we need to know is that it’s a temporary file.

We can even be nice and make the suffix optional:

Function Template(name As String) As String
    Template = drive + dotpath + "\" + name
    If Not Mid(Template, Len(Template) - 3) = ".dot" Then
        Template = Template + ".dot"
    End If
End Function

So now we could just write:

Documents.Add (Template("Work_basic"))

If the path changes, then we just change the single const declaration in the Constants module.

Indeed, we might want different modules for different kinds of constant: FileSystem, Messages, Financial, and so on.

Why VBA? (Part 2)

Let’s look at the case against:

• Yes, it’s from Microsoft, and some people react against that by instinct. But the fact is that Office is overwhelmingly the de facto standard for desktop software, and VBA is part of that platform. If Open Office does take over, then we’ll use its scripting language, which is or will be very like VBA. So I don’t think that using VBA waves any sort of flag for Microsoft – it’s just something that’s out there to use.
• Yes, there are security issues with any kind of executable code in a desktop environment, in that, obviously, an executable can potentially do anything (delete files, send emails, etc). However, we now live in an IT world where security issues are pervasive, and this is just one among many, of which we all need to be aware. I’d guess that the vast majority of VBA code is developed and used in-house, and so can be subject to normal quality control. Similarly, add-ins from reputable vendors are not going to be a problem. So the only source of malicious macros is injudicious downloads from the web, or spam emails, and we should all be extremely wary of those.
• Yes, as a language it’s not ideal. Having started as a very simple language about 30 years ago, and having been subject to many elaborations since then, it’s not exactly a language designer’s delight. There are annoyances, inconsistencies and redundancies (I’ll comment on some of these in later posts). But many other languages have been at least as bad and have been widely used (we round up the usual suspects). Personally, I’ve used probably the best designed programming language ever used commercially–Eiffel–so I set high standards; but even I can used VBA without swearing more than occasionally.
• Yes, VBA is used by people who aren’t ‘proper’ developers, but then so are many other scripting languages. We live in an age of lightweight languages, agile development, and so forth; it seems strange then to argue against that for VBA in particular. Clearly, the more serious the scale and scope of development, the more that software engineering practices such as testing and version control are necessary. One of the issues I’ll try to address in this blog is how to use VBA in a disciplined manner.
• VBA looks like it will be around for a while yet. There’s just too much of it out there for it to be radically changed or removed from Office. In any case, it’s hard to see that an application scripting language could be fundamentally different from VBA, because of the nature of the applications themselves.
• So I suppose the conclusion is: yes, it has it’s flaws, but it’s out there on nearly every PC, it’s useful and it’s usable.