This chapter is intended for those who are not familiar with Excel and the Excel macro recorder, or who are inexperienced... more

This chapter is intended for those who are not familiar with Excel and the Excel macro recorder, or who are inexperienced with programming using the Visual Basic language. If you are already comfortable with navigating around the features provided by Excel, have used the macro recorder, and have a working knowledge of Visual Basic and the Visual Basic Editor, you might want to skip straight to Chapter 2.

If this is not the case, this chapter has been designed to provide you with the information you need to be able to move on comfortably to the more advanced features presented in the following chapters. Specifically, this chapter covers the following topics:

• The Excel macro recorder

• User-defined functions

• The Excel object model

• VBA programming concepts

Excel VBA is a programming application that allows you to use Visual Basic code to run the many features of the Excel package, thereby allowing you to customize your Excel applications. Units of VBA code are often referred to as macros. More formal terminology is covered in this chapter, but you will continue to see the term macro as a general way to refer to any VBA code.

In your day-to-day use of Excel, if you carry out the same sequence of commands repetitively, you can save a lot of time and effort by automating those steps using macros. If you are setting up an application for other users who don’t know much about Excel, you can use macros to create buttons and dialog boxes to guide them through your application as well as automate the processes involved.

If you are able to perform an operation manually, you can use the macro recorder to capture that operation. This is a very quick and easy process and requires no prior knowledge of the VBA language. Many Excel users record and run macros and feel no need to learn about VBA.

However, the recorded results might not be very flexible, in that the macro can only be used to carry out one particular task on one particular range of cells. In addition, the recorded macro is likely to run much more slowly than code written by someone with knowledge of VBA. To set up interactive macros that can adapt to change and also run quickly, and to take advantage of more advanced features of Excel such as customized dialog boxes, you need to learn about VBA.

Don’t get the impression that we are dismissing the macro recorder. The macro recorder is one of the most valuable tools available to VBA programmers. It is the fastest way to generate working VBA code, but you must be prepared to apply your own knowledge of VBA to edit the recorded macro to obtain flexible and efficient code. A recurring theme in this book is recording an Excel macro and then showing how to adapt the recorded code.

In this chapter, you learn how to use the macro recorder and you see all the ways Excel provides to run your macros. You see how to use the Visual Basic Editor to examine and change your macros, thus going beyond the recorder and tapping into the power of the VBA language and the Excel object model.

You can also use VBA to create your own worksheet functions. Excel comes with hundreds of built-in functions, such as SUM and IF, which you can use in cell formulas. However, if you have a complex calculation that you use frequently and that is not included in the set of standard Excel functions—such as a tax calculation or a specialized scientific formula—you can write your own user-defined function.

... less

This chapter examines a range of Excel functionality, looking at features that are not necessarily related to each other.... more

This chapter examines a range of Excel functionality, looking at features that are not necessarily related to each other. In general, the Excel object model contains objects designed to address quite specific tasks. The Application object sits at the top of the Excel object model hierarchy and contains all the other objects in Excel. It also acts as a catch-all area for properties and methods that do not fall neatly into any other object, but are necessary for programmatic control of Excel. There are Application properties that control screen updating and toggle alert messages, for example. There is an Application method that calculates the formulas in the open workbooks.

... less

In this chapter, you learn how to create new... more

In this chapter, you learn how to create new Workbook objects and how to interact with the files that you use to store those workbooks. To do this, some basic utility functions are presented. You also see how to handle the Sheet objects within the workbook, and how some important features must be handled through the Window object. Finally, you learn how to synchronize your worksheets as you move from one worksheet to another.

... less

The... more

The Range object is probably the object you will utilize the most in your VBA code. A Range object can be a single cell, a rectangular block of cells, or the union of many rectangular blocks (a non-contiguous range). A Range object is contained within a Worksheet object.

The Excel object model does not support three-dimensional Range objects that span multiple worksheets—every cell in a single Range object must be on the same worksheet. If you want to process 3D ranges, you must process a Range object in each worksheet separately.

This chapter examines the most useful properties and methods of the Range object.

... less

One of the most useful features in Excel is the ability to create names. You can create a name by selecting the Formulas... more

One of the most useful features in Excel is the ability to create names. You can create a name by selecting the Formulas tab on the Ribbon and clicking the Name Manager button to display the Name Manager dialog box, shown in Figure 5-1. If the name refers to a range, you can create it by selecting the range, typing the name into the Name box at the left side of the Formula bar, and pressing Enter. However, in Excel, names can refer to more than just ranges.

