Using Controls On Worksheets

Category: General VBA / UserForms | [Item URL]

A companion file is available: Click here to download

Most developers know that it's possible to insert dialog box controls (such as buttons and checkboxes) directly on a worksheet. This topic, however, is the source of a great deal of confusion. This tip attempts to clarify some of the issues related to using controls on worksheets.

Two types of controls

First, it's important to understand that Excel supports two general types of controls:

  • ActiveX Controls
  • Excel Controls

These two types of controls are described in the table below.

ActiveX Controls Excel Controls
Excel versions 97, 2000 5, 95, 97, 2000
Which toolbar? Control Toolbox Forms
Controls available CheckBox, TextBox, CommandButton, OptionButton, ListBox, ComboBox, ToggleButton, SpinButton, ScrollBar, Label, Image Label, GroupBox, Button, CheckBox, OptionButton, ListBox, ComboBox, ScrollBar, Spinner
Macro code storage In the code module for the Sheet In any standard VBA module
Macro name Corresponds to the control name (e.g., CommandButton1_Click) Any name you specify.
Correspond to... UserForm controls Dialog Sheet controls
Customization Extensive, using the Properties box Minimal
Respond to events Yes Click or Change events only

Using ActiveX controls

To insert an ActiveX control on a worksheet, make sure the Control Toolbox toolbar is displayed. Then click the desired control, and drag in the sheet to create the control. After adding a control, Excel is in "design mode." To make any changes to the control, Excel must be in design mode. To test the control, Excel must not be in design mode. Use the Design Mode button on the Control Toolbox toolbar to toggle design mode.

To customize the control, right-click it and select Properties. This displays the Properties window. Each control has its own set of properties.

Some ActiveX controls can be connected to a cell (i.e., the LinkedCell property). Other controls can receive information from a range. For example, a ListBox control can get its list data from the ListFillRange property. Consult the online help for complete details regarding the properties for each control.

Each ActiveX control also triggers events. For example, a CommandButton control generates a Click event. You can write an event-handler Sub procedure to respond to the events. The procedure below, for example, displays a message box when a CommandButton (named CommandButton1) is clicked:

Private Sub CommandButton1_Click()
    MsgBox "CommandButton1 was clicked."
End Sub

Event-handler procedures must be located in the code module for the sheet on which the control is placed. It's important to understand that you cannot assign an arbitrary macro to an ActiveX control. To access the correct code module, right-click the control and choose View Code. The code module displays two drop-down controls at the top. Use these controls to determine the events supported for your control.

Using Excel controls

To insert an Excel control on a worksheet, make sure the Forms toolbar is displayed. Then click the desired control and drag in the sheet to create the control.

Right-click the control to access the Format dialog box. Any customizations to the control are made using this dialog box. Formatting options are limited. To assign a macro to the control, right-click it and select Assign Macro. Excel controls can execute any macros.


Sharing Autocorrect Shortcuts

Category: General / General VBA | [Item URL]

Q. I've set up approximately 200 Excel AutoCorrect shortcuts that represent various products and services offered by my company. What's the best way to transfer these shortcuts to other systems so that my coworkers can use them?

AutoCorrect, which debuted in Excel 95, can correct common spelling errors on the fly. As many users of Excel have discovered, you can also assign "shorthand shortcuts" that expand to a longer sequence of characters. For example, you may define "awc" as an AutoCorrect shortcut for "Amalgamated Widget Corporation of America." When you type awc into a cell, Excel will then replace it with the associated text string. You define and manage your shortcuts by using the Tools, AutoCorrect command.

Excel and all other Microsoft Office applications store AutoCorrect shortcuts in a single *.acl file in your Windows folder (the exact file name will vary). So changes you make from Excel, say, will be available in Word. However, there's no tool for manually editing the binary *.acl file and moving such a file from one system to another will invalidate your existing entries. The solution is to use a VBA macro to create the shortcuts.

Start Excel with a new workbook with one sheet, and then enter your shortcuts and their associated text into columns A and B, respectively (as in the figure below). Enter as many as you like, beginning in row 1, and don't include any blank rows between the entries. Save and name this worksheet.

Select Tools, Macro, Macros to display the Macros dialog box. Type CreateShortcuts in the Macro Name field and click Create. Then enter the following macro into the VBA module, and press Alt-F11 to return to Excel.

Sub CreateShortcuts()
  ItemCount = Application.CountA(Range("Sheet1!A:A"))
  For Row = 1 To ItemCount
    ShortText = Cells(Row, 1)
    LongText = Cells(Row, 2)
    Application.AutoCorrect.AddReplacement ShortText, LongText
  Next Row
End Sub

Save the workbook and distribute it to your coworkers. To add the AutoCorrect shortcuts, open the workbook, select Tool, Macro, Macros, and then execute the CreateShortcuts macro. Be aware that existing shortcuts with the same name will be overwritten without warning.


Restrict Cursor Movement To Unprotected Cells

Category: General / General VBA | [Item URL]

Q. The formulas in my worksheet use values in several input cells. I've unlocked the input cells and protected the sheet so the user can't change the formulas. Can I set things up so the cell cursor moves only to the input cells?

