Simulate the Federal Reserve Rates with the Click of a Button

Using Visual Basic code, you can add a button to the Basic Forecasting worksheet. Now, with the click of that button, you can simulate the Federal Reserve rate and update the contents of the table and the line plot.
  1. In the Ribbon, click the Developer tab.
  2. Click Insert, and under ActiveX Controls, select the icon for the command button.
    Insert Menu with the Icon for the Command Button Circled
  3. In the Excel worksheet, select the region where you want the button to appear.
    Adding the New Command Button to the Basic Forecasting Worksheet
  4. To change the text on this button, select Properties on the Developer tab. The Properties dialog box appears.
  5. In the Caption field, type Simulate.
    Updates to Caption Field in the Properties Dialog Box
    Close the Properties dialog box. In the worksheet, the button text now reads Simulate.
    New Simulate Button in the Basic Forecasting Worksheet
  6. Double-click Simulate to open the Visual Basic Editor. The code for CommandButton1_Click is created for you.
    Code for the Command Button As It Appears in the Microsoft Visual Basic Editor
    Next, it is important that you add a reference to the SAS Add-In 7.1 for Microsoft Office in your Visual Basic code.
  7. In the Visual Basic Editor, select Toolsthen selectReferences. The References - VBAProject dialog box appears.
  8. Select the SAS Add-In 7.1 for Microsoft Office check box.
    The SAS Add-In 7.1 for Microsoft Office Check Box in the References Dialog Box
    Click OK.
  9. To access the automation interface for the SAS Add-In for Microsoft Office, enter the following lines of code:
    Dim sas As SASExcelAddIn
Set sas = Application.COMAddIns.Item("SAS.ExcelAddIn").Object
    Two New Lines of Code in the Visual Basic Editor
  10. For Excel to recalculate the forecasts by using the formulas that you specified, add this code:
    Application.Calculate
  11. To refresh the line plot to reflect the recalculated forecasts, add this code:
    sas.Refresh ("Line_Plot")
    Note: The object name for your line plot might be different. To verify the object name for your line plot, select the line plot in the Basic Forecasting worksheet, and on the SAS tab, select Properties. The object name is available from the General tab in the Line Plot Properties dialog box.
    After adding these additional lines of code, the code in your Visual Basic Editor should appear similar to the following display:
    Visual Basic Editor with Code for the Command Button
  12. Save your changes and close the Visual Basic Editor.
  13. On the Developer tab, deselect Design Mode.
  14. In the Basic Forecasting worksheet, click Simulate. The forecasts in the table are recalculated, and the line plot is updated to show the recalculated rates.
    Recalculated Federal Reserve Rates and Updated Line Plot in the Basic Forecasting Worksheet
Copyright © SAS Institute Inc. All Rights Reserved.
Last updated: April 27, 2017