... less

This chapter shows you how to set up VBA code to manage data in lists, and code to filter and sort information in lists.... more

This chapter shows you how to set up VBA code to manage data in lists, and code to filter and sort information in lists. The features examined are:

• Sorting

• Tables (called Lists in Excel 2003)

• AutoFilter

• Advanced Filter

• Data Forms

As always, you can use the macro recorder to generate some basic code for these operations. However, the recorded code needs modification to make it useful, and the recorder can even generate erroneous code in some cases. You will see that dates can be a problem, if not handled properly, especially in an international setting.

You will also see that there is more than one way to perform some tasks. Because Excel has introduced new objects that manage, filter, and sort data, there has been some duplication of the features of older objects. This can be confusing, but it does give you a wide choice of options that you can tailor to fit your needs.

... less

PivotTables are an extension of the cross tabulation tables used in presenting statistics, and can be used to summarize complex... more

PivotTables are an extension of the cross tabulation tables used in presenting statistics, and can be used to summarize complex data in a table format. An example of cross tabulation is a table showing the number of employees in an organization, broken down by age and sex. PivotTables are more powerful and can show more than two variables, so they could be used to show employees broken down by age, sex, and alcohol, to quote an old statistician’s joke.

The input data for a can come from an Excel worksheet, a text file, an Access database, or a wide range of external database applications. PivotTables can handle up to 256 column or row fields, if you can interpret the results. They can perform many types of standard calculations, such as summing, counting, and averaging. They can produce subtotals and grand totals.

Data can be grouped as in Excel’s outline feature, and you can hide unwanted rows and columns. You can also define calculations within the body of the table. PivotTables are also very flexible if you want to change the layout of the data and add or delete variables. You can create PivotCharts that are linked to your PivotTable results in such a way that you can manipulate the data layout from within the chart.

PivotTables are designed so that you can easily generate and manipulate them manually. If you want to create many of them, or provide a higher level of automation to users, you can tap into the Excel object model. This chapter examines the following objects:

• PivotTables

• PivotCaches

• PivotFields

• PivotItems

• PivotCharts

The PivotTable feature has evolved more than most other established Excel features. With each new version of Excel, PivotTables have been made easier to use and provided with new features. Some of these capabilities and the code covered in this chapter might not work in older versions of Excel.

... less

In this chapter, you see how you can use the macro recorder to discover what objects, methods, and properties are required... more

In this chapter, you see how you can use the macro recorder to discover what objects, methods, and properties are required to manipulate charts. You will then improve and extend that code to make it more flexible and efficient. This chapter is designed to show you how to gain access to Chart objects in VBA code so that you can start to program the vast number of objects that Excel charts contain. You can find more information on these objects in Appendix A. Specifically, this chapter examines:

• Creating Chart objects on separate sheets

• Creating Chart objects embedded in a worksheet

• Editing data series in charts

• Defining series with arrays

• Defining chart labels

You can create two types of charts in Excel: charts that occupy their own chart sheets and charts that are embedded in a worksheet. They can be manipulated in code in much the same way. The only difference is that, whereas the chart sheet is a Chart object in its own right, the chart embedded in a worksheet is contained by a ChartObject object. Each ChartObject on a worksheet is a member of the worksheet’s ChartObjects collection. Chart sheets are members of the workbook’s Charts collection.

Each ChartObject is a member of the Shapes collection, as well as a member of the ChartObjects collection. The Shapes collection provides you with an alternative way to refer to embedded charts. The macro recorder generates code that uses the Shapes collection rather than the ChartObjects collection.

... less

Excel makes it very easy for you to write code that runs when a range of worksheet, chart sheet, and workbook events occur... more

Excel makes it very easy for you to write code that runs when a range of worksheet, chart sheet, and workbook events occur. Previous chapters have shown you how to highlight the active row and column of a worksheet by placing code in the Worksheet_SelectionChange event procedure (see Chapter 1). This runs every time the user selects a new range of cells. You have also seen how to synchronize the worksheets in a workbook using the Worksheet_Deactivate and Worksheet_Activate events (see Chapter 3).

It is easy to create workbook, chart sheet, and worksheet events, because Excel automatically provides you with code modules for these objects. However, note that the chart events that are supplied automatically in a chart module apply only to chart sheets, not to embedded charts. If you want to write event procedures for embedded charts, you can do so, but it takes a bit more knowledge and effort.

