Im 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:

Operator Function


Division with an integer result

Returns True if both expressions are true or both are false

Logical implication on two expressions

Compares two object variables

Compares two strings by using wildcard characters

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 Macrol located in the Personal.xls workbook:

Run "Personal.xls!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.

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 XLA 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 that 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 File ^ 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 Sheetl in a workbook named myfile.xls in the c:\files directory:

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

Note that the cell address must be in RC 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.

Was this article helpful?

0 0

Post a comment