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

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

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

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

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

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

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

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

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

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

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

Contents at a Glance

Part 1 Introducing Chapter 1 What Is Chapter 2 Jumping Right Part 11 How VBA Works with Chapter 3 Introducing the Visual Basic Chapter 4 Introducing the Excel Object Chapter 5 VBA Sub and Function Chapter 6 Using the Excel Macro Part 111 Programming Chapter 7 Essential VBA Language Chapter 8 Working with Range Chapter 9 Using VBA and Worksheet Chapter 10 Controlling Program Flow and Making Chapter 11 Automatic Procedures and Chapter 12 Error-Handling Chapter 13 Bug Extermination Chapter 14 VBA...

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

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