Create an Excel Workbook That Runs SAS Programs - Exchanging Data Between SAS and Microsoft Excel - page 233

10. The department or programming group has set up a SAS source code library with common
routines that can be reused by all of the programmers in the department. The types of SAS code
routines that can be stored here include code to generate formats, perform tests, read data files,
write data files, and any other code that is used more than once. By placing code here, you ensure
that, if the system requirements change, only one code file needs to change and all of the programs
are updated. It is also important to verify that changes made are universally acceptable.

This system described here is a simple set of Excel user forms, Excel VBA macros, SAS code files, and
Windows *.bat files. The task of building a system to meet your needs is left up to you. This workbook
stores and executes the parameters to run SAS programs, but the number of common parameters and extra
features installed into the tool is limited.

14.3 Guidelines for Building an Excel User Form Interface

14.3.1 Common Excel and Excel User Form Terms
So, let's get started. The first step will be to create an Excel Graphical User Interface (GUI). That may
sound important and hard, but it really is not hard at all. First there's a description of what we need to do,
and then we will look at some screen shots that will show how to perform the tasks. We need to create an
Excel file into which we can place a macro. That means that either an *xls, *.xlsb, or *xlsm Excel
formatted file will be created. Excel 2007, 2010, and 2013 files (*.xlsx) do not permit macros. This can be
done by opening a new Excel workbook since we do not need any special things in the output file. We can
build everything we need in a few minutes. Before we get started, we need a few simple term definitions
for the Excel structures that will be in the tool. See Figure 14.3.1.

Note: A detailed explanation of this process was presented in Benjamin, William E., Jr. 2013. “Give the
Power of SAS to Excel Users Without Making Them Write SAS Code.” Proceedings of the SAS Global
Forum 2013 Conference . Cary, NC: SAS Institute Inc. Available at
http://support.sas.com/resources/papers/proceedings13/010-2013.pdf

Figure 14.1: Glossary of Common Terms Used When Building an Excel UserForm and GUI.

Term Structure Use in the Tool

Safe Mode A method of executing Excel from a

command line within a Windows *.bat
file.

This will start Excel using
command switches that turn off
some of the features of Excel.

Integrated
Development
Environment (IDE)

Excel screen that appears when you type
the Alt+F11 keys with a spreadsheet
open.

This is the Excel tool we will use to
build our reporting tool to run our
SAS programs.

User Form (spelled
UserForm)

A program “Object” used by Excel to
store tool components.

Used to display the components of
the tool.

Label A component of a UserForm that stores

text that cannot be changed.

UserForm field describing another
Form component; used here to
describe screen fields.

TextBox A component of a UserForm that stores

text that can be changed.

A box on the UserForm that
contains a value that can be
changed; this value is used later.
We will enter fields or show data
we update.

ComboBox A component of a UserForm that stores

a list of text values that can be selected.
It provides lists of valid option values.

We will use this to store a list of
valid programs and prevent the user
from picking an invalid value.