Lab 7: Performing Calculations in Excel Solutions

Lori Turner
Microsoft Corporation

March 2004

Applies to:
    Microsoft® Visual Studio® Tools for the Microsoft Office System
    Microsoft Office Professional Edition 2003
    Microsoft Visual Basic® .NET 2003

Summary: Demonstrates how to create an Excel solution using an existing workbook so you can take advantage of the calculation and formatting capabilities available in Excel. (11 printed pages)

Contents

Introduction
Exercise 1: Managing Data Entry and Formatting in a Worksheet Using the Change Event
Exercise 2: Calling Back from Excel into Your Assembly
Next Steps

Download the VSTOLabs.exe sample file.

Introduction

The objective of this lab is to show how you can use the calculation and formatting capabilities in Microsoft® Office Excel to manage data entry in a workbook that calculates loan payments and computes an amortization schedule. The lab also demonstrates how you can call back from Excel into your assembly so you can use the return value of a function in the assembly as a worksheet function (or a user-defined function). You create your project using Microsoft Visual Studio® Tools for the Microsoft Office System.

Estimated time to complete:

• Exercise 1: Managing Data Entry and Formatting in a Worksheet Using the Change Event - 20 minutes
• Exercise 2: Making a Callback from Excel into Your Assembly - 10 minutes

For a link to all labs in this series, see Visual Studio Tools for the Microsoft Office System Training.

Note   The labs in this series are designed to be completed in order.

Exercise 1: Managing Data Entry and Formatting in a Worksheet Using the Change Event

The objective of this exercise is to illustrate how you can apply a managed code solution to an existing workbook to use the calculations and formatting in the workbook. In particular, you handle the Change event of the worksheet to manage data entry and formatting; the event is wired and un-wired on the fly.

This exercise includes the following tasks:

• Create a new Excel Workbook project for an existing workbook.
• Create an amortization schedule using Excel formatting and calculations.

Task 1: Create a New Excel Workbook Project for an Existing Workbook

Before creating the new project, examine the existing workbook.

1. Copy Amortization.xls from C:\Labs\Files to C:\Labs\Lab7.

2. Open C:\Labs\Lab7\Amortization.xls in Excel to examine the workbook.

   Note the following:

   • All cell formulas needed to calculate a monthly payment and to create an amortization schedule are already in place.
   • The ranges that you examine or change at run time are predefined with range names. On the Insert menu, click Names, and then click Define to observe the names defined for various ranges on the worksheet.
   • Only three pieces of data are needed to compute the monthly payment and schedule: Loan Amount, Interest Rate, and Years for the loan.
   • There is an additional area that is used to compute a "what-if" scenario for paying off a loan earlier than the load period. You use the Excel Solver Add-in at run time to solve the "what-if" scenario.
   • Ranges in the workbook where the user enters data are set up with data validation to ensure that the data entered by the user matches certain criteria. To examine the data validation for a cell, select the cell and click Data Validation on the Data menu.

3. Close Amortization.xls without saving your changes and quit Excel.

To create a new project for an existing workbook

1. Start Visual Studio .NET.

2. On the File menu, point to New, and click Project.

   The New Project dialog box appears.

3. Select Visual Basic Projects under Microsoft Office System Projects.

4. Select the Excel Workbook template.

5. Type Amortization for the name of your new project and C:\Labs\Lab7 for the location.

6. Click OK.

7. In the Microsoft Office Project Wizard, select Existing Document, locate C:\Labs\Lab7\Amortization.xls, and then click Finish.

Task 2: Create an Amortization Schedule Using Excel Formatting and Calculations

As noted in the previous section, the Excel workbook is already completely structured to calculate a loan payment and compute an amortization schedule. Now, you can use code to manage data entry in the worksheet and react to user input to format the workbook so that the results displayed are appropriate for the user's entries.