Yes. You've already unlocked your input cells and ensured that all other cells are locked. By default all cells are locked, but you can change that by using the Protection tab of the Format Cells dialog box. Select the cells to be changed and choose Format, Cells. In this case, the input cells are unlocked and all other cells are locked.

Protect the worksheet in Excel 97 by using Worksheet, Protect or Tools, Protection, Protect Sheet (you can specify a password to keep others from "unprotecting" the sheet). Once the sheet is protected, press Tab to move the cell pointer to the next unlocked cell.

This does not prevent the user from selecting unlocked cells using the cursor keys. To make those cells unselectable, change the worksheet's EnableSelection property. Select View, Toolbars, Control Toolbox to display the Control Toolbox toolbar. Click the Properties button to display the Properties box for the worksheet, then click the cell labeled "xlNoRestrictions" and use the drop-down list to change the EnableSelection property to xlUnlockedCells. Close the Properties box. As long as the worksheet is protected, users cannot select the locked cells on the worksheet.

This procedure does not save the EnableSelection property setting with the workbook. To create a simple macro that turns this setting on when the workbook is opened, press Alt-F11 to activate the Visual Basic Editor. Locate your workbook name in the Project window, and double-click it to expand its listing. Then double-click the item labeled ThisWorkbook and enter the following VBA code:

Private Sub Workbook_Open()
  Worksheets("Sheet1").EnableSelection = xlUnlockedCells
End Sub

This macro executes whenever the workbook is opened, and sets the EnableSelection property of Sheet1 to xlUnlockedCells. The technique can be circumvented by changing the EnableSelection property to its default value (xlNoRestrictions). Few users know about this dodge, however.



Making An Exact Copy Of A Range Of Formulas

Category: General / Formulas / General VBA | [Item URL]

Assume that A1:D10 on Sheet1 has a range of cells that contain formulas. Furthermore, assume that you want to make an exact copy of these formulas, beginning in cell A11 on Sheet1.  By "exact," I mean a perfect replica -- the original cell references should not change.

If the formulas contain only absolute cell references, it's a piece of cake. Just use the standard copy/paste commands. But if the formulas contain relative or mixed references, the standard copy/paste technique won't work because the relative and mixed references will be adjusted when the range is pasted.

If you're a VBA programmer, you can simply execute the following code:

With Sheets("Sheet1")
 .Range("A11:D20").Formula = .Range("A1:D10").Formula
End With

Following are step-by-step instructions to accomplish this task without using VBA (contributed by Bob Umlas):

  1. Select the source range (A1:D10 in this example).
  2. Group the source sheet with another empty sheet (say Sheet2). To do this, press Ctrl while you click the sheet tab for Sheet2
  3. Select Edit - Fill - Across worksheets (choose the All option in the dialog box).
  4. Ungroup the sheets (click the sheet tab for Sheet2)
  5. In Sheet2, the copied range will be selected. Choose Edit - Cut.
  6. Activate cell A11 (in Sheet2) and press Enter to paste the cut cells. A11.D20 will be selected.
  7. Re-group the sheets. Press Ctl and click the sheet tab for Sheet1
  8. Once again, use Edit - Fill - Across worksheets.
  9. Activate Sheet1, and you'll find that A11:D20 contains an exact replica of the formulas in A1:D10.

Note: For another method of performing this task, see Making An Exact Copy Of A Range Of Formulas, Take 2.



Developer FAQ - General Questions

Category: General VBA | [Item URL]

Note: This document was written for Excel 97 - 2000.

Why does Excel have two macro languages?

Early versions of Excel used a macro language called XLM. The VBA language was introduced in Excel 5, and is vastly superior in every way. XLM has been phased out, so for new macro development you should use VBA.

I need to distribute a workbook to someone who still uses Excel 4. Is there a way to have Excel 97 or Excel 2000 record my actions to an XLM macro?

No, the macro recorder in these versions can only generate VBA macro code.

Do XLM macros written for previous versions of Excel work in Excel 97 and Excel 2000?

In most cases, they will work perfectly.

I'm looking for a third-party utility that will convert my Excel 4 macros to VBA. Am I out of luck?

Yes, you are. No such utility exists, and it is unlikely that one will be written. Such conversions must be done manually. Because all versions of Excel can execute XLM macros, however, there is really no reason to convert these macros -- unless, of course, you'd like to update the macro to include new features.

Is it possible to call a VBA procedure from an Excel 4.0 XLM macro?

Yes, by using XLM's RUN function. For example, the following macro runs the Test subroutine contained in Module1 in workbook Book1.xls:

=RUN(Book1.xls!Module1.Test)

Is there a way to automatically convert 1-2-3 or Quattro Pro macros to VBA macros?

No way. The macros will have to be rewritten for Excel.

Is there a utility that will convert my Excel application into a stand-alone EXE file?

No.

How can I add a drop-down list to a cell so the user can choose a value from the list?

