Volatile functions are a type of function that will always recalculate.
That means whenever Excel needs to calculate any part of the workshet, those cells containing volatile functions will also calculate.

There are a limited set of volatile functions – these are:
RAND, NOW, TODAY, OFFSET, CELL, INDIRECT

To test their behaviour, I’ve created a User Defined Function which returns the current time.

My worksheet contains formulas in column A and the formula description (escaped with a single quote) in column B

Notice which cells in column A get updated after I write something in cell B11
Only the cells containing volatile functions were recalculated.

I’ve seen very big worksheets where all of the cells refer to cell A1 – the formula being =NOW()
Users then wonder why there are noticable delays while performing data entry! This can be even more noticable when the dependent cells are XLL functions querying external data sources – requerying each time the user enters some more data.
In this case you could either manually enter the current time (or by a macro button) to save from volatile hell
-or-
ensure Excel has Calculation turned off – Tools > Options > Calculation > Manual

Calculation mode is saved with the workbook.

Be aware, Excel has a rule that it follows with regard to the workbook calculation mode:
If the workbook is the first workbook opened in that application, calculation mode is set to that saved with the workbook, otherwise it is ignored.

User Defined Functions can also be made Volatile by including the line: Application.Volatile
I read somewhere that it must be the first line of your User Defined Function. I don’t know if that’s true, but the first line is nice and visible so it’s a good idea.

Created by Bob Umlas and David Hager

To define a global range name in Excel, go to Insert, Name, Define and, as an example, in the Names in Workbook box type “cellA1? and in the RefersTo box type this formula: =OFFSET(!$A$1,,,,).

Now, type =cellA1 in a cell on any worksheet in the workbook and it will return the value in A1 for that worksheet.

Editors note: And all this time I’ve been setting up PrevCell for each sheet in the workbook. What a chump I am.

Based on that, it appears that you don’t need the OFFSET function. You can define the name cellA1 as =!$A$1

Here’s a nice article Comments are More Important than Code by Jef Raskin on documenting your code.

The thorough use of internal documentation is one of the most-overlooked ways of improving software quality and speeding implementation.

A (great!) review of Professional Excel Development was posted on Slashdot yesterday and has triggered a very interesting discussion about the pros and cons of Excel development, and why people would create applications using Excel. Take a look.

I’ve been interviewing recently and have been subjecting the candidates to a rather nice VBA question. I thought the regulars here might get a kick out of thinking up the ‘perfect’ answer for one or more of the scenarios. It involves a Sub Main calling a Function which takes a single parameter:

Scenario a): the parameter is a Long integer and is passed using the ByRef keyword. The Function changes the parameter variable’s value. What is the purpose of the ByRef keyword in this context?

Scenario b): the parameter is a String and is passed using the ByRef keyword. The Function makes no change to the parameter variable’s value. What is the purpose of the ByRef keyword in this context?

Scenario c): the parameter is an ADO Recordset object and is passed using the ByVal keyword. The Function makes changes to the Recordset’s Filter and Sort properties. What is the purpose of the ByVal keyword in this context?

What do you think?

Hi, I’m the dot operator. You may remember me from such VBA statements as object.property and object.method.

Okay, enough of that. When you see a dot operator in a Dim statement, you’re seeing it in the form of library.object. For instance, if you’re automating Outlook, you may use:

Outlook isn’t an object, it’s the name of the library to which you are early binding. MailItem is the name of an object in that library. Another common one is an ADO Recordset:

You don’t need to call out the library for your program to compile, but you may need to for it to run properly. The problem that you may run into is when you dimension a variable as an object that exists in two different libraries. Each library has a priority and if you specify an object from multiple libraries, the object from the library with the highest priority will be used. That’s not a problem if the library you intend to use has the highest priority, but that’s not something you can depend on.

Go to the VBE and Tools > References and you’ll see a Priority label with up and down buttons. You can change the priority of the libraries that are selected. On my wife’s Office 2003 installation, the default libraries in order of priority are:

• VBA
• Excel
• OLE Automation
• Office

If I exit the References dialog and open the Object Browser (F2), I also see something called stdole. I have no idea what this is.

In general, I specify the library if it’s not a default library. If you were to examine my code, you would probably find exception after exception to this rule. I should have written that I try to specify non-default libraries. I almost never specify the MSForms library and it’s not default. MSForms gets added when you add a userform. It contains a Textbox object and there’s also a Textbox object in the Excel object library (from the Drawing toolbar, I believe). That’s one that I really should specify.

