ObjectOriented Programming An Overview
I decided early on in the process of writing this book that I would concentrate my efforts on providing guidance in the creation of class-based solutions to Excel VBA coding problems where possible. I have found through programming in Access, VB 6, and then VB.NET that using OOP techniques has helped me visualize my applications more clearly, helped me to better organize my code, and has been invaluable in making my code easier to maintain. Standard code modules and procedural programming allow...
Writing a Macro in the VBE
In this example, you'll create a macro by typing code directly in the VBE. Open the file 1-MacroExample01.xlsx shown in Figure 1-21 , and open the VBE. Note You will find all the example files and source code for this book at www.apress.com in the Downloads section of this book's home page. Figure 1-21. Sales data for the first quarter of the year Figure 1-21. Sales data for the first quarter of the year We see tour sales for the fictitious band VBA, which are received quarterly by their...
The HRWizard Business Objects
We'll begin by designing our business objects. These classes will store the data for each object person, address, equipment, and access level and will contain any business rules for each object. Add a new class module to the project and name it cPerson. Add three more class modules, naming them cAddress, cEquipment, and cAccess. Our cPerson object will contain one each of cAddress, cEquipment, and cAccess objects. To keep them in sync, we'll add an ID property to each of our four business...
Putting Data into a PivotTable Report
In the Download section for this book on the Apress web site, find the file named PivotTable01.xlsx and open it. Remember our fictitious band VBA from Chapter 1 Well, they've been out touring and their manager wants to see what's selling and what's not, and where items are selling best. PivotTable01.xlsx contains sales data from the first quarter of their tour, as shown in Figure 6-1. A good way for the manager to look at this data is via an Excel 2007 PivotTable report. We're going to record a...
Macro RecorderGenerated Text Import Code
After we stop the Macro Recorder, we are left with code that looks like Listing 2-2. Listing 2-2. Macro-Generated Text Data Import Code Sub GetTextData 2 files maillist.csv, .TextFileTextQualifier xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter False .TextFileColumnDataTypes Array 2, 2, 2 .TextFileTrailingMinusNumbers True Application.Goto Reference maillist Range A1 .Select End Sub One of the first differences to notice about this code when compared to the Access data import is how...
Blank Data Records
To see the effect of blank records on a PivotTable report, let's make Sheetl active and remove the data for Reading, PA's tank top sales. The Quantity and Sales Total values are 0, but we want to make them blank as though no data were added as shown in Figure 6-15 . Figure 6-15. Blank data for Reading, PA tank top sales Figure 6-15. Blank data for Reading, PA tank top sales 1. Activate the worksheet containing the PivotTable report. 2. Refresh the data either through the UI or the...
Of Excel Data and Object Orientation
Earlier in this book, I promised that we'd see object-oriented solutions to our coding problems in Excel 2007. Let's take our manager list-creation code and the code that lists a manager's staff, and convert them to classes. Normally, this is the way I would directly approach a solution, but up to this point we've been exploring some of the VBA possibilities in Excel 2007. Open DataAccessSample04.xlsm and save it as DataAccessSample06.xlsm. Open Modulel in the VBE and review the GetManagerList...
Using the cExcelSetup and cData Objects
Now that we've created the objects we need, let's put them to use in client code. In the VBE, add a new standard module and name it basManagers. Add two module-level variables to hold our cExcelSetup and cData objects Dim m_cData As cData Dim m_cXL As cExcelSetup These are placed at module level in case we need to use the objects across function calls. Create a new subroutine and name it GetManagers. Add the following code Dim sConnString As String Dim sSQL As String Set m_cXL New cExcelSetup...
Creating a Custom Task Pane and Data Input Form Using NET
In this example, we'll look at a .NET project that creates a custom task pane and a simulated Excel UserForm. A task pane is a window that anchors itself to the right of an Office application and contains commands to perform various functions. A common Office task pane is the Getting Started task pane, shown in Figure 9-21. Figure 9-21. Getting Started task pane in Excel 2003 Figure 9-21. Getting Started task pane in Excel 2003 Our example task pane will contain commands used by a human...
Summary Tcr
This chapter has explored UserForms in Excel 2007. UserForms allow developers to provide a clean, easy-to-navigate data entry or retrieval experience to users. When users are entering data into a large or unwieldy spreadsheet, we can provide a logical user experience by creating applications with UserForms. Form design is quick and easy using the controls provided in the UserForm Toolbox. Although both of the samples in this chapter used only one UserForm each, you can place as many as you need...
Is the File There
One common error occurs when we try to open a file that is not available. In Chapter 3, we opened files that contained various information ranging from CD lists to employee data. What if the file wasn't there or was misnamed In the Download section for this book on the Apress web site, find the file named . XML_data.xlsm, and open the file. This is a blank workbook that includes code modules from Chapter 3. 1. Open the VBE by clicking the Visual Basic command on the Code tab of the Developer...
The Object Browser
The Object Browser, shown in Figure 1-26, is displayed by choosing View gt Object Browser or by pressing the F2 function key. It displays the classes, properties, methods, events, and constants available from any object libraries, and it also shows the procedures in your project. You can also use it to find and use custom objects you create. The Object Browser window contains window elements that enable you to search for a method or property within an object library and to get information about...
Appending XML Data
Both the Excel Workbook object and the XmlMaps collection contain methods for appending or overwriting XML data in a workbook. The Workbook.XmlImport method and the Xmlmaps. Import method provide functionality to do either. Both methods take Overwrite arguments, which when set to False will append data to any existing data. The XmlImport method, however, will not append data when Overwrite is set to False if the optional Destination argument is used. In this case, nothing will happen the append...
Completing the PlaceChartDynamic Procedure
The PlaceChartDynamic subroutine currently looks like Listing 5-5, and is ready for a few modifications, including using the GetChartInfo method we just created. Listing 5-5. The PlaceChartDynamic Routine Is Ready for Modifications Sub PlaceChartDynamic Dim spacer As Integer ActiveSheet.Shapes.AddChart , arrChartInfo 2 , arrChartInfo 1 arrChartInfo 3 spacer .Select ActiveChart.SetSourceData Source Range 'Sales By Category' A 6 C 9 ActiveChart.ChartType xlPie ActiveChart.SeriesCollection 1 .Name...
Note The runtime error shown in Figure was generated in Windows XP Windows
We can't drop another PivotTable on top of an existing PivotTable. Let's make a few changes to our code to allow us to create our PivotTables dynamically based upon data that is currently being viewed by the user. There are two issues that stand out in our existing code We have to add a new worksheet for an additional PivotTable for the data because we can't use the existing sheet or we have to find a new location on the existing worksheet . What if the source data range expands or shrinks the...
Managing Lists
Some of the data inputs on our HRWizard UserForm are being displayed to the user via ComboBox controls. The HRWizard data file contains a worksheet named ListMgr that contains the data for each list. The data is stored in named ranges on the ListMgr worksheet. Our cListManager class will contain functions that let us populate our combo boxes from these named ranges. We'll also add a method to bind a list to a VBA Collection object. This concept could easily be expanded to include lists gathered...
C
cAccess class module adding to project, 161 code for, 168 cAddress class module adding to project, 161 code for, 165 call stack feature, 270-271 Call Stack window, opening, 270 Cancel button, coding, 348 Cancel command button, code for Userform1, 147 Candy chart, testing code for, 220-221 cCustSurvey class module, 140-142 coding into UserForm, 143-150 creating, 139-143 creating Save method in, 142-143 validation results, 148 cData class, code from ActiveX component, 318-319 cData class module...
ADO Example Importing SQL Data Based on a Selection
In this exercise, we'll see how we can use Excel to generate a list, and how by making a selection from that list we can view detailed information about the selected item. Adventure Works management wants to see a quick view of their reporting tree by manager. We're going to create a list of managers and then add code that will show the selected manager's reporting structure. On Modulel, add a new subroutine and name it GetManagerList. Add the following variable declarations Dim cnn As...
Laying Out the Wizard Form
1. Open the VBE and add a new UserForm. 2. Set the form's height to 320 and its width to 332. 3. Rename the form to HRWizard. 4. Add a Label to the top of the form, set its caption property to MyCompany - HR Wizard, and set the font to a large size like 18 pt. This will be the main heading for 5. Add a MultiPage control to the form. 6. Set its Height property to 216 and its Width property to 270. 7. Center it on the form, leaving room at the bottom. Your form in Design view should look...
UserForms
Excel provides us with UserForms as a means to provide a user interface UI to our Excel applications. UserForms are similar to Access or Visual Basic forms. They are containers for input and display controls. Both the forms and controls have properties, methods, and events that we can code against. Excel names new forms UserForm1, UserForm2, and so on, as they are added. They can be renamed as needed. UserForms are inserted into your project by choosing Insert gt UserForm or by right-clicking...
Designing the Form
From the sample files for this book, open the workbook named UserForm.xlsm. This file contains the Customer Survey database shown in Figure 4-1. Our custom form will collect and save its data to this table. Figure 4-1. The Customer Survey database Figure 4-1. The Customer Survey database Open the VBE, and add a UserForm to the project. You can do this by choosing Insert gt UserForm, or you can right-click in the Project Explorer window and choose Insert gt UserForm, as shown in Figure 4-2....
Coding the Presentation
Before we could access any of the Word DOM objects in the previous example, we had to add a reference to the Word Object Model to our code project. Before we can access any of the PowerPoint DOM objects, we must also add a reference to the PowerPoint Object Model. 1. Open the VBE by selecting the Developer ribbon gt Code tab gt Visual Basic command, or by pressing Alt F11. 2. In the VBE, select Tools gt References to display the References dialog box shown in Figure 8-8 . Microsoft OLE DB...
Summary
In Chapter 1, you looked at the Excel 2007 Macro Recorder and learned how to use it to let Excel generate code for you. This is useful when learning the Excel 2007 object model, and it can also be used to generate base code that you can then edit to suit your purpose. You worked with the Excel Visual Basic Editor VBE , where you created macros to enter and format data. The VBE is similar to the VB 6 code editor, and includes many of the same tools for debugging your code, such the Immediate...
Coding the UserForm
We just created a class to handle our data and modified our Excel Utility class to help the cCustSurvey class. Let's put cCustSurvey to work by coding it into our UserForm. Open the Customer Survey form UserForm1 . Open the code view by clicking the View Code button on the Project Explorer toolbar, as shown in Figure 4-7. Figure 4-7. The View Code button displays the code window for UserForm1 selected . Add the following module-level variables in the UserForm code window Private m_oCustSurvey...
The Data Class
Our data class is named cHRData. This class is being designed specifically for our HRWizard application, and will be closely coupled with our cPerson object and our EmpData worksheet. Insert a new class module and name it cHRData. Add the following module-level variables, one property, and one method Private m_oWorksheet As Worksheet Private m_lngNewRowNum As Long Private m_oEmployee As cPerson Private m_oXL As cExcelUtils Public Property Get Worksheet As Worksheet Set Worksheet m_oWorksheet...
Note The path to the XML file will vary based on where you are storing the
4. In the GetXMLData subroutine, change the name of the XML file we're opening to cd.xml. The subroutine should look like Listing 7-8 now. Listing 7-8. GetXMLData Procedure After Modification Sub GetXMLData ActiveWorkbook.XmlImport URL C Chapter 3 files cd.xml, ImportMap Nothing, Overwrite True, Destination Range A 1 End Sub 5. In Excel, make Worksheetl the active sheet. 6. From the Macro dialog box, run the GetXMLData procedure. We get a very ugly error, as shown in Figure 7-38. Run-time error...
The Watch Window
The Watch window is a tool that allows you to set conditions on which you can put your code into break mode. The Watch window, shown in Figure 7-33, has a few options as to how to handle watched values. You enter an expression in the Expression text box, and then select the context of the expression from the Procedure and Module drop-down lists. Then you select a watch type. Watch types are described in Table 7-3. Watch Expression Code execution will not be stopped. In break mode, the values of...
Class Modules
If you've done any amount ofVBA or VB coding, you have more than likely used objects in your code. Any time you've gone out to a database and retrieved records using ADO, you may have declared and instantiated a variable like this Dim rs As ADODB.Recordset Set rs New ADODB.Recordset Some of the examples you've seen thus far have also used some of Excel's built-in objects, like the Selection object, which has a Font property, or the Range object, which has many properties and methods you can use...
The Application Folder
The next folder to explore is the application folder, whose name will reflect the program used to create the file. Ours is named xl for Excel , as displayed in Figure 3-24. The application folder contains application-specific document files. Some of these files are found in their own folder, but the root contains the workbook part and the sharedStrings data part.
The [ContentTypesxml File
Another file of interest is the Content_Types .xml file found in the root folder we created unzippedExcelfiles . It lists the content types for the other parts included in the Excel file package. Content types are the types of parts that can be included in a package. Following is a list of the content types that can be found in an Office document
Using an ActiveX Component in Excel
Our ActiveX example allows us to put data on the worksheet of our choice in our current workbook and pass in a SQL statement to retrieve whatever data we need. This example will mimic the functionality of our ADO example from Chapter 2 in the file DataAccessSample03.xlsm. Listing 9-1 shows the original VBA code we wrote in Chapter 2. Listing 9-1. ADOTest Macro from Chapter 2 Sub ADOTest Dim cnn As New ADODB.Connection Dim rs As ADODB.Recordset Dim xlSheet As Worksheet Dim sConnString As String...
The rels Folder
The _rels folder is the first place you should look to see what your Excel file is made of. The _rels folder contains a file named .rels. This file contains the top-level relationships. In your file, they should look like Figure 3-23. i xml version 1.0 encoding UTF-8 standalone yes gt lt R amp lationships xm lt Relationship Id rld3 Type Target-'docProps app.xml . i Relationship Id rld2 Type- Target-'docPraps core.xmi gt lt RGlatlonshlp Id rldl Type-1 Target xl workbook.xml gt This file contains...
Creating a Simple Data Entry Form
Let's create a form for quick data entry for a call center doing a one-minute customer contact. The user's task is to call the customer and find out if they've heard of the Widget and whether they're interested in finding out more about the product. If the customer is interested, the user will mark that on the form as well so sales can follow up. Note The example files and source code for this book are available to readers atwww.apress.comin the Downloads section of this book's home page.
Creating WizardStyle Data Entry UserForms
Wizard-style entry is a fairly common technique used to help users enter data in long or complex forms. Wizards allow you to break your data into related sections, allowing you to guide the user through an orderly data entry process. From the sample files for this book, open the file named HRWizard.xlsm. This file consists of two worksheets. The employee database worksheet, named EmpData, is shown in Figure 4-14. Figure 4-14. The HRWizard.xlsm EmpData database worksheet Figure 4-14. The...
Macro Security Settings
Excel's default security settings do not allow any macro activity. Before you begin exploring macros in Excel and the Macro Recorder, you will need to tell Excel which security settings to use to control what happens when you open a workbook that contains macros or one that will contain macros . If you use antivirus software that works with Microsoft Office 2007 and you open a workbook that contains macros, the virus scanner will check the workbook for viruses before opening it. You can make...
ADO Example Importing SQL Data
For our first ADO example, we're going to use the AdventureWorks sample database provided by Microsoft. You can install a copy of the AdventureWorks database by running the file AdventureWorksDB.msi. You will be using SQL Server 2005 Management Studio Express to view the various database objects. To install Management Studio Express, run S0LServer2005_SSMSEE.msi. 1. Open a new workbook and name it DataAccessSample04.xlsm. 2. Before we begin using ADO in Excel 2007, we must add a reference to...
Adding Formatting to a PivotTable Report
The default PivotTable report Excel generates looks okay, but Excel 2007 does provide us with 75 different formatting options. To change the look of a PivotTable report using VBA code, use the PivotTable object's TableStyle2 property. This property is named TableStyle2 because there is already a TableStyle property but it's not a member of the PivotTable object's properties go figure . Add a new subroutine to a standard code module and add the following code PivotStyleLight1 PivotStyleLight22...
Changing Field Names
By default, Excel uses the name Sum of field name or Count of field name when you add summary value fields to a PivotTable. You can change the names to something with more visual appeal using VBA code. Add the Amount field to the Values list in the PivotTable Field List. Change the Count value to Sum in the Value Field Settings dialog box as shown in Figure 6-21 by clicking the Amount field in the Values list and choosing Value Field Settings from the right-click shortcut menu. Figure 6-22...
UserForm Toolbox Controls
Resizes or moves a control on a form. Holds text that is not editable except through code. Holds text that users can enter or modify. CommandButton TabStrip MultiPage ScrollBar A combination of a list box and a text box. Users can choose an item from a list or enter a value in the text box. Displays a list of items from which users can choose. Presents multiple choices, of which only one can be selected. A grouping for controls such as option buttons or check boxes. Users can only select one of...
Looking at the Code
Let's take a look at the code we've generated so far. The MakeBeverageSalesChart macro created a 3-D bar chart for us using a data range we selected. The ChartByRow macro switched the data orientation of the chart from the default, column, to row. Sub MakeBeverageSalesChart ' MakeBeverageSalesChart Macro Range A1 E7 .Select ActiveSheet.Shapes.AddChart.Select ActiveChart.SetSourceData Source Range _ 'Monthly Total Sales Amount' A 1 E 7 ActiveChart.ChartType xl3DColumnClustered End Sub The first...
Using DAO in Excel
Data Access Objects or DAO, as it's commonly known has been around Microsoft Office for many versions, going back to 1992, when Jet was introduced. DAO was the first data access tool available to VB and VBA programmers, and can still be used to manipulate data in older versions of Office and ODBC-compliant database systems. DAO is very easy to use, and you've probably encountered DAO code if you've done any work in versions of Access preceding the 2000 release, when it was the default data...
Standard Code Modules
A standard module is a code module containing only procedure Sub or Function , type, and data declarations and definitions. Module-level declarations and definitions in a standard module are public by default. In earlier versions ofVisual Basic, a standard module was referred to as a code module. Whenever a new macro is created in an Excel session, a standard module is inserted into the workbook to hold the macro. Any additional macros created in that session will also be inserted into this...
A Final Test
There is one last test to perform that will show just how using objects can compartmentalize your code and provide easy-to-reuse functionality. In our HR workbook, we will create two objects from the cXML class. Each will store its own mappings and property settings. The following code shows the contents of the standard module containing the client code for using the cXML class Dim oEmpDept As cXML Dim oHREmployees As cXML Sub GetHREmployees Set oHREmployees New cXML C Chapter 3 HREmployees.xml...
Using ADO in Excel
ActiveX Data Objects ADO was introduced by Microsoft in 1996 and has become the successor to DAO. Its database access technology is OLE DB Object Linking and Embedding Database , which is the successor to ODBC. The latest version of ADO is ADO 2.8. ADO lets us access, edit, and update data from many data sources by interfacing to these data sources via OLE DB providers. OLE DB providers speak to the database engine more directly than ODBC, and provide us with better performance. In the examples...
Simplifying the Code
The code Excel generates, while accurate, is certainly not something one would want to maintain. And you can forget about flexibility. The Array function used to pass in the connection string and database information is one scary looking piece of code. One of the first things we can do to simplify this is to create our own connection string and store it in a variable. This will give us the advantage of easier maintenance. Create a new function in Module1 and name it GetAccessData2. Paste the...
Creating the Pie Chart
In this example, we are going to create a pie chart based on the data for one product category. The chart will show the monthly sales for the category. Then we'll explore options to reuse the code and automate the creation of pie charts for each product line. 1. Select the Sales By Category worksheet. 2. Create a new macro and name it MakePieChart. 3. Select the data range that contains the data for the Baked Goods amp Mixes category A2 C5 , as shown in Figure 5-18. Figure 5-18. Selection for...
Changing the Number Format
The default number format in a new PivotTable is Excel's general number format. Most of us like to see commas or currency symbols, which make the data more readable. To change the number format, you use the PivotField.NumberFormat property. The NumberFormat property sets or returns the string value that represents the format code for the numeric value. The format code is the same string value given by the Format Codes option in the Format Cells dialog box shown in Figure 6-19. Figure 6-19. The...
Creating a Report in Word
In Chapter 5, we explored charting in Excel 2007. We are going to turn one of our chart reports into a summary report using the Word 2007 DOM from the Excel 2007 VBE. In this example, you'll learn how to do the following Open an instance of Microsoft Word programmatically Create a new document within the instance of Word Insert chart objects from Excel Let's start by opening an existing Excel project that contains numeric and chart data. We'll use the charts in our summary report in Word. 1....
Note In the Immediate window when you precede a command or variable name with
We passed in 30 as the Age parameter you didn't think I'd put my real age in there, did you and set the flag to True, indicating that the birthday had passed for this year. This code was run in the year 2007, so the result I would expect to see is 1977 but that's not what we see in Figure 7-5, is it Figure 7-5. Unexpected result in Immediate window Figure 7-5. Unexpected result in Immediate window Let's check our variables and make sure they're holding correct values by inserting a few message...
Importing Access Data
Let's look at how Excel brings in external data by recording a quick macro to import data from an Access database. Create a new macro named GetAccessData. We're going to import the Extended Employees list query from the Northwind 2007 database onto Sheet1 in a new Excel workbook. 2. Select From Access from the Get External Data section of the Data ribbon. 3. Navigate to wherever you have the Northwind database stored. Note The files for these examples can be found in the Source Code Download...