To manage formatting and data entry in the worksheet, you handle the worksheet Change event. To wire up the Change event for the worksheet, you do not use WithEvents. Instead, you use AddHandler to wire the Change event delegate to a specific function at run time. You use AddHandler so that you can in turn use RemoveHandler to un-wire the Change event delegate from the handler at points in the code where you are programmatically changing the worksheet and do not want the Change event handler to execute.

To create an amortization schedule

1. Add the following class-level variables to the OfficeCodeBehind class:

   Dim sheet As Excel.Worksheet
   Dim rngLoanAmt As Excel.Range
   Dim rngIntRate As Excel.Range
   Dim rngYears As Excel.Range
   Dim rngWhatIfYears As Excel.Range
   Dim rngWhatIfArea As Excel.Range
   Dim rngAddlPmt As Excel.Range
   Dim rngMonthlyPmt As Excel.Range
   Dim nScheduleMonths As Int32
   

   The "WhatIf_Area" range on the worksheet should not be available to the user until all the data required to calculate a loan payment and schedule has been entered. The WhatIfAvailable function enables or disables the cells of the "WhatIf_area" based on a Boolean parameter.

2. Add WhatIfAvailable to the OfficeCodeBehind class:

   Private Sub WhatIfAvailable(ByVal bAvailable As Boolean)
   
       ' Reset the formula for WhatIf_Years = Years and reset
       ' the Additional_Payment = $0.
       rngWhatIfYears.Formula = "=Years"
       rngAddlPmt.Value = 0
   
       ' Lock/unlock the WhatIf_Years range.
       rngWhatIfYears.Locked = Not (bAvailable)
   
       ' Format the WhatIf_Area to give the appearance that the range
       ' is either enabled or disabled.
       If bAvailable Then
           rngWhatIfArea.Font.ColorIndex = Excel.Constants.xlAutomatic
           rngWhatIfYears.Interior.ColorIndex = Excel.Constants.xlAutomatic
       Else
           rngWhatIfArea.Font.ColorIndex = 48  'Gray
           rngWhatIfYears.Interior.ColorIndex = 19  'Yellow
       End If
   
   End Sub
   

   The ShowSchedule function formats the Schedule range on the workbook so that the schedule displays the correct number of payments based on the user's most current entries for the loan calculation. ShowSchedule also calls the WhatIfAvailable to enable or disable the "What-if" area on the worksheet; ShowSchedule un-wires the Change event handler while modifying the worksheet and then re-wires the Change event when those modifications are complete.

3. Add ShowSchedule to the OfficeCodeBehind class:

   Private Sub ShowSchedule(ByVal WhatIfReset As Boolean)
   
       Try
           ' Unprotect the sheet and remove the handler for the Change
           ' event so that your programmatic changes are not caught
           ' by the Change event handler.
           sheet.Unprotect()
           RemoveHandler sheet.Change, AddressOf LoanChange
   
           Dim rngSchedule As Excel.Range = sheet.Range("Schedule")
   
           ' If there is data in the loan_amount, interest_rate,
           ' and months defined ranges, then go ahead and create
           ' the amortization schedule on open, otherwise set the
           ' status bar text.
           If rngLoanAmt.Value Is Nothing Or _
               rngIntRate.Value Is Nothing Or _
               rngYears.Value Is Nothing Then
   
               ' Set the status bar with instructional text.
               ThisApplication.StatusBar = "Please provide a " & _
                   "Loan Amount, Interest Rate, and Number of Months."
   
               ' Disable the WhatIf Area of the worksheet.
               WhatIfAvailable(False)
   
               ' Hide the Amortization schedule.
               rngSchedule.Rows.Hidden = True
   
           Else
   
               ' Show the number of rows in the Schedule defined range
               ' based on the number of months in the schedule.
               rngSchedule.Rows.Hidden = True
               rngSchedule = rngSchedule.Resize(nScheduleMonths + 1, _
                   rngSchedule.Columns.Count)
               ThisWorkbook.Names.Add("Schedule", rngSchedule)
               rngSchedule.Rows.Hidden = False
               CType(rngSchedule.Item(2, 1), Excel.Range).Select()
   
               ' Set the status bar to the default.
               ThisApplication.StatusBar = False
   
               ' Enable the WhatIf Area on the worksheet.
               If WhatIfReset Then WhatIfAvailable(True)
   
           End If
   
           ' Re-protect the sheet and wire up the Change event handler
           ' for the sheet.
           AddHandler sheet.Change, AddressOf LoanChange
           sheet.Protect()
           sheet.EnableSelection = _
               Excel.XlEnableSelection.xlUnlockedCells
   
       Catch ex As Exception
           MessageBox.Show("Error creating amortization schedule: " & _
               ex.Message, "Amortization", MessageBoxButtons.OK, _
               MessageBoxIcon.Error)
       End Try
   
   End Sub
   

   The LoanChange function serves as the handler for the worksheet Change event delegate. You use range intersection and union to determine if the user has entered data in the Loan_Amount, Interest_Rate, or Years defined ranges and, if so, call the ShowSchedule function.

