Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

2017-07-07 02:10:07

What's the difference between a VBA procedure 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 is a grouping of VBA instructions that can be called by name . If these instructions are to give an explicit result (such as a value) back to the instruction that called them, they most likely belong to a Function procedure. Otherwise, they probably belong to a Sub procedure.

What is a variant data type?

Variables that aren't specifically declared are assigned the Variant type by default, and VBA automatically converts the data to the proper type when it's used. This is particularly useful for retrieving 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 with the Dim , Public , or Private statement because using variants is a bit slower and is not the most efficient use of memory.

What's the difference between a variant array and an array of variants?

A variant is a unit of memory with 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 following code creates a variant that contains a three-element array:

Dim As Variant = Array(30, 40, 50)

A normal array can contain items of a specified data type, including nontyped variants. The following statement creates an array that consists of three variants:

Dim (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%

VBA supports these type-declaration characters :

Integer: %

Long: &

Single: !

Double: #

Currency: @

String: $

Type-definition characters are included primarily for compatibility. Declaring variables by using words is the standard approach.

I would like to create a procedure that automatically changes the formatting of a cell based on the data that 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 Home Styles Conditional Formatting command.

The Conditional Formatting feature 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 Help system 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 opened. What's wrong?

You probably put the procedure in the wrong place. Workbook event procedures must be in the code module for the ThisWorkbook object. Worksheet event procedures must be in the code module for the appropriate Sheet object, as shown in the VBE Project window.

I can write an event procedure for a particular workbook, but can I 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 19.

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 listed in the following table:

Open table as spreadsheet

Operator	Function
`\`	Division with an integer result
`Eqv`	Returns `True` if both expressions are true or both are false
`Imp`	A bitwise logical implication on two expressions (rarely used)
`Is`	Compares two object variables
`Like`	Compares two strings by using wildcard characters
`Xor`	Returns `True` if only one expression is true

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

Use the Run method of the Application object. The following instruction executes a procedure named Macro1 located in the Personal.xlsb workbook:

Run "Personal.xlsb!Macro1"

Another option is to add a reference to the workbook. Do this by choosing the Tools References command in the VBE. After you've added a reference, you can then run the procedures in the referenced workbook without including the name of the workbook.

I've used VBA to create several custom functions. 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 XLAM add-in. When the add-in is open, you can use the functions in any other worksheet without referencing the function's filename.

In addition, 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, choose 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 workbook's ThisWorkbook object.

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

Yes. Hold down Shift when you issue the Office Open command. To prevent a Workbook_ BeforeClose procedure from executing, press Shift when you close the workbook. Using the Shift key will not prevent these procedures from executing when you're opening an add-in.

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

VBA can't do it, but Excel's old XLM language can. Fortunately, you can execute XLM from VBA. Here's a simple example that retrieves the value from cell A1 on Sheet1 in a workbook named myfile.xlsx in the c:\files directory:

MsgBox ExecuteExcel4Macro("'c:\files\[myfile.xlsx]Sheet1'!R1C1")

Note that the cell address must be in R1C1 notation.

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

You can use this statement:

ActiveWorkbook.Close SaveChanges:=False

Or, you can set the workbook's Saved property to True by using a statement like this:

ActiveWorkbook.Saved = True

This statement, when executed, does not actually save the file, so any unsaved changes will be lost when the workbook is closed.

A more general solution to avoid Excel prompts is to insert the following instruction:

Application.DisplayAlerts = False

Normally, you'll want to set the DisplayAlerts property back to True after the file is closed.

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

You need to use the OnTime method of the Application object. This enables you to specify a procedure to execute at a particular time of day. When the procedure 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 procedure by using the Private keyword:

Private Sub MyMacro()

Or you can add a dummy optional argument, declared as a specific data type:

Sub MyMacro (Optional FakeArg as Long)

Can I save a chart as a GIF file?

Yes. The following code 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, 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.