Many other high-level events also can be accessed, for the Application object, for example. These events are covered later on in Chapters 16 and 26. Events associated with controls and forms are also discussed in their own chapters. This chapter examines in more detail worksheet, chart, and workbook events and related issues.

Event procedures are always associated with a particular object and are contained in the class module that is associated with that object, such as the ThisWorkbook module or the code module behind a worksheet or a UserForm. Don’t try to place an event procedure in a standard module.

... less

As discussed in Chapter 1, you can add two different types of controls to Excel worksheets: ActiveX controls or Form controls... more

As discussed in Chapter 1, you can add two different types of controls to Excel worksheets: ActiveX controls or Form controls. The Form controls originated in Excel 5 and Excel 95 and provide controls for the dialog sheets used in those versions, as well as controls embedded in a worksheet or chart. Dialog sheets have been superseded by UserForms since the release of Excel 97, and UserForms utilize the ActiveX controls.

... less

Text files provide one way to communicate information between different types of computers. There is no universal format... more

Text files provide one way to communicate information between different types of computers. There is no universal format for the binary files that usually provide the most efficient format for working within a particular computer system, so text files are often used for this. This chapter examines how to create text files and how to read them.

Increasingly, XML is becoming the standard way to exchange data across the Internet. XML files are text files, but in a highly organized format. They are covered in Chapter 12. However, there are many legacy systems, particularly where mainframe computers are concerned, where text files in a variety of formats are used.

Excel is capable of importing text files and can save data in .csv (comma separated variables) and .prn (print) files, as well as other formats. Often these features are not flexible enough to cater to specific needs. Using VBA, you can produce text files in whatever format you like and read text files in whatever format is provided.

This chapter also discusses the FileDialog object, which allows you to display the Office dialogs for opening and saving files and browsing folders.

... less

XML (Extensible Markup Language) functionality has been available in various forms since Office 2000. It made its debut in... more

XML (Extensible Markup Language) functionality has been available in various forms since Office 2000. It made its debut in the Office suite of applications in 1999 with relatively little fanfare, waiting there quietly until the release of Office 2003, where it was touted as one of the most significant improvements in Excel. Office 2003 came with many new XML capabilities and the promise of major changes in the way businesses would work with data. In addition to seamless exchange of data, XML promised easy analysis, dynamic reporting, and the ability to consume data from an untold number of external sources.

Unfortunately, XML has failed to find a place in the hearts of many Excel programmers. The problem is that many Excel programmers still look at XML as a solution to a problem that they haven’t quite encountered yet. This is because much of the functionality offered by XML can be handled by existing technologies and processes that programmers are already comfortable with. In addition, most Excel developers don’t live in environments where XML shines the brightest—environments where data is routinely exchanged between disparate platforms (such as the web). The reality is that most Excel programmers live in a world where the Office suite of applications and a few SQL Server databases are as diverse as it gets. The bottom line is that there has never been that one compelling reason to leave the comfort of existing technologies and processes to go to XML. That is, not until now.

Why has XML suddenly become so important? Two words: Open XML. With Office 2007, Microsoft gives XML a leading role by introducing the Open XML file format. These new file formats are XML-based, meaning that each Excel workbook you create in Office 2007 is essentially a group of XML documents. These XML documents are saved as a collection of parts, compressed into a Zip container, and given a file extension (for example, .xlsx, .xlsm, .xlam, and so on).

As illogical as Microsoft’s move toward XML may seem, the decision to bet on XML is a fairly rational one. An XML-based Office will be able to move into an increasing number of environments as XML becomes a widely adopted standard. An XML-based Office can be integrated with much wider array of XML-capable software and web-based applications. An XML-based Office opens up new opportunities for programmers to develop applications that revolve around the Office suite.

With this move toward XML, Microsoft takes a huge step toward making Excel spreadsheets universal widgets that can be integrated into almost any application or web-based solution. Within the next few years, XML-based solutions will start materializing everywhere until XML becomes a part of the Excel developer’s everyday vernacular.

So as Microsoft pushes us all into a new realm of development, it’s important to start to get a grasp of the XML technology. In this chapter, you will get a firm understanding of XML as it pertains to both Excel and the new Open XML file formats. That being said, it’s important to note that the goal of this chapter is not to make you an expert XML developer. Indeed, the topic of XML is a robust one that cannot be fully covered in one chapter. The goal of this chapter is to give you a solid understanding of all the aspects of XML you will need to be familiar with when working with XML in Excel.

... less

