Daily Dose of Excel

I regret to say there will be no “Daily Dose” today. That darn paying job I have is getting in the way. Check back tomorrow for more tips.

Excel’s sort feature allows you to sort on up to three different columns. If you need to sort on more than three, you can sort the same range multiple times. This takes advantage of the fact that if two values are equal, Excel will keep them in their original order. For this to work properly, you must sort in reverse order. For instance, if you’re sorting on five columns, first sort on columns 3,4,5, then sort on 1,2.

In this example, the data is sorted on Company Name, Contact Name, Country, Region, City, in that order.

The unsorted range:

The first sort:

The second sort:

The final sorted range:

A recent discussion prompted me to search for a particularly long formula that I had written. I stumbled on the following formula which computes the amount of a grant for a specific period. There are very specific rules to figure this out that I won’t go into here. Here’s the monstrosity.

=(mround((IF(OR(DAY(FED)=1,DAY(FED)=16),FED,IF(DAY(FED)>15,DATE(YEAR(FED),MONTH(FED),16), DATE(YEAR(FED),MONTH(FED),1)))-IF(OR(DAY(FBD)=1,DAY(FBD)=16),FBD,IF(DAY(FBD)>15,DATE( YEAR(FBD),MONTH(FBD)+1,1),DATE(YEAR(FBD),MONTH(FBD),16)))-1)/15,0.5)+(networkdays(IF(OR( DAY(FED)=1,DAY(FED)=16),FED,IF(DAY(FED)>15,DATE(YEAR(FED),MONTH(FED),16),DATE(YEAR(FED), MONTH(FED),1))),FED)/networkdays(IF(OR(DAY(FED)=1,DAY(FED)=16),FED,IF(DAY(FED)>15,DATE( YEAR(FED),MONTH(FED),16),DATE(YEAR(FED),MONTH(FED),1))),DATE(YEAR(FED),MONTH(FED)+IF( DAY(FED)>15,1,0),IF(DAY(FED)>15,0,15))))+(networkdays(FBD,IF(OR(DAY(FBD)=1,DAY(FBD)=16), FBD,IF(DAY(FBD)>15,DATE(YEAR(FBD),MONTH(FBD)+1,1),DATE(YEAR(FBD),MONTH(FBD),16)))-1)/ networkdays(DATE(YEAR(FBD),MONTH(FBD),IF(DAY(FBD)>15,15,1)),IF(OR(DAY(FBD)=1,DAY(FBD)=16), FBD,IF(DAY(FBD)>15,DATE(YEAR(FBD),MONTH(FBD)+1,1),DATE(YEAR(FBD),MONTH(FBD),16)))-1)))*(AR/24)

What I want now is the all-time worst formula. It has to be long, virtually uneditable, and used in real life. By the way, the above formula is 879 characters.

The Application has a Volatile method which you can use in your user defined functions (UDF’s) to force the UDF to calculate whenever the sheet calculates. Many users misunderstand this method, so I want to set the record straight on it and tell you when I use it.

The Volatile method, when set to True in a UDF, will cause the UDF to execute when the sheet from which it is called recalculates. Normally, a function only executes when one of it’s arguments is changed. This function

Function AddTwo(Rng As Range) As Double

will only execute when Rng is changed. By adding

Application.Volatile True

to the function, it will recalculate whenever the sheet recalcs, not just when Rng is changed. Don’t be fooled into thinking that because you add this line that the function will always show the correct results. We saw in the SumReds post that changing the interior color of a cell doesn’t trigger a recalculation. If the sheet doesn’t recalc, it doesn’t matter if you have Application.Volatile or not, the UDF will not execute.

So when do you use Volatile? Almost never. If you want your UDF to always show the correct result, include everything you need as arguments to the function. In AddTwo() above, everything this function needs is in the arguments. If Rng changes, it recalculates. If something other than Rng changes, it doesn’t and shouldn’t.

Using Volatile sparingly will afford you two benefits. Your worksheet will recalculate faster because it’s only recalculating what it needs to. And UDF’s are notoriously slow compared to built-in functions. The other advantage is that your code is more self-documenting. I should be able to tell from the function name and the argument names just what the function does. You wouldn’t know what this function does

Function AddTwoRanges(cell As Range) As Double

If Range(“A1?) changes, this function doesn’t execute because Range(“A1?) isn’t an argument. You could make it Volatile, but the user still can’t tell what the function does. And there’s the whole calculation time thing. If you really want to add a cell to Range(“A1?), include two Range arguments to the function. You’ll end up with a more flexible function that could be used for other things and users who understand what’s going on.

Certainly there are times when making a function Volatile is necessary. Excel’s NOW() and INDIRECT() functions are volatile, and rightly so. If you think your function needs to be Volatile, make sure you think through your inputs and arguments. Sometimes it’s just necessary, but use it judiciously.

Thanks for the great post suggestion, Vincent.

rzf sends a question to which I can’t find a suitable answer. Maybe you can help. The problem is to copy the formula from one cell to another, but not adjust the ranges relatively. The other constraints are that the cell must be copied normally (e.g. Cntl+C) and that absolute values can’t be required.

If A10 contains the formula =SUM(A1:A9), and the user copies A10, the macro must paste =SUM(A1:A9) in whichever cell the user chooses, say B10.

I think the meat of this problem is getting the “Range Object” from the clipboard. I doubt the clipboard contains a range object, but it certainly contains the address and the formula.

If you have an idea, post a comment. Thanks.

Andrew’s Excel Tips has a new home – www.andrewsexceltips.com. Good luck with the new site Andrew.

Life In Chile is a blog that has nothing to do with Excel, but it’s pretty interesting. If you check it out, be sure to read the Chile vs. AZ page.

On the right side of the status bar are several squares. One of the squares shows information about the cells you have selected. The default information is SUM, but you can change it be right-clicking on the box and choosing another operations.

I use this all the time. The best time to use it is when you’re looking at a spreadsheet with someone who doesn’t know it exists. You can select a range of cells – and they don’t have to be contiguous – and quickly tell the onlooker the sum. When they ask you how you added those numbers up so fast, the correct response is “What? You can’t add that fast?”. Never tell your secrets.

I’ve been posting about using class modules to get at some events that may not be available otherwise. One last one that you mind find useful is for External Data. You can create a class for QueryTables and access the BeforeRefresh and AfterRefresh events.

You set it up the same as the others; create a variable in a class module using WithEvents, create a global variable in a standard module to hold the class instance, assign the class variable to the actual object, code the events. As always, here’s an example:

In a Standard Module

Option Explicit

In a Class Module

Option Explicit

Using Excel’s conditional formatting (Format>Conditional Format), you can’t set the number format as one of the formatting options. To do that, you have to use an event macro, namely the Worksheet_Calculate event.

In this example, if the cell’s value is 1 or less, the cell is formatted as a percent. Otherwise the format is set to show a comma and two decimal places. The Abs() function is used to handle negative numbers and the IsError() function will avoid a run-time error if the cell contains an error such as #DIV/0.

Private Sub Worksheet_Calculate()

Colo’s Junk Room

Reading the Excel Masters’ bios is pretty interesting.