Archive for the ‘Uncategorized’ Category.

Warning: Special Cells slows to a crawl across multiple columns

Further to my previous post on Goto Special/Blanks, it looks like there is a pretty serious problem with the SpecialCells method if you use it on more than one column.

That’s because it’s blindingly fast at finding things in one column, but painfully slow in finding things in any other columns. Which is a problem, because anyone who’s anyone knows that using SpecialCells is supposed to be much more efficient than looping through a big range. Well, Mr and Mrs Anyone may want to reconsider using the loop on multiple columns. Or rather, they might want to consider looping through the columns, and use SpecialCells on each one individually. Best of both worlds.

So how fast is it on one column? And how slow on multiple columns? Let’s find out.

Here’s your test code.

Sub TestingTestingIsThisThingOn()
Dim TimeTaken As Date
Dim rng As Range
Dim Jeff As AboveAverage

TimeTaken = Now()
Set rng = Range("A:B")
rng.SpecialCells(xlCellTypeConstants, xlTextValues).Select

TimeTaken = Now() - TimeTaken

Debug.Print "UsedRange:" & vbTab & ActiveSheet.UsedRange.Address
Debug.Print "Range Searched:" & vbTab & rng.Address
Debug.Print "Time Taken:" & vbTab & Format(TimeTaken, "HH:MM:SS") & " seconds."
Debug.Print "Cells: " & Selection.Cells.Count & vbNewLine & vbNewLine
End Sub

Fill A:A with some text, such as “Blindingly Fast”, and run that sub. Here’s what I get:

UsedRange: $A:$A
Range Searched: $A:$B
Time Taken: 00:00:00 seconds.
Cells: 1048576

Now fill B:B with “Painfully Slow”, and run it again. Here’s what I get:
UsedRange: $A:$B
Range Searched: $A:$B
Time Taken: 00:02:02 seconds.
Cells: 2097152

What the…? So it took a blindingly fast 1 second to process one column, and a painfully slow three minutes to process two? That’s about as linear as my life story before I met and married Excel 2000. (My first wife. I still see her round from time to time in the forums. She hasn’t aged gracefully.)

So either there’s something SpecialCells doesn’t like about the number of columns, or there’s something it doesn’t like about the number 1048576. Let’s find out.

If you delete rows 524288 down, then you’re left with 2 columns of text totaling 1048576 cells. Here’s what I get if I run the code on that:

UsedRange: $A$1:$B$524287
Range Searched: $A:$B
Time Taken: 00:00:57 seconds.
Cells: 1048574

It took one minute to process this, as opposed to the one second it took to process the same amount of cells in one column. So it seems that it’s the multiple columns aspect that is causing the issue, and that the extra time incurred is dependent on the number of cells in that 2nd column. In fact, if I fill those blank cells in column A with our text (while leaving half of Column B with blanks) and rerun the code, then it doesn’t take Excel any longer to process those extra half million cells:

UsedRange: $A:$B
Range Searched: $A:$B
Time Taken: 00:00:52 seconds.
Cells: 1572863

So it seems the issue is in how Excel is handling the union of all those cells it identified in the first column with any cells it identifies in the second. And given how slow it is, I wouldn’t be surprised if it’s unioning those cells one at a time.

The same thing goes for blanks, by the way. This turned up in the comments of Doug Jenkin’s latest post.

Moral of the story: Do NOT use SpecialCells on more than one column at a time.

Question: How are you supposed to bring this stuff to MS’s attention, apart from posting it somewhere conspicuous like here?

—Update—

Over in the comments of his blog, Doug Jenkins hypothesizes:

A possibility is that a single column is treated as being a single array with 1 million values, but two columns are treated as being 1 million arrays, each with 2 values. When the operation is done on the spreadsheet with F5-Special, the name box shows that it works row by row, rather than all of Col A then all of Col B.

I think he’s right. And also after further testing, I realize that those times are down to how long Excel took to search all these arrays, and not to do with how long it took Excel to union the results. Because there weren’t that many results to union in my simple example, so it took practically no time at all.

But if you set up the data like so:
 
