Working with ADO

ActiveX Data Objects (ADO) is an object model that enables you to access data stored in a variety of database formats. This allows you to use a single object model for all your databases. In this section I present a simple example that uses ADO to retrieve data from an Access database. ADO programming is a very complex topic. If you need to access external data in your Excel application, invest in one or more books that cover this topic in detail. This example is here so you can get a feel for...

Table of Contents

Is This the Right So You Want to Be a Why What I Assume about Obligatory Typographical Conventions Check Your Security How This Book Is Part I Introducing Part II How VBA Works with Part III Programming Part IV Developing Custom Dialog Part V Creating Custom Toolbars and Part VI Putting It All Part VII The Part of Marginal Get the Sample Now Part 1 Introducing Chapter 1 What Is VBA Okay, So What Is What Can You Do with Inserting a text Automating a task you perform Automating repetitive...

Okay So Whats an AddIn

An Excel add-in is something you add to enhance Excel's functionality. Some add-ins provide new worksheet functions you can use in formulas other add-ins provide new commands or utilities. If the add-in is designed properly, the new features blend in well with the original interface, so they appear to be part of the program. Excel ships with several add-ins. Some of the more popular include the Analysis ToolPak, Conditional Sum Wizard, and Solver. You can also...

The IfThen structure

Okay, I'll say it If-Then is VBA's most important control structure. You'll probably use this command on a daily basis (at least I do). As in many other aspects of life, effective decision making is the key to success in writing programs. If this book has the effect I intend, you'll soon share my philosophy that a successful Excel application boils down to making decisions and acting upon them. What is structured programming Does it matter If you hang around with programmers, sooner or later...

Working with Add Ins

The most efficient way to load and unload add-ins is by choosing ToolsO Add-Ins. This command displays the Add-Ins dialog box shown in Figure 22-1. The list box contains the names of all add-ins that Excel knows about. In this list, check marks identify any currently open add-ins. You can open and close add-ins from the Add-Ins dialog box by selecting or deselecting the check boxes. The Add-Ins dialog box lists all of the add-ins known to Excel. Provides functions and interfaces for financial...

Developing Applications Step by Step

No simple recipe exists for developing a spreadsheet application. Besides, this isn't a cookbook. Fact is, everyone develops his or her own style for creating spreadsheet applications. In this section I provide you with some general guidelines that I find useful. At the very least, this information can help you improve your own development style. Spreadsheet developers typically perform some of the following activities. You won't necessarily perform all these steps for every application, and...

Introducing the Visual Basic Editor

Understanding the Visual Basic Editor Discovering the Visual Basic Editor parts Knowing what goes into a VBA module Understanding three ways to get VBA code into a module Customizing the VBA environment J s an experienced Excel user, you know a good deal about workbooks, formulas, charts, and other Excel goodies. Now it's time to expand your horizons and explore an entirely new aspect of Excel the Visual Basic Editor (VBE). In this chapter, you find out how to work with the VBE, and get down...

Custom Dialog Box Alternatives

Saving time by using any of several alternatives to custom dialog boxes Using the InputBox and MsgBox functions to get information from the user Getting a filename and path from the user Writing VBA code to display any of the Excel built-in dialog boxes Xou can't use Excel very long without being exposed to dialog boxes. They seem to pop up almost every time you select a command. Excel like most Windows programs uses dialog boxes to obtain information, clarify commands, and display messages....

Almost Ten Excel Resources

7his book is only an introduction to Excel VBA programming. If you hunger for more information, you can feed on the list of additional resources I've compiled here. You can discover new techniques, communicate with other Excel users, download useful files, ask questions, access the extensive Microsoft Knowledge Base, and lots more. Several of these resources are online services or Internet resources, which tend to change frequently. The descriptions are accurate at the time I'm writing this,...

Changing Boolean settings

Like a light switch, a Boolean setting is either on or off. For example, you might want to create a macro that turns the worksheet row and column headings on and off. With the headings turned on, Excel generates the following code if you record your actions while accessing the Options dialog box ActiveWindow.DisplayHeadings False On the other hand, if the headings are turned off when you record the macro, Excel generates the following code This may lead you to suspect that you need two macros...