UserForms are essentially user-defined dialog boxes. You can use them to display information and to allow the user to input... more

UserForms are essentially user-defined dialog boxes. You can use them to display information and to allow the user to input new data or modify the displayed data. The MsgBox and InputBox functions provide simple tools to display messages and get input data, respectively, but UserForms take you to a new dimension. With these, you can implement nearly all the features that you are accustomed to seeing in normal Windows dialog boxes.

You create a UserForm in the VBE window using Insert>UserForm. You add controls from the Toolbox in the same way that you add controls to a worksheet. If the Toolbox is not visible, use View>Toolbox.

UserForms can contain Labels, TextBoxes, ListBoxes, ComboBoxes, CommandButtons, and many other ActiveX controls. You have complete control over the placement of controls and can use as many controls as you need. Naturally, each control can respond to a wide variety of events.

... less

One of the biggest changes in Office 2007 is, of course, the Ribbon. Early in the design of the Ribbon, Microsoft realized... more

One of the biggest changes in Office 2007 is, of course, the Ribbon. Early in the design of the Ribbon, Microsoft realized that there had to be a way for it to be customized by developers and (to a certain extent) end users. That realization led to RibbonX, the Ribbon’s programmability mechanism. This chapter provides an introduction to RibbonX and explains how you can customize the Ribbon, both for yourself and within your applications.

... less

The... more

The CommandBars collection is an object contained in the Office Object Model, documented in Appendix C. It contains all the menus, toolbars, and shortcut popup menus that are already built into Excel and the other Office applications, as well as any of those objects that you create yourself. You access command bars through the CommandBars property of the Application object.

Command bars were first introduced into Office in Office 97. Excel 5 and 95 supported menu bars and toolbars as separate object types. Shortcut menus, or popups, such as those that appear when you right-click a worksheet cell, were a special type of menu bar. In Excel 97 and later versions, command bar is a generic term that includes menu bars, toolbars, and shortcut menus.

Excel 2007 has made a giant leap forward and replaced menus and toolbars with the Ribbon to provide access to commands. Only the popup type command bars are included in the standard Excel 2007 user interface. However, Excel 2007 still maintains all the old command bars internally and still allows you to create your own command bars, although the way menus and toolbars are exposed is different compared with previous versions. User-defined menus and toolbars now appear in the Add-Ins tab of the Ribbon.

The Visual Basic Editor window in Excel 2007 still has the menu and toolbar interface it has always supported. You can manipulate the VBE command bars using the same techniques that are outlined in this chapter. See Chapter 26 for more information.

Command bars contain items that are called controls. When clicked, some controls execute operations, such as copy. Until this chapter gets down to the nuts and bolts, these types of controls will be referred to as commands. There are other controls, such as File, that produce an additional list of controls when clicked. These controls are referred to as menus. You can create new controls, or you can use the built-in controls that come with Excel.

This chapter shows you how to create and manipulate these useful tools. It takes a look at how command bars were used in Excel 2003 and then shows how to use them in Excel 2007.

... less

Class modules are used in VBA to create your own customized objects. Most VBA users will never have to create their own objects... more

Class modules are used in VBA to create your own customized objects. Most VBA users will never have to create their own objects because Excel already provides all of the objects they need. However, there are occasions when class modules can be very useful. You can use them to:

• Respond to application events; you can write code that is executed whenever any open workbook is saved or printed, for example

• Respond to embedded chart events

• Set up a single event procedure that can be used by a number of ActiveX controls, such as text boxes in a UserForm

• Encapsulate Windows API code so it is easy to use

• Encapsulate standard VBA procedures in a form that is easy to transport into other workbooks

In this chapter, you create some simple (if not terribly useful) objects, to get the idea of how class modules work. Then you apply the principles to some more useful examples. You are already familiar with Excel’s built-in objects, such as the Worksheet object, and you know that objects often belong to collections such as the Worksheets collection. You also know that objects have properties and methods, such as the Name property and the Copy method of the Worksheet object.

Using a class module, you can create your own “blueprint” for a new object, such as an Employee object. You can define properties and methods for the object, such as a Rate property that records the employee’s current rate of pay, and a Training method that consumes resources and increases the employee’s skills. You can also create a new collection for the object, such as the Employees collection. The class module is a plan for the objects you want to create. From it you can create instances of your object. For example, Mary, Jack, and Anne could be instances of an Employee object, all belonging to the Employees collection.