Type the list of valid entries in a single column (this column can be hidden, if desired). Select the cell or cells that will display the list of entries, choose Data - Validation, and select the Settings tab. From the Allow drop-down list, select List. In the Source box, enter a range address or a reference to the items in your sheet. Make sure the In-cell dropdown box is selected. This technique does not require any macros.

How can I increase the number of columns in a worksheet?

You can't. This number is fixed and cannot be changed.

How can I increase the number of rows in a worksheet?

See the answer to the previous question.

Is it possible to change the color and font of the sheet tabs?

You can't change the color. You can, however, change the font size. In the Windows Control Panel, select Display. In the Display Properties dialog box, click the Appearance tab. In the Item list, select Scrollbar. Use the spinner to increase or decrease the size. This setting will affect other programs.

Note: Excel 2002 offers the ability to change the color of sheet tabs.

How can I print the workbook's full path and filename in a page header?

Amazingly, Microsoft continues to ignore what must amount to thousands of requests per year for this feature. The only way to print a workbook's path in a header or footer is to use VBA. The best approach is to take advantage of the WorkbookBeforePrint event. For example, place the following subroutine in the code module for the ThisWorkbook object to print the workbook's full path and filename in the left header of each sheet.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    For Each sht In ThisWorkbook.Sheets 
        sht.PageSetup.LeftHeader = ThisWorkbook.FullName 
    Next sht 
End Sub 


Developer FAQ - Visual Basic Editor

Category: General VBA | [Item URL]

Note: This document was written for Excel 97 - 2000.

In Excel 95, my VBA modules were located in my workbook. I can't see them when I open the file using Excel 97 or Excel 2000.

The modules are still there, but you view and edit them in the Visual Basic Editor. Press Alt+F11 to toggle between the VBE and Excel.

Can I use the VBA macro recorder to record all of my macros?

No. Recording is useful only for very simple macros. Macro that use variable, looping, or any other type of program flow changes cannot be recorded. However, you can often take advantage of the macro recorder to write some parts of your code or to discover the relevant properties or methods.

Excel 95 had a "record at mark" feature that let you record a macro beginning at a particular location within an existing macro. Is that feature still available?

No, it was removed beginning with Excel 97. To add new recorded code to an existing macro, you need to record it and then cut and paste the code to your existing macro.

I have some macros that are general purpose in nature. I would like to have these available all the time. What's the best way to do this?

Consider storing those general purpose macros in your Personal Macro Workbook. This is a (normally) hidden workbook that is loaded automatically by Excel. When you record a macro, you have the option of recording it to your Personal Macro Workbook. The file, Personal.xls, is stored in your \XLStart directory.

I can't find my Personal Macro Workbook. Where is it?

The Personal.xls file doesn't exist until you record a macro to it.

Every time my macro copies a worksheet, the new sheet name appears in the Project window of VBEas something like Sheet11111111111(Sheet 1(9)). What's the deal with this?

These strange names are the "code names" for Sheet objects, and they can get very unweildy if you do a lot of sheet copying. You can change the code name using the Properties window in the VBE.

I locked my project with a password, and forget what it was. Is there any way to unlock it?

Although Excel 97 and Excel 2000 workbooks are more secure than previous versions, several third-party password cracking products exist. Use a Web search engine and search for Excel password.

How can I write a macro to change the password of my project?

You can't. The protection elements of a VBA project are not exposed in the object model. Most likely, this was done to make it more difficult for password cracking software.

When I insert a new module, it always starts with an "Option Explicit" line. What does this mean?

If this line is included at the top of a module, it means that you must declare every variable before you use it (which is a good idea). If you don't want this line to appear in new modules, activate the VBE and select the Tools - Options command, click the Editor tab, and uncheck the Require Variable Declaration check box. Then you can either declare your variables or let VBA handle the data typing automatically.

Why does my VBA code appear in different colors? Can I change these colors?

VBA uses color to differentiate various types of text -- comments, keywords, identifiers, statements with a syntax error, and so on. You can adjust these colors (as well as the font used) by using the Tools - Options command (Editor Format tab) in the VBE.

I want to delete a VBA module by using VBA code. Can I do this?

Yes. The code below deletes Module1 from the active workbook.

With ActiveWorkbook.VBProject
    .VBComponents.Remove .VBComponents("Module1")
End With

I'm having trouble with the concatenation operator (&) in VBA. When I try to concatenate two strings, I get an error message.

This is probably because VBA is interpreting the ampersand as a type declaration character. Make sure that you insert a space before and after the concatenation operator.

I can't seem to get the VBA line continuation character (underscore) to work.

The line continuation sequence is actually two characters: a space followed by an underscore.

In Excel 95, I set my VBA module to be "very hidden" to prevent users from seeing it. When the workbook is opened in Excel 97, the module can be viewed in the VBE. Is this right?

I don't know if it's right, but that's the way it is. Excel 97 and later does not support the xlVeryHidden property for modules.

I've noticed after deleting a major amount of VBA code that the XLS file size is not reduced accordingly. Why is this?

This is because Excel doesn't always do a good job of cleaning up after itself. This sometimes causes some subtle problems with variables that you no longer use. One way to fix it is to export your module to a file, delete the module, and then import it again.

