Top secret trick to write Excel macros faster…
Have you ever had these problems?
- Struggling to type the full syntax of a particular Excel VBA command or object, or
- Guessing a lot about what variations exist for each VBA keyword, which arguments you can leave out, etc…
- Receiving compile error messages like “Argument not optional” over and over
Then keep reading please…
I discovered a unique and simple trick you can use RIGHT NOW to write the full VBA syntax of your macros with less typing and without doubting…
Here’s the “VBA-syntax-template” trick…
Macros are a serious “creation” deal, there is no such thing as “where do I find the code of my macro project”, so on this article, I assume you have done your homework: you know how Excel VBA language works, you are familiar with the Excel Object Model and available VBA commands and you have designed the logic of your macro.
This technique will accelerate the code typing of your macro and eliminate the hassle…
1) Paste the full VBA syntax instead of writing it
Yes, paste the full syntax from the Excel VBA help into your macro module (first search the command/object property or method in the Excel VBA help) e.g.
- MsgBox(prompt[, buttons] [, title] [, helpfile, context])
- expression.PrintOut(From, To, Copies, Preview, ActivePrinter,PrintToFile, Collate, PrToFileName, IgnorePrintAreas)
2) Remove VBA keywords with confidence
Remove optional arguments you won’t use (keep those you need).
Optional arguments are in square brackets ; for objects see the “Required/Optional” detail in the parameters table below the syntax description in Help
E.g. all the arguments of the PrintOut Workbook method are optional so you can arrange the syntax in several ways
Full VBA syntax example 1:
expression.PrintOut(From, To, Copies, Preview, ActivePrinter)
Full VBA syntax example 2:
3) Fill in the blanks easily
Provide the values for required and optional arguments.
Tip 1: Provide expected values only. See the parameters table to see if the argument expects a string, integer, etc.
Tip 2: Use Named arguments this way…ARGUMENT:= (just add “:=” to each argument name you just pasted. See below…)
expression.PrintOut(From:=2, To:=22, Copies:=2, Preview:=TRUE,
If you were using the MsgBox command your full syntax could be…
MsgBox(prompt:= “YOUR MESSAGE HERE”, title:=”YOUR TITLE HERE”)
Tip 3: Enter Constants quickly and easily by using the “Auto List members” feature. Launch this tool using the shortcut CTRL+SHIFT+J
You can benefit from this simple technique right now!! You will type less and have the security that you are including everything that is needed to produce the results you want.
What would happen when you control the vastness of VBA keywords and know what is available to accomplish any given task?
“Hello, John…I am enjoying your course about writing macros. I can’t wait until I can start using this information in my daily work schedule.”
Stuart Barnes – watchfiresigns(.)com
That’s all for today!
All the best
P.S. To receive the latest Excel secrets, click here to join my community of 10K+ professionals and be among those who receive my Excel Secrets Newsletter in their inbox.