You might not be aware of it, but you have been using some class modules already. The modules behind worksheets, charts, workbooks, and UserForms are class modules. However, they are special types of class modules that behave a little differently from those you create yourself. They are designed specifically to support the object with which they are associated, they give you access to the event procedures for that object, and they cannot be deleted without deleting the associated object.

... less

If you want to make your workbook invisible to the user in the Excel window, you can turn it into an Add-in file. An Add-in... more

If you want to make your workbook invisible to the user in the Excel window, you can turn it into an Add-in file. An Add-in can be loaded into memory using Open under the Microsoft Office button, but it generally makes more sense to access it via the Add-Ins dialog box, which is covered later in this chapter. Either way, the file does not appear in the Excel Application window, but the macros it contains can be executed from the user interface. Any user-defined functions it contains can be used in worksheet calculations. The Add-in’s macros can be attached to menu commands and toolbar buttons, and the Add-in can communicate with the user through UserForms and VBA functions such as InputBox and MsgBox.

It is widely believed that an Add-in is a compiled version of a workbook. In programming, compilation involves translating the human-readable programming code into machine language. This is not the case with an Excel Add-in. In fact, all that happens is that the workbook is hidden from the user interface. The Add-in’s worksheets and charts can no longer be seen by anyone. Its code modules can still be viewed, as normal, in the VBE window and remain complete with comments as well as code.

However, it is possible to create a compiled version of an Add-in. This is referred to as a COM (Component Object Model) Add-in. COM Add-ins are discussed separately in Chapter 18.

This chapter has taken the CommandBars2.xlsm file used in Chapter 15, saved it as AddIn.xlsm prior to converting it to Addin.xlam, and adapted the code to make it suitable for an Add-in. The code on popup menus has been removed because it is not relevant.

In previous versions of Excel, it is not necessary to give an Add-in filename any special extension. In Excel 2007, however, it is necessary to give an Add-in filename an .xlam extension. It is a good idea to do so, in any case, because it identifies the file as an Add-in and ensures that the Add-in icon appears against the file in the Windows File Manager. The conversion of a workbook file to an Add-in file is covered later in this chapter.

... less

With the release of Office 2000, Microsoft introduced a new concept for creating custom Add-Ins for all the Office applications... more

With the release of Office 2000, Microsoft introduced a new concept for creating custom Add-Ins for all the Office applications. Instead of creating application-specific Add-Ins (xlam in Excel, dotm in Word, and so on), you can create DLLs using Visual Basic, C++, or .NET that all the Office applications can use. Because these DLLs conform to Microsoft’s Component Object Model, they are known as COM Add-Ins. The second half of this chapter explains how to create and implement your own COM Add-Ins.

In Excel 2002, Microsoft extended the concept and simplified the implementation of the COM Add-In mechanism, so their functions could be used in the same way as worksheet functions and VBA user-defined functions. These Add-Ins are known as Automation Add-Ins.

In Excel 2007, Microsoft has further extended COM Add-Ins to support application-level customization of the Ribbon and the creation of custom task panes.

... less

The Office application programs Excel, Word, PowerPoint, Outlook, and Access all use the same VBA language. Once you understand... more

The Office application programs Excel, Word, PowerPoint, Outlook, and Access all use the same VBA language. Once you understand VBA syntax in Excel, you know how to use VBA in all the other applications. Where these applications differ is in their object models.

One of the really nice things about the common VBA language is that all the Office applications are able to expose their objects to each other, and you can program interaction between all of the applications from any one of them. To work with Word objects from Excel, for example, you only need to establish a link to Word, and then you have access to its objects as if you were programming with VBA in Word itself.

This chapter explains how to create the link in a number of different ways, and presents some simple examples of programming the other application. In all cases, the code is written in Excel VBA, but it could easily be modified for any other Office application. The code is equally applicable to products outside Office that support the VBA language. These include other Microsoft products such as Visual Basic and SQL Server. There is also a growing list of non-Microsoft products that can be programmed in the same way.

... less

ActiveX Data Objects, or ADO for short, is Microsoft’s technology of choice for performing client-server data access between... more

ActiveX Data Objects, or ADO for short, is Microsoft’s technology of choice for performing client-server data access between any data consumer (the client) and any data source (the server). There are other data-access technologies you may have heard of in relation to Excel, including DAO and ODBC. However, these are not covered in this chapter because Microsoft intends for ADO to supercede these older technologies, and for the most part this has occurred.