Every 2nd blank
 
 
…then you see that the actual unioning takes a heck of a lot longer than the searching. For instance, as per above it took nearly two minutes for SpecialCells to search 1 million rows across two columns and identify a handful of blank areas. Whereas from the below table, you can see that with the greatly increased number of blanks that it’s got to union, it took over two minutes to union just 45,000 cells in one column.
 
Results

What good is the Data Model without PowerPivot?

So in the book I’m forever writing, I have an example of how you can use the Data Model to effectively do VLOOKUPS. Take the example where we’ve got a transactional list of sales that has a product ID but no ‘friendly’ name or price, and another table that maps Product ID to price and friendly name.
 
Two tables
 
 
We can use the Data Model to set up a relationship between these two tables…
 
Edit Relationship
 
 
…so that we can display friendly product names from the Price table against aggregated sales quantities from the Sales Data:
 
PivotTable1
 
 
Sweet. No VLOOKUPs necessary. So…given that we’ve got quantity, and prices, how much money did we make? Let’s find out. First, lets’ drag that Price field into the Values area of the PivotTable and see what it gives us:
 
PivotTable2
 
 
Well, that obviously isn’t what we’re after…it gives us the price of every item, not total revenue. Maybe we need to create a calculated field to multiply Qty against Price:
 
Calculated Dialog
Oh wait…we can’t. Unless the average user has a premium version of Excel that includes the glorious thing that is PowerPivot (not to mention the wherewithal to not be daunted by a completely different, programmer-centric User Interface) then they can’t do this rather basic datamodel-ish thing.

So here we are…15.64% of the way through this century, and yet your average user still doesn’t have the ability to mash together relational tables in the world’s most widely used business productivity tool. (Or mash together flat files from different sheets or workbooks, for that matter.) Unless of course they go back to last century and do it like this:
 
ms query
 
Query Pivot
 
 
I just don’t get this. What’s the point of exposing non-premium users to something like the Data Model if it doesn’t let them do diddly squat? Without calculated fields, surely it’s a big red herring that unnecessarily confuses users? (And okay, so if the Data Model on its own isn’t the hammer for this particular nut, what is?)

I thought I was missing something here, so asked someone smarter than me about these matters (Sam). Sam tells me:

From 2013 onward the Data Model and the DAX Engine are separate
The Data Model is only to create Relationships (Joins) – available to all versions of Excel. The DAX engine if for the Calculations – the Pro Plus and Stand Alone versions.
The only extra function on which you can aggregate data in the Data Model based Pivot Table is Distinct Count at the expense of Numerical Count and Product of a normal pivot. A Data model based pivot table disables both Calculated fields and Items as well as the ability to Group. So the short answer is No – you need DAX alternately use SQL to create the Joins

Don’t get me wrong…Powerthis and Powerthat is all very exciting. But Microsoft: How about showing the tiniest bit of love for the common man (and woman) who live in the common version. Those folk shouldn’t have to upgrade to some fully-featured business intelligence flagship product merely so they can mash data from within Excel together, surely? (And yes, they might be able to use PowerQuery for free to join their relational data together, but leaving aside the fact that they’d probably end up with a flat file in Excel after importing the data from PQ, it’s just not good enough that they would have to go learn another programmer-centric tool just to do something that should be childs-play in Excel itself. )

Just to be clear, I’m not one of those that think that PowerPivot should be bundled into all versions of Excel just because we want it to be. Microsoft have spent a lot of money on PowerPivot, and I don’t expect them to have a sudden attack of benevolence merely because it suits users. Theirs is a money-making venture. And their shareholders expect them to do the best job possible at that. But here’s the thing: It’s nearly 2016, and MS still have not given users the ability to do some pretty basic stuff natively in Excel, such as mash together data in separate sheets or workbooks and serve it up piping hot as a PivotTable. Don’t they know that in the real world, people have to mash together data from different sheets all the time? You get a sheet emailed to you from division A, a sheet from division B, and a sheet from division C. You need to mash it up to get an overview. A lot.

I’m not asking for Tableau here. I’m just asking for a core product that also moves with the times. Or at the very least, keeps up with last century.

Easy way to back up CF formats

