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

Figure 13.3.4: Windows Trust Center Macro Settings Window.
On the Excel screen shown in Figure 13.3.4, selecting the “Trust access to the VBA project object module”
checkbox will turn on access to running of VBA code on your computer. It is always wise to verify if you
are permitted to modify these settings. Some companies have strict policies about modifying PC settings
without permission. Most software that is written or executed from trusted directories on your computer
will be allowed to execute. These adjustments allow you to create tools that can control several steps and
eliminate manual steps. These tools should be behind strong firewalls. You might even consider Write-
protecting the macro code to prevent the code from unauthorized changes.
13.3.3 Where to Store VBS/VBA Scripts and Macros
So far, in this chapter we have discussed naming conventions, and workstation setup options to allow
macros to run. Before we look at how we create visual basic scripts, Excel data files, or VBA macros, let's
look at how to store and reference them. As was mentioned before, naming conventions should be defined
before any code is written. No programmer wants to be the person named when something breaks. It can be
hard on your job or working relationship within the company or department. Consider including both a
“Production” and a “Development” environment into the naming conventions. In addition, to prevent code
changes from promoting code from the development environment to the production environment, the
naming convention should have something like “PROD” or “DEVL” built into the names.
The type of tool that we are building in this chapter is a little different from the one in Chapter 12. The tool
in Chapter 12 pushed control information from SAS directly to an Excel macro. The Excel macro then had
to decide whether or not to process a predefined set of Excel macros or do nothing. That tool was built for
one user and had to be duplicated if multiple users wanted to use the tool. This type of a tool lends itself to
being modified for each copy of the tool. The following table compares the two tools.