Developer FAQ - UserForms

Category: UserForms | [Item URL]

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

I need to get just a few pieces of information and a UserForm seems like overkill. Are there any alternatives?

Yes, check out VBA's MsgBox function and its InputBox function. Alternatively, you might want to use Excel's InputBox method.

I have 12 CommandButtons on a UserForm. How can I assign a single macro to be executed when any of the buttons is clicked?

There is no easy way to do this, because each CommandButton has its own Click event procedure. One solution is to call another subroutine from each of the CommandButton_Click subroutines. Another solution is to use a class module to create a new class. Follow this link for an example.

Is there any way to display a chart in a UserForm?

There is not direct way to do this. One solution is to write that saves the chart to a GIF file, and then loads the GIF file into an Image control. Follow this link for an example.

How can I remove the "x" from the title bar of my UserForm? I don't want the user to click that button to close the form.

You can't remove the Close button on a UserForm's title bar. However, you can intercept all attempts to close the UserForm by using a UserForm_QueryClose event procedure in the code module for the UserForm. The example below does not allow the user to close the form by clicking the Close button.

Private Sub UserForm_QueryClose _
   (Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        MsgBox "You can't close the form like that."
        Cancel = True
    End If
End Sub

I've created a UserForm, and the controls are linked to cells on the worksheet. Is this the best way to do this?

In general, you should avoid using links to worksheet cells unless you absolutely must. Doing so can slow your application down, because the worksheet is recalculated every time a control changes the cell.

Is there any way to create a control array for a UserForm? It's possible with Visual Basic, but I can't figure out how to do it with Excel.

You can't create a control array, but you can create an array of Control objects. The code below creates an array consisting of all CommandButton controls.

Private Sub UserForm_Initialize()
    Dim Buttons() As CommandButton
    Cnt = 0
    For Each Ctl In UserForm1.Controls
        If TypeName(Ctl) = "CommandButton" Then
            Cnt = Cnt + 1
            ReDim Preserve Buttons(1 To Cnt)
            Set Buttons(Cnt) = Ctl
        End If
    Next Ctl
End Sub

Is there any difference between hiding a UserForm and unloading a UserForm?

Yes, the Hide method keeps the UserForm in memory, but makes it invisible. The Unload statement unloads the UserForm.

How can I make my UserForm stay open while I do other things?

Excel 97 UserForms are modal -- which means that the form must be dismissed before you can do anything else. Excel 2000, however, supports modeless UserForms.

I need to display a progress indicator (like those used during software installation) while a lengthy process is being executed. How can I do this?

You can do this with a UserForm. Follow this link for an example.

How can I get a list of files and directories into my UserForm so the user can select a file from the list?

There's no need to do that. Use VBA's GetOpenFileName statement. This displays a "file open" dialog box in which the user can select a drive, directory, and file.

I have several 1-2-3 for Windows files and Quattro Pro for Windows files that contain custom dialog boxes. Is there a utility to convert these to Excel dialog boxes?

No.

I need to concatenate strings and display them in a ListBox control. But when I do so, they aren't aligned properly. How can I get them to display equal spacing between strings?

You can use a monospaced font (such as Courier New) for the ListBox. A better approach, however, is to set up your ListBox to use two columns.

Is it possible to display a built-in Excel dialog box from VBA?

Most (but not all) of Excel's dialog boxes can be displayed by using the Application.Dialogs method. For example, the following statement displays the dialog box that lets you format numbers in cells:

Application.Dialogs(xlDialogFormatNumber).Show 

Use the Object Browser to display a list of the constants for the built-in dialog boxes. Press F2 from the VBE, then select the Excel library and then the Constants object. The Method/Properties list will display the constants for the built-in dialog boxes (they all begin with xlDialog).

I tried the technique in the preceding question and received an error message. Why is that?

The Dialogs method will fail if the context isn't appropriate. For example, if you attempt to display the Chart Type dialog box (xlDialogChartType) when a chart is not activated, you'll get an error message.

Every time I create a UserForm, I go through the steps of adding an OK button and a Cancel button. Is there a way to get these controls to appear automatically?

Yes, create a UserForm set up with the controls you use most often. Then select File - Export File to save the UserForm. When you want to add a new form to another project, select File - Import File.

Is it possible to create a UserForm without a title bar?

No. The closest you can get is to make the dialog box's caption blank by setting the Caption property to an empty string.

I recorded some VBA code to print to a file. However, there seems to be no way to supply the filename in my code. No matter what I try, I keep getting the prompt to supply a filename.

This was a common problem that was corrected in Excel 2000. In Excel 2000, you can provide a PrToFileName argument for the PrintOut method. Here's an example:

ActiveSheet.PrintOut _
  PrintToFile:=True, PrToFileName:="test.prn"

When I click a button on my UserForm nothing happens. What am I doing wrong?

Controls added to a UserForm do nothing unless you write event-handler procedures for them.

I wrote a subroutine named Workbook_Open, but it doesn's get executed when the workbook is opened.

The most likely cause is that your subroutine is located in a normal VBA module. Workbook event procedures must be located in the code module for the ThisWorkbook object.

Can I create a custom dialog box that displays in the same size, regardless of the video display resolution?

You can, but it's probably not worth the effort. You can write code to determine the video resolution, and then make use of the Zoom property of a UserForm to change its size. The normal way to deal with this sort of thing is to simply design your UserForm for a 640x480 display.

Is it possible to create a UserForm box that lets the user select a range in a worksheet by pointing?

Yes. Use the RefEdit control for this.

Is there a way to change the start-up position of a UserForm?

Yes, you can set the UserForm's Left and Top properties. But in order for these to be effective you need to set the StartUpPosition property to 0.

Can I add an Excel 5/95 dialogsheet to my workbook?

Right-click any sheet tab in a workbook and select Insert from the shortcut menu. In the Insert dialog box, select MS Excel 5.0 Dialog.



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.



Developer FAQ - CommandBars

Category: CommandBars & Menus | [Item URL]

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

I have a macro attached to a toolbar button. Is it possible to have the macro perform a different action if the user presses Shift while the button is clicked?

Yes, but you have to use a Windows API call to detect if the Shift key is pressed.

Excel 95 had a handy menu editor, but it's missing in Excel 97 and Excel 2000. What gives?

Beginning with Excel 97, the toolbars and menus in Excel are entirely different. Both are called CommandBars. The menu editor is gone, but users can edit CommandBars using the Customize dialog box (select Tools- Customize).

Is there an easy way to create a new menu on the fly?

Yes. Follow this link.

When I change a menu using the Customize dialog box, the menu is changed permanently. How can I make the menu change apply only to one workbook?

You'll need to perform you menu changes (using VBA code) when the workbook is opened, and restore the menu to normal when the workbook is closed.

I know you can use the FaceId property to add an image to a toolbar control. But how do I figure out which FaceID value goes with a particular image?

Microsoft didn't provide any way to do this, but several utilities exist that make it easy to identify the FaceID values. Follow this link to download such a utility.

I attached a new version of my toolbar to a workbook, but Excel continues to use the older version. How do I get it to use the new version of my toolbar?

When Excel opens a workbook that has an attached toolbar, it displays the toolbar only if one with the same name does not already exist on the user's system. The best solution is to write VBA code to create the toolbar on the fly when the workbook is opened, and delete it when the workbook is closed.

I've made lots of changes to Excel's toolbars. How can I restore all of these toolbars to their original state?

You can use the Customize dialog box and reset each one manually. Or, run the subroutine listed below.

Sub ResetAllToolbars()
    For Each tb In CommandBars
        If tb.Type = msoBarTypeNormal Then
            If tb.BuiltIn Then tb.Reset
        End If
    Next tb
End Sub

How can I set things up so my custom menu is displayed only when a particular workbook is active?

You need to make use of the WorkbookActivate and WorkbookDeactivate events. In other words, write subroutines (stored in the code module for the ThisWorkbook object) that hide the custom menu when the workbook is deactivated, and unhide the custom menu when the workbook is activated.

How can I add a "spacer" between two buttons on a toolbar?

Set the BeginGroup property of the control to True.

How do you make a menu item have a checkmark next to it?

A checkmark on a menu item is controled by the State property. The statement below, for example, displays a checkmark next to

CommandBars(1).Commands("MyMenu"). _
    Commands("My Item").State = msoButtonDown

To uncheck the menu item, set the State property to msoButtonUp

I accidentally deleted some items from the Worksheet menu and can't get them back. Restarting Excel doesn't fix it.

Select Tools - Customize and select the Toolbars tab in the Customize dialog box. Select the Worksheet Menu Bar item and click the Reset button.

How can I disable all of the right-click shortcut menus?

The subroutine below will do the job.

Sub DisableAllShortcutMenus()
    Dim cb As CommandBar
    For Each cb In CommandBars
        If cb.Type = msoBarTypePopup Then cb.Enabled = False
    Next cb
End Sub

Is there a way to disable the shortcut menus that appear when the user clicks the right mouse button?

Yes, the statement below will do the job:

CommandBars("Toolbar List").Enabled = False

The statement listed in the previous question doesn't work!

The original version of Excel 97 had a problem with this statement. It was corrected in the SR-1 service release for Excel 97



Animated Hypocycloid Charts

Category: Charts & Graphics / General VBA | [Item URL]

A companion file is available: Click here to download

With a few simple macros, you can creating some interesting animation effects in charts. For example, I created a workbook that displays an infinite number of dazzling animated charts. The workbook is set up with a simple user interface that makes it very easy to generate a new chart or save the parameters for charts that you like. Turn animation on or off with the click of a button.

The chart displays what's known as a hypocycloid curve -- the path formed by a point on a circle that rolls inside of another circle. This, as you may recall from your childhood, is the same technique used in Hasbro's popular Spirograph toy.

The workbook is not protected, so you can view the formulas and VBA macros that are used.

hypo1.gif (7866 bytes)
 



Spreadsheet Protection FAQ

Category: General / General VBA | [Item URL]

The topic of "protecting" a spreadsheet receives a great deal of attention in the Excel newsgroups. This document provides answers to some common questions.

Excel provides three primary ways to protect information in a spreadsheet:

NOTE: This document was written prior to the release of Excel 2007.

Worksheet Protection

Questions in this section deal with protecting the contents of cells and objects on a worksheet.

How do I protect a worksheet?

Activate the worksheet to be protected, then choose Tools - Protection - Protect Sheet. You will be asked to provide a password (optional). If you do provide a password, that password will be required to unprotect the worksheet.

I tried the procedure outlined above, and it doesn't let me change any cells! I only want to protect some of the cells, not all of them.

Every cell has two key attributes: Locked and Hidden. By default, all cells are locked, but they are not hidden. Furthermore, the Locked and Hidden attributes come into play only when the worksheet is protected. In order to allow a particular cell to be changed when the worksheet is protected, you must unlock that cell.

How do I unlock a cell?

  1. Select the cell or cells that you want to unlock.
  2. Choose Format - Cells
  3. In the Format Cells dialog box, click the Protection tab
  4. Remove the checkmark from the Locked checkbox.

Remember: Locking or unlocking cells has no effect unless the worksheet is protected.

How do I hide a cell?

  1. Select the cell or cells that you want to unlock.
  2. Choose Format - Cells
  3. In the Format Cells dialog box, click the Protection tab
  4. Add a checkmark to the Hidden checkbox.

Remember: Changing the Hidden attribute of a cell has no effect unless the worksheet is protected.

I made some cells hidden and then protected the worksheet. But I can still see them. What's wrong?

When a cell's Hidden attribute is set, the cell is still visible. However, it's contents do not appear in the Formula bar. Making a cell Hidden is usually done for cells that contain formulas. When a formula cell is Hidden and the worksheet is protected, the user cannot view the formula.

I protected my worksheet, but now I can't even do simple things like sorting a range. What's wrong?

Nothing is wrong. That's the way worksheet protection works. Unless you use Excel 2002 or later.

How is worksheet protection different in Excel 2002 and later?

Excel 2002 and later provides you with a great deal more flexibility when protecting worksheets. When you protect a worksheet using Excel 2002 or later, you are given a number of options that let you specify what the user can do when the worksheet is protected:

  • Select locked cells
  • Delete columns
  • Select unlocked cells
  • Delete rows
  • Format cells
  • Sort
  • Format columns
  • Use AutoFilter
  • Format rows
  • Use PivotTable reports
  • Insert columns
  • Edit objects
  • Insert rows
  • Edit scenarios
  • Insert hyperlinks

Why aren't these options available in earlier versions of Excel?

Good question. Only Microsoft knows for sure. The limitations of protected worksheets have been known (and complained about) for a long time. For some reason, Microsoft never got around to addressing this problem until Excel 2002.

Can I lock cells such that only specific users can modify them?

Yes, but it requires Excel 2002 or later.

How can I find out more about the protection options available in Excel 2002 or later?

Start with Excel's Help system. If you're a VBA programmer, you may be interested in this MSDN article that discusses the Protection object.

Can I set things up so my VBA macro can make changes to Locked cells on a protected sheet?

Yes, you can write a macro that protects the worksheet, but still allows changes via macro code. The trick is to protect the sheet with the UserInterfaceOnly parameter. Here's an example:

ActiveSheet.Protect UserInterfaceOnly:=True

After this statement is executed, the worksheet is protected -- but your VBA code will still be able to make changes to locked cells and perform other operation that are not possible on a protected worksheet.

If I protect my worksheet with a password, is it really secure?

No. Don't confuse protection with security. Worksheet protection is not a security feature. Fact is, Excel uses a very simple encryption system for worksheet protection. When you protect a worksheet with a password, that password -- as well as many others -- can be used to unprotect the worksheet. Consequently, it's very easy to "break" a password-protected worksheet.

Worksheet protection is not really intended to prevent people from accessing data in a worksheet. If someone really wants to get your data, they can. If you really need to keep your data secure, Excel is not the best platform to use.

So are you saying that protecting a worksheet is pointless?

Not at all. Protecting a worksheet is useful for preventing accidental erasure of formulas. A common example is a template that contains input cells and formulas that calculate a result. Typically, the formula cells would be Locked (and maybe Hidden) the input cells would be Unlocked, and the worksheet would be protected. This helps ensure that a novice user will not accidentally delete a formula.

Are there any other reasons to protect a worksheet?

Protecting a worksheet can also facilitate data entry. When a worksheet is locked, you can use the Tab key to move among the Unlocked cells. Pressing Tab moves to the next Unlocked cell. Locked cells are skipped over.

OK, I protected my worksheet with a password. Now I can't remember the password I used.

First, keep in mind that password are case-sensitive. If you entered the password as xyzzy, it won't be unprotected if you enter XYZZY.

Here's a link to a VBA procedure that may be able to derive a password to unprotect the worksheet. This procedure has been around for a long time, and is widely available -- so I don't have any qualms about reproducing it here. The original author is not known.

If that fails, you can try one of the commercial password-breaking programs. I haven't tried any of them, so I have no recommendations.

How can I hide a worksheet so it can't be unhidden?

You can designate a sheet as "very hidden." This will keep the average user from viewing the sheet. To make a sheet very hidden, use a VBA statement such as:

Sheets("Sheet1").Visible = xlVeryHidden

A "very hidden" sheet will not appear in the list of hidden sheets, which appears when the user selects Format - Sheet - Unhide. Unhiding this sheet, however, is a trivial task for anyone who knows VBA.

Can I prevent someone from copying the cells in my worksheet and pasting them to a new worksheet?

Probably not. If someone really wants to copy data from your worksheet, they can find a way.

Workbook Protection

Questions in this section deal with protecting workbooks.

What types of workbook protection are available?

Excel provides three ways to protect a workbook:

  • Require a password to open the workbook
  • Prevent users from adding sheets, deleting sheets, hiding sheets, and unhiding sheets
  • Prevent users from changing the size or position of windows

How can I save a workbook so a password is required to open it?

Choose File - Save As. In the Save As dialog box, click the Tools button and choose General Options to display the Save Options dialog box, in which you can specify a password to open the file. If you're using Excel 2002, you can click the Advanced button to specify encryption options (for additional security). Note: The exact procedure varies slightly if you're using an older version of Excel. Consult Excel's Help for more information.

The Save Options dialog box (described above) also has a "Password to modify" field. What's that for?

If you enter a password in this field, the user must enter the password in order to overwrite the file after making changes to it. If the password is not provided, the user can save the file, but he/she must provide a different file name.

If I require a password to open my workbook, is it secure?

It depends on the version of Excel. Password-cracking products exist. These products typically work very well with versions prior to Excel 97. But for Excel 97 and later, they typically rely on "brute force" methods. Therefore, you can improve the security of your file by using a long string of random characters as your password.

How can I prevent a user for adding or deleting sheets?

You need to protect the workbook's structure. Select Tools - Protection - Protect Workbook. In the Protect Workbook dialog box, make sure that the Structure checkbox is checked. If you specify a password, that password will be required to unprotect the workbook.

When a workbook's structure is protected, the user may not:

  • Add a sheet
  • Delete a sheet
  • Hide a sheet
  • Unhide a sheet
  • Rename a sheet
  • Move a sheet

How can I distribute a workbook such that it can't be copied?

You can't.

VB Project Protection

How can I prevent others from viewing or changing my VBA code?

If you use Excel 97 or later... Activate the VB Editor and select your project in the Projects window. Then choose Tools - xxxx Properties (where xxxx corresponds to your Project name). In the Project Properties dialog box, click the Protection tab. Place a checkmark next to Lock project for viewing, and enter a password (twice). Click OK, then save your file. When the file is closed and then re-opened, a password will be required to view or modify the code.

Is my add-in secure?

The type of VB Project protection used in Excel 97 and later is much more secure than in previous versions. However, several commercial password-cracking programs are available. These products seem to use "brute force" methods that rely on dictionaries of common passwords. Therefore, you can improve the security of your file by using a long string of random characters as your password.

Can I write VBA code to protect or unprotect my VB Project?

No. The VBE object model has no provisions for this -- presumably an attempt to thwart password-cracking programs. It may be possible to use the SendKeys statement, but it's not completely reliable.



Page 16 of 17 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 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

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

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