ADO is a vast topic, easily the subject of its own book. This chapter necessarily presents only a small subset of ADO, covering the topics and situations that I’ve run across most frequently in my career as an Excel programmer. This chapter focuses on ADO 2.5. This version of ADO ships natively with Windows 2000 or Office 2000 and higher, so you can assume it will be present on any computer you distribute your application to.

... less

Chapter 20 discussed how to access data with essentially unlimited flexibility using ADO. Excel also provides some built-in... more

Chapter 20 discussed how to access data with essentially unlimited flexibility using ADO. Excel also provides some built-in data management features, primarily through the QueryTable, ListObject, and WorkbookConnection objects. Excel’s built-in data management features have less flexibility than custom ADO programming. For example, you can only use these features to retrieve data, not modify it. But they are simpler and offer a number of useful capabilities right out of the box. This chapter examines some of Excel’s built-in data management capabilities.

... less

Document security issues have become inescapable for Excel VBA developers. Even if you intend to develop macros only for... more

Document security issues have become inescapable for Excel VBA developers. Even if you intend to develop macros only for use on your own computer, you still need to understand at least a few document security settings in order for them to run properly. This chapter covers the security settings in the Trust Center, as well as automating the removal of personal information from Excel workbooks.

... less

The dominant database type in most organizations is the OLTP (On-line Transaction Processing) database. Indeed, most of you... more

The dominant database type in most organizations is the OLTP (On-line Transaction Processing) database. Indeed, most of you are probably working with some form of an OLTP database as you read this. The main characteristics of this type of database are: they typically contain many tables, each table usually contains multiple relationships with other tables, and records within any given table can be routinely added, deleted, or updated.

Although OLTP databases are effective in gathering and managing data, they typically don’t make for effective data sources for reporting, for three main reasons:

• Complexity: The large number of tables and relationships that can exist in an OLTP database can leave you wondering exactly which tables to join and how the tables relate to each other.

• Volume: OLTP databases normally contain individual records. Lots of them. To create any number of aggregate reports and views, you would have to run views that group, aggregate, and sort records on the fly. The sheer volume of data in the database could very well inundate you with painfully slow reporting.

• Consistency: By its very nature, the records in a transactional database are ever-changing. Building a reporting solution on top of this type of database will inevitably lead to inconsistent results from month to month, or even from day to day.

Some organizations avoid these woes by building their reporting solutions on top of OLAP (On-Line Analytical Processing) databases. OLAP databases are data islands that are isolated from the hustle and bustle of transactional databases. An OLAP database can help alleviate these problems in the following ways:

• Structured Data: In an OLAP database, all of the relationships between the various data points have been predefined and stored in what are known as cubes. These cubes contain the hierarchical structures that allow for the easy navigation of available data dimensions and measures. With this configuration, you no longer have to create joins yourself or try to guess how one data table relates to another. All of that complexity is taken care of behind the scenes, leaving you free to develop the reports you need.

• Predefined Aggregations: The data in an OLAP database is not only organized, but it is aggregated. This means that grouping, sorting, and aggregations are all predefined in OLAP databases. In addition, OLAP databases make heavy use of indexes, a technique that allows a database to search for records more efficiently. All of this amounts to reporting solutions that are optimized to provided the reports you need as fast as possible.

• Consistent Results: OLAP databases only contain snapshots of data. That is to say, the data in an OLAP database is typically historical data that is read-only, stored solely for reporting purposes. New data is typically appended to the OLAP database on a regular basis, but the existing data is rarely edited or deleted. This allows you to retrieve consistent results when building your reporting solutions.

Excel has some effective built-in tools that allow for the exploration and reporting of data from OLAP databases. In this chapter, you will discover some of the ways you can browse the OLAP data sources in your organization via Excel and VBA.

You cannot create an OLAP database using Excel. OLAP databases are typically created with SQL Server Analysis Services. If your organization does not utilize OLAP datbases, you may want to speak with your SQL Server DBA to discuss the possiblity of some OLAP reporting solutions.

... less

Until a few years ago, a typical Excel-based application was almost entirely contained within Excel itself; the only external... more

Until a few years ago, a typical Excel-based application was almost entirely contained within Excel itself; the only external interaction would be with the user, from whom you obtained data and to whom you presented results. If you needed to store data, you’d use separate workbooks and try to mimic a relational database as best you could.