I’m working on an application that Project Managers use to test their competencies against a competency framework. There’s a lot of questions and I don’t want to freak them out by showing all at once, so I use Conditional Formatting to unhide the questions in batches, as well as to highlight the next thing that needs to be answered, and to show how their self assessment corresponds to the ratings key.

Here’s how it looks before users start filling it out: only the first batch of questions relating to the first competency area are displayed:
 
Comp blank_Small
 
 
As they complete it, their answers get color-coded to match the key:
 
Comp Partial_Small
 
 
When they complete the last question in regards to the current competency they’re working on, the next batch of questions for the next competency appears, and the definition for that competency overwrites the previous one in the top left:
 
Comp next_Small
 
 

I’m using CF because I don’t want to use code to do this if I can help it, so I don’t have to deal with security settings or questions about enabling macros. Plus I’m just there for 4 weeks, and there isn’t going to be a VBA developer picking up the project after me.

But the CF dialog – how shall I put this delicately – seems as if it was built by Microsoft. (Ok, so I threw tact to the wind there. It’s a dog, a pig, and a slug, all rolled into one needy, dirty, slimy son-of-a-bitch pest.) Here’s all it shows me in all of its non-resizable gory. (Yes, gory, not glory.):
 
CF Manager
 
 
Using it is like trying to build a ship in a bottle inside of another ship in a bottle. Now I know how proctologists feel. If it was half the dialog box that the Name Manager was, it would at least yet you resize so you could see all your conditions at once:
 
Slightly better
 
 
Just look what the Name Manager gives us, by way of comparison:
 
Name Manager
 
The Name Manager gives us friendly names, notes where we can record what does what, resizable columns, and a resizable dialog. Compared to that, the CF dialog is like the punchline from a Monty Python skit.
 

“Oh, we used to dream of having a resizable dialog box.”
 
 
What’s worse, I keep noticing that the order of my CF conditions gets scrambled – maybe when I copy the sheet to a new template, maybe when I insert or delete columns, maybe when I don’t look suitably impressed whenever it does work correctly. I don’t know.

So I thought I’d look for a way to back them up. I looked at Dicks’ posts Listing Format Conditions and Listing Format Conditions Redux, but those would need more work still in order to record the formatting, and then a routine to restore everything from the storage table.

But then I discovered this: If you fire up the macro recorder, bring up the CF dialog box, select Manage Rules, select Show formatting rules for This Worksheet, and then makes some trivial change – such as click the down arrow to move the currently selected CF condition down one, then click the up arrow to put it back:
 
CF Manager_Move
 
 
…then as soon as you push OK the Macro Recorder spits out a macro that clears all the format conditions on the entire sheet, and then builds those CF rules again from scratch. All of them. Here’s an excerpt:

Sub Macro7()
    Cells.FormatConditions.Delete
    Range("PM[Definition " & Chr(10) & "(Experience)]").Select
    Range("G14").Activate
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(ISBLANK($I14),$T14=FALSE)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True
    Range("PM[Definition " & Chr(10) & "(Knowledge)]").Select
   Range("G14").Activate
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=ISBLANK($G14)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True
   
End Sub

That – my friend – is your CF backup. The only thing wrong with this code is that it keeps putting the line Range(“G14″).Activate in the middle of each block for some strange reason. That happened to be the cell I had selected when I recorded the macro. But if you do a find and replace on the code to ditch it in favor of a more helpful “” then the code works fine. (Yes I know that CF is relative, but that’s still no reason why it should select the cell I have selected and then apply the formatting to it rather than to the desired target). (Edit: You need this activation if you are dealing with CF conditions with relative referencing. But it doesn’t work properly in the event that you originally applied CF to a range while you had another range outside of that CF area selected. The CF manager takes account of this properly, but the macro output doesn’t, meaning it selects a range, then activates a cell/range outside of that selection, which screws things up. So it pays to eyeball the code for situations like this.)

Now what would be cool is if someone out there was to code up a routine that uses extensibility to read this from the VBE and populate a handy table in the worksheet with all the parameters. Extra credit to someone who devises a way to make the macro recorder fire up and do this automatically for each sheet.

Even better: it would be amazing if Microsoft would show the CF dialog the slightest amount of love. CF could be one of the most powerful features of Excel for non coders. But not as it stands at the moment. Right now, it’s a pigdogslug.