Using the Excel Macro Recorder

Recording your actions using the Excel built-in macro recorder Understanding the types of macros you can record Setting the appropriate options for macro recording y can use two methods to create a macro Record it using the Excel macro recorder Write it with VBA This chapter deals specifically with the ins and outs of using the Excel macro recorder. Recording a macro isn't always the best approach, and some macros simply can't be recorded, no matter how hard you try. You see, however, that the...

Application Calculation xlCalculation Manual End

I discovered the xlCalculationManual constant by recording a macro that changed the calculation mode. I also could have looked in the Help system under Microsoft Excel Constants. As shown in Figure 7-3, the Help system lists all the built-in constants. The actual value of the built-in xlCalculationManual constant is -4135. Obviously, it's easier to use the constant's name than to look up the value (even if you knew where to look). By the way, the constant for changing to automatic calculation...

Using Assignment Statements

An assignment statement is a VBA statement that assigns the result of an expression to a variable or an object. Excel's Help system defines the term expression as . . . a combination of keywords, operators, variables, and constants that yields a string, number, or object. An expression can be used to perform a calculation, manipulate characters, or test data. I couldn't have said it better myself. Much of your work in VBA involves developing (and debugging) expressions. If you know how to...

The macros not quite perfect

It works pretty well, doesn't it Now try entering a negative number when you are prompted for a value. Oops. Trying to calculate the square root of a negative number is illegal on this planet. Excel responds with the message shown in Figure 12-2, indicating that your code generated a run-time error. For now, just click the End button. Or click the Debug button Excel suspends the macro so you can use the debugging tools. (I describe the debugging tools in Chapter...

Using Automation in Excel

You can write an Excel macro to control other applications, such as Microsoft Word. More accurately, Excel macros control the most important component of Word the so-called automation server. In such circumstances, Excel is called the client application, and Word is the server application. MsgBox Can't start & Program Figure 23-1 shows the Windows calculator displayed as a result of running this procedure. The Shell function returns a task identification number for the application. You can...

Ls the macro perfect yet

Now this code is absolutely perfect, right Not quite. Try running the procedure while the active sheet is a Chart sheet. As shown in Figure 12-3, Excel displays another message that's as illuminating as the other error messages you've seen. This error occurs because there is no active cell on a Chart sheet. Running the procedure when a chart is selected generates this error. Running the procedure when a chart is selected generates this error. The following listing uses the TypeName function to...

Are events useful

At this point, you may be wondering how these events can be useful. Here's a quick example. Suppose you have a workbook that other people use for data entry. Any values entered must be greater than 1,000. You can write a simple macro that Excel executes whenever someone enters data into a cell. (Entering data is an event.) If the user enters a value less than 1,000, the macro displays a dialog box reprimanding the user. The DataO Validation command in Excel provides another way to perform this...

Applying chart formatting

This example applies several different types of formatting to the active chart ActiveChart.Type xlArea ActiveChart.ChartArea.Font.Name Arial ActiveChart.ChartArea.Font.FontStyle Regular ActiveChart.ChartArea.Font.Size 9 xlNone True _ True ActiveChart.Legend.Position xlBottom End Sub You must activate a chart before executing this macro. Activate an embedded chart by clicking it. To activate a chart on a chart sheet, activate the chart sheet. To ensure that a chart is selected, you can add some...

An Addin Example

In this section I discuss the basic steps involved in creating a useful add-in. The example uses the ChangeCase text conversion utility that I describe in Chapter 16. The XLS version of this example is available at this book's Web site. You can create an add-in from this workbook. The workbook consists of one blank worksheet, a VBA module, and a UserForm. In addition, I added code to the ThisWorkbook object that creates a new menu item on the Tools menu. The following code, located in the Code...

The Select Case structure

The Select Case structure is useful for decisions involving three or more options although it also works with two options, providing an alternative to the If-Then-Else structure . The syntax for the Select Case structure follows Select Case testexpression Case expressionlist-n Don't be scared off by this official syntax. Using the Select Case structure is quite easy. The following example shows how to use the Select Case structure. This also shows another way to code the examples presented in...

