Macro customizing

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 following example relies on DATA step programming, a few SAS macro variables, and a final DATA NULL step with PUT statements for custom table presentation. Here are the adverse event summary annotated SAS program, notes for the program, and the output. 

Miscellaneous physical chemical measurements, some quite empirical, are of great importance to the petroleum industry because they are used for control in manufacture and are included in customer s specifications. Macro methods are of course available, but occasionally the sample is too small and this is frequently the case when the problem is particularly important. Micro modifications of these macro methods have often proved extremely helpful. Microchemistry is not a fad but it is not a panacea either. It should be employed where it is necessary, as where the sample is very small or where it offers a definite and substantial advantage in accuracy, precision, or economy of time or materials. If in a particular case it offers none of these advantages there is no good reason to employ it. 

The relation between microstructure and properties at the macro-scale is only one face of materials science. The second step consists in working on the relation between functions and properties. Structure/properties/functions are like the three summits of the base triangle of material science. [14] Contemporary materials are tailored for specific purposes, they are adapted to a set of specific tasks. In contrast to conventional materials that have standard specifications and a world market, more advanced materials are developed according to the functional demands of the final product and the services expected from the manufactured product. In other words, in the language of economics, instead of supplying commodities that would be finalized by the customers, new materials are the end-products of a cooperation between customers and suppliers. 

User specific application software, written by the user or by a third party for a specific user to meet the specific functional needs in the user s laboratory. Examples are macros to customize a system for specific user needs. This software must be validated prior to and during routine use. 

Level 1 If the data is to be manipulated using spreadsheet macros, the spreadsheet application must be considered as a GAMP Category 5 component. Macros in Microsoft Excel are Visual Basic computer programs written specifically for that application. While they may be simpler than the other customized elements of the integrated CDS, they stiU carry the same liabilities. 

Bespoke Customization Bespoke programming (e.g., macros defining reports and forms, and interfaces especially written for the client or server). GAMP level 5 software requiring a supplier audit and validation of the bespoke code (e.g., SAP R/3 ABAP form and report programs, and Microsoft SMS client scripts). 

SCADA/MES should be validated. There is hkely to be a mix of COTS software and bespoke code. The correct operation of statistical analysis software and predictive control software must be assured. User interfaces should receive particular attention. User reports should be defined and tested. Custom programming such as macros should conform to good programming practices and be subject to source code reviews. Interfaces between systems should also be specified and tested. 

There may be more than one output to a process, and sometimes outputs have secondary value for internal customers or regulators. Having a common and consistent definition of outputs allows everyone to set their sights on what the process produces and for whom—again, at a macro level. 

Function procedures augment Excel s library of built-in functions. A function macro is used in a worksheet in the same way as, for example, the SORT function. It is entered in a single cell of a worksheet, performs a calculation and returns a single result (or an array result) to the cell in which it is located. For example, a custom function macro named ALPHA can be used to calculate aj, the fraction of an acid-base species in one of its protonated forms HjX at a particular pH. The function takes three arguments the pH of the solution, the range of pKa values of the weak acid and the coefficient j. This function is useful in constructing distribution diagrams, titration curves, and so on. 

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... 

Function procedures can t be recorded you must type them on a module sheet. You can have several macros on the same module sheet, so if you recorded the ScientificFormat macro earlier in this chapter, you can type this custom function procedure on the same module sheet. If you do not have a module sheet available, insert one by choosing Module from the Insert menu. 

Unfortunately, if you re entering the custom function in a different workbook than the one that contains the custom function, the function name must be entered as an external reference, e.g. PERSONAL.XLS FtoC. This can make typing the function rather cumbersome, and means that you ll probably enter the function by using Excel s Paste Fimction. But, see "Creating Add-In Function Macros" in Chapter 17 for a solution to the problem. 

The complete macro is shown in Figure 16-1. Right now you ll have to use the Macro Run dialog box to run the macro, which isn t very convenient. Of course you could assign a shortcut key to the macro, as described in Chapter 13. In Chapter 19 you ll learn how to create a custom toolbutton and assign the macro to it. 

When you use Paste Function to enter a function, the function s description appears at the bottom of the dialog box. For a custom function, the default description is "Choose the Help button for Help cn this function and its arguments", but you can provide a custom description for your custom function. There are two ways to do this you can use the Macro dialog box (normally used only for Sub procedures), or you can write and run a simple one-line VBA Sub procedure. Either way, the description becomes part of Excel and does not need to be entered each time you start Excel. 

Type the name of the custom function in the Macro Name box. 

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. 

If you have written a number of related macros, you may even decide to create a custom menu bar with several custom menus, each containing custom commands. While both "Custom Menu Command" and "New Menu" can be... 

To Add a Custom Menu Command to a Menu Bar and Assign a Macro to It... 

Right-click on the custom menu item to display the shortcut menu and choose Assign Macro... 

Excel displays the Assign Macro dialog box. Choose the name of the macro to be assigned to the custom menu item (best located in the Personal Macro Workbook). Click the OK button. 

If you create a macro to be used by other people, you can make it easy for them to use the macro by installing a custom menu command. As well, you can install the new menu command by means of an automatic procedure, so that all the users have to do is open the macro document. There are two ways you create an automatic procedure by creating an Auto Open macro in a module sheet, or by creating a Workbook Open event procedure. These are described in the sections that follow. 

Figure 18-2 illustrates an Auto Open procedure that installs a new menu command in the Tools menu and assigns the Sub procedure named CommandHandler to it. To install your own custom menu command in a menu, replace Tools, New Menu Item and CommandHandler with the name of the menu, the text of your menu command and the name of your macro. 

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. 

The Macros category in the Commands tab (Figure 19-5) contains a custom toolbutton that can be assigned to a macro by following the procedure in the following box. 

Excel Tip. When you place a custom toolbutton on a toolbar, you don t have to assign a macro to it right away. Later, if you click a custom toolbutton that doesn t have a macro assigned to it, the Assign to Tool dialog box will appear. 

Two custom toolbuttons, a FullPage(Portrait) toolbutton and a FullPage(Landscape) toolbutton, were created and positioned on the left side of the Standard toolbar. The FullPage macro was assigned to both buttons, as described earlier. 

Custom toolbuttons were created for the Full Page Portrait, Full Page Landscape Chemical Format H2O and Toggle Between Floating Point and Scientific macros. The Chemical Format macro is described in Chapter 16. Module sheets containing these macros should be saved in the Personal Macro Workbook. 

---