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

Store Macro In

The Store Macro In option tells Excel where to store the macro that it is recording. By default, Excel puts the recorded macro in a module in the active workbook. If you prefer, you can record it in a new workbook (Excel opens a blank workbook) or in your Personal Macro Workbook. Your Personal Macro Workbook is a hidden workbook that opens automatically when Excel starts. This is a good place to store macros that you'll use with multiple workbooks. The Personal Macro Workbook is named personal....

Properties of Command Bar controls

CommandBar controls have a number of properties that determine how the controls look and work. This list contains some of the more useful properties for CommandBar controls i Caption The text displayed for the control. If the control shows only an image, the Caption appears when you move the mouse over the control. i FaceID A number that represents a graphics image displayed next to the control's text. i BeginGroup True if a separator bar appears before the control. i OnAction The name of a VBA...

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

A function with no argument

Like Sub procedures, Function procedures need not have arguments. For example, Excel has a few built-in worksheet functions that don't use arguments, including RAND, TODAY, and NOW. Here's a simple example of a function with no arguments. The following function returns the UserName property of the Application object. This name appears in the Options dialog box (General tab). This simple but useful example shows the only way you can get the user's name to appear in a worksheet formula ' Returns...

Adding eventhandler procedures

Here's how to add an event-handler procedure for the Cancel and OK buttons VBE activates the Code window for the UserForm and inserts an empty procedure The procedure named CancelButton_Click is executed when the Cancel button is clicked, but only when the dialog box is displayed. In other words, clicking the Cancel button when you're designing the dialog box won't execute the procedure. Because the Cancel button's Cancel property is set to True, pressing Esc also triggers the...

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 Options tab

Figure 19-4 shows the Options tab of the Customize dialog box. The options on this tab control how both menus and toolbars behave. The options that affect toolbars are as follows The Options tab of the Customize dialog box. The Options tab of the Customize dialog box. 1 Using one row for two toolbars You can save a little bit of valuable screen space by removing the checkmark from the Show Standard and Formatting Toolbars on Two Rows check box to force Excel to stuff both toolbars on a single...

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

Disabling shortcut menus

You may want to disable one or more shortcut menus while your application is running. For example, you may not want the user to access the commands by right-clicking a cell. If you want to disable all shortcut menus, use the following procedure, also available on this book's Web site Dim cbar As CommandBar For Each cbar In Application.CommandBars If cbar.Type msoBarTypePopup Then cbar.Enabled False End If Next cbar End Sub Disabling the shortcut menus remains between sessions. Therefore, you...

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

Writing an Event Handler Procedure

The VBE helps you out when you're ready to write an event-handler procedure it displays a list of all events that Excel can recognize. Figure 11-2 shows a Code window for the ThisWorkbook object the code window is maximized to fill the entire code window area . To display this empty Code window, double-click the ThisWorkbook object in the Project window. This Code window has two drop-down lists at the top. An empty Code window for the This Workbook object. Figure 11-2 shows a Code window for...

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

The Delete method

Clearing a range differs from deleting a range. When you delete a range, Excel shifts the remaining cells around to fill up the range you deleted. The following example uses the Delete method to delete row 6 When you delete a range that's not a complete row or column, Excel needs to know how to shift the cells. To see how this works, experiment with the Excel EditODelete command. The following statement deletes a range and then fills the resulting gap by shifting the other cells to the left...

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

Controlling Excel from Word

As you might expect, you can also control Excel from another application such as another programming language or a Word VBA procedure . For example, you might want to perform some calculations in Excel and return the result to a Word document. You can create any of the following Excel objects with the adjacent functions Application object Workbook object CreateObject Excel.Sheet Chart object An Excel VBA procedure created this Word document. An Excel VBA procedure created this Word document....

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

Executing the procedure from a button or shape

You can create still another means for executing the macro by assigning the macro to a button or any other shape on a worksheet. To assign the macro to a button, follow these steps 2. Add a button from the Forms toolbar. To display the Forms toolbar, right-click any toolbar and choose Forms from the shortcut menu. 3. Click the Button tool on the Forms toolbar. 4. Drag in the worksheet to create the button. After you add the button to your worksheet, Excel jumps right in and displays the Assign...

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

Simplifying object references

As you probably already know, references to objects can become very lengthy. For example, a fully qualified reference to a Range object may look like this You can instruct Excel to not display these types of alerts while running a macro. .Range InterestRate If your macro frequently uses this range, you may want to create an object variable by using the Set command. For example, the following statement assigns this Range object to an object variable named Rate Set Rate WorkbooksCMyBook.xls _...