Using Comments in Your VBA Code

A comment is the simplest type of VBA statement. Because VBA ignores these statements, they can consist of anything you want. You can insert a comment to remind yourself why you did something or to clarify some particularly elegant code you wrote. Use comments liberally to describe what the code does which isn't always obvious by reading the code itself . Often, code that makes perfect sense today mystifies you tomorrow. Been there. Done that. You begin a comment with an apostrophe ' . VBA...

Selecting a range

In some cases, you may want the user to select a range while a dialog box is displayed. An example of this choice occurs in the second step of the Excel Chart Wizard. The Chart Wizard guesses the range to be charted, but the user is free to change it from the dialog box. To allow a range selection in your dialog box, add a RefEdit control. The following example displays a dialog box with the current region's range address displayed in a RefEdit control, as shown in Figure 18-6. The current...

Other operators

Besides the equal sign operator discussed in the previous section , VBA provides several other operators. Table 7-3 lists these operators, with which you are familiar from your worksheet formulas experience. The term concatenation is programmer speak for put together. Thus, if you concatenate strings, you are combining strings to make a new and improved string. As shown in Table 7-4, VBA also provides a full set of logical operators. Consult the Help system...

Using Worksheet Functions in VBA

Although VBA offers a decent assortment of built-in functions, you might not always find exactly what you need. Fortunately, you can also use most of Excel's worksheet functions in your VBA procedures. The only worksheet functions that you cannot use are those that have an equivalent VBA function. VBA makes Excel's worksheet functions available through the WorksheetFunction object, which is contained in the Application object. Remember, the Application object is Excel. Therefore, any statement...

An Intentional Error

Sometimes you can use an error to your advantage. For example, suppose you have a macro that works only if a particular workbook is open. How can you determine whether that workbook is open Perhaps the best solution is to write a general-purpose function that accepts one argument a workbook name and returns True if the workbook is open, False if it's not. This function takes advantage of the fact that Excel generates an error if you refer to a workbook that is not open. For example, the...

Working with Charts

Charts are packed with different objects, so manipulating charts with VBA can be quite confusing. To get a feel for this, turn on the macro recorder, create a chart, and perform some routine chart-editing tasks. You may be surprised by the amount of code Excel generates. After you understand the objects in a chart, however, you can create some useful macros. To write macros that manipulate charts, you must understand some terminology. An embedded chart on a worksheet is a ChartObject object....

Displaying Excels Builtin Dialog Boxes

You can write VBA code that performs the equivalent of selecting an Excel menu command and making choices in the resulting dialog box although Excel doesn't actually display the dialog box. For example, the following statement has the same effect as choosing the EditOGo To command, specifying a range named InputRange, and clicking OK Application.Goto Reference InputRange When you execute this statement, the Go To dialog box does not appear. This is almost always what you want to happen you...

The Before RightClick event

The BeforeRightClick event is similar to the BeforeDoubleClick event, except that it consists of right-clicking a cell. The following procedure checks to see whether the cell that was right-clicked contains a numeric value. If so, the code displays the Format Number dialog box and sets the Cancel argument to True avoiding the normal shortcut menu display . If the cell does not contain a numeric value, nothing special happens the shortcut menu is displayed as usual. Private Sub...

Controlling Word from Excel

The example in Figure 23-2 demonstrates an automation session by using Word. The MakeMemos procedure creates three customized memos in Word and then saves each memo to a separate file. The information used to create the memos is stored in a worksheet. Word automatically generates three memos based on this Excel data. Create Word Memos Using Data on This Worksheet The monthly sales data for your region is listed below. This information was obtained From the central database. Please call if you...

Identifying specific errors

Some are serious and some are less serious. Although you may ignore errors you consider inconsequential, you must deal with other, more serious errors. In some cases, you need to identify the specific error that occurred. When an error occurs, Excel stores the error number in an Error object named Err. This object's Number property contains the error number. You can get a description of the error by using the VBA Error function. For example, the following...

The OnTime event

