Core Office Solution Development
Lab version: 1.0.1
Last updated: 3/28/2012
OVERVIEW ................................................................................................................................................... 4
EXERCISE 1: SETTING UP A DATA CONNECTION ................................................................................. 5
Task 1 – Accessing External Data in Excel ............................................................................................. 6
EXERCISE 2: CREATING AND USING PARAMETERIZED QUERIES IN EXCEL ................................... 8
Task 1 – Defining the Data Source ........................................................................................................ 8
Task 2 – Building the Query .................................................................................................................. 9
Task 3 – Hook up the query parameters to cells on the worksheet ................................................... 12
Exercise 2 Verification ......................................................................................................................... 13
EXERCISE 3: CREATE A NO-CODE, DATA-BOUND INTERACTIVE DISPLAY IN EXCEL .................. 14
Task 1 – Add Formulas to the Forecast worksheet ............................................................................. 14
Task 2 – Add Interactivity to the worksheet ....................................................................................... 15
Exercise 3 Verification ......................................................................................................................... 17
EXERCISE 4: INTRODUCTION TO VBA .................................................................................................. 17
Task 1 – Connect the Forecasting workbook to the Stores table in the Forecasting Database ......... 18
Task 2 – Add a mechanism for filtering Store information ................................................................. 19
Task 3 – Name ranges so they can be referenced easily in VBA ......................................................... 21
Task 4 – Create the User Form ............................................................................................................ 21
Task 5 – Provide a way to display the User Form ............................................................................... 26
Exercise 4 Verification ......................................................................................................................... 27
EXERCISE 5: USING VBA WITH THE EXCEL OBJECT MODEL ........................................................... 28
Task 1 – Unlock Parameter Cells on Forecast Worksheet .................................................................. 29
Task 2 – Name key ranges ................................................................................................................... 30
Task 3 – Add code to dynamically lock/unlock cells ........................................................................... 31
Task 4 – Add a feature to reset the worksheet formulas ................................................................... 32
Exercise 5 Verification ......................................................................................................................... 33
EXERCISE 6: DOCUMENT ASSEMBLY WITH WORD CONTENT CONTROLS AND VBA ................... 34
Task 1 – Add Content Controls to the Document ............................................................................... 34
Task 2 – Add VBA to populate the Content Controls .......................................................................... 37
Exercise 6 Verification ......................................................................................................................... 39
SUMMARY .................................................................................................................................................. 43
By combining the power and flexibility of core Microsoft Office applications with a little bit of
programming it is possible to develop solutions quickly and easily. In this HOL you will receive an
introduction to Office development.
The objective of this Hands-On Lab is to introduce you to core solution development techniques for
Office 2010. In particular, you will
Learn how to connect Microsoft Excel to external data sources
See how to use Microsoft Query to create more sophisticated data connections
Create a codeless interactive data analysis solution in Excel
Receive an introduction to the Visual Basic Editor and VBA to create a user form that interacts
with an Excel workbook
Use VBA with the Excel Object Model
Experience document assembly using Word content controls and VBA
You must have the following items to complete this lab:
Microsoft Access 2010
Microsoft Excel 2010
This Hands-On lab assumes that the Hands-On lab files are located in a folder named
%Office2010DeveloperTrainingKitPath%\Labs\VBA\Source on the drive you downloaded to. If you
haven’t already done so, perform the following steps
1. Open Windows Explorer
2. Navigate to the Source folder for this lab
This Hands-On Lab comprises the following exercises:
1. Setting up a Data Connection in Excel
2. Creating and using Parameterized Queries in Excel
3. Create a no-code, data-bound interactive display in Excel
4. Use VBA to create a user form
5. Using VBA with the Excel Object Model
6. Document assembly with Word content controls and VBA
Estimated time to complete this lab: 75 minutes.
This Hands-On Lab includes the following starting materials.
Access Database. The lab provides the database that you can use as starting point for the
The Budget database contains sample budgeting and forecasting information that is
accessed by this labs exercises.
Forecasting Workbook. The lab provides a workbook to use as a starting point for the Exercises
2 and 3.
Files\Forecasting.xlsm: The Budget database contains sample budgeting and forecasting
information that is accessed by this labs exercises.
Note: See the Completed Files folder for a completed example of the Forecasting workbook.
Exercise 1: Setting up a Data Connection
Out of the box, Microsoft Excel contains numerous features that allow you to connect Excel to external
data. For many reporting scenarios it is often possible to use a combination of data connections with
lookup formulas and data validation to create surprisingly interactive workbooks without writing any
Exercise 1 is designed to allow you to begin exploring a technique for accessing external data in Excel.
Task 1 – Accessing External Data in Excel
1. Open a new workbook in Excel
2. Click on the Data tab
3. In the Get External Data group click From Access
4. Open the database named Budget.accdb located at
5. Select the Stores table and click OK
6. Click OK. Notice that the data appears in a table in Excel.
7. On the Table Tools Design tab of the ribbon, click on Properties to display the External Data
Properties dialog box. Explore some of the properties you can modify.
External Data Properties
8. Click on the Connection Properties button in the upper right quadrant of the dialog box. This
will display the Connection Properties dialog box. Explore some of the connection properties
that you can modify.
9. Click on the Definition tab and explore the properties that you can modify.
10. Change the value of the Command text from Stores to Accounts.
11. Click OK to close the Connection Properties and then click OK again to close the External Data
Properties dialog. Notice that Excel displays the Accounts table now.
12. On the Table Tools Design tab of the ribbon, click Properties
13. Click on the Connection Properties button
14. Click on the Definition tab
15. Change the Command type to SQL
16. Set the Command text to SELECT * FROM [FACTS CROSSTAB]
WHERE [STORE NAME]='Warehouse District Store'
AND [FISCAL YEAR]=2009
17. Click OK to close the Connection Properties and then click OK again to close the External Data
Properties dialog. Observe that Excel displays information from the query you specified.
18. Notice that the table functionality in Excel allows you to easily sort and filter the information.
For example, click on the drop-down arrow in the Scenario Type column heading.
19. Uncheck Budget and click OK. Notice that Excel filters the records for you.
20. Close the Workbook, don’t save it.
Exercise 2: Creating and using
Parameterized Queries in Excel
It is amazing how many scenarios you can cover using simple data connections such as the one created
in Exercise 1. Combined with PivotTables, this is a powerful way to approach ad-hoc data analysis. For
those times when you need a more structured approach, more control over the presentation of the
data, or when you are working with more complex database structures, you can also employ the
capabilities of Microsoft Query. Query has been around for quite some time offers some great
capabilities, the most compelling of which is the ability to create parameterized queries. In this section,
you’ll learn how to use Microsoft Query to create parameterized queries.
Task 1 – Defining the Data Source
When accessing data with Microsoft Query, you need to have a data source defined for the data source
you wish to access. Defining a data source is a one-time activity. That is, once you define a data source,
it is available from that point on within any workbook on the computer.
1. Open the workbook named Forecasting.xlsm located at
2. Add a new worksheet to the workbook and name it Forecast Data
3. Click on the Data tab in the ribbon and click the Get External Data button
4. Select From Microsoft Query under the From Other Sources button
5. On the Databases tab, select <New Data Source> and click OK
6. Name the data source Budget Database
7. Select the driver named Microsoft Access Driver (*.mdb, *.accdb)
8. Click Connect…
9. Click Select… in the ODBC Microsoft Access Setup
10. Select the database named Budget.accdb located at
%Office2010DeveloperTrainingKitPath%\Labs\VBA\Source\Database and click OK
11. Click OK to close the ODBC Microsoft Access Setup dialog box
ODBC Microsoft Access Setup
12. Click OK to close the Create New Data Source dialog. You do not need to select a default table.
Task 2 – Building the Query
Now that you have a data source defined, the next step is to define the query. If you already have a data
source defined, you would skip steps 5-12 in the previous task as move directly in to the first step of this
task. Building a query with Microsoft Query is somewhat similar to designing a query in Microsoft
1. Picking up from where you left off in the previous task, you should be looking at the Choose
Data Source dialog box with the Budget Database data source selected.
2. Ensure that Use the Query Wizard to create/edit queries is not checked. The Query Wizard is
helpful for simple table-based queries, but it does not allow you to create a parameterized
query, which is what we want for this exercise.
3. Click OK in the Choose Data Source dialog to close this window and display Microsoft Query.
Note that after you define a data source as you did in steps 5-12, you can use it in the future
simply be selecting it in the Choose Data Source dialog box
4. Select Facts Crosstab from the list of tables and click Add.
5. Close the Add Tables dialog
Select ViewCriteria to show the criteria pane
6. Add fields from the Facts Crosstab table (technically it is a query defined in the Budget
database) to the query fields by double-clicking on each of them in the table. These will be
added to the results area at the bottom of the window. Be sure to add them in the following
a. Account – click the Sort Ascending button in the toolbar after adding this field to order
the results by Account.
n. Account Name
o. Fiscal Year
p. Scenario Type
7. Drag the following fields from the Facts Crosstab table to the Criteria Field in the Criteria pane
b. Fiscal Year
c. Scenario Type
8. Set the Criteria values as shown below. The brackets around the values designate these criteria
values as parameters.
a. Store: [Store ID]
b. Fiscal Year: [Year]
c. Scenario Type: [Scenario]
9. Test the query by selecting RecordsQuery Now. Supply the following values for the
a. Store ID = 1
b. Year = 2009
c. Scenario = Actual
10. Select FileReturn Data to Microsoft Excel
11. Put the data in the Data worksheet in cell B5.
12. Click OK
Task 3 – Hook up the query parameters to cells on the worksheet
At this point, you have data on the worksheet, but you have not really achieved much more than you
can do using the basic functionality you learned in Exercise 1. To harness the power of this technique
you need to map cells on the worksheet to the values used for the parameters.
1. In cell B1 enter the value: Store
2. In cell B2 enter the value: Year
3. In cell B3 enter the value: Scenario
4. In cell C1 enter the value: 1
5. In cell C2 enter the value: 2009
6. In cell C3 enter the value: Actual
7. Right-click on any cell in the data table and select TableParameters
8. Select the Store ID parameter
9. Choose the option Get the value from the following cell
10. Put the cursor in the selection text box and then choose cell C1 on the Data worksheet
11. Check Refresh automatically when cell value changes
12. Repeat steps 8-11 for the Year and Scenario parameters
13. Click OK
Exercise 2 Verification
In order to verify that you have correctly performed all steps of exercise 2, proceed as follows:
In this verification, you will test your work by changing the values of the cells that you mapped to query
1. Select cell C1 and change the value from 1 to 2. You should see a slight delay while the query is
processed, followed by a refresh of the data in the table.
2. Select cell C2 and change the value from 2009 to 2008. You should see a slight delay while the
query is processed, followed by a refresh of the data in the table.
3. Select cell C3 and change the value from Budget to Actual. You should see a slight delay while
the query is processed, followed by a refresh of the data in the table.
4. To prepare for the next exercise, set the values for the cells above back to:
a. Store = 1
b. Year = 2009
c. Scenario = Actual
Exercise 3: Create a No-code, Data-bound
Interactive Display in Excel
Exercise 2 demonstrated a powerful technique for displaying data in a dynamic fashion. For many
scenarios, displaying data in a table such as this is sufficient. Other scenarios however, require more
control over the presentation of the data. In this type of scenario, you can design the layout on a
separate worksheet and create formulas to retrieve data from the query table as needed. In this
exercise, you will create the forecast/budget report by mating the forecast worksheet to the table on
the Data worksheet.
Task 1 – Add Formulas to the Forecast worksheet
1. Switch to the Forecast worksheet. Note that some basic setup work is complete.
a. The income statement has been formatted as desired
b. Sub-totals have been added where appropriate
c. Unnecessary columns and rows have been hidden
d. Account ID’s corresponding to the account used in a given row have been added to
column A. This is to avoid having to hard-code these in formulas.
e. Column offsets are located in row one. This is to avoid having to hard-code these in
2. Select cell G12 and enter the formula:
3. Select the range G12:R12 and press CTRL+R to fill the formula from cell G12 to the rest of the
cells through December
4. With the range G12:R12 still selected, grab the drag handle in the lower right corner of cell R12
and drag fill the formula down to row 16
5. Select the range G12:R12 and press CTRL+C to copy the range
6. Select cell G20 and press CTRL+V to paste the formulas
7. With the range G20:R20 still selected, grab the drag handle in the lower right corner of cell R20
and drag fill the formula down to row 33
8. Select the range G12:R12 and press CTRL+C to copy the range
9. Select cell G37 and press CTRL+V to paste the formulas
10. With the range G37:R37 still selected, grab the drag handle in the lower right corner of cell R37
and drag fill the formula down to row 60
11. Test your work:
a. Note the total Net Income in cell S63. This value should be $47,252 using the parameter
values of Store=1, Year=2009, and Scenario=Actual.
b. Switch back to the Forecast Data worksheet and change the Year to 2008
c. Observe that the data refreshes with data from 2008.
d. Switch back to the Forecast worksheet
e. Note the total Net Income value in cell S63. This value should be $33,284 using the
parameter values of Store=1, Year=2008, and Scenario=Actual.
Task 2 – Add Interactivity to the worksheet
1. In cell E6, enter the value STORE
2. In cell E7, enter the value YEAR
3. In cell E8, enter the value SCENARIO
4. Add data validation to cell F6
a. Select cell F6
b. On the Data tab of the ribbon, click Data Validation
c. Choose List from the Allow drop-down box
d. Enter the values 1,2,3,4,5,6,7 in the Source text box
e. Click OK
5. Similarly, add data validation to cell F7 using the values 2008,2009 in the Source text box
6. Add data validation to cell F8 using the values Actual, Budget in the Source text box
7. Set the values of the drop-downs to the following values:
8. Switch to the Forecast Data worksheet and enter the following formulas:
a. In cell C1: =Forecast!F6
b. In cell C2: =Forecast!F7
c. In cell C3: =Forecast!F8
9. Add Sparklines to the Forecast worksheet
a. Switch back to the Forecast worksheet
b. Select cell F12
c. From the Insert tab in the Sparklines group, select Line
d. In the Create Sparklines dialog box, set the Data Range to G12:R12 and the Location
Range to $F$12. Click OK.
e. Fill cell F12 down to other rows containing data
10. Save the workbook
11. Test your work:
a. Choose store 2 from the drop-down list in cell F6. Observe that Excel refreshes the value
of the cells.
b. Choose 2008 from the drop-down list in cell F7. Observe that Excel refreshes the value
of the cells.
c. Choose Budget from the drop-down list in cell F8. Observe that Excel refreshes the value
of the cells.
12. (OPTIONAL) Add some polish to the worksheet:
a. Right-click on the heading for row 1 and select Hide to hide the row
b. Select columns A:C
c. Right-click and select Hide to hide the columns
d. Click on the View tab in the ribbon
e. Uncheck Gridlines, Headings, and Formula Bar
f. Press Ctrl + F1 to minimize the ribbon
Exercise 3 Verification
Assuming all of the “Test your work” steps in the exercise were successful, you should be looking at an
interactive worksheet that resembles Figure 1. As you change the values for Store, Year, and Scenario
you should see the entire worksheet refresh as Excel retrieves the values from the database on the
Forecast Data worksheet and then recalculates to display the correct values on the Forecast worksheet.
Exercise 3 Verification
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.
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
a. Select any cell located in the Stores table
b. Click on the Design tab of the ribbon
c. In the Properties group, change the value of Table Name to Table_Stores
Change Table Name Property
5. In cell B4, enter the formula: =DGET(Table_Stores[#All], B2,$B$2:$K$3)
a. 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.
7. Experiment with your work:
a. Change cell B3 to 2. Observe that the details for store 2 appear.
b. Change cell B3 to 10. Note that a #VALUE! error value appears because there isn’t a
c. Delete the value in cell B3 and enter the value Eagan in cell F3.
d. 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
e. 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:
a. B4: ‘Store Data’!StoreID
b. C4: ‘Store Data’!StoreName
c. E4: ‘Store Data’!StoreAddress
d. F4: ‘Store Data’!StoreCity
e. G4: ‘Store Data’!StoreState
f. H4: ‘Store Data’!StoreZip
g. J4: ‘Store Data’!StoreTelephone
h. 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
a. If the Developer tab isn’t visible on your ribbon, right-click anywhere on the ribbon
and choose Customize the Ribbon
b. 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
c. 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
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.
a. Select the worksheet with the actual name Forecast in the Project Explorer window.
b. 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.
c. Similarly, rename the Forecast Data worksheet to wsForecastData
d. 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 Store
8. Add controls to the form
a. Click on the Label control in the Toolbox (you can hover over the items to determine
what each one is)
b. 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.
i. You can move the control to a different location using drag and drop
ii. You can resize a control by dragging one of the drag handles that
appears on the control when it is selected
c. Click on the TextBox control in the Toolbox
d. Click on a location in the form to the right of the label you added in step b
e. 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.
Store Information Form
f. Click on the CommandButton control in the Toolbox
g. 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
h. Change the Caption properties of the labels
i. Using Figure 2 as a reference, select each label and modify the
ii. For example, click on the top-most label and set the value of the
Caption property to Store Name
i. Name the Text box controls
i. Select the text box to the right of the Store Name label
ii. Change the (Name) property to txtStore. Similarly, name the
remaining text box controls.
iii. Address: txtAddress
iv. City, State Zip: txtCity
v. Phone: txtPhone
vi. Manager: txtManager
j. Modify the CommandButton:
i. Name: cmdClose
ii. 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
Dim nStore As Integer
Public Property Let Store(StoreNumber As Integer)
nStore = StoreNumber
Public Property Get Store() As Integer
Store = nStore
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.
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 & " " & _
Me.txtPhone.Text = wsStoreData.Range("StoreTelephone").Value
Me.txtManager.Text = wsStoreData.Range("StoreManager").Value
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
14. Add the Statement Unload Me to the cmdClose_Click event handler. The entire event handler
should look as follows:
Private Sub cmdClose_Click()
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
9. On the Developer tab, click Visual Basic to go back to the VBE
10. 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.
11. 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.
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
Private Sub ShowStoreInfo(nStoreID As Integer)
Dim frm As New UserForm1
frm.Store = nStoreID
Set frm = Nothing
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.
Exercise 4 Verification
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.
Exercise 5: Using VBA with the Excel
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
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
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
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.
Private Sub SetRangeProtection(nMonthsToLock As Integer)
Dim nOffset As Integer
Dim bLock As Boolean
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
4. In the Object combo box immediately above the code module, change from (General) to
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.
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
' 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
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.
Private Sub ResetFormulas()
Dim sFormula As String
' Default formula in R1C1 notation
sFormula = _
Me.Range("JanRevenue").Resize(, 12).Formula = sFormula
Me.Range("JanCOGS").Resize(, 12).Formula = sFormula
Me.Range("JanExpenses").Resize(, 12).Formula = sFormula
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.
If Target.Address = Me.Range("Scenario").Address Or _
Target.Address = Me.Range("Year").Address Or _
Target.Address = Me.Range("Store").Address Then
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.
a. Click on wsForecastData in the Project Explorer pane
b. Set the value of the Visible property to 2 – xlSheetVeryHidden
c. Hide wsStoreData in the same manner
d. 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
Exercise 6: Document Assembly with
Word Content Controls and VBA
Thus far, you have worked exclusively with Microsoft Excel. In this Exercise, you will learn how to
combine Word Content Controls with VBA for robust data-driven document assembly. Many businesses
use Word templates for everything from one-off memo’s to extensive, highly customized and polished
research reports. Many business templates often require their users to populate specific portions of the
document with external data. In the absence of anything else, it is common, though not efficient, to just
enter the data manually. Not only is this terribly inefficient, it is often error prone. A much better
approach is to combine Word content controls with a little bit of VBA so that a user can simply select the
data used to generate the document.
In this Exercise, you will add content controls to a letter used by HR to notify job applicants of a hiring
decision. Then, using VBA, you will populate the values of the content controls based on a user’s
Task 1 – Add Content Controls to the Document
1. Open the document HR Applicant Letter.docm found at
%Office2010DeveloperTrainingKitPath%\Labs\VBA\Source\Starter Files. Several things
have already been done to this document:
a. A custom button labeled Select Applicant on the Insert tab (it is in a custom group
b. The document also contains a user form with the caption Generate Correspondence.
Provided the database this form uses is present, it should display a list of applicants
along with the type of correspondence to generate. Click on the custom button in
the ribbon labeled Select Applicant to view this form. Note that it does not do
anything else yet – that is what you’ll do.
c. Open the VBE by pressing CTRL + F11.
d. Double-click on ThisDocument in the HR Applicant Letter project. Observe that a
handful of code is present. You will be completing this code to implement the
e. Double-click on the CONSTANTS module in the HR Applicant Letter project. This
module contains the connection string used to connect to the Job Applicants
database (Job Applicants.accdb) that should be located at
%Office2010DeveloperTrainingKitPath%\Labs\VBA\Source\Database. If you move
this database or it is in a different location, modify the path located in the
f. The CONSTANTS module also contains a constant named BOILERPLATE. This is the
path to the document containing generic boilerplate text for use in generating
letters. If you move this document or it is in a different location, modify the path of
this constant. By default the value of this
g. Double-click on the form named frmApplicants in the HR Applicant Letter project.
This form is complete – you should not need to modify this form.
h. Switch back over to Word to view the HR Applicant Letter document
2. If the Developer tab is not visible in the ribbon, right-click on the ribbon and choose
Customize the Ribbon.
a. Place a check next to the Developer item on the right side of the window
b. Click OK
3. Add a content control for the applicant’s name
a. Select the text <TO DO: Name>
b. Click on the Developer tab on the ribbon
c. In the Controls group, click the small button with the screen-tip Rich Text Content
Control. Word will place a rich text content control in the document for the selected
Add Content Controls
d. Click Properties
e. Set the Title to Applicant
f. Check Content control cannot be deleted. The ability to “lock” a content control in
this manner is a chief advantage of using content controls as opposed to performing
a mail merge or using bookmarks. Notice that you can also prevent a content control
from being edited.
Content Control Properties
g. Click OK
4. Add a content control for the applicant’s address
a. Select the text <TO DO: Address>
b. Insert a rich text content control
c. Click Properties
d. Set the Title to Address
e. Check Content control cannot be deleted
f. Click OK
5. Repeat step 4 for the following items:
a. <TO DO: City> (set the title to City)
b. <TO DO: State> (set the title to State)
c. <TO DO: Zip> (set the title to Zip)
d. <TO DO: Salutation> (set the title to Salutation)
e. <TO DO: Body> (set the title to Body)
6. Press CTRL + S to save your work
Task 2 – Add VBA to populate the Content Controls
In this task, you will add some VBA code to retrieve data from the Job Applicant database to populate
the content controls.
1. Press ALT + F11 to switch to display the VBE
2. One key procedure that you need to write is a way to set the text of a content control given
a content control’s title. Double-click on ThisDocument in the HR Applicant Letter project to
view the code associated with ThisDocument.
3. Enter the following code at the bottom of this module (below the ResetContentControls
procedure). This code loops through the content controls in the document looking for
content controls with a title equal to the sCCTitle argument. When a match is found, the
procedure sets the text of the content control equal to the value supplied by the sValue
Private Sub SetCCValue(sCCTitle As String, sValue As String)
Dim cc As ContentControl
For Each cc In ThisDocument.ContentControls
If cc.Title = sCCTitle Then
cc.Range.Text = sValue
4. To test this code, complete the procedure named ResetContentControls. Enter the following
code beneath the TO DO comment in this procedure
SetCCValue "Applicant", "<applicant>"
SetCCValue "Address", "<address>"
SetCCValue "City", "<city>"
SetCCValue "State", "<state>"
SetCCValue "Zip", "<zip>"
SetCCValue "PositionTitle", "<positiontitle>"
SetCCValue "Salutation", "<salutation>"
SetCCValue "Body", "<body>"
5. Switch back to Word
6. Press Alt + F8 to display a list of runnable macros in the document
7. Highlight the macro named ResetContentControls and click Run. Observe that Word
updates the values in the content controls. If you run into problems, be sure to double-check
the spelling of each of the content controls’ title in the document and in the
ResetContentControls Macro Result
8. Press Alt + F11 to return to the VBE
9. The procedure LoadApplicant, immediately below the SelectApplicant procedure, is
responsible for retrieving the details associated with an applicant from the Job Applicants
database. This database is located at
Applicants.accdb. After you retrieve the record from the database, you can use the
SetCCValue procedure completed in step 3 to update the appropriate content controls. To
do this, enter the following code beneath the TO DO comment in the LoadApplicant
SetCCValue "Applicant", rst.Fields("ContactName").Value
SetCCValue "Address", rst.Fields("Address").Value
SetCCValue "City", rst.Fields("City").Value
SetCCValue "State", rst.Fields("StateProvince").Value
SetCCValue "Zip", rst.Fields("ZipPostal").Value
SetCCValue "PositionTitle", rst.Fields("Position Title").Value
SetCCValue "Salutation", rst.Fields("ContactName").Value
10. Review the procedure named LoadBody immediately below the LoadApplicant procedure.
This procedure loads the body content control with the appropriate text depending on what
type of correspondence is required. For this exercise, the boilerplate text is stored in a
separate document called HR Boilerplate Text.docx. LoadBody opens this document “behind-
the-scenes”, retrieves the content from the appropriate content control, and then copies the
content into the body content control. LoadBody requires one parameter, bOffer, that is
used to determine if the procedure loads an offer letter or a rejection letter.
11. The final step is to hook up the LoadBody and LoadApplicant procedures to the
SelectApplicant procedure. SelectApplicant is the procedure called when you click the
custom SelectApplicant button on the ribbon. All you need to do to finish this is call
LoadBody and LoadApplicant, passing in the applicant ID selected in the user form. Enter the
following lines of code below the TO DO comment in the SelectApplicant procedure:
Exercise 6 Verification
Perform the following steps to verify your work.
1. Press Ctrl + S to save your work
2. In the VBE, select DebugCompile Project. If everything is ok, the code will compile almost
instantly without any warnings or dialog boxes.
3. Switch back to Word
4. Click on the Insert tab
5. Click on the button labeled Select Applicant
6. Select the first applicant in the list, choose Offer Letter and click OK. Observe that the
applicant’s data appears in the document along with text for an offer letter.
Applicant Offer Letter
7. Click on Select Applicant again. This time choose the same applicant, except choose the
Rejection Letter option.
Applicant Rejection Letter
8. Notice in the first sentence of the rejection letter that it references the correct job title. This job
title is not hard-coded in the boilerplate document. The boilerplate document contains nested
content controls that VBA populates at runtime after copying them into the document.
9. Verify that when you close the form by canceling, nothing in the document is changed. Click
Select Applicant on the ribbon
10. Select the 2nd Applicant in the list
11. Close the Select Applicant form by clicking on the Close button (the X in the upper right corner).
Observe that nothing in the document changes.
The core Microsoft Office applications contain a great depth of functionality that you can leverage to
develop Office-based solutions. In this lab, you learned a basic and an advanced way to connect Excel to
an external data source to provide an interactive reporting experience. Additonally, you were
introduced to the Visual Basic Editor – a built-in development environment for writing Visual Basic for
Applications (VBA) code to automate Microsoft and develop custom user interfaces for your