Jamie recently metioned in a newsgroup post that he sometimes includes the Excel library in his Dim statements. In Excel VBA, this isn’t necessary because Excel is always the highest priority library (unless the user changes it). However, if you want to port your code to VB6 or some other VBA, you will save yourself time by including the Excel library. Unless I specifically intend to port the code, I leave it off. But I think it’s a good point, and it really costs nothing to specify the Excel library. If nothing else, I’d learn what was in the Excel library vs. the VBA library.

EEE#21: Excel User Defined Functions That Incorporate MapPoint Functionality

By David Hager

–Note: Excel 2003 and MapPoint 2004 were used in the development of this technique and its use with other versions has not been tested.–

The integration that Microsoft provides between Excel and MapPoint for importing and exporting data provides a powerful method for visual data analysis. As it turns out, there is another way to obtain information from MapPoint to Excel that is not as well-known.

User-defined functions (UDF) are used in Excel worksheets is the same way that native Excel functions are used. The VBA code for an Excel UDF is written in a standard module in the Excel VBE and called by entering the function in a formula in a worksheet cell. In order to incorporate MapPoint automation in an Excel UDF, open the Visual Basic Environment (VBE) by using the Alt-F11 keys. Then, under Tools|References check the Microsoft MapPoint 11.0 Object Library (North America).

A simple example of an Excel UDF that incorporates MapPoint functionality is shown below.

It is quite rare to find an example where the VBA code for an Excel UDF successfully instantiates an application. In this case, an instance of MapPoint is created with the statement

that allows the Excel UDF access to a valuable source of location information.

The FindResults method works in the same way that the Place/Data tab in the dialog box opened from Edit|Find of the MapPoint main menu does. The Item(1) property returns the best choice based on the available information. As an example, type the following in an Excel worksheet.

A1: Beaumont, Tx
A2: Houston, Tx
A3: =StraightLineDist(A1, A2)

The result returned in cell A3 is 77.11092, which is the number of miles as the crow flies between Beaumont and Houston Texas as calculated by the DistanceTo method.

In order to improve performance of the StraightLineDist function, the statement

can be used in the code to limit function calls to only those times when the input data is changed. It might be also advantageous to set the MapPoint objects to nothing in order to remove them from memory.

The next example returns the distance for a multiple point route and contains an element that is perhaps unique among UDF s.

The iMPType variable is the type of route to be returned:

0 = Travel using the quickest route
1 = Travel using the shortest route
2 = Travel on preferred roads as set in the Preferred Roads dialog box

The ParamArray WPoints() statement allows for an arbitrary number of waypoint function arguments along the route.

By entering the following formula, the distance for the quickest round trip route from Houston is returned (882.89018 miles).

=MPRouteDist(0,A1, A2,A3,A4,A1)

where:

A1: Houston, Tx
A2: Dallas, Tx
A3: San Antonio, Tx
A4: Corpus Christi, Tx

This function actually makes changes on the active MapPoint map (not visible) and a prompt appears asking to save those changes unless the statement

objMap.Saved = True

is included in the code. This writer does not know of another example where an Excel UDF modifies another application. This unusual behavior serendipitously provides a simple way to generate route maps from Excel. If the following formula is entered without the objMap.Saved = True statement in the code, the route map from Houston to Dallas is generated and can be saved. Thus, generation of route maps from MapPoint is now as easy as entering an address in a worksheet cell.

=MPRouteDist(0,A1, A2)

In summary, Excel UDFs provide easy access to the rich store of information in MapPoint and these examples just scratch the surface of what is possible. For example, I have made Excel UDFs that draw geometric shapes around MapPoint locations. This and other challenges are left to the reader.

David Hagar, former Excel MVP, used to distribute an electronic letter called the Excel Experts E-Letter or EEE. Until recently, the last EEE was published in 2001. David recently wrote EEE#21 and it sparked a conversation between JWalk and me.

JWalk currently stores the archives of the EEEs at the Spreadsheet Page. I was trying convince him to give me all the Spreadsheet Page content and used the EEE as an example. It’s great content that isn’t getting much exposure and could be repackaged and republished to a whole new audience. So JWalk sent an email to David on my behalf and I got permission to republish them.

I’ll be starting with #21 later today. Look for more in the coming weeks. And thanks to David and JWalk for making this material available.