My workbook contains a VBA subroutine named Test. I tried to use Excel's Name box to create a range named Test, and Excel dumped me to the VB Editor, with the cursor on my Test subroutine. What's this all about?

I can only guess that it's a bug of some sort. Apparently, Excel thinks the name is already defined, possibly as an XLM macro. If you use the Insert - Name - Define command to define your range name, the problem won't occur.

I distributed an XLS application to many users. On some machines, my VBA error-handling procedures don't work. Why not?

The error-handling procedures won't work if the user has the Break on All Errors option set. This option is available in the Options dialog box (General tab) in the VBE. Unfortunately, there is no way to change this setting using VBA. To avoid this problem, you can distribute your application as an XLA add-in.



Developer FAQ - Subroutines

Category: General VBA | [Item URL]

Note: This document was written for Excel 97 - 2000.

What's the difference between a VBA subroutine and a macro?

Nothing, really. The term macro is a carry-over from the old days of spreadsheets. These terms are now used interchangeably.

What's a procedure?

A procedure can be either a subroutine or a function.

What is a Variant data type?

Variables that aren't specifically declared are assigned as a variant type, and VBA automatically converts the data to the proper type when it's used. This is particularly useful when getting values from a worksheet cell when you don't know in advance what the cell contains. Generally, it's a good idea to specifically declare your variables (using the Dim statement), because using variants is quite a bit slower.

What's the difference between a Variant array and an array of Variants?

A Variant is a special data type that can contain any kind of data -- a single value, or an array of values (that is, a Variant array). The code below creates a Variant that contains an array.

Dim X as Variant
X = Array(30, 40, 50)

A normal array can contain items of a specified data type, including non-typed Variants. The statement below creates an array that consists of 12 Variants.

Dim X (0 to 2) as Variant

Although a Variant containing an array is conceptually different from an array whose elements are of type Variant, the array elements are accessed in the same way.

What's a type definition character?

VBA lets you append a character to a variable's name to indicate the data type. For example, you can declare the MyVar variable as an integer by tacking % onto the name, as follows:

Dim MyVar% 

Here's a list of the type-declaration characters supported by VBA:

Integer %
Long &
Single !
Double #
Currency @
String $

Can a custom worksheet function written in VBA perform the same types of actions as a subroutine?

No. Functions called from a worksheet formula have some limitations. In general, they must be strictly "passive" -- they can't change the active cell, apply formatting, open workbooks, change the active sheet, and so on.

Functions can only perform calculations and return a value. An exception to this rule is the VBA MsgBox function. A custom function can display a MsgBox whenever it is recalculated. This is very handy for debugging a custom function.

I would like to create a function or subroutine that automatically changes the formatting of a cell based on the data I enter. For example, if I enter a value greater than 0, the cell's background color should be red. Is this possible?

It's certainly possible, and you don't need any programming. Use Excel's Conditional Formatting feature, accessed with the Format - Conditional Formatting command.

The Conditional Formatting features is useful, but I'd like to perform other types of operations when data is entered into a cell.

In that case, you can take advantage of the Change event for a worksheet object. Whenever a cell is changed, the Change event is triggered. If the code module for the Sheet object contains a procedure named Worksheet_Change, this procedure will be executed automatically.

What other types of events can be monitored?

Lots! Search the online help for events to get a complete listing.

I tried entering an event procedure (Sub Workbook_Open), but the procedure isn't executed when the workbook is open. What's wrong?

You probably put the procedure in the wrong places. Workbook event procedures must be in the code module for the ThisWorkbook object. Sheet event procedures must be in the code module for the appropriate Sheet object.

I can write an event procedure for a particular workbook. Is it possible to write an event procedure that will work for any workbook that's open?

Yes, but you need to use a class module. Details are in Chapter 18 of Excel 2000 Power Programming With VBA.

I'm very familiar with creating formulas in Excel. Does VBA use the same mathematical and logical operators?

Yes. And it includes some additional operators that aren't valid in worksheet formulas. These additional VBA operators are:

Operator Function

\ Division with an integer result

Eqv Returns True if both expressions are true or both are false

Imp Logical implication on two expressions

Is Compares two object variables

Like Compares two strings using wildcard characters

Xor Returns True if only one expression is true

How can I execute a subroutine that's in a different workbook?

Use the Run method of the Application object. The statement below executes a subroutine named Macro1 located in the Personal.xls workbook.

Run "Personal.xls!Macro1"

I've created several custom functions using VBA. I like to use these functions in my worksheet formulas, but I find it inconvenient to precede the function name with the workbook name. Is there any way around this?

Yes. Convert the workbook that holds the function definitions to an XLA add-in. When the add-in is open, you can use the functions in any other worksheet without referencing the function's filename.

Also, if you set up a reference to the workbook that contains the custom functions, you can use the function without preceding it with the workbook name. To create a reference, use the Tools - References command in the VBE.

I would like a particular workbook to be loaded every time I start Excel. I would also like a macro in this workbook to execute automatically. Am I asking too much?