An even easier way

So I got to thinking that if Excel will quickly spit out a complete list of FormatConditions at the ‘AppliesTo’ level for the sheet, it must have them stored in a collection somewhere. And that collection is…drum roll please…the Cells object.

Want some code to quickly and easily print out every format condition everywhere? Here ’tis:

Sub FindCF()

'We have to Dim WhatIsIt as a generic Object instead of declaring as FormatCondition because DataBars screw things up.
'See http://excelmatters.com/2015/03/04/when-is-a-formatcondition-not-a-formatcondition/

Dim WhatIsIt As Object
Dim fc As FormatCondition
Dim db As Databar
Dim cs As ColorScale
Dim ics As IconSetCondition

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    For Each WhatIsIt In ws.Cells.FormatConditions
        Select Case TypeName(WhatIsIt)
        Case "Databar":
            Set db = WhatIsIt
            Debug.Print "Type: " & "DataBar" & vbTab & "Applies To: " & db.AppliesTo.Address
        Case "FormatCondition"
            Set fc = WhatIsIt
            Debug.Print "Type: " & "FormatCondition" & vbTab & "Applies To: " & fc.AppliesTo.Address & vbTab & "Formula: " & fc.Formula1
        Case "ColorScale"
            Set cs = WhatIsIt
             Debug.Print "Type: " & "ColorScale" & vbTab & "Applies To: " & cs.AppliesTo.Address
        Case "IconSetCondition"
            Set ics = WhatIsIt
             Debug.Print "Type: " & "IconSet" & vbTab & "Applies To: " & ics.AppliesTo.Address
        Case Else
        Stop
        End Select
    Next WhatIsIt
Next ws
End Sub

Code? Luxury!

Global name meets Local, Global freaks out

So here’s something I came across today. Somehow I’d managed to set up both local AND global names in a sheet, and found that I pretty much couldn’t do anything with the Global name via VBA as a result.

Say I set up this global name:
 
SomeName - Global
 
 
Note that it says SomeName in the NameBox when I have the name selected. Nothing weird about that.

Now I add a local name with the same name, pointing to A2:
 
SomeName - Local
 
 
…and here you can see both names in the Name Manager. Note that it says SomeName in the NameBox when I have the local name selected. Nothing weird about that, either…local names take precedence over global.
 

SomeName - Both_Name Manager
 
 
If I type = then select A1 where the global name lives, note that no name comes up in the intellisense:
 
Equals Global
 
 
..but if I do the same with the local name, I get intellisense:
 
Equals Local
 
 
Now things get a little weirder. If I type =SomeName, I get two options in the intellisense:
 
Equals SomeName
 
 
If I select the SomeName (Workbook) option, Excel qualifies the reference with the workbook name:
 
Book2 SomeName
 
 
…but when I push Enter, it’s still the local name that’s being referenced:
 
Book2 SomeName Not
 
 
…and when I select it again, I see that Excel has bizarrely changed that Book1! qualifier to a Sheet1! qualifier. In other words, it’s said ‘I know you asked for workbook, but I’m giving you local':
 
Book2 SomeName Local Qualifier
 
 
So what the hell is the point of those two options in the intellisense, if no matter what you do, Excel gives you the local name? Beats me. What’s worse, if your workbook name has a space in it, then Excel forgets to wrap apostrophes around it, and the Update Values dialog comes up:
 
Update Values dialog
 
 
What a mess. Push cancel, and you’re left with an invalid name:
 
Book2 SomeName spaces
 
 

Names and Collections

It turns out that the local name gets added to both the ActiveWorkbook.Names collection AND the ActiveSheet.Names Collection:
? activeworkbook.Names.Count
2
? activesheet.names.count
1

You can reference both names by index number just fine:
? activeworkbook.Names(1).Name
Sheet1!SomeName
? activeworkbook.Names(2).Name
SomeName

…but any attempt to reference the globally scoped one by name gives you the local name. Here, I retrieve their names from their index numbers:
? activeworkbook.Names(1).Name
Sheet1!SomeName
? activeworkbook.Names(2).Name
SomeName

