xxxiv
Contents
Chapter 40: Creating Custom Worksheet Functions . . . . . . . . . . . . . . . . 823
Overview of VBA Functions ..............................................................................................823
An Introductory Example ..................................................................................................824
A custom function ...................................................................................................824
Using the function in a worksheet ...........................................................................824
Analyzing the custom function ................................................................................825
About Function Procedures ..............................................................................................826
Executing Function Procedures .........................................................................................827
Calling custom functions from a procedure .............................................................827
Using custom functions in a worksheet formula ......................................................828
Function Procedure Arguments ........................................................................................828
A function with no argument ..................................................................................829
A function with one argument .................................................................................829
Another function with one argument ......................................................................830
A function with two arguments ...............................................................................831
A function with a range argument ...........................................................................832
Debugging Custom Functions ...........................................................................................833
Inserting Custom Functions ..............................................................................................834
Learning More ..................................................................................................................836Chapter 41: Creating UserForms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 837
Why Create UserForms? ...................................................................................................837
UserForm Alternatives ......................................................................................................838
The InputBox function ............................................................................................838
The MsgBox function ..............................................................................................839
Creating UserForms: An Overview ....................................................................................842
Working with UserForms ........................................................................................843
Adding controls.......................................................................................................843
Changing the properties of a control .......................................................................844
Handling events ......................................................................................................846
Displaying a UserForm ............................................................................................846
A UserForm Example ........................................................................................................846
Creating the UserForm ............................................................................................847
Testing the UserForm ..............................................................................................848
Creating an event-handler procedure ......................................................................849
Another UserForm Example ..............................................................................................850
Creating the UserForm ............................................................................................850
Testing the UserForm ..............................................................................................852
Creating event-handler procedures ..........................................................................853
Testing the UserForm ..............................................................................................854
Making the macro available from a worksheet button .............................................855
Making the macro available on your Quick Access toolbar ......................................855
More on Creating UserForms ............................................................................................856
Adding accelerator keys...........................................................................................856
Controlling tab order ..............................................................................................856
Learning More ..................................................................................................................85702_474877-ftoc.indd xxxiv02_474877-ftoc.indd xxxiv 3/29/10 11:34 PM3/29/10 11:34 PM