Exercise 5: Using VBA with the Excel Object Model

In the first four exercises, you have started building a budgeting/forecasting application in Excel. Of course, in practice, an actual application involves many more considerations. Other things you need to think about include but are not limited to:

• Saving data to a database – thus far, the application only reads data from a database
• Security features that control user data access and manipulation. In budgeting/forecasting applications, you typically allow a user to see only the data that is associated with their scope of management. A department manager only has access to the numbers for his department while a division manager has access to the numbers for every department within her division.
• Data integrity features. These features prevent end-users from changing data that they should not change. In a budgeting/forecasting application for example, you typically would not want people to edit or change numbers that the company has already realized. For example, in October you would not allow people to change the actual income/expense numbers from March.
• A Formula reset feature – Although you can perform many tasks with formulas, many times either your application or users need to “overwrite” formulas to perform specific tasks. For these occasions, you need to provide a way for users to reset the default formulas.
• Workbook object visibility – Hiding non-essential worksheets, rows, or columns to protect the integrity of the workbook and increase user-comprehension of its contents.

In this exercise, you will explore the Excel object model by implementing basic data integrity features and a formula reset feature. The data integrity feature will dynamically lock/unlock ranges depending on the scenario and month. Since companies typically lock budgets after they complete them, whenever the scenario is changed to budget, everything will be locked. For forecasting (the “actual” scenario in the workbook), the procedure will consider the year and month. Everything will be locked except the future months – the months that represent the forecast.

You will also implement a formula reset feature. This feature would be required to allow users to adjust their forecasts. Since the data on the Forecast worksheet is retrieved via a lookup function, when a user changes their forecast, they would overwrite this formula. Although saving the data to a database is outside of the scope of this HOL, the process could work as follows (one of several ways you could go about this):

1. User modifies forecast and clicks on a Submit Forecast button
2. Using VBA, application reads data from Forecast worksheet and saves it to the database
3. Using VBA, the application resets the formulas prior to refreshing the data with new data from the database. This could occur the next time the user changes one of the parameters (store, year, or scenario) that triggers a refresh from the database.

Task 1 – Unlock Parameter Cells on Forecast Worksheet

In order to implement the data integrity feature, you need to protect the Forecast worksheet. By default, all cells are locked when you protect a worksheet. In order to allow the user to be able to change the store number, the scenario, and the year, you need to unlock these cells.

1. Open the Forecasting workbook from exercise 4.
2. Select the range F6:F8
3. Right-click and choose Format Cells

4. Click on the Protection tab and uncheck the Locked option

   Figure 1

   Format Cells Dialog

5. Click OK

Task 2 – Name key ranges

When working with the Excel object model, to make your programming task easier and the process more robust, it helps to name a few key ranges. For dynamically locking and unlocking cells on the forecast worksheet, your code needs to know which rows to lock/unlock. You should not just unlock entire columns because you do not want users to change the sub-total and total rows.

1. Select the range G12:G16
2. In the Name box, enter the name Forecast!JanRevenue and press Enter. Be sure to press Enter rather than click outside of the Name box, otherwise Excel will not associate the name with the range. This creates a worksheet scoped named range.
3. Similarly, select the range G20:G33 and name the range Forecast!JanCOGS
4. Select the range G37:G60 and name it Forecast!JanExpenses
5. Select the range F6 and name it Forecast!Store
6. Select the range F7 and name it Forecast!Year
7. Select the range F8 and name it Forecast!Scenario
8. Protect the worksheet. On the ribbon, select the Review tab and click Protect Sheet.
9. Click OK

Task 3 – Add code to dynamically lock/unlock cells

1. Press ALT + F11 to open up the VBE
2. Double-click on the Microsoft Excel Object named wsForecast in the Project Explorer pane to view the code associated with the Forecast worksheet

3. Add the following procedure at the bottom, underneath the procedure named ShowStoreInfo. This procedure uses the named ranges you established in the previous task along with an offset value to loop through all of the columns representing January through December. It determines what to lock by comparing the desired number of months to lock (nMonthsToLock) with the current column offset. Use the Offset method to move a specific number of rows and columns away from a given range.

   VBA

   Private Sub SetRangeProtection(nMonthsToLock As Integer) Dim nOffset As Integer Dim bLock As Boolean Me.Unprotect For nOffset = 0 To 11 bLock = (nOffset + 1) <= nMonthsToLock Me.Range("JanRevenue").Offset(0, nOffset).Locked = bLock Me.Range("JanCOGS").Offset(0, nOffset).Locked = bLock Me.Range("JanExpenses").Offset(0, nOffset).Locked = bLock Next nOffset Me.Protect End Sub

