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.
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... and this module...
Option Explicit Dim Testing123 as String Sub Main() Testing123 = "hello" Debug.Print "Var=" & Testingl23 End Sub... the answer? Well, try them and see. The first one will produce output "Var=". Eh? Why not "Var=hello"? Well, try the second one. You'll get an error. In the Print command, in both modules, there is a lower case "L" instead of a "1". Because (without Option Explicit) Visual Basic defines variables as you use them, it creates "TestingL23" as an empty variable as soon as you use it.
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 SubExcel is copying your actions exactly, when that's not necessarily what you were after. You didn't really want to move the cursor to cell A1, then type the text in. What you really wanted was to just put the text into cell A1. The lines:
Range("A1").Select ActiveCell.FormulaR1C1 = "hello"Can be replaced by:
Range("A1").FormulaR1C1 = "hello"This has the advantage that it will run without moving the cursor around, or making the screen flicker as that happens. Likewise, we didn't really want to perform a "ctrl-C" on cell A1, and then a "ctrl-V" on cell B2. We just wanted the contents of cell A1 to be copied into B2. Looking up "Copy" in the VBA help shows us that as well as using .Copy then .Paste, we can simply pass a parameter to .Copy to say where to paste it. So the lines:
Selection.Copy Range("B2").Select ActiveSheet.PasteCan be replaced with:
Range("A1").Copy Range("B2")Right - we've now rewritten this recorded macro to read:
Sub Macro2() Range("A1").FormulaR1C1 = "hello" Range("A1").Copy Range("B2") End SubAs well as being shorter, our new macro has two advantages. Firstly, it will run without changing the selection, which is almost always what we want. And secondly, the alternative .Copy method has another advantage - because it is a one-step action, it doesn't actually use the clipboard to copy. So we've preserved that too.
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 SubOkay, I know it's a fairly worthless program. Anyway, to give this a "Please Wait" dialog, do the following. Create a new userform and call it "PleaseWait". Create a label on this form saying something along those lines. Inside the code of the form, enter:
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 SubReplace your original AutoOpen routine with:
Sub AutoOpen() PleaseWait.Show End SubThere you have it - a "please wait" box. Don't say I'm not good to you.
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.