Docstoc
EXCLUSIVE OFFER FOR DOCSTOC USERS
Try the all-new QuickBooks Online for FREE.  No credit card required.

Lab

Document Sample
Lab Powered By Docstoc
					Hands-On Lab
Core Office Solution Development
Lab version:    1.0.1
Last updated:   3/28/2012
CONTENTS

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
Overview
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.


Objectives
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



System Requirements
You must have the following items to complete this lab:
       Microsoft Access 2010

       Microsoft Excel 2010



Setup
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
Exercises
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.


Starting Materials
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
        exercises.
            ◦   %Office2010DeveloperTrainingKitPath%\Labs\VBA\Source\Database\Budget.accdb:
                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.
            ◦   %Office2010DeveloperTrainingKitPath%\Labs\VBA\Source\Starter
                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
code.
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
       %Office2010DeveloperTrainingKitPath%\Labs\VBA\Source\Database
    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.




        Figure 1
        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.
   Figure 2
   Connection Properties


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
       %Office2010DeveloperTrainingKitPath%\Labs\VBA\Source\Starter Files
    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




        Figure 3
        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
Access.
    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 ViewCriteria to show the criteria pane




   Figure 4
   View Criteria


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
   order:
       a. Account – click the Sort Ascending button in the toolbar after adding this field to order
          the results by Account.
       b. 1
       c. 2
       d. 3
       e. 4
       f.   5
       g. 6
       h. 7
       i.   8
       j.   9
       k. 10
       l.   11
       m. 12
       n. Account Name
       o. Fiscal Year
       p. Scenario Type
       q. Store
7. Drag the following fields from the Facts Crosstab table to the Criteria Field in the Criteria pane
       a. Store
       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]




            Figure 5
            Criteria Values


9. Test the query by selecting RecordsQuery Now. Supply the following values for the
   parameters:
            a. Store ID = 1
            b. Year = 2009
            c. Scenario = Actual
    10. Select FileReturn 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 TableParameters
    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
        Figure 6
        Parameters


    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
parameters.
    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
               formulas.
    2. Select cell G12 and enter the formula:
       =VLOOKUP($A12,Table_Query_from_Budget_Database[#All],Forecast!G$1,FALSE)
    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:
           a. Store=1
           b. Year=2009
           c. Scenario=Actual
    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.




           Figure 7
           Create Sparklines


       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.




Figure 8
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.




        Figure 9
        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
            Figure 10
            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.




   Figure 11
   DGET
    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
                   matching record.
                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
                   unique values.
                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 ViewProject Explorer
4. Ensure that the Properties windows is visible by selecting ViewProperties Window




   Figure 12
   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 InsertUserForm. 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 ViewToolbox.
7. In the Properties window, change the value of the UserForm’s Caption property to Store
   Information
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.
Figure 13
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
                    caption property
               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
      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




Figure 14
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.
        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 DebugCompile 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.




Figure 15
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
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 16
       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.
            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
       again.



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
selection.
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
                   labeled Contoso).
                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.
        Figure 17
        Project Explorer
        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
           desired functionality.
        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
           CONN_STRING constant.
        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
             %Office2010DeveloperTrainingKitPath%\Labs\VBA\Source\Starter Files\HR
             Boilerplate Text.docx
        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
           range
        Figure 18
        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.




        Figure 19
        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
          argument.
        VBA
        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
                End If
            Next
        End Sub
4. To test this code, complete the procedure named ResetContentControls. Enter the following
   code beneath the TO DO comment in this procedure
VBA
      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 procedure.




Figure 20
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
          %Office2010DeveloperTrainingKitPath%\Labs\VBA\Source\Database\ Job
          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
          procedure:
        VBA
                  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:
        VBA
                   LoadBody frm.optOffer
                   LoadApplicant nID




Exercise 6 Verification
Perform the following steps to verify your work.
    1. Press Ctrl + S to save your work
    2. In the VBE, select DebugCompile 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




   Figure 21
   Applicant Selection


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.
   Figure 22
   Applicant Offer Letter


7. Click on Select Applicant again. This time choose the same applicant, except choose the
   Rejection Letter option.
   Figure 23
   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.
Summary

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
applications.

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:19
posted:3/28/2012
language:English
pages:43