4. In the Object combo box immediately above the code module, change from (General) to Worksheet
5. In the Procedure combo box to the right of the Object combo box, select the Change item. Notice when you do this that VBA adds a Change event handler named Worksheet_Change to your code module.

6. Add the following code to the Worksheet_Change procedure. This code executes whenever there is a change to the worksheet. The procedure first checks to see if the change occurred to either the Scenario or Year. If so, it calls the SetRangeProtection procedure passing the appropriate number of months to lock.

   VBA

   If Target.Address = Me.Range("Scenario").Address Or _ Target.Address = Me.Range("Year").Address Then If Me.Range("Scenario").Value = "Budget" Then ' Lock all months in budget scenario SetRangeProtection 12 Else ' For purposes of HOL & simplicity, current year is ' assumed to be 2009 and the current month is assumed ' to be October If Me.Range("Year").Value = 2009 Then SetRangeProtection 10 Else SetRangeProtection 12 End If End If End If

Task 4 – Add a feature to reset the worksheet formulas

The final task is to add a feature that will reset the worksheet to the default formulas. When working with formulas programmatically, it is often helpful to use R1C1 notation. To view formulas using R1C1 notation, go to Excel’s options, click on Formulas, and place a check mark next to the R1C1 reference style option. Note that although R1C1 is useful during the development process, it does not matter which style of notation your end-users use – Excel will display the formula using whichever notation style is in use.

1. Add the following procedure to the bottom of the wsForecast module. This procedure sets the formula for all applicable cells on the Forecast worksheet. Using R1C1 notation in this instance allows you to specify one formula that works for every cell. R1C1 is preferred because if you use standard notation, you have to build the proper formula for each cell dynamically. This procedure uses the named ranges you created earlier. By using the Resize method, you can easily refer to all of the applicable cells by resizing the range so that is spans 12 columns rather than just 1.

   VBA

   Private Sub ResetFormulas() Dim sFormula As String Me.Unprotect ' Default formula in R1C1 notation sFormula = _ "=VLOOKUP(RC1,Table_Query_from_Budget_Database[#All],Forecast!R1C,FALSE)" Me.Range("JanRevenue").Resize(, 12).Formula = sFormula Me.Range("JanCOGS").Resize(, 12).Formula = sFormula Me.Range("JanExpenses").Resize(, 12).Formula = sFormula Me.Protect End Sub

2. The final step is to modify the Worksheet_Change event that so that you call the ResetFormulas procedure whenever data is refreshed. Keep in mind that the data is refreshed automatically whenever the Store, Year, or Scenario changes. Consequently, you can accomplish this by watching all changes to the worksheet. If a change occurs to one of these three cells, then call the ResetFormulas procedure. Add the following code to the Worksheet_Change procedure immediately above the End Sub statement.

   VBA

   If Target.Address = Me.Range("Scenario").Address Or _ Target.Address = Me.Range("Year").Address Or _ Target.Address = Me.Range("Store").Address Then ResetFormulas End If

3. Hide non-essential worksheets. The Forecast Data and Store Data worksheets are not essential to the use of the Forecasting workbook. When you have worksheets such as this, it is best to hide these worksheets from your end-users. While most people are familiar with “hidden” worksheets, Excel also provides a way to hide worksheets so that they are not even listed in the dialog box that allows you to unhide worksheets.
   1. Click on wsForecastData in the Project Explorer pane
   2. Set the value of the Visible property to 2 – xlSheetVeryHidden
   3. Hide wsStoreData in the same manner
   4. Switch back to Excel and observe that the worksheets are no longer visible
4. Press Ctrl + S to save your work.

Exercise 5 Verification

Perform the following steps to verify exercise 5.

1. On the Forecast worksheet, set Store=1, Year=2009, and Scenario=Budget.
2. Try to change values in row 12 (Sales-Groceries-taxable). Notice that all values are protected from January through December.
3. Set Scenario=Actual
4. Change the value of cell Q12 (November) to 5,000. Observe that you can edit this cell.
5. Select the range R12:R16 and press delete
6. Set Scenario=Budget. Observe that formulas are reset, data is refreshed, and all cells are locked again.