Programming Tips

Just in case I've come across problems that other people are also experiencing, here are a few useful pieces of information that probably took me ages to figure out. This is really for as much my benefit as anyone else's, because I am no doubt going to forget them.

Always record
The easiest way to find out which object does what, which method to use or just generally how Excel/Word goes about things is to record a macro. Just record a new macro, perform the basic operation you're after (or one like it), hit "stop" and see what the application itself came out with. Don't cut and paste the code (it's often very inefficient) - just write down the object names.

Use Option Explicit
Don't worry, it's not rude. I'm going to demonstrate this one with an example. What's the difference between this module...

Sub Main()
   Testing123 = "hello"
   Debug.Print "Var=" & Testingl23
End Sub

Option Explicit
Dim Testing123 as String
Sub Main()
   Testing123 = "hello"
   Debug.Print "Var=" & Testingl23
End Sub

The "Option Explicit" command, used at the beginning of modules, will force you to declare your variables. It's a bit more hassle but, believe me, it's worth it. Apart from anything else, it's very good programming practice. To switch it on permanently, go to "Tools/Options/Editor" and tick "Require Variable Declaration". Tada! No more misspelled variable problems.

Avoid working with the Selection object
When you record a macro, the office apps will repeatedly use the Selection object. This is almost always not the right way to work. Assuming you know a little about how objects work, let's look at what happens if you record a macro, select a cell and enter some text into it. I've removed the comments from the macro for a bit of brevity.

Sub Macro1()
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "hello"
    Selection.Copy
    Range("B2").Select
    ActiveSheet.Paste
End Sub

    Range("A1").Select
    ActiveCell.FormulaR1C1 = "hello"

    Range("A1").FormulaR1C1 = "hello"

    Selection.Copy
    Range("B2").Select
    ActiveSheet.Paste

    Range("A1").Copy Range("B2")

Sub Macro2()
    Range("A1").FormulaR1C1 = "hello"
    Range("A1").Copy Range("B2")
End Sub

As well as producing cleaner code, this is an important principle - as often as possible, your code should run without altering the user environment. There's nothing more annoying than running someone's macro to discover that it's closed one of your open documents, turned calculation to automatic and hidden the scroll bars.

Use Line Input # instead of Input #
Okay, a rather obscure one, I'll admit. For those unaware, Input and Line Input are used to read data from text files. Input (the obvious candidate for the job) has a very irritating feature which is hardly mentioned in the help - it treats the file as lump of fields. This is fine usually - as it treats the end-of-line character as the end of a field, your program will appear to function perfectly normally. Until, that is, you have a comma in the datafile and it ignores everything after it (you don't even want to know how long it took me to work this one out). Using Line Input means that you'll always get the whole line.

Use forms as "please wait" screens
A commonly-asked question is how to create a "please wait" dialog box inside a program. Well, here's how. Create a new User Form (right-click in the Project Explorer, then click "Insert", then "User Form"). Add to this a label from the toolbox and into it type your "please wait" message. Resize the form so it looks a bit nicer. View the code for the form and inside the UserForm_Activate() procedure write whatever code you want to run. As the last line, put "Unload UserForm1". In place of your original routine, put "UserForm1.Show".

It might be a little clearer with an example. Let's say that inside your document is a module with an AutoOpen() routine which does the following:

Sub AutoOpen()
    Dim z as Long
    For z=1 to 10000
        DoEvents
    Next z
End Sub

Sub UserForm_Activate()
    PleaseWait.Repaint  ' Make sure that the screen updates
    Dim z as Long
    For z=1 to 10000
        DoEvents
    Next z
    Unload PleaseWait
End Sub

Sub AutoOpen()
    PleaseWait.Show
End Sub

If you have any suggestions for other gems which ought to be on this page, please feel free to mail me. Either way, you may well want to head back to the index for my Visual Basic for Applications Pages.