xxxvi
Contents
Recording VBA macros ............................................................................... 924
Recording your actions to create VBA code: the basics ......................... 925
Recording a macro: a simple example .................................................. 926
Examining the macro ........................................................................ 926
Testing the macro ............................................................................. 928
Editing the macro ............................................................................. 928
Absolute versus relative recording ...................................................... 929
Another example .............................................................................. 929
Running the macro ........................................................................... 930
Examining the macro ........................................................................ 930
Rerecording the macro ...................................................................... 931
Testing the macro ............................................................................. 932
More about recording VBA macros ............................................................... 933
Storing macros in your Personal Macro Workbook ................................ 933
Assigning a macro to a shortcut key ................................................... 933
Assigning a macro to a button ........................................................... 934
Adding a macro to your Quick Access toolbar ...................................... 935
Writing VBA code ...................................................................................... 935
The basics: entering and editing code ................................................. 936
The Excel object model ...................................................................... 937
Objects and collections ...................................................................... 937
Properties ........................................................................................ 938
Methods ........................................................................................... 939
The Range object .............................................................................. 940
Variables .......................................................................................... 941
Controlling execution ........................................................................ 941
A macro that can’t be recorded ........................................................... 944
Learning More ................................................................................................... 945
Chapter 43: Creating Custom Worksheet Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . 947
Introducing VBA Functions ................................................................................. 947
Seeing a Simple Example .................................................................................... 948
Creating a custom function ........................................................................ 948
Using the function in a worksheet .............................................................. 949
Analyzing the custom function .................................................................. 949
Learning about Function Procedures ................................................................... 950
Executing Function Procedures ........................................................................... 952
Calling custom functions from a procedure .................................................. 952
Using custom functions in a worksheet formula ........................................... 952
Using Function Procedure Arguments .................................................................. 953
Creating a function with no arguments ....................................................... 953
Creating a function with one argument ....................................................... 954
Creating another function with one argument ............................................. 954
Creating a function with two arguments ..................................................... 956
Creating a function with a range argument ................................................. 956
Creating a simple but useful function ......................................................... 957