The OnTime event occurs when a particular time of day occurs. The following example demonstrates how to program Excel so that it beeps and then displays a message at 3 00 p.m. In this example, I use the OnTime method of the Application object. This method takes two arguments the time 0.625 or 3 00 p.m. and the code to execute when the time occurs DisplayAlarm . This procedure is quite useful if you tend to get so wrapped up in your work that you forget about meetings and appointments. Just set...

Why Create dialog Boxes

Some of the VBA macros you create behave the same every time you execute them. For example, you may develop a macro that enters a list of your employees into a worksheet range. This macro always produces the same result and requires no additional user input. You might develop other macros, however, that behave differently under various circumstances or that offer the user options. In such cases, the macro may benefit from a custom dialog box. A custom dialog box provides a simple means for...

Curent Rate

This misspelled variable, which is difficult to spot, will probably cause your routine to give incorrect results. If you use Option Explicit at the beginning of your module and declare the CurrentRate variable , Excel generates an error if it encounters a misspelled variation of that variable. To ensure that the Option Explicit statement is inserted automatically whenever you insert a new VBA module, turn on the Require Variable Definition option. You'll find it in the Editor tab of the Options...

Cube Root

The cell displays 12, which is indeed the cube root of 1728. As you might expect, you can use a cell reference as the argument for the CubeRoot function. For example, if cell A1 contains a value, you can enter CubeRoot A1 . In this case, the function returns the number obtained by calculating the cube root of the value in A1. You can use this function any number of times in the worksheet. As with Excel's built-in functions, your custom functions also appear in the Insert Function dialog box....

Setting breakpoints in your code

Earlier in this chapter, I discuss using MsgBox functions in your code to monitor the values of certain variables. Displaying a message box essentially halts your code in midexecution, and clicking the OK button resumes execution. Wouldn't it be nice if you could halt a routine's execution, take a look at any of your variables, and then continue execution Well, that's exactly what you can do by setting a breakpoint. You can set a breakpoint in your VBA code in several ways 1 Move the cursor to...

A function with an indefinite number of arguments

Some Excel worksheet functions take an indefinite number of arguments. A familiar example is the SUM function, which has the following syntax The first argument is required, but you can have as many as 29 additional arguments. Here's an example of a SUM function with four range arguments Here's a function that can have any number of single-value arguments. This function doesn't work with multicell range arguments. Function Concat string1, ParamArray string2 ' Demonstrates indefinite number of...

Scroll Bar control

How Link Vba Scrollbar Label

The ScrollBar control is similar to a SpinButton control described later . The difference is that the user can drag the ScrollBar's button to change the control's value in larger increments. Figure 17-14 shows a ScrollBar control. Its Value is displayed in a Label control. A ScrollBar control, with a Label control below it. A ScrollBar control, with a Label control below it. The following is a description of the most useful properties of a ScrollBar control 1 Value The control's current value....

The Select method

Use the Select method to select a range of cells. The following statement selects a range on the active worksheet Before selecting a range, make sure you've activated the range's worksheet otherwise, you get an error or the wrong range is selected. For example, if Sheet1 contains the range you want to select, use the following statements to select the range Sheets Sheet1 .Activate Range A1 C12 .Select Contrary to what you may expect, the following statement generates an error. In other words,...

Option Button control

OptionButtons are useful when the user needs to select from a small number of items. OptionButtons are always used in groups of at least two. Figure 17-12 shows two sets of OptionButtons Report Destination and Layout . One set uses graphics images set with the Picture property . Two sets of Option Button controls, each contained in a Frame control. Report Destination Printer C File Email The following is a description of the most useful OptionButton control properties Accelerator A letter that...

Save the file from the VBE by choosing FileOSave

If you create an add-in that stores information in a worksheet, you must set the workbook's IsAddIn property to False to view the workbook. You do this in the Property window when the ThisWorkbook objects is selected see Figure 22-5. After you've made your changes, make sure that you set the IsAddIn property back to True before you save the file. You now know how to work with add-ins and why you might want to create your own add-ins. One example in this chapter shows you the steps for creating...

Using a User Form as a progress indicator

Userform Visual Basic Excel