Here I retrieve their refersto ranges from those index numbers:
? activeworkbook.Names(1).RefersTo
=Sheet1!$A$2
? activeworkbook.Names(2).RefersTo
=Sheet1!$A$1

Here I try to retrieve their refersto ranges from their names:
? activeworkbook.Names(“Sheet1!SomeName”).RefersTo
=Sheet1!$A$2
? activeworkbook.Names(“SomeName”).RefersTo
=Sheet1!$A$2

And here I try to retrieve their names from their names:
? activeworkbook.Names(“Sheet1!SomeName”).Name
Sheet1!SomeName
? activeworkbook.Names(“SomeName”).Name
Sheet1!SomeName

As per this Stack Overflow thread, If you want to identify the global one, you have to loop through the collection of names and find the one that has an “!” in its name.

So there you have it: if you’re referencing names by name, don’t assume you’ll get the one you want. Tedious!

CF meets Paste Names, CF freaks out.

Here’s a funny little bug I came across today. So I’m adding a CF condition, and I push F3 to bring up Paste Names rather than type out a reference or name:
 
CF1
 
 
And clicking on that named range duly inserts it into my CF formula:
 
CF2
 
 
…but the only problem is, now I can’t seem to do anything else. I can’t type the closing bracket, because Excel won’t let me. I can’t backspace to remove the offending name, because Excel won’t let me. The cursor is still flashing, so Excel hasn’t hung.
CF2

If you type the name in manually rather than using F3, it works perfectly. It turns out its some kind of focus issue: if you push ALT + TAB the screen flickers, but you still have the Conditional Format dialog open and now it works perfectly.

It’s incredible what you can do with CF if you’ve got a good memory. The good memory bit is required because CF doesn’t let you write notes about what each condition does like Names do, or even assign those CF conditions meaningful names. Let alone (heaven forbid) resize the dialog box so you can see all your rules at once, to help you work out which does what:
 
CF dialog
 
 
With a good memory, it’s an incredibly strong and useful tool. With my memory, it’s downright mysterious.

Shame, because I’m using it to really good effect to steer users through a pretty complicated self-assessment tool. I pity the poor sap that comes after me that has to amend these rules. On the other hand, that poor sap will probably end up hiring me back at my urgent pickle rate. Thanks, Microsoft.

CellAbove trick no longer needed?

I was writing up the CellAbove trick for the book. You know, the trick outlined at Ken Puls’ place.

First I was going to show how SUM doesn’t handle row insertions immediately above the SUM function itself. So I duly made up a crappy example (if you’ll pardon the crappy pun):
 
Underwear 1
 
 
…and then did the screenshot of the row insert, that showed how the formula didn’t adjust:
 
Underwear 2
 
 
…and then went to add some ridiculously large number of underwear, for readers who need some point laboring:
 
Underwear 3
 
 
…and then pushed enter, and to my amazement saw that my example of “broken” ain’t, no more:
 
Underwear 4
 
 
How utterly inconsiderate of Microsoft to fix this. Ruined a perfectly good example that I figure I could have dragged out for another half a chapter. Bugger! Now I have to write something else. God I hope they don’t fix the CONCATENATE function before I finish the book, or I’m screwed.
 
 

Edit

I just heard from Bill Jelen, who mentions when this gets autocorrected in this really great podcast of his:

He says “Going back to Excel 2003, provided you have a series of three or more numbers, Excel’s going to rewrite the formula to handle the new rows”.

But do check out that link: It’s hilarious. Also check out the follow-up podcasts.

There’s nothing like an Excel nerd with too much time on his hands…

Timing Formulas

In my last post, I looked at how much faster the Double VLOOKUP trick was on sorted data compared with the usual linear VLOOKUP on unsorted data.

Below is the code for the timing routine I use. I stole the guts of it from joeu2004 who made some incredibly insightful comments at this great thread at MrExcel

If you’re going to time formulas, then that thread is required reading, because it makes this important point: we cannot always accurately measure the performance of a formula simply by measuring one instance of it. (But that does depend on the nature of the formula and the situation that we are trying to measure. Sometimes we need to measure one instance of a formula, but increase the size of ranges that it references in order to overcome the effects of overhead.)

