UEU-co logo

Objects, Properties, Methods, and Events


Previous Page

Next Page

Objects, Properties, Methods, and Events

Is there a listing of the Excel objects I can use?

Yes. The Help system has that information.

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 use the Object Browser available in the VBE. Press F2 to access the Object Browser and then choose Excel from the Libraries/Workbooks drop-down list. The Classes list (on the left) shows all the Excel objects. When you select an object, its corresponding properties and methods appear in the Member Of list on the right.

You can also get a list of properties and methods as you type. For example, enter the following:

Range(“A1”).

When you type the dot, you’ll see a list of all properties and methods for a Range object. If the list doesn’t appear, choose Tools Image from book Options (in the VBE), click the Editor tab, and place a check mark next to Auto List Members. Unfortunately, Auto List Members doesn’t work for all objects. For example, you won’t see a list of properties and methods when you type this statement:

ActiveSheet.Shapes(1).

And, of course, the Help system for VBA is very extensive; it lists the properties and methods available for most objects of importance. The easiest way to access these lists is to type the object name into the Immediate window at the bottom of the VBE and move the cursor anywhere within the object name. Then 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, which is an object that contains a group of related objects, 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) 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 occurs when you attempt to access an element in a collection that doesn’t exist. For example, the following instruction generates the error if the active workbook does not contain a worksheet named MySheet:

