Exercise 4: Introduction to VBA

So far, without writing a single line of code, you have created an interactive forecast/budget reporting workbook. In this exercise, you will receive an introduction to working in the Visual Basic Editor (VBE) to create a user form and perform basic event handling. Specifically, you will create a user form to display Store information when a user clicks a hyperlink on the Forecast worksheet.

When displaying information from an external data source, there are several approaches you can take to bring the information into Excel. In this exercise, you will keep a local copy of the Store information in the workbook. This approach is useful when you have smaller data sets as it supports a “disconnected” workflow (i.e. the user does not have to have a persistent network connection) yet still provides an easy way to keep the data in synch with the system of record. In addition, it eliminates the need to write specialized data retrieval code.

Task 1 – Connect the Forecasting workbook to the Stores table in the Forecasting Database

The first step is to connect the workbook to the Stores table located in the Budget database.

1. If it is not already open, open up the Forecasting.xlsm workbook you used in Exercise 3.
2. Add a new worksheet named Store Data to the workbook.
3. Select cell B6 on the Store Data worksheet

4. Follow steps 2-6 from Exercise 1 to display data from the Stores table in the Budget database.

   Figure 1

   Connected Stores Table from the Budget Database

Task 2 – Add a mechanism for filtering Store information

Thinking about the ultimate goal – to display an individual Store’s record in a user form, at this point you may be tempted to write some code to loop through the list of stores to find the store you need. However, this is where you should resist the urge to write code and leverage the incredible functionality of Microsoft Excel to do the work for you.

From a user perspective, obviously it is easy to filter tables in Excel using Excel’s user interface. For example, if you want to filter the records to locate the store with a StoreID=3, you can click on the drop-down arrow in the StoreID column and uncheck everything except for 3.

From a programmatic perspective, things are a little more difficult. While it is easy, to apply the same filtering, there isn’t a good way to retrieve the result without writing a few extra lines of logic.

You could loop through the records in code, but this is inefficient and slow – especially as the size of your dataset grows.

One useful approach is to create a filter criteria range and then use Excel’s built-in database functions (specifically DGET()) to retrieve the correct record for you. The benefit of this approach is that Excel’s database functions are extremely fast, the criteria range is both easy to use and powerful, and the resulting record is in a set location so it is easy to reference via code.

1. On the Store Data worksheet, select the range B6:K6 and press CTRL + C to copy the range
2. Select cell B2 and press CTRL + V to paste the copied range
3. Enter the value 1 in cell B3. This will simply be an initial value for testing the formula you’re about to write.
4. Rename the data table to something meaningful
   1. Select any cell located in the Stores table
   2. Click on the Design tab of the ribbon

   3. In the Properties group, change the value of Table Name to Table_Stores

      Figure 2

      Change Table Name Property