Recording in relative mode

In some cases you want your recorded macro to work with cell locations in a relative manner. You may want the macro to start entering the month names in the active cell. In such a case, you need to use relative recording. The Stop Recording toolbar, which consists of only two buttons, is displayed when you are recording a macro. You can change the manner in which Excel records your actions by clicking the Relative Reference button on the Stop Recording toolbar. This button is a toggle. When the...

Adding a menu item

The example in the preceding section demonstrates how to create a new menu. The following example adds a menu item to the Excel Format menu. This menu item, when clicked, executes a macro named ToggleWordWrap. The ToggleWordWrap procedure changes the WrapText property of the selected cells. After creating the menu item, I change the Caption, OnAction, and BeginGroup properties. Setting BeginGroup to True displays a separator bar before the new menu item. Figure 20-5 shows the modified Format...

Using other toolbar button operations

When Excel is in customization mode that is, when the Customize dialog box is displayed , you can right-click a toolbar control to display a shortcut menu of additional actions. Figure 19-6 shows the shortcut menu that appears when you right-click a button in customization mode. These commands are described in the following list. Note that some of these commands are unavailable for certain toolbar controls. 1 Reset Resets the control to its original state. 1 Delete Deletes the control. 1 Name...

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

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

Set MItem Outlook AppCreateltemO

The code sets the To, Subject, and Body properties, and then uses the Send method to send each message. Figure 23-7 shows one of the e-mails created by Excel. Create a personalized e-mail by using Excel. Create a personalized e-mail by using Excel. This example is available on this book's Web site. To use this example you must have Microsoft Outlook installed.

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

The Address property

Address, a read-only property, displays the cell address for a Range object in absolute notation a dollar sign before the column letter and before the row number . The following statement displays the message box shown in Figure 8-1. MsgBox Range Cells 1, 1 , Cells 5, 5 .Address This message box displays the Address property of a 1-by-5 range.

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

Copying a range

Copying a range ranks right up there as one of the most favorite Excel activities of all time. When you turn on the macro recorder and copy a range from A1 A5 to B1 B5, you get this VBA macro Notice the last statement. This statement was generated by pressing Esc, which cancels the marching ants display that appears in the worksheet when you copy a range. This macro works fine, but you can copy a range more efficiently than this. You can produce the same result with the following one-line...

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

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

Changing properties for a User Form control

Every control you add to a UserForm has a number of properties that determine how the dialog box looks or behaves. You can change these properties with the Properties window, shown in Figure 16-4. The Properties window appears when you press F4, and the properties shown in this window depend on what is selected. If you select a different control, the properties change to those appropriate for that control. To hide the Properties window, click the close button in its title bar. Use the...

Displaying a chart in a dialog box

If you need to display a chart in a UserForm, you find that Excel doesn't provide any direct way to do so. Therefore, you need to get creative. This section describes a technique that lets you display one or more charts in a UserForm. The UserForm has an Image control. The trick is to use VBA code to save the chart as a GIF file and then specify that file as the Image control's Picture property. Figure 18-12 shows an example, which displays three charts. The Previous and Next buttons switch the...

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

Creating Excel Applications for Others

Developing spreadsheets for yourself and for other people Knowing what makes a good spreadsheet application Using guidelines for developing applications for others xcel programmers develop two basic types of spreadsheets spreadsheets that only they use and spreadsheets that other people use. This distinction often determines how much effort you need to put into creating a spreadsheet. Usually, developing spreadsheets for your use is much easier than developing spreadsheets that others will...

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

Image control

Image Control Excel Vba

You may want to use an Image control to display your company's logo in a dialog box. Figure 17-8 shows a dialog box with an Image control that displays a photo of a famous Excel book author. The following list describes the most useful Image control properties i Picture The graphics image that is displayed. i PictureSizeMode How the picture is displayed if the control size does not match the image size. When you click the Picture property, you are prompted...

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

Recording Basics

You take the following basic steps when recording a macro. I describe these steps in more detail later in this chapter. 1. Determine what you want the macro to do. This step determines how well your macro works. 3. Determine whether you want cell references in your macro to be relative or absolute. 4. Choose ToolsOMacroORecord New Macro. Excel displays its Record Macro dialog box. 5. Enter a name, shortcut key, macro location, and description. Each of these items with the exception of the name...

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