As data access technologies developed, from ODBC drivers through DAO to the current versions of ADO (documented in Chapter 20), it became more commonplace to store data in external databases and retrieve data from (and update data in) other systems across the network. It is now quite common to see Excel used as a front-end querying and analysis tool for large corporate databases, using QueryTables and PivotTables to retrieve the data. The data available to Excel applications was, however, limited to what was available across the company network, and to those databases you could get permission to access.

Starting with the release of Office 97, Microsoft has slowly extended Excel’s reach to include the Internet and associated technologies, either by adding native functionality directly to Excel (such as Web Queries), or by ensuring that Excel developers can easily use standard external objects (such as the Internet Transfer Control, the Web Browser control, and the MSXML parser) and including those objects within the Office installation.

In Excel 2007, you have sufficient functionality to think outside of the pure Excel/ADO environment in terms of obtaining data, publishing results, monitoring applications, and sharing data with many disparate systems across the Internet.

This chapter introduces the functionality available in Excel 2007 and demonstrates how to use some of it to exploit the Internet within your applications. A complete discussion of all of Excel’s Internet-related functionality is beyond the scope of this book.

Note that throughout this chapter, the term Internet is used in its broadest sense, covering both internal and external networks. The chapter assumes a basic understanding of the Internet and how it works. The examples use a web server running on a local PC. However, these techniques are equally applicable to applications running on a remote server.

... less

If you think that your application may be used internationally, it has to work with any choice of Windows Regional Setting... more

If you think that your application may be used internationally, it has to work with any choice of Windows Regional Setting, on any language version of Windows, and with any language choice for the Excel user interface.

If you are very lucky, all your potential users will have exactly the same settings as your development machine and you won’t need to worry about international issues. However, a more likely scenario is that you will not even know who all your users are going to be, let alone where in the world they will live or the settings they will use.

Any bugs in your application that arise from the disregarding or ignoring of international issues will not occur on your development machine unless you explicitly test for them. However, they will be found immediately by your clients.

The combination of Regional Settings and Excel language is called the user’s locale, and the aim of this chapter is to show you how to write locale-independent VBA applications. To do this, we include an explanation of the features in Excel that deal with locale-related issues, and highlight areas within Excel where locale support is absent or limited. Workarounds are provided for most of these limitations, but some are so problematic that the only solution is to not use the feature at all.

The rules provided in this chapter should be included in your coding standards and used by you and your colleagues. It is easy to write locale-independent code from scratch; it is much more difficult to make existing code compatible with the many different locales in the world today.

... less

Up until now, the book has focused on writing VBA procedures to automate Excel. While writing the code, you have been working... more

Up until now, the book has focused on writing VBA procedures to automate Excel. While writing the code, you have been working in the Visual Basic Editor (VBE), otherwise known as the Visual Basic Integrated Design Environment (VBIDE).

An object library is provided with Office 2007 that is shown as Microsoft Visual Basic for Applications Extensibility 5.3 in the VBE’s Tools>References list. The objects in this library and their methods, properties, and events enable you to:

• Programmatically create, delete, and modify the code, UserForms, and references in your own and other workbooks

• Program the VBE itself to create useful Add-ins to assist you in your development efforts and automate many of your development tasks

There have been no significant changes to the Visual Basic for Applications Extensibility library between Office 2000 and Office 2007, so all the examples in this chapter apply equally to all versions.

The only responsible way to start this chapter is with a warning. Macro viruses work by using the methods shown in this chapter to modify the target file’s code, thus infecting it. To prevent this, Microsoft has made it possible to disable access to all workbooks’ VBProjects. By default the access is disabled, so none of the code in this chapter will work. To enable access to the VBProjects, place a check mark next to the Trust Access to the VBA Project Object Model checkbox in Excel 2007’s Office Menu>Excel Options>Trust Center>Trust Center Settings>Macro Settings dialog.

This chapter explains how to write code to automate the VBE by walking you through the development of an Excel-related VBE Toolkit to speed up your application development. You will then add a few utilities to the toolkit that demonstrate how to programmatically manipulate code, UserForms, and references. For simplicity, most of the code examples in this chapter have not been provided with error handling. You can find the completed toolkit Add-in at www.wrox.com.

... less

Visual Basic for Applications is a high-level language that provides you with a rich, powerful, yet quite simple set of functionality... more

Visual Basic for Applications is a high-level language that provides you with a rich, powerful, yet quite simple set of functionality for controlling the Office suite of products, as well as many other applications. You are insulated—some would say protected—from the “mundane minutiae” of Windows programming that, say, a C++ programmer has to contend with.