I’ve assigned my code to a custom button in the ribbon. You just select the cells you want the formula to time, and click the button. (By the way, my upcoming book not only shows you how to do this, but also gives you routines for every one of the icons shown above and a lot more besides.)
 
TimeFormulas 1
 
 
In the above case, I’ve selected a 1 row by 3 column range. If your selection is only one row deep – and there’s more rows with data below – then the app assumes you want to time everything below too, up to the first blank cell it encounters. I may want to rethink that, but it works good for now.

And after I click the magic button, here’s the result:
 
Timing message
 
 
As you can see from the output screenshot above, it puts a new table in a new sheet, populates it with your timings and relevant parameters, and then displays a message with joeu2004’s warning in it. Plus – and I think this is the genius part – it lets you push OK to return back to the sheet where the original formulas are, or push Cancel if you want to stay in the output sheet.

Note the ‘Formula’ column in the output table. For now, it just lists the formula that was in the top-left cell in the user’s selection. I think listing multiple formulas would be overkill. If there’s no formula in the top left cell, it sees if there’s a formula in the cell below. That way you can select headers in a Table, and you’ll still get the formula in the output.

It also can time multiple areas in one pass. So if I select that same range as a non-contiguous selection by holding Ctrl down and clicking each cell as I’ve done here (not that it’s easy to tell the difference visually from the previous screenshot):
 
noncontiguous
 
 
…then here’s what I get:
 
Output 2
 
 
Man, that is sooo much better than having to do each formula separately and then having to manually copy the results from a messagebox into a Table, like I used to do.

As you can see, I put a a databar on the important column, so you can visually eyeball results instead of conceptually juggling scientific notation in your head. But I’m damned if I can get this to display exactly as I want via VBA. For instance, here’s the databars I want if I add them manually:
Nice Databars

Notice that those have nice borders, and sensible setting for the minimum values, meaning that first result also gets a databar. The code that VBA spits out when you add this default databar is pretty ugly:

Selection.FormatConditions.AddDatabar
    Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .MinPoint.Modify newtype:=xlConditionValueAutomaticMin
        .MaxPoint.Modify newtype:=xlConditionValueAutomaticMax
    End With
    With Selection.FormatConditions(1).BarColor
        .Color = 13012579
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).BarFillType = xlDataBarFillGradient
    Selection.FormatConditions(1).Direction = xlContext
    Selection.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
    Selection.FormatConditions(1).BarBorder.Type = xlDataBarBorderSolid
    Selection.FormatConditions(1).NegativeBarFormat.BorderColorType = _
        xlDataBarColor
    With Selection.FormatConditions(1).BarBorder.Color
        .Color = 13012579
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
    With Selection.FormatConditions(1).AxisColor
        .Color = 0
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).NegativeBarFormat.Color
        .Color = 255
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).NegativeBarFormat.BorderColor
        .Color = 255
        .TintAndShade = 0
    End With

My code is just doing the first line:

'Add DataBars to the Each Cell column for easier comparison
        lo.ListColumns("Each Cell (sec)").Range.FormatConditions.AddDatabar

…because no matter what I try, doing anything beyond merely adding the bars causes the code to error out. For instance, even if I just try and set the minpoint and maxpoint I get an error:
 
error 1
 
 
…and this successfully adds the min setting I want, but errors out when I try to add a listrow:
 
error 2
 
 
Even worse, when I end the routine, the screen no longer updates no matter what I do. So I have to close out of Excel entirely.

If anyone can tell me where I’m going wrong, I’d be much obliged. Might be another peculiarity of Excel Tables. Meanwhile, I’ll just run with those simple bars.

Here’s my draft code:

Option Explicit

Public Declare Function QueryPerformanceFrequency Lib "kernel32" _
    (ByRef freq As Currency) As Long
Public Declare Function QueryPerformanceCounter Lib "kernel32" _
    (ByRef cnt As Currency) As Long
   
   
'Code adapted from http://www.mrexcel.com/forum/excel-questions/762910-speed-performance-measure-visual-basic-applications-function.html
'   Description:    Determines formusa execution time
'   Programmer:     Jeff Weir
'   Contact:        excelforsuperheroes@gmail.com

