Building an Excel Add-in

Hi there!

Only recently I read this quote somewhere: “If you want something done, ask a busy person”. I found two entirely different people as the originator of this quote: Benjamin Franklin and Lucille Ball. I wonder which it is…

Well, turns out I’ve been quite busy as of late. So I decided it was time to dust off some old stuff I prepared to add to my site but never came round to finishing (I must have become less busy when I was almost done :-) ).

If you’re about to embark on the journey to create an add-in out of a set of macro’s you have been using for some time now, this article is a nice read as it takes you through most of the steps needed when building an add-in for Excel.

Enjoy!

 

Jan Karel Pieterse

www.jkp-ads.com

 

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

Emailing Access Report in Email Body

I’ve been doing a fair bit of work in Access lately, which is why I’m so irritable. I like Access for it’s rapid development characteristics, but I have specific user interface requirements that don’t happen in Access out of the box. By the time I implement those quirks, I feel like I should have just written the damn thing from scratch in C#.

The quirk du jure is sending an Access report via email not as an attachment, but rather in the body of the email. I thought this would be pretty darn easy. I would display the report and the user would use some built-in send function under the File menu to send it off. Oddly, to send an Access object via email, you have to look on the External Data tab, not the File menu. The Export Email function does not have the option of putting the report in the body of the email, only as an attachment. At least as far as I can see. Off to VBA, I guess.

Private Sub cmdEmailBlended_Click()
   
    Dim sFile As String, lFile As Long, sHtml As String
    Dim olApp As Outlook.Application
    Dim olMail As Outlook.MailItem
       
    UpdateBlendedQuery
   
    'Output the report to HTML in the temp directory
    sFile = Environ$("TEMP") & "\Blended" & Format(Date, "yyyymmdd") & ".html"
    DoCmd.OutputTo acOutputReport, "BlendedRackReport", acFormatHTML, sFile
   
    'Read in the HTML File
    lFile = FreeFile
   
    Open sFile For Input As lFile
        sHtml = Input$(LOF(lFile) - 1, lFile)
    Close lFile
   
    'Put the file contents in the email body
    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)
    olMail.To = "nobody@example.com"
    olMail.Subject = "Special Pricing"
    olMail.HTMLBody = sHtml
    olMail.Display
   
End Sub

Ah, the ubiquitous DoCmd object. I hate DoCmd (I told you I was irritable). If I was building the Access Object Model, that line would read ActiveReport.Export sFile, acFormatHTML.

In Excel, I like to write my own HTML converters, but not so in Access. Taking an Excel Range and turning into an HTML table is one thing. But taking a Recordset and creating an HTML report out of it is nuts. There’s calculated fields, headers and footers, and a whole bunch of other considerations. So I have no problem using the built-in HTML converter, even though my 18 record report produces 199 lines of HTML (actually less than I expected).

On the first go ’round, I encountered a Input past end of file error. You might notice on my Input$ statement that I take the length of the file minus 1. That’s not how I normally do it. In fact, I have an AutoHotKey for when I type Input$