The price you pay for this protection is an inability to investigate and control many elements of the Windows platform. You can, for example, use Application.International to read most of the Windows Regional Settings and read the screen dimensions from Application.UsableWidth and Application.UsableHeight, but that’s about it. All the Windows-related items available to you are properties of the Application object and are listed in Appendix A.

The Windows platform includes a vast amount of low-level functionality that is not normally accessible from VBA, from identifying the system colors to creating a temporary file. Some of the functionality has been exposed in VBA, but only to a limited extent, such as creating and using an Internet connection (for example, you can open a page from the Internet using Workbooks.Open "<URL>", but you can’t just download it to disk). There are also a number of other object libraries typically available on Windows computers that provide high-level, VBA-friendly access to the underlying Windows functionality. Examples of these are the Windows Scripting Runtime and the Internet Transfer Control.

There are times, though, when you need to go beyond the limits of VBA and the other object libraries, and delve into the files that contain the low-level procedures provided and used by Windows. The Windows Operating System is made up of a large number of separate files, mostly dynamic link libraries (DLLs), each containing code to perform a discrete set of interrelated functions. DLLs are files that contain functions that can be called by other Windows programs or other DLLs. They cannot be run like programs themselves.

These files are collectively known as the Windows Application Programming Interface, or Windows API. Some of the most common files you’ll use in the Windows API are detailed in the following table.

File	Function Group(s)
USER32.EXE	User interface functions (such as managing windows, the keyboard, clipboard, and so on)
KERNEL32.DLL	File- and system-related functions (such as managing programs)
GDI32.DLL	Graphics and display functions
SHELL32.DLL	Windows shell functions (such as handling icons and launching programs)
COMDLG32.DLL	Standard Windows dialog functions
ADVAPI32.DLL	Registry and NT Security functions
MPR.DLL and NETAPI32.DLL	Network functions
WININET.DLL	Internet functions
WINMM.DLL	Multimedia functions
WINSPOOL.DRV	Printing functions

This chapter explains how to use the functions contained in these files in your VBA applications and includes a number of useful examples. All of the Windows API functions are documented in the Platform SDK section of the MSDN Library at http://msdn.microsoft.com/library/
default.asp?url=/library/en-us/winprog/winprog/
windows_api_start_page.asp, which can be thought of as the online help for the Windows API. Because Microsoft regularly changes its URLs, that page can be found in the MSDN Library menu under Win32 and COM Development>Development Guides>Windows API>Windows API.

... less

Most of the objects in the Excel object model have objects with associated collections. The collection object is usually... more

Most of the objects in the Excel object model have objects with associated collections. The collection object is usually the plural form of the associated object. For example, the Worksheets collection holds a collection of Worksheet objects. For simplicity, each object and associated collection will be grouped together under the same heading.

... less

Officially known as “Microsoft Visual Basic for Applications Extensibility 5.3,” the VBE object library provides access to... more

Officially known as “Microsoft Visual Basic for Applications Extensibility 5.3,” the VBE object library provides access to the code and forms within an application, and to the various objects that compose the Visual Basic Integrated Development Environment (VBIDE). By default, this object library is not included in the list of referenced libraries for new projects. In order to use the objects referred to in this chapter, a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 library must be created using the Tools>References menu in the VBE.

Many of the objects in the VBE object model have the same names as objects in the Excel object model. To distinguish the libraries and to ensure that you have the object from the VBE library, you need to include the VBIDE library name in any Dim statements you may use:

Dim oWinVB As VBIDE.Window      'Always gives a VBE Window

Dim oWinXL As Excel.Window      'Always gives an XL Window

Dim oWin As Window              'Gives an XL Window

All of the applications in Office 2007 share the same development environment—the VBE. The code and forms that belong to each Excel workbook, Word document, Access database, or PowerPoint presentation (that is, the “host document”) are grouped into Visual Basic projects (the VBProject object). There is one project for each host document. Outlook has a single Project, which “belongs” to the application.

... less

Common Properties with Collections and Associated Objects

Most of the objects in the Office object model have objects with... more

Common Properties with Collections and Associated Objects

Most of the objects in the Office object model have objects with associated collections. The collection object is usually the plural form of the associated object. For example, the CommandBars collection holds a collection of CommandBar objects. For simplicity, all the objects and associated collections are grouped together under the same heading.

In most cases, the purpose of the collection object is only to hold a collection of the same objects. The common properties of the collection objects are listed in the following section. Only unique properties, methods, or events are mentioned in each object section.

... less