Overview

Sometimes users want to be able to enter data in a Microsoft Excel spreadsheet, use this data as input to a process, and display some results. This sample enables you to perform these actions by using SAS Add-In for Microsoft Office.

First, you will enter values into a specific location in the spreadsheet. Then, you will press a button within that spreadsheet that uses the power of SAS^® to process the data. SAS Add-In for Microsoft Office calculates statistics using a stored process behind the scenes. Rather than displaying the entire output from the stored process, selected values are displayed in output parameter locations on the worksheet. All of this process is done using the Visual Basic scripting that is available in SAS Add-In for Microsoft Office. The Visual Basic scripting enables you to manipulate the input and output parameters and execute the stored process.

Note: This sample was updated using SAS^® Add-In 8.3 for Microsoft Office and SAS^® Enterprise Guide^® 8.3.

Before You Begin

First, you need to complete the following steps to set up Microsoft Excel to enable you to use the Visual Basic functionality:

1. Enable the Developer tab with Microsoft Office 2010 or later.
   1. Navigate to File ► Options ► Customize Ribbon.
   2. Select the Developer check box on the Main Tabs list in the right selection box.
      
      
2. Select the SAS Add-In for Microsoft Office Object Reference type library so that Microsoft Excel can find the SAS objects that are referred to by the Visual Basic code.
   1. Navigate to Developer ► Visual Basic ► Tools ► References.
   2. Scroll down the list and select the SAS Add-In 8 for Microsoft Office check box.
      
      

Create the Stored Process

Before you can create your Microsoft Excel spreadsheet, you must create the stored process that will calculate your statistics. The input data for this code comes from the Microsoft Excel spreadsheet, so you will need to define an input data source as well as some output parameters. Using SAS^® Enterprise Guide^® is the easiest way to create the stored process.

1. Create a project in SAS Enterprise Guide by selecting File ► New ► Project.
2. Add a Program node by navigating to File ► New ► Program. Then, add the following code. (Note: You will not run this code in SAS Enterprise Guide.)

     libname input1 xml;
   data foo;
    Set input1.&_WEBIN_SASNAME;
   run;

   PROC MEANS DATA=foo MEAN SUM NONOBS noprint;
       VAR SCORES;
   OUTPUT OUT=test MEAN()= mean SUM()= sum;

   run;

   data _null_;
    set test;
    call symput('avg',put(mean,10.2));
    call symput('sum',sum);
    run;

   1. Right-click the Program node in the Project Tree and select Create Stored Process from the pop-up menu.

3. In the Name and Description window, enter a Name for the stored process and specify the Location for the stored process metadata. You will refer to this location when you modify the Visual Basic code. Then, click Next.

4. The SAS Code window displays the code for the stored process, which should look identical to what you just created in the Program node. Click Next.

5. In the Execution Options window, fill in the execution server and source file path. You can leave the server type as Default Server.

a. In the Source Code Location and Execution section, select Allow execution on selected application server only. Then, choose whether you want to save the code in the metadata or a physical location.

b. If you choose a physical location, make sure that you specify a path. The name defaults to the name of the stored process with underscores and the .sas extension. You must select the Stream check box in the Result Capabilities section in order to use an input data source.

c. Click Next.

6. Define prompts for your stored process in the Prompts window. You must define the two output parameters that you are using in this stored process: AVG and SUM. Then, click the New button that is displayed beside the Output Parameters section. 

1. 1. When the dialog box opens, enter the name of the macro (AVG) and change the Expected Type to Integer. Do the same for SUM.
   2. Click OK.

c. After you enter both parameters, click Next.

7. In the Data Sources and Targets window, fill in your input data definition. This definition is the data that comes from the Microsoft Excel spreadsheet. 

a. Click the New button beside the Data Sources section to define your input data source.

Note: In the stored process code, you defined a libref named input1 with the XML engine. This is your input data source. 

b. In the dialog box, enter a Fileref of input1. Then, select the Allow rewinding stream option.

c. Click OK to close the New Data Source dialog box. Then, click Next.

8. Click Finish to finish creating the stored process. The stored process in SAS Enterprise Guide might attempt to execute when you click Finish. If this attempt occurs, note that the code fails, which is expected. The stored process was created as needed for Microsoft Excel.  

Creating the Microsoft Excel spreadsheet

In Microsoft Excel, create a spreadsheet where users can enter their values and press a button to execute the stored process, which does the following:

• runs the scripting code that executes the stored process
• displays the output parameters in specific locations on the spreadsheet

Here is a screenshot of the sample spreadsheet:

To create a Microsoft Excel spreadsheet, complete the following steps:

1. Set up the spreadsheet with the areas and labels. The user needs to know where to enter their data values. In this sample, this area is highlighted in pink. It is important that you create a heading above this input area since the heading is used as the variable name in the stored process code.
   1. When following these sample steps, make sure that you have a label called SCORES.
   2. You should also set up the place where your statistics are displayed, which you can do easily by entering some headers. Later, using the Visual Basic code, you will tell the program to place the output in the cells to the right of these headers.
2. Insert a button in your spreadsheet.
   1. Click the Developer tab ► Insert.
   2. Click the Command button icon under the ActiveX Controls list. 
      
      
   3. Click and draw the area where you want the button to be displayed.
   4. Right-click the button and select Properties. Change the Caption to however you want the label of the button to read. In this sample, the label is Calculate Stats.
3. Select SAS ► Tools ► Options.
   1. On the Results tab, set one option and deselect another.
      1. Select the Automatically insert results into the Office document when opening a report check box. By selecting this check box, the results are automatically placed in the worksheet when you run the code.
      2.  Clear the Show placeholder for results with no visual output check box. The stored process executes, but there will be no visible output from the procedure. By deselecting this option, the placeholder that displays the name of the stored process and when the stored process ran will not be displayed in the spreadsheet.
4. Click Developer ► Visual Basic.
   1. In the left window, double-click the Sheet1 object under the Microsoft Excel Objects list.
   2. From the drop-down list, select CommandButton1. Note that the method signature block for the CommandButton1_Click() is automatically included in your code window. 
      
      
   3.  Enter the following code within the method block. Make sure that the block starts with the Private Sub CommandButton1_Click() line and ends with the End Sub line.
       

          Dim sas As SASExcelAddIn

          Set sas = Application.COMAddIns.Item("SAS.ExcelAddIn").Object

         

          Dim list As SASStoredProcesses

          Set list = sas.GetStoredProcesses(ThisWorkbook)

         

          For i = 1 To list.Count

         

              Dim stp As SASStoredProcess

              Set stp = list.Item(i)

              ' Change the path below to the folder where your stored process was saved

              If (stp.Path = "/Shared Data/Stored Processes/stp with excel input and output") Then

                  stp.Refresh

                  Exit Sub

              End If

                 

          Next i

          

          ' Input streams for the stored process

          Dim inputStreams As SASRanges

          Set inputStreams = sas.CreateSASRangesObject

          ' Modify the range where the user is typing in the input data.

          ' Make sure this range includes the label that will be used as the variable name

          inputStreams.Add "input1", Sheet1.Range("A2:A10")

        

         

          ' Output parameter locations that have been added to the stored process

          Dim outputParams As SASRanges

          Set outuptParams = sas.CreateSASRangesObject

          ' Modify the output parameter locations below

          outputParams.Add "sum", Sheet1.Range("B13")

          outputParams.Add "avg", Sheet1.Range("B14")

             

          ' Modify the parameters for the method below to the location of your stored process

          ' Here is the syntax of the method

          ' object.InsertStoredProcess(path,outputLocation,[prompts],[outputParameters],[inputStreams])

          Set stp = sas.InsertStoredProcess("/Shared Data/stored Processes/stp with excel input and

          output", Sheet1.Range("A20"), , outputParams, inputStreams)

   4. Within this code, modify the path of your stored process location with the correct stored process path location for the following:
      • the stp.Path property
      • the path in the sas.InsertStoredProcess method parameters
   5. Verify that the range of cells specified for the inputStreams is correct, as well as the outputParams location where you want to place the output on your spreadsheet.
   6. Close the Visual Basic window after you have made these changes.

Once you complete these steps, you should be able to enter some values in your Microsoft Excel spreadsheet. Click the Calculate Stats button to calculate the mean and average statistics of the values to be displayed in the spreadsheet.

When you save your Microsoft Excel workbook, save the workbook as a spreadsheet with macro code enabled. By default, Microsoft Excel might try to save the workbook as a Microsoft Excel Workbook (*.xlsx). However, because you attached a VB Project to the workbook, you must save the workbook as a macro-enabled file type by completing the following steps:

1. In the Save dialog box, change the file type to Excel Macro-Enabled Workbook (*.xlsm).
2. Then, save your workbook.

Additional Documentation

For more information about using Visual Basic with SAS Add-In for Microsoft Office, complete the following steps in Microsoft Excel:

1. Click the SAS tab. 
   2. Click Help ► SAS Add-In For Microsoft Office Help. 
   3. On the Contents tab, expand Automating the SAS Add-In for with Visual Basic Code.

For more information about SAS Add-In for Microsoft Office, see the following resources:

• the SAS Add-In for Microsoft Office Documentation page.
• SAS KB0036546, “Creating a button to refresh content in the SAS® Add-In for Microsoft Office”
• SAS Note 40977, “Using Microsoft Excel data as input to a task in the SAS® Add-In for Microsoft Office”

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.