:*:Input$(::
    sendinput Input$(lof(lfile),lfile)
    Return

I couldn’t figure out why I was getting this error. I’m using LOF to get the length of the file, what more could you possibly want. I subtracted one just to see what would happen and it worked. Then I opened the file in Notepad++ to see if I could see what was happening.

Look at that little guy at the end. What’s that about?

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!

Excel 2013 VBA Unreasonably Slow

I’m not the kind of guy to upgrade too soon. I spend a lot of time in VBA, so new features in Excel don’t generally inspire me. Recently a client of mine upgraded to 2013 with, let’s say, disasterous consequences.

First, there’s the Single Document Interface (SDI). That’s where every document is in it’s own application container. But not really. It’s all depends on where you draw the line. If you have two linked documents open and you calculate, both documents go into the calculation tree. So it’s not exactly separate application containers. Its main purpose is to allow you to put one spreadsheet on one monitor and a different spreadsheet on another monitor. A worthy goal.

My problem with SDI is that I was still using Excel 2003 menus for five addins. They don’t work so well in SDI. It forced me to rewrite them using the RibbonUI – not a bad thing, but my client probably wasn’t including that cost in the cost of upgrade. But we have fancy new ribbon icons, so all is well, right?

Next I learn that Excel uses SHA1 to encrypt spreadsheets instead of the previous method, which I assume was some sort of XOR with a one-character password. With the new encryption scheme, protecting and unprotecting a worksheet in code takes a touch longer. And by touch a mean a shit-ton.

I don’t know what exactly to do about the protection problem. My code opens templates, unprotects them, writes stuff, combines them into a final report, and reprotects them. The first thing I did was get stingy about my protection. Whereas before I was pretty liberal, protecting and unprotecting whenever I needed to, now I’m unprotecting and reprotecting exactly one time. That made a little difference, but not enough. As an experiment, I removed all protection from the templates and removed all the unprotecting and reprotecting from my code. Here’s the before and after timing of that.

Process Cum Time ThisProcess Description
Start 0
End 18,700.37 18,700.37 Current 2013
Start
End 10,149.81 10,149.81 No protection 2013

That cuts it in half, although I can’t release this into the wild with no protection. My client reports that it takes ~90 seconds on his machine. Not surprisingly, my desktop has better specs than his company-issued machine. But I think it all scales. I can cut his time down to ~45 seconds by removing the protection. Even if we could stomach having unprotected templates, that’s not good enough.

I sprinkled some splits around the code to see if there was one area causing the problem. Then I ran the same code in Excel 2010. There must be something else in 2013 that’s taking a disproportionately long time compared to 2010.

Process Cum Time ThisProcess Description
Start
Open Templates 1,544.60 1,544.60
Fill and validate dump 1,630.98 86.38
Dump page setup 1,763.43 132.45
Store dump properties 2,062.55 299.12
Fill solutions 2,186.50 123.95
Fill waves 4,380.67 2,194.16
Sort Solutions 4,388.46 7.79
Fill Finals 9,937.29 5,548.83
End 9,937.60 0.31 No protection splits 2013
Start
Open Templates 513.72 513.72
Fill and validate dump 547.94 34.22
Dump page setup 571.68 23.74
Store dump properties 666.54 94.86
Fill solutions 735.07 68.54
Fill waves 2,146.86 1,411.78
Sort Solutions 2,154.69 7.83
Fill Finals 4,288.38 2,133.69
End 4,288.79 0.40 No protection split 2010

Nope. Everything just takes longer as far as I can tell. I’m going to increase my number of splits, but I’m not hopeful I’ll find the golden key.

Have you experienced performance problems with 2013? What caused them? How did you solve it?

AET Excel Utilities

Hi there. For the last few months I’ve been working on my main add-in, AET Excel Utilities.

Update
So far I’m in the process of setting up some partners, had a translation offer, and downloads are happening as I write this. Thanks very much to everybody for your help. Don’t be shy if you are interested!


I first started working on it in 2005, as a hobby, and a way to learn VBA. Over time it’s grown from having a handful of very simple tools, to what it is now – well over a hundred utilities (more like over two hundred), and some of them quite complex, even if I do say so myself. Useful? I like to think so. Not a day goes by that I don’t use it, and I can honestly say it saves me lots of time.

But there’s a problem. Even though I like these utilities, I’m not very good at selling myself, letting alone anything I’ve made. And in the world of Excel, most folk have either never heard of me or think I’ve retired if they have. That’s been fine until now, with me plugging away in a corner, tinkering away, but it’s always bothered me that my tools could be so much more.

So, I’d ask all of you for some help. I’m making the tools shareware. And I’m looking for people to help sell them. Do you have a site? If so, are you willing to become a partner or an affiliate? Like I say, I’m not great at sales so any assistance would be appreciated. Translations? Great! Let’s talk about a percentage. I guess the main thing is making people aware of them. Apart from making a bit of pocket money, serious interest will give me incentive to improve them and maybe even try to give my site a bit of an overhaul. (Please contact me using aengwirda [at] gmail.com if you are interested).

Here’s a few screenshots to whet your interest. (Well maybe more than a few…). Look to the left, the AET UTILITIES tab shares both my main utilities and free add-ins (which you can download here).

Worksheet Tools

Rows And Columns

Formula Tools

Deletion Tools

Object Tools

Export Tools

Text Tools

Number Tools

Time And Date

Chart Tools

Path And Folder

Workbook Tools

Developer Tools

Fun And Games

Other Utilities

Cell Menu

Row Menu

Column Menu

Sheet Menu

Here’s the download page link. On the same webpage, you can also download a copy of the Help files for more details on the individual tools, plus the password to see how the code works.

In addition to adding more tools over the next few weeks, I’ll be working on my free utilities too. More details on them, and also some new code samples, that I’m looking forward to posting about in the near future.

April Fools, Excel-style.

Thought I’d share my two favorite pranks with you.

Number one: Take a screenshot of an MVP from their own website, subtly change it somehow, then email it back to them saying “Did you really say this, or was your site hacked”.

Here’s an example. Spot the original.
Jon Originaljon

Number two: Take a screenshot of someone’s spreadsheet next time they walk away from their desk, then paste it neatly over the real thing – like this one that I’m in the process of doing:

 
screenshot

 
 

Hopefully you’ll catch them before they quit Excel forcibly using End Task. (But don’t worry if you don’t…they’ll be none the wiser.)

My only problem is that I’m forced to play this last prank on myself, which kinda ruins the surprise. But I’ve still got that first one to get me through.

Any new Excel-related April Fools pranks out there from anyone? I still have a chapter to write of my Evil Genius book on arrays or something unimportant like that, but I’m running out of steam. So I’ll just put your nastiest pranks in there instead. That outta help folk out with career progression, one way or the other…