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

Figure 12.4.2a shows the results of running our SAS code and letting our macro (Macro1) execute. Like
most other SAS programmers who are also Excel users, I determined that Excel likes to dance to its own
tune, which means that as soon as someone tells you what Excel will do in a particular situation, it will
seem to do something else. This is the case with getting the macros listed here to execute correctly
(meaning how you want them to execute, not how Excel thinks they should run). To that end, it should be
pointed out that if you double-click on an Excel file to open it, Excel may do its processing slightly
differently than if the SAS System executes an “X” command to run Excel. Yes, that does not sound right,
but try it for yourself.
Figure 12.4.2a: Resulting Excel File When the SAS Program with the “X” Command Runs.
The code reproduced below includes a clever little trick. It is like the code that was shown in Figure 12.4.1
as the VBA subroutine called Workbook_Open. The upgraded version in Figure 12.4.2b below includes
two message boxes to display information about what occurs when the code is running. This will display
information contained in VBA code variables and pause the program. Since this is the first VBA code to
execute, you do not have access to the Excel VBA debugger yet. However, you can print values in the
message box so that you can examine them. This neat little debugging tool can be useful in figuring out
what is going on with Excel at the start of the program (when you cannot start the debugger) to give you
guidance about how to process the code. The VBA code shown will reference only the
“PERSONAL.XLSB” Excel files.
Figure 12.4.2b: Sample VBA Code to Trace the Path Through the VBA Code.
Private Sub Workbook_open()
Dim my_count As Integer, my_book As String, my_sheet As String
My_count = Workbooks.Count
'Define message
Msg = "sub=workbook_open - workbook count= " + Str(my_count)
'Define buttons
Style = vbOKOnly
'Define title
Title = "Workbook name = " + ThisWorkbook.Name