Creating a button to refresh content in the SAS® Add-In for Microsoft Office


Overview

You can use the power of Visual Basic to customize and automate functionality in the SAS Add-In for Microsoft Office. In this sample, you will learn how to create a button that will refresh your SAS content. This Article uses Microsoft Excel data and the SAS graph created in Sample 40977.

Creating the button

  1. To create the refresh button, click the Developer tab and select Insert ► the Command Button.

    Clicking this option turns your pointer into a cross and enables you to draw the outline of a button wherever you want it in your Excel sheet.

2. Right-click the button and select Properties. Then, customize the value for Caption so that the button reads Push to Refresh (or whatever text you like). When you are done, close the Properties window.

Coding the button

1.  Double-click the button to bring up the Visual Basic editor. You should see a code editor that has CommandButton1 and Click by default at the top. The editor should contain some code to get you started.

2. Before you enter any code, you must add a reference to SAS® Add-In for Microsoft Office 4.3. To do this, select Tools ► References. Then, scroll down and select SAS Add-In 4.3 for Microsoft Office. Click OK.

3. Enter the following code between the Private Sub and End Sub lines:

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

4. Before you can enter the code that will refresh the chart, you need to know the name of our chart. Return to the Excel worksheet, right-click the graph, and select Properties. On the General tab, make note of the Object name. In this sample, the Object name is Bar_Chart_2.

5. Click OK to exit the bar chart properties and then return to the Visual Basic Editor. Add the following line to the existing code.

  sas.Refresh ("Bar_Chart_2")


The completed code should look similar to the following:

Testing the button

  1. Exit Visual Basic and return to Excel. Select Design Mode to exit Design Mode. Click the Push to Refresh button to test that you have made a connection to SAS. If the connection is successful, you will see the same brief window pop-up that is displayed if you right-click on the graph and select Refresh.

    To test the button in a more interactive mode, change some of the win values for the teams, as shown in the following display.
  2. Then, click the Push to Refresh button and see how the bar chart updates to reflect the new data.

Additional Documentation

For more information about using Visual Basic with the SAS Add-In for Microsoft Office, from the SAS tab, select Help ► SAS Add-In For Microsoft Office Help. On the Contents tab, expand Automating the SAS Add-In for Microsoft Office with Visual Basic Code.

For more information about the SAS Add-In for Microsoft Office, see the SAS Add-In for Microsoft Office documentation page.

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.