Command Macros

A command macro (a Sub procedure) can automate any sequence of actions that can be performed by the use of menu commands or keystrokes. Many simple but useful command macros can be created entirely by using the Recorder, as described in Chapter 13. But command macros can also carry out much more complicated actions. In this chapter we ll look at some examples of creating more advanced Sub procedures. 

---

You can create two different kinds of macro Sub procedures, often called command macros, and Function procedures, called function macros, custom function macros, or user-defined functions. Although these procedures can use many of the same set of VBA commands, they are distinctly different. 

The Recorder creates Visual Basic commands. You don t have to know anything about Visual Basic to record a command macro in Visual Basic. This provides a good way to gain some initial familiarity with Visual Basic. 

Here are some (but not all) of the ways to run a command macro from the Macro dialog box, as described in this chapter by means of a shortcut key, as described in this chapter by means of a custom menu command, as described in Chapter 18 by means of a custom button on a toolbar, as described in Chapter 19 by means of a button on a worksheet, as described in Chapter 8 as a subroutine called by another macro, as described in Chapter 15... 

Chapter 13 provided an introduction to Sub procedures and Function procedures. By now it should be clear that a Sub procedure (a command macro) is a computer program that you "run" it can perform actions such as formatting, opening or closing documents, etc. A Function procedure (a user-defined fimction) is a computer program that calculates a value and returns it to the cell in which it is typed. A Function procedure cannot change the worksheet environment ( e.g., it can t make a cell Bold). 

In previous chapters you learned how to create useful Sub procedures. You may find that you use a particular macro so often that it would be more convenient to have it on one of Excel s drop-down menus, rather than having to "run" it by means of the Run command from the Macro menu. Running a command macro by means of an Excel menu command makes the custom command accessible to Excel users who are not familiar with the use of macros. 

Some of your macros will be written for a very specific purpose, such as to prepare a specialized report. The workbook containing the macro will be opened only when you want to assemble the report. Other macros automate tasks that you perform often, and you ll want to have them available whenever you re using Excel. These macros should be saved in the Personal Macro Workbook. To make a command macro even easier to use, you can add a custom toolbutton to a toolbar and assign the macro to it. The three macros described in this section — the NumberFormatConvert macro, the FullPage macro and the ChemicalFormat macro — are particularly convenient to use when they are assigned to a button. 

SolvStat.xls is a command macro that returns the standard deviations for nonlinear regression analysis performed by the Solver. See "Instructions for Using SolvStat" at the end of this appendix. 

NumFmt.xls is a simple command macro that toggles between floating-point and scientific munber formats. The macro can be easily assigned to a toolbutton. 

FullPage.xls is a simple command macro that can be used to obtain the maximum amount of space on a page for printing a worksheet. It sets either portrait or landscape orientation, sets margins to zero and removes header and footer text. The macro can be easily assigned to a toolbutton. 

This command macro returns the standard deviations of regression coefficients obtained by using the Solver, plus the correlation coefficient and the RMSD these statistical parameters are not available from the Solver. The array of values returned is in a format similar to that returned by LINEST. 

The material concerning charts has been changed extensively to reflect the changes that were made to the ChartWizard. The chapters on programming with VBA have been revised, and the chapters on creating command macros and custom functions using VBA have been completely re-written. 

---