4. Add the LoanChange function to the OfficeCodeBehind class:

   Private Sub LoanChange(ByVal Target As Microsoft.Office.Interop.Excel.Range)
   
       Dim rng As Excel.Range
   
       Try
           ' Check to see if the changed cell is for Interest_Rate,
           ' Loan_Amt, or Years, and if so, compute the number 
           ' of months and show the Amortization schedule.
           rng = ThisApplication.Intersect(Target, _
               ThisApplication.Union(rngIntRate, rngLoanAmt, rngYears))
           If Not (rng Is Nothing) Then
               nScheduleMonths = Convert.ToInt32(rngYears.Value) * 12
               ShowSchedule(True)
               Return
           End If
       Catch ex As Exception
           MessageBox.Show("Error creating amortization schedule: " & _
               ex.Message, "Amortization", MessageBoxButtons.OK, _
               MessageBoxIcon.Error)
       End Try
   
   End Sub
   

Now you add the functionality needed to calculate the user's "What-If" scenario. The Change event handler for the worksheet detects when the user changes the number of years for their "What-If" scenario. When the number of years for the "What-If" scenario changes, you use the Excel GoalSeek function to compute the additional payment required to result in a zero balance for the number of years entered by the user.

To calculate the "What-If" scenario

1. Append the following code to the LoanChange function, which serves as the event handler for the worksheet Change event:

       Try
           ' Check to see if the changed cell is WhatIf_Years, and if
           ' so, use the Goal Seek to compute the additional payment
           ' required to reach a zero balance in the specified number
           ' of years and show the Amortization Schedule.
           rng = ThisApplication.Intersect(Target, rngWhatIfYears)
           If Not (rng Is Nothing) Then
               sheet.Unprotect()
               RemoveHandler sheet.Change, AddressOf LoanChange
               Dim rngToZero As Excel.Range = CType( _
                   sheet.Range("$G$" & (10 + Convert.ToInt32( _
                   rngWhatIfYears.Value) * 12)), _
                   Excel.Range)
               rngToZero.GoalSeek(0, rngAddlPmt)
               AddHandler sheet.Change, AddressOf LoanChange
               nScheduleMonths = Convert.ToInt32( _
                   rngWhatIfYears.Value) * 12
               ShowSchedule(False)
               sheet.Protect()
               sheet.EnableSelection = _
                   Excel.XlEnableSelection.xlUnlockedCells
           End If
       Catch ex As Exception
           MessageBox.Show("Error creating amortization schedule: " & _
               ex.Message, "Amortization", MessageBoxButtons.OK, _
               MessageBoxIcon.Error)
       End Try
   

   When the workbook opens, you set up references to the defined ranges on the worksheet, wire the worksheet's event delegate to the LoanChange handler function, and call ShowSchedule to display (or hide) the amortization schedule.