'   Name/Version:       Date:       Ini:   Modification:
'   TimeFormula         20150426    JSW    Added in ability to record times to ListObject


Sub TimeFormula()
    Dim sc As Currency
    Dim ec As Currency
    Dim dt As Double
    Dim sMsg As String
    Dim sResults As String
    Dim i As Long
    Dim N As Long
    Dim oldCalc  As Variant
    Dim myRng As Range
    Dim lo As ListObject
    Dim lr As ListRow
    Dim bUnique As Boolean
    Dim strFormula As String
    Dim myArea As Range
    Dim lngArea As Long
    Dim ws As Worksheet
    Dim wsOriginal As Worksheet
    Dim bNewListObject As Boolean
    Dim lngAreas As Long
    Dim varResults As Variant
    Dim varMsg As Variant
    Dim fc As FormatCondition
   
   
    Const passes As Long = 10
   
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        oldCalc = .Calculation
        .Calculation = xlCalculationManual
    End With

   
    Set myArea = Selection
    lngAreas = myArea.Areas.Count
    Set wsOriginal = myArea.Worksheet
    ReDim varResults(1 To lngAreas, 1 To 6)
    For Each myArea In Selection.Areas
        lngArea = lngArea + 1
        dt = 0
        Set myRng = myArea
        If myRng.Rows.Count = 1 Then
            If Not IsEmpty(myRng.Cells(1).Offset(1)) Then Set myRng = Range(myRng, myRng.End(xlDown))
        End If
        N = myRng.Count
        If myRng.Cells.Count > 1 Then
            'Get formula from 2nd row in case we're dealing with multiple cells and happen to be on a header
            If myRng.Cells(2).HasFormula Then strFormula = myRng.Cells(1).Formula
            If myRng.Cells(2).HasArray Then strFormula = myRng.Cells(1).HasArray
        End If
   
        If myRng.Cells(1).HasFormula Then strFormula = myRng.Cells(1).Formula
        If myRng.Cells(1).HasArray Then strFormula = myRng.Cells(1).HasArray
       
        With myRng
            For i = 1 To passes
                sc = myTimer
                .Calculate
                ec = myTimer
                dt = dt + myElapsedTime(ec - sc)
            Next
           
            'Record results for this pass
            varResults(lngArea, 1) = myRng.Address
            If strFormula <> "" Then varResults(lngArea, 2) = "'" & strFormula
            varResults(lngArea, 3) = N
            varResults(lngArea, 4) = dt / passes
            varResults(lngArea, 5) = dt / N / passes
            varResults(lngArea, 6) = Now
        End With
       
         
    Next
   
    bNewListObject = True
    For Each ws In ActiveWorkbook.Worksheets
        For Each lo In ws.ListObjects
            If lo.Name = "appTimeFormulas" Then
                bNewListObject = False
                ws.Activate
                Exit For
            End If
        Next
    Next

   
    If bNewListObject Then
        Set ws = ActiveWorkbook.Worksheets.Add
        On Error Resume Next
        ws.Name = "TimeFormula"
        On Error GoTo 0
        Range("A1").Value = "Range"
        Range("B1").Value = "Formula"
        Range("C1").Value = "Count"
        Range("D1").Value = "Entire Range (sec)"
        Range("E1").Value = "Each Cell (sec)"
        Range("F1").Value = "TimeStamp"
        Set lo = ws.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes)
        lo.Name = "appTimeFormulas"
       
        'Add DataBars to the Each Cell column for easier comparison
        lo.ListColumns("Each Cell (sec)").Range.FormatConditions.AddDatabar
       
    Else: Set lo = ActiveSheet.ListObjects("appTimeFormulas")
    End If
   
    Set lr = lo.ListRows.Add
    lr.Range.Resize(lngAreas).Value = varResults
    lo.Range.EntireColumn.AutoFit
    With lo.ListColumns("Formula").Range
        If .ColumnWidth > 30 Then
            .ColumnWidth = 30
            .WrapText = True
        End If
    End With
   
    With Application
        .EnableEvents = True
        .Calculation = oldCalc
        .ScreenUpdating = True
    End With
   
    sMsg = "Here are the average timings for the selected range over " & passes & " passes "
    sMsg = sMsg & vbNewLine & vbNewLine
    sMsg = sMsg & "Note that timings include some overhead incurred during the actual measurement process itself. "
    sMsg = sMsg & "So if the functions you are tring to time are really really fast, then it's possible that "
    sMsg = sMsg & "the measurement time included in the above result dwarfs the "
    sMsg = sMsg & "actual recalculation time of the formulas themselves."
    sMsg = sMsg & vbNewLine & vbNewLine
    sMsg = sMsg & "For best results, either time the functions over a really big range (hundreds "
    sMsg = sMsg & "of rows or more) or increase the size of the ranges that the formulas "
    sMsg = sMsg & "refer to. Furthermore, pay more heed to the average per-formula time than the overall time when "
    sMsg = sMsg & "making comparisons with other formulas."
    sMsg = sMsg & vbNewLine & vbNewLine
    sMsg = sMsg & "Do you want to return to the formulas, or stay in the result sheet?"
    sMsg = sMsg & vbNewLine & vbNewLine
    sMsg = sMsg & "(Press YES to return to formulas, and NO to stay in this results sheet.)"

    varMsg = MsgBox(Prompt:=sMsg, Title:="Recalculation time for selection:", Buttons:=vbYesNo)
    If varMsg = vbYes Then wsOriginal.Activate
   