Not at all. To open the workbook automatically, just store it in your \XLStart directory. To have the macro execute automatically, create a Workbook_Open macro in the code module for the ThisWorkbook object.

I have a workbook that uses a Workbook_Open subroutine. Is there a way to prevent this from executing when I open the workbook?

Yes. Hold down Shift when you issue the File - Open command. This technique also works with the Workbook_BeforeClose procedure. However, it does not work if the workbook you're opening is an add-in.

Can a VBA procedure access a cell's value in a workbook that is not open?

No. A formula in a worksheet can do this, but VBA cannot.

However, there is a way to do this by using an XLM macro.

How can I prevent the "save file" prompt from being displayed when I close a workbook from VBA?

Insert the following statement to eliminate this and other prompts:

Application.DisplayAlerts = False

How can I set things up so my macro runs once every hour?

You need to use the OnTime method of the Application object. This lets you specify a subroutine to execute at a particular time of day. When the subroutine ends, use the OnTime method again to schedule another event in one hour.

How do I prevent a macro from showing in the macro list?

Declare the subroutine using the Private keyword:

Private Sub MyMacro()

Or, you can add a dummy optional argument:

Sub MyMacro (Optional FakeArg)

I wrote a macro that creates lots of charts. After some of the charts are created, I get a "not enough memory" error. My system has lots of memory, so what's the problem?

Most likely, your system is running low on system resources. In some versions of Excel, creating charts uses system resources that are not returned to the system. The only way to regain the system resources is to restart Windows. Try upgrading to SR-2.

Is it possible to save a chart as a GIF file?

Yes, the code below saves the first embedded chart on Sheet1 as a GIF file named Mychart.gif.

Set CurrentChart = Sheets("Sheet1").ChartObjects(1).Chart
Fname = ThisWorkbook.Path & "\Mychart.gif"
CurrentChart.Export Filename:=Fname, FilterName:="GIF"

Are variables in a VBA procedure available to other VBA procedures? What if the procedure is in a different module? Or in a different workbook?

You're talking about a variable's scope. There are three levels of scope: local, module-level, and public. Local variables have the narrowest scope and are declared within a procedure. A local variable is visible only to the procedure in which it was declared. Module-level variables are declared at the top of a module, prior to the first procedure. Module-level variables are visible to all procedures in the module. Public variables have the broadest scope, and they are declared by using the Public keyword.



Developer FAQ - Functions

Category: General VBA / VBA Functions | [Item URL]

Note: This document was written for Excel 97 - 2000.

I created a custom worksheet function. When I access this function using the Insert Function dialog, it says Choose the Help button for help on this function and its arguments. How can I get Insert Function dialog box to display a description of my function?

As you discovered the message displayed in the Insert Function dialog box is erroneous and and misleading. To add a description for your custom function, select Tool - Macro - Macros to display the Macro dialog box. Your function won't be listed, so you must type it manually into the Macro name box. After typing the function's name, click Options to display the Macro Options dialog box. Enter the descriptive text in the Description box.

Can I also display help for the arguments for my custom function in the Paste Function dialog box?

Unfortunately, no.

My custom worksheet function appears in the User Defined category in the Insert Function dialog box. How can I make my function appear in a different function category?

You need to do this using VBA. The statement below assigns the function named MyFunc to category 1 (Finanacial)

Application.MacroOptions Macro:="MyFunc", Category:=1

The table below lists the valid function category numbers.

  1. No category (appears only in All)
  2. Financial
  3. Date & Time
  4. Math & Trig
  5. Statistical
  6. Lookup & Reference
  7. Database
  8. Text
  9. Logical
  10. Information
  11. Commands (this category is normally hidden)
  12. Customizing (this category is normally hidden)
  13. Macro Control (this category is normally hidden)
  14. DDE/External (this category is normally hidden)
  15. User Defined (default)
  16. Engineering (this category is valid only if the Analysis Toolpak add-in is installed)

How can I create a new function category?

You can't.