2. Add the following code to the workbook Open event:

   Private Sub ThisWorkbook_Open() Handles ThisWorkbook.Open
   
       ThisApplication.ScreenUpdating = False
   
       ' Set up references for the worksheet and the defined
       ' ranges on the worksheet.
       sheet = CType(ThisWorkbook.Worksheets(1), Excel.Worksheet)
       rngLoanAmt = sheet.Range("Loan_Amount")
       rngIntRate = sheet.Range("Interest_Rate")
       rngYears = sheet.Range("Years")
       rngWhatIfYears = sheet.Range("WhatIf_Years")
       rngWhatIfArea = sheet.Range("WhatIf_Area")
       rngAddlPmt = sheet.Range("Additional_Payment")
       rngMonthlyPmt = sheet.Range("Monthly_Payment")
   
       AddHandler sheet.Change, AddressOf LoanChange
   
       ' Create the Amortization schedule.
       ShowSchedule(True)
   
       ThisApplication.ScreenUpdating = True
   
   End Sub
   

Testing the Application

Try it out:

1. Press F5 to build and run the project.

2. Enter the required data into the worksheet:

   Loan Amount: 175000

   Interest Rate: 7.5%

   Years: 30

   The monthly payment is calculated, and the amortization schedule appears. For the loan example above, the monthly payment would be $1223.63.

3. Change the number of years in the "What-If" scenario to 25.

   The additional payment required to reach a zero balance in 25 years is computed, and the amortization schedule is updated as illustrated in Figure 1 below.

   

   Figure 1. Updated amortization schedule

Exercise 2: Calling Back from Excel into Your Assembly

The objective of this exercise is to illustrate how you can call back from Excel into your assembly. One reason you might wish to make a callback is to use the return value of a function in the assembly as a worksheet function (or a user-defined function).

There is no way to call back into the assembly without using Visual Basic for Applications (VBA) code. All of the projects that you have created thus far have run under High macro security mode because managed code extensions are governed by Microsoft .NET Framework security and are not affected by the Excel VBA macro security rules. By injecting VBA code into the workbook to make a callback, the Amortization solution is subject to your VBA macro setting.

To call back from Excel into your assembly

1. Start Excel.

2. On the Tools menu, click Macro, and then click Security.

3. Change your security to Medium and click OK.

4. On the File menu, click Open.

5. Browse to select C:\Labs\Lab7\Amortization.xls.

6. Hold down the SHIFT key to suppress your code from running, and click Open.

   **Note   **To prevent your code from executing, you must open the workbook using the File menu. The code is not suppressed if you hold the SHIFT key and open the workbook from the Getting Started task pane.

7. Select cell G2.

8. On the Format menu, click Cells.

9. Select the Protection tab, clear the Locked check box, and click OK.

10. Press ALT+F11 to start the Visual Basic Editor.

11. On the Insert menu in the Visual Basic Editor, click Module.

12. Add the following VBA code to the module:

    Public objManaged As Object
    
    Public Sub RegisterCallback(o As Object)
        Set objManaged = o
    End Sub
    
    Public Function UserName() As Variant
        UserName = objManaged.UserName
    End Function
    

13. Close the Visual Basic Editor.

14. Save your changes to Amortization.xls and quit Excel.

15. In your Visual Studio .NET Amortization solution, append the following line of code to the Open event handler of the workbook:

    ThisApplication.Run("RegisterCallback", Me)
    

16. Add the UserName function to the OfficeCodeBehind class:

    Public Function UserName() As Object
        Try
            Return System.Security.Principal.WindowsIdentity. _
                GetCurrent.Name.ToString
        Catch ex As Exception
            Return "Error!"
        End Try
    End Function
    

Testing the Application

Try it out:

1. Press F5 to build and run the project.

2. In cell G2 of Amortization.xls, type the following formula and press ENTER:

   =UserName()
   

   The value returned by the UserName function in the managed code extension is returned to the cell.

   **Note   **If your VBA macro security is set to High, the managed code extension would continue to work, but the VBA wrapper function for UserName would not work unless the workbook is signed.

Next Steps

For more information, see Visual Studio Tools for the Microsoft Office System or visit the Office Developer Center.

For more information, as well as links to other labs in this series, see Visual Studio Tools for the Microsoft Office System Training.