5. In cell B4, enter the formula: =DGET(Table_Stores[#All], B2,$B$2:$K$3)
   1. NOTE: The DGET is used to retrieve a field value from a specific record in a database. Though the functions are called database functions in Excel, they actually operate on lists in Excel. DGET takes three parameters. The first parameter is a reference to a range containing a list. The second parameter is the index number of the name of the field that the function should retrieve a value from. The final parameter is a reference to a range containing the criteria that is used to filter the list.

6. Grab the drag handle of cell B4 and use it to fill the formula across to cell K4.

   Figure 3

   DGET

7. Experiment with your work:
   1. Change cell B3 to 2. Observe that the details for store 2 appear.
   2. Change cell B3 to 10. Note that a #VALUE! error value appears because there isn’t a matching record.
   3. Delete the value in cell B3 and enter the value Eagan in cell F3.
   4. Enter the value St. Paul in cell F3. Note that a #NUM! error value appears. This is because DGET found more than 1 record. Because of this behavior, it is best to limit the use of DGET to situations in which you are certain only unique records will be returned. In this case, filtering on StoreID is appropriate because this field contains unique values.
   5. Delete the value in cell F3 and enter the value 1 in cell B3.

Task 3 – Name ranges so they can be referenced easily in VBA

Now that you have a way to locate a specific store from the list of stores, it is helpful to name the cells your code will reference. You can scope named ranges so that they are visible to either a worksheet or the entire workbook. Generally, it is best to scope named ranges to a worksheet. To create a workbook named range, you just enter the desired name in the Name Box (to the left of the Formula Bar). To create a worksheet named range, do the same but preface the name with the name of the worksheet in single quotes followed by an exclamation mark.

1. On the Store Data worksheet, select cell B3.
2. In the name box, enter the value ‘Store Data’!StoreLookupID and press Enter to save the name.
3. In a similar fashion, name the following cells:
   1. B4: ‘Store Data’!StoreID
   2. C4: ‘Store Data’!StoreName
   3. E4: ‘Store Data’!StoreAddress
   4. F4: ‘Store Data’!StoreCity
   5. G4: ‘Store Data’!StoreState
   6. H4: ‘Store Data’!StoreZip
   7. J4: ‘Store Data’!StoreTelephone
   8. K4: ‘Store Data’!StoreManager

Task 4 – Create the User Form

It is surprisingly easy to create user forms for your Office application. If you have used a visual form designer in other programming environments or languages, you will not have any problem creating a custom form using the Visual Basic Editor.

1. Make sure the Developer tab is visible
   1. If the Developer tab isn’t visible on your ribbon, right-click anywhere on the ribbon and choose Customize the Ribbon
   2. Place a check next to the Developer item in the list of Main Tabs on the right-hand side of the Excel Options dialog box
   3. Click OK
2. On the Developer tab, click Visual Basic
3. Ensure that the Project Explorer window is visible by selecting ViewProject Explorer

4. Ensure that the Properties windows is visible by selecting ViewProperties Window

   Figure 4

   Project Explorer and Properties Window

5. Rename the Code Names of the existing worksheets in the book. Worksheets have two “names”. The name we most often think of is the name that appears on the tab of a worksheet. For example, Forecast worksheet, Forecast Data worksheet, and Store Data worksheet. Worksheets also have a code name. This is the name of the worksheet in the VBA environment. You can see the code name of each worksheet in the Project Explorer (you may need to expand the node named Microsoft Excel Objects. The Project Explorer displays the code name of each worksheet followed by the name of the worksheet in parenthesis.
   1. Select the worksheet with the actual name Forecast in the Project Explorer window.
   2. In the Properties window, change the value of the (Name) property to wsForecast. Note that the name you are changing is actually the code name rather than the name that appears on the worksheet’s tab.
   3. Similarly, rename the Forecast Data worksheet to wsForecastData
   4. Rename the Store Data worksheet to wsStoreData
6. Right-click on the item in the Project Explorer labeled VBAProject (Forecasting.xlsm) and select InsertUserForm. Notice that the VBE displays a Toolbox window in addition to displaying a new form in design view. If you inadvertently close the Toolbox window, you can display it again by selecting ViewToolbox.
7. In the Properties window, change the value of the UserForm’s Caption property to StoreInformation
8. Add controls to the form
   1. Click on the Label control in the Toolbox (you can hover over the items to determine what each one is)
   2. Click on the location where you would like to position the upper-left corner of the label control. Observe that the VBE creates a label control in the location you clicked.
      1. You can move the control to a different location using drag and drop
      2. You can resize a control by dragging one of the drag handles that appears on the control when it is selected
   3. Click on the TextBox control in the Toolbox
   4. Click on a location in the form to the right of the label you added in step b

   5. Repeat steps a-d 4 more times. Use the image below as a guide for arranging the controls. In the interest of time, do not worry about pixel perfect placement.

      Figure 5

      Store Information Form

   6. Click on the CommandButton control in the Toolbox
   7. Click on a location in the form near the bottom to place the CommandButton. Note – you can easily resize the form by clicking on the appropriate drag handle and dragging to the desired size
   8. Change the Caption properties of the labels
      1. Using Figure 2 as a reference, select each label and modify the caption property
      2. For example, click on the top-most label and set the value of the Caption property to Store Name
   9. Name the Text box controls
      1. Select the text box to the right of the Store Name label
      2. Change the (Name) property to txtStore. Similarly, name the remaining text box controls.
      3. Address: txtAddress
      4. City, State Zip: txtCity
      5. Phone: txtPhone
      6. Manager: txtManager
   10. Modify the CommandButton:
       1. Name: cmdClose
       2. Caption: OK
9. Right-click on UserForm1 in the Project Explorer window and choose View Code

10. Add a property named Store to the form. Add the following code starting at the top of the module:

    VBA

    Option Explicit Dim nStore As Integer Public Property Let Store(StoreNumber As Integer) nStore = StoreNumber LoadStore End Property Public Property Get Store() As Integer Store = nStore End Property

11. Beneath the code you added in the previous step, add a method that transfers the store information located on the Store Data worksheet to the controls on the user form.

    VBA

    Private Sub LoadStore() ' Using the store lookup mechanism on the Store Data ' worksheet, lookup the required store. wsStoreData.Range("StoreLookupID").Value = nStore ' Transfer the values to the controls on this form Me.txtStore.Text = wsStoreData.Range("StoreName").Value Me.txtAddress.Text = wsStoreData.Range("StoreAddress").Value Me.txtCity.Text = wsStoreData.Range("StoreCity").Value & ", " & _ wsStoreData.Range("StoreState").Value & " " & _ wsStoreData.Range("StoreZip").Value Me.txtPhone.Text = wsStoreData.Range("StoreTelephone").Value Me.txtManager.Text = wsStoreData.Range("StoreManager").Value End Sub

12. Right-click on UserForm1 in the Project Explorer window and choose View Object
13. Double-click on the CommandButton control on the form. Observe that the VBE automatically switches to the Code view of the form and generates an event handler for the CommandButton click event.

14. Add the Statement Unload Me to the cmdClose_Click event handler. The entire event handler should look as follows:

    VBA

    Private Sub cmdClose_Click() Unload Me End Sub

Task 5 – Provide a way to display the User Form

The final task is to create a way to allow the user to access the Store Information dialog box.

1. Switch back to Excel by clicking on the Excel item in the Windows taskbar
2. Select the Forecast worksheet
3. Select cell G6
4. On the Insert ribbon tab, click on the Hyperlink item
5. On the left side of the dialog box, click on Place in This Document
6. Set Text to display equal to Info
7. Type the cell reference should be G6
8. Click OK to close the Insert Hyperlink dialog

1. On the Developer tab, click Visual Basic to go back to the VBE
2. Double-click on the Microsoft Excel Object named wsForecast (Forecast) in the Project Explorer to open up the code module that is associated with the Forecast worksheet.

3. Add the code shown below. The ShowStoreInfo procedure is used to create a new instance of the Store Information user form, set the store property to the appropriate value, and then show the form. The Worksheet_FollowHyperlink procedure is an event handler that gets called by Excel whenever a hyperlink is clicked on the Forecast worksheet. If cell containing the hyperlink is equal to “Info” then the procedure gets the value of the cell immediately to the left of the hyperlink (the current store ID) and then calls the ShowStoreInfo procedure. Just in case the cell to the left of the hyperlink does not contain an integer value, the statement On Error Resume Next is used to ignore any errors and allow the procedure to go on about its business.

   VBA

   Option Explicit Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) On Error Resume Next If Target.Range.Value = "Info" Then ShowStoreInfo Target.Range.Offset(0, -1).Value End If End Sub Private Sub ShowStoreInfo(nStoreID As Integer) Dim frm As New UserForm1 frm.Store = nStoreID frm.Show Set frm = Nothing End Sub

Exercise 4 Verification

To verify your work, begin by trying to compile your code. To do this:

1. Open up the VBE
2. Select DebugCompile VBAProject

If everything is OK, your project will compile without warning or notifications. Compilation happens extremely quickly. If your project contains an error, the VBE highlights the offending line and displays a dialog box indicating what the problem is. Note that if you do not compile the project, the VBE will automatically compile your code as it is needed.

Next, switch back to Excel (you can leave the VBE open in case you need to perform some debugging) and click on the hyperlink labeled Info on the Forecast worksheet. This should display the Store Information dialog box and it should contain the current store’s general information. When you click OK the Store Information dialog should disappear.

Finally, change the Store on the Forecast worksheet. You should see the worksheet update itself with the budget information for the selected store. Now if you click on Info, you should see the new store’s information displayed in the Store Information dialog box.