Repeating Actions in VBA
Do Loops Do While and Do Until Watching a Procedure Execute While Wend Loop For Next Loop For Each Next Loop Exiting Loops Early Nested Loops What's Next. Now that you've learned how conditional statements can give your VBA procedures decision-making capability, it's time to get more involved. Not all decisions are easy. Sometimes you will need to perform a number of statements several times to arrive at a certain condition. On other occasions, however, after you've reached the decision, you...
Deleting a Custom Toolbar
If you create a toolbar and decide that you don't want to keep it, you can get rid of it without closing the Excel application. Simply use the Delete method. For example, to delete the Budget Plans toolbar you can enter the following statement in the Immediate window CommandBars Budget Plans .Delete Note You cannot delete built-in toolbars. Using the CommandBar Properties The CommandBar object has a number of properties. You'll work with some of them in the Immediate window. 1. Use the...
Creating a Submenu
Menu options containing a black triangle to the right of the menu option name display a submenu of additional commands. Suppose that you want to add a submenu to the Tools menu. 1. To add a submenu to the Tools menu, enter the following statement on one line in the Immediate window CommandBars Worksheet menu Add Type msoControlPopup, Before 1 .Caption My Submenu When you press Enter, the above instruction places at the top of the Tools menu Worksheet menu bar a custom submenu called My Submenu....
Tools for Creating User Forms
When you design a form, you can insert appropriate controls to make it useful. The Toolbox contains standard Visual Basic buttons for all the controls that you can add to a form. It can also contain additional controls that have been installed on your computer. Controls available in the Toolbox are known as ActiveX controls. These controls can respond to specific user actions, such as clicking a control or changing its value. You will learn how to use the Toolbox controls in the remaining...
Changing the Name of a File or Folder the Name Function
To rename a file or folder, use the Name function, as follows Name old_pathname As new_pathname Old_pathname is the name and path of a file or folder that you want to rename. New_pathname specifies the new name and location of the file or folder. Using the Name function, you can move a file from one folder to another however, you can't move a folder. Try this function out in the Immediate window replace the example names with the actual names of your files . Here are some precautions to...
Understanding VBA Errors
In the course of writing or editing VBA procedures, no matter how careful you are, you're likely to make some mistakes. For example, you may misspell a statement, misplace a comma or quote, or forget a period or ending parenthesis. These kinds of mistakes are known as syntax errors. Fortunately, Visual Basic is quite helpful in spotting this kind of error. To have Visual Basic automatically check for correct syntax after you enter a line of code, choose Tools Options in the Visual Basic window....
Executing a Function Procedure
In Chapter 1, you learned various ways to execute a subroutine procedure. Unlike a subroutine, a function procedure can be executed in just two ways. You can use it in a worksheet formula or you can call it from another procedure. Function procedures that you create in VBA cannot be accessed by choosing Tools Macro Macros in the Microsoft Excel window. And they cannot be run by pressing the F5 key when the mouse pointer is located inside the code of the function procedure. In the following...
Chart Events
As you know, you can create charts in Excel that are embedded in a worksheet or located on a separate chart sheet. In this section, you will learn how to control chart events no matter where you've decided to place your chart. Before you try out selected chart events, perform the following tasks 1. Open a new Excel workbook and save it as ChartEvents.xls. 2. Enter sample data, as shown in Figure 14-6. 3. Select cells A1 D4, and click the Chart Wizard button on the Standard toolbar. 4. Prepare a...
Saving a Range of Cells as an XML Document
In the beginning of this chapter you learned how to save an Excel spreadsheet in the XML-SS spreadsheet format, both by using File Save As and programmatically via the Save As method of the Workbook object and the special parameter xlXMLSpreadsheet. At times, however, it may be desirable to save only a specific range of cells in the XML format. While the user interface does not offer such an option, Excel 2002 now offers a Value property of the Range object with the xlRangeValueXMLSpreadsheet...
Formatting Grouping and Sorting a PivotTable Report
You can modify the display and format of a PivotTable programmatically by using a number of different properties of the PivotTable object. For example, you may want to reposition the fields within the PivotTable layout, sort the data by a specific field, or group your data by years, quarters, months, and so on. The example procedure below reformats the PivotTable report generated earlier in this appendix see Figure A-6 . 1. Add a new module to the current workbook and enter the procedure code,...
SmartTagRecognizer Object
When you type certain data in Microsoft Excel, data that is recognized by the installed recognition engine is tagged. Use the SmartTagRecognizer object to determine if tag recognizers are enabled for the entire application. The SmartTagRecognizer is a member of the SmartTagRecognizers collection. You can return a single SmartTagRecognizer via the Item property of the SmartTagRecognizers collection. Now let's spend a few minutes working with the above-mentioned objects. 1. Enter MSFT in cell B5...
Saving PivotTable Report and PivotChart Report as a Web Page
Check out Appendix D on using Microsoft Office web components. By publishing a PivotTable report or PivotTable chart as a web page, you can work with your PivotTable or PivotChart interactively inside Microsoft Internet Explorer. When you select the PivotTable report, Excel uses the Microsoft PivotTable web component to publish your data. When you save your PivotChart report as a web page, the publishing job is handled by the Microsoft Chart web component. To manually save your PivotTable...
XML Support in Excel
Microsoft Excel 2002 offers extensive XML support. To begin with, instead of saving your workbook file as a standard .xls file, you can just as easily save it as an XML spreadsheet. To do this, no programming skills are required. In the Microsoft Excel application window, simply choose File Save As and select XML Spreadsheet in the Save as type drop-down box of the Save As dialog box Figure 17-1 . You can also open the XML spreadsheet back into Excel and use it as if it were a standard Excel...
Installing Internet Information Services US or Personal Web Server
Insert the Windows 98 CD-ROM into a drive. When the Windows 98 CD-ROM window appears, double-click Browse This CD. When the Microsoft Personal Web Server Setup dialog box appears, click Typical to indicate the type of installation that you want to perform. The screen indicating the location of the Personal Web Server home directory will appear. Press Next to continue the installation. When the installation is complete, click Finish. A message will appear, asking you to restart your computer....
FormatCondition Object
You can add conditional formatting to your spreadsheet by using the For-matCondition object. Conditional formatting is associated with a particular range of cells. The FormatCondition object is a member of the Format-Conditions collection. This collection can contain up to three FormatCondition objects for a given range. Use the Count method of the FormatConditions collection to return the number of objects in the collection. Use the Add method of the FormatConditions collection to create a new...
Creating a Virtual Directory
The default home directory for the WWW service is C InetPub wwwroot. Files located in the home directory and its subdirectories are automatically available to visitors to your web site. You can create virtual directories to make web pages that are not stored in the home directory or its subdirectories available for viewing. A virtual directory appears to client browsers as if it were physically contained in the home directory. For the purposes of this chapter, in order to try out the example...
Understanding Linking and Embedding
Before you learn how to control other applications from a VBA procedure using Automation, let's take a look at how the manual method is used to link and embed an object. Object linking and embedding, known as OLE, allows you to create compound documents. A compound document contains objects created by other applications. For example, if you embed a Word document in a Microsoft Excel worksheet, Excel only needs to know the name of the application that was used to create this object and the...
Whats Next Ibr
In this chapter you learned how to test your VBA procedures to make sure they perform as planned. You debugged your code by stepping through it using breakpoints and watches. You learned how to work with the Immediate window in break mode. You found out how the Locals window can help you monitor the values of variables and how the Call Stack dialog box can be helpful in keeping track of where you are in a complex program. You've learned to specify parts of your procedure that you want to...
The XML Flattener
It's quite interesting to see how Excel 2002 deals with XML files that have been created outside of Excel but do not use the XML-SS that Microsoft uses to describe spreadsheet data. Let's again assume that you've just received an e-mail with an XML file attachment listing the VBA course schedule for the coming year. The Courses.xml file is shown in Figure 17-11. You don't care how this file was generated. It could have been typed directly in a text editor or outputted from a database using a...
Whats Next Jsl
This chapter introduced several new VBA concepts, including information about data types, variables, and constants. You learned how to declare various types of variables. You also saw the difference between a variable and a constant. Now that you know what variables are and how to use them, you are capable of creating VBA procedures that can manipulate data in more meaningful ways than you saw in previous chapters. In the next chapter, you will expand your VBA knowledge by using procedures with...
Retrieving Data with the GetRows Method
To place Microsoft Access data into an Excel spreadsheet, you can use the GetRows method. This method returns a two-dimensional array where the first subscript is a number representing the field, and the second subscript is the number representing the record. Record and field numbering begins with 0. You can programmatically return data to a Microsoft Excel worksheet by using Data Access Objects DAO in your VBA procedure. The following example procedure demonstrates how to run the Invoices...
Stopping a Procedure
Do you know how to stop a Visual Basic procedure If you are thinking of pressing the Esc key, you are correct. If you run a procedure and then suddenly press Esc, Visual Basic will halt execution of your program and display the message shown in Figure 13-1. However, in addition to the mighty and very reliable, in most circumstances, Escape key, VBA offers other methods of stopping your procedure and entering into a so-called break mode Setting one or more breakpoints Inserting the Stop...
Tip What is XPath
XML Path Language XPath is a query language used to create expressions for finding data in the XML. These expressions can manipulate strings, numbers, and Boolean values. They can also be used to navigate an XML tree structure and process its elements with XSLT instructions. XPath is designed to be used by XSL Transformations XSLT see Tip 17-9 . With XPath expressions, you can easily identify and extract from the XML document specific elements nodes based on their type, name, values, or the...
Setting Up References to Object Libraries
To work with Microsoft Access 2002 objects, begin by creating a reference to the Microsoft Access 10.0 Object Library. Choose Microsoft Access 9.00 Object Library if you are working with Microsoft Office 2000. 1. In the Visual Basic Editor window, choose Tools References to open the References dialog box. This dialog displays a list of all the type libraries that are available on your computer. 2. Locate the Microsoft Access 10.0 Object Library in the list of entries and select its check box....
Creating Custom Objects
Creating a new, non-standard VBA object involves inserting a class module into your project and adding code to that module. However, before you do so, you need a basic understanding of what a class is. If you refer back to the beginning of this chapter, you will find out that a class is a sort of object template. A frequently used analogy is comparing an object class to a cookie cutter. Just as a cookie cutter defines what a particular cookie will look like, the definition of the class...
Establishing a Reference to an Object Library
If you decide to use early binding to connect to another application via Automation, you should start by establishing a reference to the object library whose objects you are planning to manipulate. Follow these steps to create a reference to the Microsoft Word Object Library 1. Activate the Visual Basic Editor window. 2. Select the current project in the Project Explorer window, and choose Tools References. 3. In the References dialog box, choose the name of the application in the Available...
Late Binding
When you declare a variable As Object or As Variant, Visual Basic uses late binding. Late binding is also known as run-time binding. Late binding simply means that Visual Basic doesn't associate your object variable with the Automation object at design time but waits until you actually run the procedure. Because the declaration As Object or As Variant is very general in nature, Visual Basic cannot determine at compile time that the object your variable refers to indeed has the properties and...
Exiting Loops Early
Sometimes you may not want to wait until the loop ends on its own. It's possible that a user enters the wrong data, a procedure encounters an error, or perhaps the task has been completed and there's no need to do additional looping. You can leave the loop early without reaching the condition that normally terminates it. Visual Basic has two types of Exit statements The Exit For statement is used to end either a For .Next or a For Each .Next loop early. The Exit Do statement immediately exits...
Creating a PivotTable Report
Before you can create a PivotTable, you need to prepare the data. You can get the data from one of the following sources A range on an Excel worksheet type in your data or paste from other sources External data source such as a Microsoft Access or SQL Server database Multiple consolidation ranges Another PivotTable or PivotChart report Figure A-1 displays the data that was dumped into a Microsoft Excel worksheet from an SQL Server database. The workbook file named WarrantyCounts.xls is located...
Navigating with Bookmarks
In the process of analyzing or reviewing your VBA procedures, you will often find yourself jumping to certain code areas. Using the built-in bookmark feature, you can easily mark the spots that you want to navigate between. 1. Click anywhere in the statement that you want to define as a bookmark. 2. Choose Edit Bookmarks Toggle Bookmark or click the Toggle Bookmark button on the Edit toolbar see Figure 13-16 . Visual Basic will place a blue, rounded rectangle in the left margin beside the...
Removing Objects from a Custom Collection
Removing an item from a custom collection is as easy as adding an item. To remove an object, use the Remove method in the following format object is the name of the custom collection that contains the object you want to remove. item is the object you want to remove from the collection. To demonstrate the process of removing an item from a collection, let's modify the GetComments procedure that you prepared in the preceding section. At the end of this procedure, we'll display the contents of the...
Creating an Embedded Chart from Microsoft Access Data
Using VBA, you can easily create a chart based on the data retrieved from a Microsoft Access database. The ChartData procedure shown below uses the data fetched from the Microsoft Access Northwind database to create an embedded chart. The chart is created by using the Add method of the Charts collection. The source of the chart data is provided by the Range object. The CurrentRegion method returns all the non-blank cells surrounding cell A1. The remaining part of the procedure formats the chart...
Implicit Variable Declaration
If you misspell a variable name in your procedure, Visual Basic may display a run-time error or create a new variable. You are guaranteed to waste some time troubleshooting problems that could have been easily avoided had you declared your variable at the beginning of the procedure. Since Visual Basic does not know what type of data your variable will store, it assigns it a Variant data type. This causes your procedure to run slower because Visual Basic has to check the type of data every time...
Declaring and Using Object Variables
The variables that you've learned so far are used to store data. Storing data is the main reason for using normal variables in your procedures. In addition to the normal variables that store data, there are special variables that refer to the Visual Basic objects. These variables are called object variables. In Chapter 2, you learned a few things about various objects. Now, you will learn how you can represent an object with the object variable. Object variables don't store data. They tell...
Learning about Objects Properties and Methods
When you learn new things, theory can give you the necessary background, but how do you really know what's where The majority of people think in pictures. To make it easy to understand the Microsoft Excel object hierarchy, the Visual Basic online help offers a diagram of the object model, as shown in the following figures. Figure 2-4 Microsoft Excel object hierarchy Page 1 Figure 2-4 Microsoft Excel object hierarchy Page 1 Notice that the Application object is positioned at the very top of the...
Modifying a Builtin Shortcut Menu
Microsoft Excel offers 60 shortcut menus with different sets of frequently used menu options. The shortcut menu appears when you right-click on an object in the Microsoft Excel application window. Using VBA, you can return the exact number of the shortcut menus, as well as their names. 1. Enter the ShortcutMenus procedure in the current project's module, as shown below Dim myBar As CommandBar Dim counter As Integer For Each myBar In CommandBars If myBar.Type msoBarTypePopup Then counter counter...
Acknowledgments
I would like to express my grateful appreciation to all the people involved in the creation of this book. Special thanks go to Wordware Publishing, Inc. editors Beth Kohler and Heather Hill for their help, comments, and thorough review of this book. It was a pleasure to work with you ladies. Your queries were so skillfully put together that I've decided to bind them for future reference. Many thanks to Wes Beckwith for all his efforts and patience in coordinating this project. To Witold...
Dialog Boxes and Custom Forms
Excel Dialog Boxes File Open and File Save As Dialog Boxes GetOpenFilename and GetSaveAsFilename Methods Creating Forms Tools for Creating User Forms Placing Controls on a Form Sample Application 1 Info Survey Adding Buttons, Check Boxes, and Other Controls to a Form Changing Control Names Setting Other Control Properties Preparing a Worksheet to Store Custom Form Data Displaying a Custom Form Setting the Tab Order Understanding Form and Control Events Writing VBA Procedures to Respond to Form...
AutoCorrect Object
The AutoCorrect object has properties and methods that allow you to work with Excel's AutoCorrect features. The following two procedures will get you started working with AutoCorrect programmatically. The first procedure uses the ReplacementList method to retrieve common misspelled words and their automatic replacements into an array. The procedure then reads the values of this array and enters them into a worksheet so that you can print them out easily. If you need to update your AutoCorrect...
Array Upper and Lower Bounds
By default VBA assigns zero 0 to the first element of the array. Therefore, number 1 represents the second element of the array, number 2 represents the third, and so on. With numeric indexing starting at 0, the one-dimensional array cities 6 contains seven elements numbered from 0 to 6. If you'd rather start counting your array's elements at 1, you can explicitly specify a lower bound of the array by using an Option Base 1 statement. This instruction must be placed in the declaration section...
Using the CreateObject Function
To create a reference to the Automation object from a VBA procedure, use the CreateObject function with the following syntax The argument class is the name of the application you want to reference. This name includes the object class type as discussed earlier see the section on early binding . The Automation object must be assigned to the object variable by using the Set keyword, as shown below Set variable_name CreateObject class For example, to activate Word using the Automation object,...
Whats Next Afo
In this chapter you learned the difference between subroutine procedures that perform actions and function procedures that return values. While you can create subroutines by recording or typing, function procedures cannot be recorded because they can take arguments. You must write them manually. You saw examples of function procedures called from a worksheet and from another Visual Basic procedure. You learned how to pass arguments to functions and determine the data type of a function's...
Using the immediate Window in Break Mode
Once the procedure execution is suspended, when the Code window appears, you can activate the Immediate window and type VBA instructions to find out, for instance, which cell is currently active or the name of the active sheet. You can also use the Immediate window to change the contents of variables in order to correct values that may be causing errors. By now, you should be an expert when it comes to working in the Immediate window. Figure 13-6 shows the suspended ChangeCode procedure and the...
The Spreadsheet Web Component
Because you already know how to use a Microsoft Excel Workbook object, you will find it extremely easy to work with the Spreadsheet component. This component is very useful for displaying formatted data, performing calculations, or simply providing your users with a quick way to enter, calculate, and store data. The current XP version of the Spreadsheet component supports multiple worksheets containing 262,144 rows and 18,278 columns ZZZ each. Creating business solutions that utilize the...
Doing Things with Workbooks and Worksheets
Now that you've got your feet wet working with worksheet cells and ranges, it's time to move up one level and learn how you can control a single workbook, as well as an entire collection of workbooks. You cannot prepare a new spreadsheet if you don't know how to open a new workbook. You cannot remove a workbook from the screen if you don't know how to close a workbook. These important tasks are handled by two VBA methods Add and Close. The next series of drills will give you the language skills...
Writing Procedures to Synchronize the Text Box with the Spin Button
The Info Survey form has a text box in front of the spin button control. To indicate a percent of time that the selected Hardware or Software item is used, the user can type a value in the text box or use the spin button. The initial value of the text box is set to zero 0 . Suppose the user entered 10 in the text box and now wants to increase this value to 15 by using the spin button. To enable this action, the text box and the spin button have to be synchronized. Each of these objects requires...
Diagram Object
You can illustrate various concepts using diagramming tools in Excel 2002. A diagram is a collection of shapes. Diagram types include Cycle, Target, Radial, Venn, and Pyramid. Each diagram contains nodes. A node represents an individual shape object in a diagram. To add a Diagram object programmatically to a worksheet, use the AddDiagram method of the Shapes collection. To add shapes to the diagram, use the DiagramNode object within the Shape object. The following procedure creates a diagram...
Step Creating a Virtual Directory
We will store all of the files required by our web timesheet application in a virtual directory under C Inetpub wwwroot. Follow these steps to set up a virtual folder on your computer 1. Create a new folder under C Inetpub wwwroot and name it TimeTrack. 2. Right-click the TimeTrack folder name, select Sharing from the shortcut menu, and click the Web Sharing tab. Click the Share this folder option button and type Time in the Alias text box. In the Access Permission area, click the check box...
Storing Smart Tag List Definition Files
Before you or your users can take advantage of the State Explorer tag you've just defined, you must save your smart tag list definition code in an XML file in a proper location on your computer. 1. Move or copy the State.xml file that you prepared in the previous section into the following folder Program Files Common Files Microsoft Shared Smart Tag Lists Smart tag lists placed in the above directory will be available to all users. Note If the smart tag has been designed for a specific user,...
The IsArray Function
Using the IsArray function, you can test whether a variable is an array. The IsArray function returns either true, if the variable is an array, or false, if it's not an array. Here's an example 1. Insert a new module into the current project and rename it IsArray_Function. 2. Enter the code of the IsThisArray procedure, as shown below 'declare a dynamic array Dim sheetNames As String Dim totalSheets As Integer Dim counter As Integer 'count the sheets in the current workbook totalSheets...