I have a custom function that will be used in a worksheet formula. If the user enters arguments that are not appropriate, how can I make the function return a true error value (#VALUE)?

If your function is named MyFunction, you can use the following statement to return an error value to the cell that contains the function:

MyFunction = CVErr(xlErrValue)

In this example, xlErrValue is a predefined constant. Constants for the other error values are listed in the online help.

Can I use Excel's built-in worksheet functions in my VBA code?

In most cases, yes. Excel's worksheet functions are accessed via the WorksheetFunction method of the Application object. For example, you could access the POWER worksheet functions with a statement such as the following:

Ans = Application.WorksheetFunction.Power(5, 3) 

This example raises 5 to the third power.

Generally, if VBA includes an equivalent function, you cannot use Excel's worksheet version. For example, because VBA has a function to compute square roots (Sqr) you cannot use the SQRT worksheet function in your VBA code.

Excel 95 doesn't support the WorksheetFunction method. Does that mean I can't make my Excel 2000 application compatible with Excel 95?

No. Actually, using the WorksheetFunction method is superfluous. The following statements have exactly the same result:

Ans = Application.WorksheetFunction.Power(5, 3) 
Ans = Application.Power(5, 3) 

Is there any way to force a line break in the text of a message box?

Use a carriage return or a line feed character to force a new line. The following statement displays the message box text on two lines. vbCr is a built-in constant that represents a carriage return.

MsgBox "Hello" & vbCr & Application.UserName


Developer FAQ - Objects, Properties, And Methods

Category: General VBA | [Item URL]

Note: This document was written for Excel 97 - 2000.

I don’t understand the concept of objects. Is there a listing of all of the Excel objects I can use?

Yes. The online help includes the information in a graphical format.

I’m overwhelmed with all the properties and methods available. How can I find out which methods and properties are available for a particular object?

There are several ways. You can also use the Object Browser available in the VBE. Press F2 to access the Object Browser, then choose Excel from the Libraries/Workbooks drop-down list. The list on the left shows all the Excel objects. When you select an object, its corresponding properties and methods appear in the list on the right.

The on-line help system for VBA is very extensive, and lists the properties and methods available for every object. The easiest way to access this is to enter the object in your VBA module and move the cursor anywhere within the object name. Press F1, and you’ll get the help topic appropriate for the object.

What’s the story with collections? Is a collection an object? What are collections?

A collection is an object that contains a group of related objects. A collection is designated by a plural noun. For example, the Worksheets collection is an object that contains all the Worksheet objects in a workbook. You can think of this as an array. Worksheets(1), for example, refers to the first Worksheet object in the Workbook. Rather than use index numbers, you can also use the actual worksheet name, such as Worksheets("Sheet1"). The concept of a collection makes it easy to work with all related objects at once, and to loop through all objects in a collection by using the For Each...Next construct.

When I refer to a worksheet in my VBA code, I get a "subscript out of range" error. I'm not using any subscripts. What gives?

This error will occur if you attempt to access an element in a collection that doesn't exist. For example, the statement below will generate the error if the active workbook does not contain a sheet named "MySheet".

Set X = ActiveWorkbook.Sheets("MySheet")

How can I prevent the user from scrolling around the worksheet?

You can either hide the unused rows and columns, or use a VBA statement to set the scroll area for the worksheet. The statement below, for example, sets the scroll area on Sheet1 so the user cannot activate any cells outside of B2:D50.

Sheets("Sheet1").ScrollArea = "B2:D50"

To set scrolling back to normal, use a statement like this:

Sheets("Sheet1").ScrollArea = ""

Be aware that the ScrollArea setting is not saved with the workbook. Therefore, you'll need to execute the VBA statement whenever the workbook is opened. You can use a Workbook_Open subroutine to do this.

What’s the difference between using Select and Application.Goto?

Range.Select will select a range on the active worksheet only. Use Application.Goto to select a range on any worksheet in a workbook. Application.Goto may or may not make another sheet the active sheet. The Goto method also lets you scroll the sheet so the range is in the upper left corner.

What's the difference between activating a range and selecting a range?

In some case, the Activate method and the Select method have exactly the same effect. But in other cases, they produce quite different results. Assume that range A1:C3 is selected. The statement below activates cell C3. The original range remains selected, but C3 becomes the active cell.

Range("C3").Activate

Again, assuming that range A1:C3 is selected, the statement below selects a single cell (which also becomes the active cell).

Range("C3").Select

I know how to write a VBA statement to select a range using a cell address, but how can I write a statement to select a range if I know only the row and column number?

Use the Cells method. The statement below, for example, selects the cell in the fifth row and the 12th columns.

Cells(5, 12).Select

Is there a VBA command to quit Excel? When I try to record the File - Exit command, Excel closes down before I can see what code it generates!

Use the following statement to end Excel.

Application.Quit 

How can I turn off the screen updating while a macro is running?

The following statement turns off screen updating and speeds up macros that modify the display:

Application.ScreenUpdating=False

Is it possible to display messages in the status bar while a macro is running? I have a lengthy macro, and it would be nice to display its progress in the status bar.

Yes. Assign the text to the StatusBar property of the Application object. Here's an example:

Application.StatusBar = "Now processing File " & FileNum

When your routine finishes, return the status bar back to normal with the following statement:

Application.StatusBar = False

I recorded a VBA macro that copies a range and pastes it to another area. The macro uses the Select method. Is there a more efficient way to copy and paste?

Yes. Although the macro recorder generally selects cells before doing anything with them, selecting is not necessary and may actually slow down your macro. Recording a very simple copy-and-paste operation generates four lines of VBA code (two of which use the Select method). Here’s an example:

Range("A1").Select
Selection.Copy
Range("B1").Select
ActiveSheet.Paste

These four lines can be replaced with a single statement, such as the following:

Range("A1").Copy Range("B1")

Notice that this statement does not use the Select method.

I have not been able to find a method to sort a VBA array. Does this mean that I have to copy the values to a worksheet and then use the Range.Sort method?

There is no built-in way to sort an array in VBA. Copying the array to a worksheet is one method, but you'll probably be better off if you write your own sorting routine. There are many sorting algorithms available, and some are quite easy to code in VBA. Excel 2000 Power Programming With VBA contains VBA code for several sorting techniques.

My macro works with the selected cells, but it fails if something else (like a chart) is selected. How can I make sure that a range is selected?

You can use VBA's TypeName function to check the Selection. Here's an example:

If TypeName(Selection) <> "Range" Then
    MsgBox "Select a range!"
    Exit Sub
End If

Another alternative is to use the RangeSelection property, which returns a Range object that represents the selected cells on the worksheet in the specified window (even if a graphic object is active or selected). This property applies to a Window object, not a Workbook object. The statement below, for example, displays the address of the selected range.

MsgBox ActiveWindow.RangeSelection.Address

My VBA macro needs to count the number of rows selected by the user. Using Selection.Rows.Count doesn’t work when nonadjacent rows are selected. Is this a bug?

Actually, this is the way it’s supposed to work. The Count method returns the number of elements in only the first Area of the selection (a noncontiguous selection has multiple areas). To get an accurate row count, your VBA code must first determine the number of areas in the selection and then count the number of rows in each area. Use Selection.Areas.Count to count the number of areas. Here's an example that stores the total number of selected rows in the NumRows variable:

NumRows = 0
For Each area In Selection.Areas
    NumRows = NumRows + area.Rows.Count
Next area

By the way, this is also relevant to counting columns and cells.

Is there a workbook property that will force an Excel workbook to always remain visible so it won't be hidden by another application's window?

No.

Is there a way to stop Excel from displaying messages while my macro is running? For example, I'd like to eliminate the message that appears when my macro deletes a worksheet.

The following statement turns off most of Excel’s warning messages:

Application.DisplayAlerts = False

Is there a VBA command to select the last entry in a column or row? Normally, I can use Ctrl+Shift+down arrow or Ctrl+Shift+right arrow to do this, but how can I do it with a macro?

The VBA equivalent for Ctrl+Shift+down arrow is the following:

Selection.End(XLDown).Select 

The constants used for the other directions are XLToLeft, XLToRight, and XLUp.

How can I determine the last non-empty cell in a particular column?

The statement below displays the address of the last non-empty cell in column A.

MsgBox ActiveSheet.Range("A65536").End(xlUp).Address

The statement in the previous question doesn't work if cell A65536 is not empty.

To handle that unlikely occurrence, use this code:

With ActiveSheet.Range("A65536")
  If .Value <> "" Then
      MsgBox .Address
  Else
      MsgBox .End(xlUp).Address
  End If
End With

VBA references can become very lengthy, especially when you need to fully qualify an object by referencing its sheet and workbook. Is there a way to reduce the length of these references?

Yes, create an object variable by using the Set command. Here’s an example:

Dim MyRange as Range
Set _MyRange = _
  ThisWorkbook.Worksheets("Sheet1").Range("A1")

After the Set statement is executed, you can refer to the Range object simply as MyRange. For example, you can assign a value to the range with the following:

MyRange.Value = 10

Besides making it easier to refer to objects, using object variables can also make your code execute more quickly.

Is there a way to dimension an array if you don’t know how many elements it will have?

Yes. You can dimension the array with the Dim command by using empty parentheses, and then redimension with the ReDim command when you know how many elements it has. Use ReDim Preserve if you don’t want to lose the current array contents when redimensioning it.

How can I write a macro to select some, but not all of the sheets in a workbook?

Use an argument (False) for the Select method. For example, the subroutine below selects all Chart sheets in the active workbook.

Sub SelectSheets()
    For Each sht In Sheets
        If TypeName(sht) = "Chart" Then sht.Select False
    Next sht
End Sub

Can I let the user undo my macro?

Yes, but it's not something that can be done automatically. In order to allow the user to undo the effects of a macro, your macro must keep track of what was changed by the macro, and then restore the original state if the user selects Edit - Undo.

To enable the Edit - Undo command, use the OnUndo method as the last action in your macro. This method lets you specify text that will appear on the Undo menu item, and also specify a procedure to run if the user selected Edit - Undo. Here's an example:

Application.OnUndo "The Last Macro", "MyUndoMacro"

I have a 1-2-3 macro that pauses so the user can enter data into a certain cell. How can I get the same effect in a VBA macro?

Excel can't duplicate that type of behavior, but you can use Excel's InputBox statement to get a value from a user and place it in a particular cell. The statement below, for example, displays an input box. When the user enters a value, that value is placed in cell A1.

UserVal = Application.InputBox("Value?", , , , , , , 1)
If UserVal <> False Then Range("A1") = UserVal

VBA has an InputBox function, and there's also an InputBox method for the Application object. Are these the same?

No. Excel's InputBox method is more versatile since it allows validation of the user's entry. The preceding example uses 1 (which represents a numeric value) for the last argument of the InputBox method. This ensures that the user enters a value into the input box.

I can use Excel's File - Properties command to add custom properties to a workbook. How can my VBA macro read these custom properties?

Unfortunately, there is no way to do that. Chalk it up to a deficiency of the object model.

When I use the RGB function to assign a color, the color sometimes isn't correct. What am I doing wrong?

Probably nothing. An Excel workbook can only use 56 different colors (the color palette). If a specified RGB color isn't in the palette, Excel uses the closest match it can find.

I'm trying to write a VBA statement that creates a formula. In order to do so, I need to insert a quote character within quoted text. How can I do that?

Sure. Assume you want to enter the following formula into cell B1 using VBA

=IF(A1="Yes",TRUE,FALSE)

The statement below generates a syntax error:

Range("B1").Formula = "=IF(A1="Yes",TRUE,FALSE)"

The solution is to use double quotes. The statement below produces the desired result.

Range("B1").Formula = "=IF(A1=""Yes"",TRUE,FALSE)"

Another approach is to use VBA's Chr function with an argument of 34 (which returns a quotation mark). The example below demonstrates.

Range("B1").Formula = _
  "=IF(A1=" & Chr(34) & "Yes" & Chr(34) & ",TRUE,FALSE)"

I created an array, but the first element is really the second element. What's wrong?

Unless you tell it otherwise, VBA uses 0 as the first index number for an array. If you want your arrays to start with 1, insert the following statement at the top of your VBA module:

Option Base 1

Or, you can specify the upper and lower bounds of an array when you dimension it. Here's an example:

Dim Months(1 to 12) as String

I would like my VBA code to run as quickly as possible. Any suggestions?

Yes. Here are a few general tips. Make sure that you declare all of your variables (use Option Explicit at the top of your modules to force yourself to do this). If you reference an object more than once, create an object variable. Use the With...End With construct whenever possible. Finally, if your macro writes information to a worksheet, turn off screen updating by using:

Application.ScreenUpdating = False


Developer FAQ - Add-Ins

Category: General VBA | [Item URL]

Note: This document was written for Excel 97 - 2000.

Where can I get Excel add-ins?

You can get Excel add-ins from a number of places:

  • Excel includes several add-ins that you can use if you need them.
  • Third-party developers sell add-ins for special purposes.
  • Many developers create free add-ins and distribute them via the Internet sites
  • You can create your own add-ins

How do I install an add-in?

You can load an add-in by using the Tools Add-Ins command, or by using the File Open command. Using Tools Add-Ins command is the preferred method. An add-in opened with the the File Open command cannot be closed without using VBA

When I install my add-in using Excel's Add-Ins dialog box, it shows up without a name or description. How can I give my add-in a description?

Before creating the add-in, use the File - Properties command to bring up the Properties dialog box. Click the Summary tab. In the Title box, enter the text that you want to appear in the Add-Ins dialog box. In the Comments field, enter the description for the add-in. Then create the add-in as usual.

I have several add-ins that I no longer use, yet I can't figure out how to remove them from the Add-Ins Available list in the Add-Ins dialog box. What's the story?

Oddly, there is no direct way to remove unwanted add-ins from the list directly from Excel. You must edit the Windows Registry and remove the references to the add-in files you don't want listed. Another way to do this is to move or delete the add-in files. Then, when you attempt to open the add-in from the Add Ins dialog box, Excel will ask if you want to remove the add-in from the list.

How do I create an add-in?

Activate any sheet and select File - Save As. Then select Microsoft Excel Add-in (*.xla) from the Save as type drop-down

I try to create an add-in, but the Save as type drop-down box doesn't provide Add-in as an option.

The most likely reason is because your workbook doesn't contain at least one worksheet.

Should I create an add-in from all of my workbooks?

No! Although you can create an add-in from any workbook, not all workbooks are suitable. When a workbook is converted to an add-in, it is essentially invisible. For most workbooks, being invisible isn't a good thing.

Is it necessary to keep two copies of my workbook -- the XLS version and the XLA version?

With versions prior to Excel 97, maintaining an XLS and an XLA version was necessary. Beginning with Excel 97, however, this is no longer necessary. An add-in can be converted back to a normal workbook.

How do I modify an add-in after it's been created?

Activate the VBE (Alt+F11) and set the IsAddIn property of the ThisWorkbook object to False. Make your changes and then set the IsAddIn property to True and resave the file.

What's the difference between an XLS file and an XLA file created from it? Is the XLA version compiled? Does it run faster?

There isn't a great deal of difference between the files, and you generally won't notice any speed differences. VBA code is always "compiled" before it is executed. This is true if it's in an XLS file or an XLA file. However, XLA files contain the actual VBA code, not compiled code. The main difference is that the casual user can't view XLA files.

How do I protect the code in my add-in from being viewed by others?

Activate the VBE and select Tools xxxx Properties (xxxx is the name of your project). Click the Protection tab, and select Lock project for viewing and enter a password.

Are my XLA add-ins safe? In other words, if I distribute an XLA file, can I be assured that no one else will be able to view my code?

You can protect your add-in by locking it with a password. This will prevent most users from being able to access your code. However, the password can be broken by using any of a number of utilities. Bottom line? Don't think of an XLA as being a secure file.



Page 5 of 6 pages
[Previous page]   [Next page]

Search for Tips


All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

© Copyright 2017, J-Walk & Associates, Inc.
Privacy Policy