Building a System of Microsoft Windows Scripts to Control Excel Macros - Exchanging Data Between SAS and Microsoft Excel - page 229

Figure 13.3.14: Updated Class_Graph Macro to Process the Thick Red Border.
13.4 Conclusion
Like Chapter 12, this chapter presents a complete tool that allows you to control all aspects of a task. But
this chapter shows you how to expand a single user tool and make one that anyone in the department can
use. Some of the features of the Macro Library Tool include the following:
● The Macro Library Tool can easily be included when creating naming conventions.
● The output file has no macros and can be saved as a *.xlsx file.
● The SAS code is limited and flexible. It can be placed into just about any SAS program.
● The VBS code is limited and also has some flexibility with respect to saving files and renaming
output files.
● The Excel macros can be easily created by recording macros and exported to your macro library.
• Optimized VBA macros can be stored and available for general use by anyone in your department.
These do not need to do big things. For example, some might just create small red boxes.
New concepts that can be made available because of the Macro Library Tool include, but may not be
limited to, some of these options. Feel free to dream up some of your own.
● The Macro Library Tool has no manual intervention steps, and the output file is closed when the
job is finished. This can allow other data to be added to the file and other macro processing to
occur.
● The macros can be Write-protected and saved in a secure location.
● A set of standardized VBA macros can be used to increase programmer productivity.
● Each report or task can have a different set of macros, but if data is formatted, the same one macro
can process several reports.
• The VBS routines are not restricted to processing only Excel files.