Set × = ActiveWorkbook.Worksheets(“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 instruction to set the scroll area for the worksheet. The following instruction, for example, sets the scroll area on Sheet1 so that the user cannot activate any cells outside of B2:D50:

Worksheets(“Sheet1”).ScrollArea = “B2:D50”

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

Worksheets(“Sheet1”).ScrollArea = “”

Keep in mind that the ScrollArea setting is not saved with the workbook. Therefore, you need to execute the ScrollArea assignment instruction whenever the workbook is opened. This instruction can go in the Workbook_Open event handler procedure.

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

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

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

In some cases, 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 following statement activates cell C3. The original range remains selected, but C3 becomes the active cell – that is, the cell that contains the cell pointer.

Range(“C3”).Activate

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

Range(“C3”).Select

Is there a quick way to delete all values from a worksheet yet keep the formulas intact?

Yes. The following code works on the active sheet and deletes all nonformula cells. (The cell formatting is not affected.)

On Error Resume Next Cells.SpecialCells(xlCellTypeConstants, 23).ClearContents

The second argument, 23, is the sum of the values of the following built-in constants: xlErrors (16), xlLogical (4), xlNumbers (1), and xlTextValues (2).

Using On Error Resume Next prevents the error message that occurs if no cells qualify.

I know how to write a VBA instruction to select a range by using a cell address, but how can I write one to select a range if I know only its row and column number?

Use the Cells method. The following instruction, for example, selects the cell in the 5th row and the 12th column (that is, cell L5):

Cells(5, 12).Select

When I try to record the Office Image from book Exit Excel command, Excel closes down before I can see what code it generates. Is there a VBA command to quit Excel?

Use the following instruction to end Excel:

Application.Quit

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

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

Application.ScreenUpdating = False

When your procedure ends, the ScreenUpdating property is set back to True. However, you can resume screen updating at any time by executing this statement:

Application.ScreenUpdating = False

What’s the easiest way to create a range name in VBA?

If you turn on the macro recorder while you name a range, you get code something like this:

Range(“D14:G20″).Select ActiveWorkbook.Names.Add Name:=”InputArea”, _ RefersToR1C1:=”=Sheet1!R14C4:R20C7″

A much simpler method is to use a statement like this:

Sheets(“Sheet1”).Range(“D14:G20”).Name = “InputArea”

How can I determine whether a particular cell or range has a name?

You need to check the Name property of the Name object contained in the Range object. The following function accepts a range as an argument and returns the name of the range (if it has one). If the range has no name, the function returns False.

Function RangeName(rng) As Variant On Error Resume Next RangeName = rng.Name.Name If Err <> 0 Then RangeName = False End Function

Can I disable the Setup and Margins buttons that are displayed in Excel’s Print Preview window?

Yes, use a statement like this:

ActiveSheet.PrintPreview EnableChanges:=False

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

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

Application.StatusBar = “Now processing File ” & FileNum

Before your routine finishes, return the status bar back to normal with the following instruction:

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 can 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 instruction, such as the following:

Range(“A1”).Copy Range(“B1”)

Notice that this instruction 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 procedure. Many sorting algorithms are available, and some are quite easy to code in VBA. This book 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 object. Here’s an example:

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

Another approach 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 following instruction, for example, displays the address of the selected range:

MsgBox ActiveWindow.RangeSelection.Address

How can I determine if a chart is activated?

Use a statement like this:

If ActiveChart Is Nothing Then MsgBox “Select a chart”

The message box will be displayed only if a chart is not activated. (This includes embedded charts and charts on a chart sheet.)

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 process is also relevant to counting columns and cells.

I use Excel to create invoices. Can I generate a unique invoice number?

One way to do this is to use the Windows Registry. The following code demonstrates:

Counter = GetSetting(“XYZ Corp”, “InvoiceNum”, “Count”, 0) Counter = Counter + 1 SaveSetting “XYZ Corp”, “InvoiceNum”, “Count”, Counter

When these statements are executed, the current value is retrieved from the Registry, incremented by one, and assigned to the Counter variable. Then this updated value is stored back to the Registry. You can use the value of Counter as your unique invoice number.

You can adapt this technique for other purposes. For example, you can keep track of the number of times a workbook has been opened by including similar code in a Workbook_Open procedure.

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

No.

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

The VBA equivalent for Ctrl+Shift+ ↓ 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 following instruction displays the address of the last non-empty cell in column A:

MsgBox ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Address

But that instruction won’t work if the last cell in the column is not empty. To handle that unlikely occurrence, use this code:

With ActiveSheet.Cells(Rows.Count, 1) If IsEmpty(.Value) Then MsgBox .End(xlUp).Address Else MsgBox .Address End If End With

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

Yes. Use the Set statement to create an object variable. 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 this single-cell Range object simply as MyRange. For example, you can assign a value to the cell with the following:

MyRange.Value = 10

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

Can I declare an array if I don’t know how many elements it will have?

Yes. You can declare a dynamic array with the Dim statement by using empty parentheses; then allocate storage for that array later with the ReDim statement when you know how many elements the array should have. Use ReDim Preserve if you don’t want to lose the current array contents when reallocating it.

Can I let the user undo my macro?

In most cases, yes – but undoing a macro is not something that can be done automatically. To enable the user to undo the effects of your macro, your VBA code module must keep track of what was changed by the macro and then be capable of restoring the original state if the user chooses Undo.

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

Application.OnUndo “The Last Macro”, “MyUndoMacro”

Can I pause a macro so the user can enter data into a certain cell?

You can use Excel’s InputBox statement to get a value from a user and place it in a particular cell. The first instruction that follows, for example, displays an input box. When the user enters a value, that value is placed in cell A1.

UserVal = Application.InputBox(prompt:=”Value?”, Type:=1) If TypeName(UserVal)<>”Boolean” Then Range(“A1”) = UserVal

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

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

I’m trying to write a VBA instruction that creates a formula. To do so, I need to insert a quote character (“) within quoted text. How can I do that?

Assume that you want to enter the following formula into cell B1 with VBA:

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

The following instruction generates a syntax error because of the embedded quote characters:

Range(“B1”).Formula = “=IF(A1=”Yes”,TRUE,FALSE)” ‘erroneous

The solution is to use two double quotes side by side. When two quotes are embedded within another set of quotes, Excel interprets the double quote characters as a single quote. The following instruction 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 following example demonstrates:

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

Yet another technique is to compose your formula using apostrophes in place of the quote marks. Then use VBA’s Replace function to replace the apostrophes with quote characters:

TheFormula = “=IF(A1=’Yes’,TRUE,FALSE)” Range(“B1”).Formula = Replace(TheFormula, “‘”, Chr(34))

I created an array, but the first element in that array is being treated as 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 all your arrays to always 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 declare 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?

Here are a few general tips:

  • Make sure that you declare all your variables. Use Option Explicit at the top of your modules to force yourself to do this.

  • If you reference an Excel object more than once, create an object variable for it.

  • Use the With-End With construct whenever possible.

  • If your macro writes information to a worksheet, turn off screen updating by using Application.ScreenUpdating = False.

  • If your application enters data into cells that are referenced by one or more formulas, set the calculation mode to manual to avoid unnecessary calculations.

Previous Page

Next Page

Leave a Reply


Time limit is exhausted. Please reload the CAPTCHA.

Categories

apply_now Pepperstone Group Limited