Microsoft Office Tutorials and References
In Depth Information
If you want to make the arguments you pass to a procedure easier to read, if a bit more ver
bose, you can use named arguments . A named argument consists of the name of the argument
followed by a colon and an equal sign (:=) and the value assigned to the argument. For exam
ple, the MsgBox procedure has the following syntax:
MsgBox(prompt[, buttons] [, title] [, helpfile, context])
If you wanted to create a message box with a specific title and prompt (two of the arguments
listed earlier), you could do so with the following statement:
MsgBox Title:="Status Report", Prompt:="Order Accepted"
For more information on creating message boxes, see Chapter 4, “VBA Programming Starter Kit.”
Organizing for Success
It can be tempting to throw every bit of code you need to complete a series of tasks into a sin
gle procedure, but it’s much more efficient and effective to write bite-sized procedures that
do one task and then call the individual tasks from within a single main procedure. For
example, if you wanted to create a program that wrote the contents of your daily sales
worksheet to a database, saved the current file under a new name based on the date, deleted the
contents of the sales worksheet, and saved and closed all open files, you might have a main
program that looks like the following code:
The contents of the individual procedures are not a concern to the Main procedure. In fact,
you can change them as often as you like without having to go in and mess around with all
the code in the Main procedure. This type of routine has been a hallmark of programming
for years, and it’s a practice you would do well to adopt.
In this chapter, you learned how to create Sub and Function procedures, the containers for
your VBA code. Remember the big difference between the two: Sub procedures don’t return
a result to the main program, but Function procedures do. And now that you know how to
create those containers, you’re ready to start affecting Excel workbooks with your code.
Chapter 6 starts you on your way by introducing the Application object.