End Sub

Function myTimer() As Currency
    ' defer conversion to seconds until myElapsedTime
    QueryPerformanceCounter myTimer
End Function

Function myElapsedTime(dc As Currency) As Double  ' return seconds
    Static df As Double
    Dim freq As Currency
    If df = 0 Then QueryPerformanceFrequency freq: df = freq
    myElapsedTime = dc / df
End Function

How much faster is the double-VLOOKUP trick?

Quick post. I was writing up the Double-VLOOKUP trick I learnt from Charles Williams for the book.

Standard VLOOKUP on unsorted data:
=VLOOKUP([@ID],Table2,2,FALSE)

Double VLOOKUP trick on sorted data:
=IF(VLOOKUP([@ID],Table2,1,TRUE)=[@ID],VLOOKUP([@ID],Table2,2,TRUE),NA())

The point of the double VLOOKUP trick is this:

  • Standard VLOOKUPS on unsorted data are slow, because your VLOOKUP has to look at each item in turn until it finds a match. So on average, it looks at – and discounts – half the things in your lookup list before it finds that match.
  • Binary searches are lightning fast. Because your data is sorted, they can start half way through the lookup list, and check if the item at that point is bigger or smaller than what they’re looking for. Meaning they can ditch half the list immediately, then look halfway through the remainder. And over and over, ditching half the list each time until they either find the item they are looking for, or rule out all items.
  • VLOOKUP and MATCH will quite happily do a binary search for you. But for reasons known only to Microsoft, they offer an exciting plot-twist: if they don’t find what you’re looking for in the list, they return the closest match they can find to it, which happens to be the only thing left in the list when they’d divided it in half enough times. Only they don’t tell you its not an exact match. Phooey.
  • Charles’ brilliant trick is to do two lightning-fast Binary searches – the first one simply looks for the closest match to your input term among the Lookup terms. If it’s an exact match with what you fed it, you know your lookup term is in the list. So now that you KNOW it’s there for sure, you kick off a second approximate match VLOOKUP, which will grab the corresponding value you want from the lookup table. Go read his blog for the specifics.

I’ve known about this for a while, but it’s only as I’m writing this up for the book that I’ve gained an appeciation of just how much faster Binary Searches – and the Double VLOOKUP trick that gets around Microsoft’s crap implementation of them – are, compared to unsorted/linear VLOOKUPs.

See for yourself:
 
Double VLOOKUPv2
 
 
That says that:

  • At one extreme, if your lookup table has 10,000 things in it, the double VLOOKUP trick on sorted data is 28 times faster than the standard VLOOKUP on unsorted data
  • At the other, if your lookup table has 1,000,000 things in it, the double VLOOKUP trick on sorted data is 3,600 times faster than the standard VLOOKUP on unsorted data

Kinda puts the ‘hassle’ of sorting your lookup table ascending into perspective, don’t it!