Building a System of Excel Macros Executable by SAS - Exchanging Data Between SAS and Microsoft Excel - page 206

• This subroutine defines several local variables. They are named here.
◦ Variable my_count is defined as an integer variable. This variable is set to the number of
workbooks that Excel currently has open. The VBA “For” loop is similar to the SAS “DO”
loop, and will execute once for each open workbook. Information about workbooks and sheets
is stored in arrays,
◦ Variable my_book is defined as string variable. This variable is set equal to the name of the
currently open workbook Information about workbooks and sheets are stored in arrays.
◦ Variable my_sheet is defined as string variable. This variable is set equal to the name of the
currently active worksheet. The name of the workbook is tested to make sure it is not
"PERSONAL.XLSB" or “PERSONAL.XLS”. If it is one of these workbooks, then no action
is taken. If the workbook name test is false, then the rest of the “if-then” condition is executed
and the contents of Cell “A1” of sheet one of the current user workbook is placed into the
Excel variable my_cell_a1. The VBA code shown will reference only the
“PERSONAL.XLSB” Excel files.
◦ Variable my_cell_1 is a string variable. This variable is a work space for processing cell A1 in
the worksheet. The Excel variable my_cell_a1 is tested for a “?” by looking at all of the
characters in the variable except the first character and loading the location of the “?” into the
variable my_flag_size. If the length of my_flag_size is greater than 1, then there is more than
one character between the two question marks on the left side of cell A1 of the worksheet. If
there is no question mark on the left side of the cell A1 value, then the value of variable
my_report generated later will not match a “CASE” test value.
◦ Variable my_cell_size is an integer variable. This variable is set to the number of characters
that are in Excel variable my_cell_a1.
◦ Variable my_report is derived by getting the left part of the value without the question mark
and calculating the size of the temporary value of my_report. To get the final contents of the
variable my_report with no question marks, the left question mark is removed. This returns the
original value of cell A1 to the workbook cell A1 without the left most control characters.
● This code looks first to find a non “PERSONAL.XLSB” workbook. If you are using Excel 2003,
then code “PERSONAL.XLS” as the workbook name.
● This “Select Case” command works much like a SAS “SELECT” statement, “Select Case
my_report”, which is the same as “Select When(my_sas_variable_name)”. The 'Case “graph_1”'
value and the next line are the same as the 'When(“value”) SAS_command;' code. The “Case
Else” command is the same as the SAS “Otherwise” command. This code structure is also case
sensitive like the SAS SELECT statement.
● This VBA subroutine is sensitive to parameters passed to Excel in cell “A1” of the Excel
workbook that is opened. This code is looking for exactly “?graph_1?” in the first nine characters
of cell “A1” of the first sheet of the workbook. The test is a case-sensitive test and anything except
“?graph_1?” will fail the test. No other processing is done to the workbook when the test fails; the
workbook is just displayed for you to examine or update the workbook.
● When SAS writes variable name labels as the first row of the XML file sent to Excel, the label of a
variable can contain special characters and spaces that are not valid in SAS variable names. The
Excel VBA code can search for these special characters and use their presence or absence to
control actions in the Excel VBA subroutine. The control information ("?graph_1?) that we
inserted into the label of the “Region” variable ("?graph_1?Region") will be stripped off to
become the value of the VBA variable “my_report”, and it will be used as the test case in the VBA
“Select Case” command. The question marks are removed, too. The rest of the SAS variable
names or labels are output in row one and used as column header names.
• The Select command from the end the “Workbook_Open” VBA subroutine and is currently set up
to process one report type, namely graph_1. It should be easy enough to add more. The Excel
my_graph_1 subroutine called is the subject of the nest section of this chapter.