One of the most common Excel programming questions I hear is How can I make a UserForm display the progress of a lengthy macro Use Excel's custom dialog box to easily create an attractive progress indicator, as shown in Figure 18-9. Such a use of dialog boxes does, however, require a few tricks which I'm about to show you. This UserForm functions as a progress indicator for a lengthy macro. This UserForm functions as a progress indicator for a lengthy macro. Creating the progress indicator...

A function with an optional argument

Many Excel built-in worksheet functions use optional arguments. An example is the LEFT function, which returns characters from the left side of a string. Its official syntax follows The first argument is required, but the second is optional. If you omit the optional argument, Excel assumes a value of 1. Therefore, the following formulas return the same result The custom functions you develop in VBA also can have optional arguments. You specify an optional argument by preceding the argument's...

Creating a tabbed dialog box

Tabbed dialog boxes are useful because they let you present information in small, organized chunks. The Excel Options dialog box which is displayed when you choose ToolsOOptions is a good example. This dialog box uses a whopping 13 tabs to add some organization to an overwhelming number of options. Creating your own tabbed dialog boxes is relatively easy, thanks to the MultiPage control. Figure 18-11 shows a custom dialog box that uses a MultiPage control with three pages, or tabs. When the...

Commanding the Command Bars collection

You manipulate Excel toolbars and menus, for that matter by using objects located in the CommandBars collection. The CommandBars collection consists of 1 All Excel built-in toolbars 1 Any other custom toolbars that you create 1 A built-in menu bar named Worksheet menu bar, which appears when a worksheet is active 1 A built-in menu bar named Chart menu bar, which appears when a chart sheet is active 1 Any other custom menu bars that you create As I mention at the beginning of this chapter, the...

Looping through a range efficiently

Many macros perform an operation on each cell in a range, or they might perform selected actions based on each cell's content. These macros usually include a For-Next loop that processes each cell in the range. The following example demonstrates how to loop through a range of cells. In this case, the range is the current selection. A variable named Cell refers to the cell being processed. Within the For-Next loop, the single statement evaluates the cell and changes its interior color if the...

Wrapping Your Mind around Collections

Office Vba Object Model

Collections are another key concept in VBA programming. A collection is a group of objects of the same type. And to add to the confusion, a collection is itself an object. Here are a few examples of commonly used collections i Workbooks A collection of all currently open Workbook objects i Worksheets A collection of all Worksheet objects contained in a particular Workbook object i Charts A collection of all Chart objects chart sheets contained in a particular Workbook object i Sheets A...

Recording in absolute mode

Follow these steps to record a simple macro in absolute mode. This macro simply enters three month names into a worksheet 1. Choose ToolsOMacroO Record New Macro. 2. Type Absolute as the name for this macro. 3. Click OK to begin recording. 4. Activate cell B1 and type Jan in that cell. 5. Move to cell C1 and type Feb. 6. Move to cell D1 and type Mar. 7. Click cell B1 to activate it again. 9. Press Alt F11 to activate the VBE. 10. Examine the Module1 module. When executed, this macro selects...

Planning an application that meets user needs

After you determine the end users' needs, you might be tempted to jump right in and start fiddling around in Excel. Take it from someone who suffers from this problem Try to restrain yourself. Builders don't construct a house without a set of blueprints, and you shouldn't develop a spreadsheet application without a plan. How formal you make your plan depends on the project scope and your general working style. You should, however, spend at least some time thinking about what you need to do and...

The Get OpenFilename Method

If your VBA procedure needs to prompt the user for a filename, you could use the InputBox function. An InputBox usually isn't the best tool for this job, however, because most users find it difficult to remember paths and directory names, and typographic errors often result. For a better solution to this problem, use the GetOpenFilename method of the Application object, which ensures that your application gets a valid filename, including its complete path. The GetOpenFilename method displays...

The Before DoubleClick event

You can set up a VBA procedure to be executed when the user double-clicks a cell. In the following example which is stored in the Code window for a Sheet object , double-clicking a cell makes the cell bold if it's not bold or not bold if it is bold Private Sub Worksheet_BeforeDoubleClick _ ByVal Target As Excel.Range, Cancel As Boolean Target.Font.Bold Not Target.Font.Bold Cancel True End Sub The Worksheet_BeforeDoubleClick procedure has two arguments Target and Cancel. Target represents the...

