Microsoft Office Tutorials and References
In Depth Information
5. Click OK to close the Excel Options dialog box.
6. Choose Developer ⇒ Code ⇒ Stop Recording.
7. Press Alt+F11 to activate the VB Editor.
8. In the Project window, select the project that corresponds to your workbook.
9. Double-click the VBA module that contains your recorded code.
Generally, this will be the module with the highest number (for example, Module3).
You'll find a VBA procedure that looks something like this:
‘ Macro1 Macro
Application.UserName = “Robert Smith”
Note that this is a Sub procedure, not a Function procedure. In other words, you can't use this procedure in a
worksheet formula. If you examine the code, however, you'll see a reference to the UserName property. You can
use this information when creating a Function procedure. For example, the following Function procedure uses
the UserName property. This function, when used in a worksheet formula, returns the name of the user.
USER = Application.UserName
You can consult the VBA Help system to identify various properties, but using the macro recorder is often more
efficient if you don't know exactly what you're looking for. After you identify what you need, you can check the
Help system for details.
You can use the Excel Options dialog box to change the UserName property back to
what it was. Or, you can make the change by using VBA. Just edit the code in the recor-
ded macro (replace the name quotes with the original username). Then, move the curs-
or anywhere within the Macro1 procedure and choose Run ⇒ Run Sub/UserForm (or
press F5) to execute the macro. Executing the macro changes the UserName property.
Copying VBA code
This section has covered entering code directly and recording your actions to generate VBA code. The final
method of getting code into a VBA module is to copy it from another module. For example, you may have writ-
ten a custom function for one project that would also be useful in your current project. Rather than reenter the
code, you can open the workbook, activate the module, and use the normal Clipboard copy-and-paste proced-
ures to copy it into your current VBA module.