Text Box control

A TextBox control lets the user enter text. Figure 17-16 shows a dialog box with two TextBox controls. The following is a description of the most useful TextBox control properties 1 AutoSize If True, the control adjusts its size automatically, depending on the amount of text. i ControlSource The address of a cell that contains the text in the TextBox. i IntegralHeight If True, the TextBox height adjusts automatically to display full lines of text when the list is scrolled vertically. If False,...

Whats a Spreadsheet Application

Excel programming is essentially the process of building applications that use a spreadsheet rather than a traditional programming language. In many cases, people other than the application developer use these applications. My working definition of a spreadsheet application is this A spreadsheet file or group of related files designed so that someone other than the developer can perform useful work without extensive training. Based on this definition, most of the spreadsheet files you've...

The Before Close event for a workbook

Here's an example of the Workbook_BeforeClose event-handler procedure, which is automatically executed immediately before the workbook is closed. This procedure is located in the Code window for a ThisWorkbook object Sub Workbook_BeforeClose Cancel As Boolean Would you like to make a backup of this file FName F BACKUP amp ThisWorkbook.Name This routine uses a message box to ask the user whether he would like to make a backup copy of the workbook. If the answer is yes, the code uses the...

List Box example

ListBoxes are useful controls, but working with them can be a bit tricky. Before displaying a dialog box that uses a ListBox, fill the ListBox with items. Then, when the dialog box is closed, you need to determine which item s the user selected. When dealing with list boxes, you need to know about the following properties and methods 1 AddItem You use this method to add an item to a ListBox. 1 ListCount This property returns the number of items in the ListBox. 1 ListIndex This property returns...

Looping through the Chart Objects collection

This example changes the chart type of every embedded chart on the active sheet. The procedure uses a For-Next loop to cycle through each object in the ChartObjects collection, access the Chart object in each, and change its Type property. For Each cht In ActiveSheet.ChartObjects The following macro performs the same function but works on all the chart sheets in the active workbook For Each cht In ActiveWorkbook.Charts

Knowing when to ignore errors

In some cases, it's perfectly okay to ignore errors. That's when the On Error Resume Next statement comes into play. The following example loops through each cell in the selected range and converts the value to its square root. This procedure generates an error message if any cell in the selection contains a nonpositive number If TypeName Selection lt gt Range Then Exit Sub In this case, you may want to simply skip any cell that contains a value you can't convert to a square root. You could...

What Is VBA

Gaining a conceptual overview of VBA Finding out what you can do with VBA Discovering the advantages and disadvantages of using VBA Taking a mini-lesson on the history of Excel rhis chapter is completely devoid of any hands-on training material. It does, however, contain some essential background information that assists you in becoming an Excel programmer. In other words, this chapter paves the way for everything else that follows and gives you a feel for how Excel programming fits into the...

Keypress events

While you work, Excel constantly monitors what you type. Because of this, you can set up a keystroke or a key combination to execute a procedure. Here's an example that reassigns the PgDn and PgUp keys Here's an example that reassigns the PgDn and PgUp keys Application.OnKey PgDn , Application.OnKey PgUp , After setting up the OnKey events by executing the Setup_OnKey procedure, pressing PgDn moves you down one row. Pressing PgUp moves you up one row. Notice that the key codes are enclosed in...

DoUntil loop

The Do-Until loop structure is similar to the Do-While structure. The two structures differ in their handling of the tested condition. A program continues to execute a Do-While loop while the condition remains true. In a Do-Until loop, the program executes the loop until the condition is true. Do Until condition statements Exit Do statements The following example is the same one presented for the Do-While loop but recoded to use a Do-Until loop ActiveCell.Value ActiveCell.Value 2...

Introduction

Reetings, prospective Excel programmer . . . Thanks for buying my book. I think you'll find that it offers a fast, enjoyable way to discover the ins and outs of Microsoft Excel programming. Even if you don't have the foggiest idea of what programming is all about, this book can help you make Excel jump through hoops in no time well, it will take some time . Unlike most programming books, this one is written in plain English, and even normal people can understand it. Even better, it's filled...