Author Archive

PowerPivot Stats

Here’s what a recent PowerPivot process looked like

  • 14,512,074 Rows
  • 7 Columns
  • 14:09 to load data from SQL Server at 30M
  • 5:36 to save file
  • 129MB file size
  • 0:14 recalc time
  • 1,559,700KB used by the EXCEL.EXE process

On this client machine:

  • Windows 8.1 Pro 64bit
  • Excel 2010 64bit
  • 36GB RAM
  • 2.4Ghz Xeon processor

Leave your story in the comments.

Excel Is Waiting for another Application to Complete an OLE Action

Have you ever seen this message? It’s not an error. You can’t click Debug and go see which line of code it’s on when this happens. You can’t even click Cancel. All you can do is click OK every 10 seconds or so until it’s done. Brutal.

I ran into this message recently on some code that someone else wrote but that I’d modified (see how I’m already deflecting the blame). The code runs through a hundred or so customers and sends them an email. Each customer has its own worksheet and that worksheet is turned into HTML to be used in the body of the email. Incidentally they used Ron de Bruin’s RangeToHTML function to do the conversion. I happened to have written that function back when I had a website called dicks-clicks.com. Ah, memories.

The code I modified was working well for a few weeks before it started acting up. One line in the code looks like this

sh.ExportAsFixedFormat xlTypePDF, sAttachFile

That saves the sheet as a PDF. I use the ExportAsFixedFormat method a lot in loops and I get the Run-time error 1004. Document not saved. the document may be open, or an error may have been encountered when saving error every so often. When I get this error I hit Debug and F5 and everything works fine. I know it’s a timing issue, but haven’t taken the time to figure out how to avoid it. It started happening on this customer email workbook. I couldn’t hardly ask a normal user to click Debug, F5, and close the VBE when it’s done. I’m not a monster.

I put a one second delay before line to allow Windows to have time to release the file lock or whatever the heck is going on. It only executes on about seven of the 100 customers, so it makes a 10 second procedure run in 17 seconds. We can live with that. And it worked. No more errors.

Everything was fine until the OLE Action message started popping up a couple days later. Of course when anything goes wrong after a code change, you have to blame the code change. The angry villagers were at my door demanding that the one second delay be removed. I wasn’t convinced (spoiler: I’m the hero in this story). I sat at the user’s computer, ran the code, and got the message. I changed the status bar to show me which customer it was on when the message appears. When I ran the code again, it was on Vandelay Industries. We looked at some past Vandelay emails and we noticed that the format was all messed up in yesterday’s email, but otherwise looked OK. Formatting problems don’t cause OLE messages, so I ignored it.

I did some Binging and saw my old buddy Shane Devonshire recommended checking the Ignore other applications that use Dynamic Data Exchange checkbox in Tools – Options. I could tell he was grasping at straws, but I was at the straw-grasping stage, so I went with it. I ran the code for the third time (a tedious process because of all the OLE messages) and it got stuck on Vandelay Industries again. A clue!

I discussed this new information with the user. Since Vandelay did not get a pdf attachment, I concluded that the problem was Outlook and not whatever generates PDFs from Office. Maybe we messed up the email address and Outlook was churning away trying to resolve it. Nope, no change there. But he did mention that he added a note to the bottom of their worksheet. More specifically, he copied the note from another customer’s sheet and pasted it to Vandalay’s. Oh, and one other thing. When he pasted the message, he accidentally selected the entire row, which put the message in every cell in that row, rather than just the first one. But he deleted all the extraneous messages, so it was fine.

“Aha!”, I said. I went to their sheet and pressed Ctrl+End. That took me to cell XFD92. In the code, the (now enormous) UsedRange was being passed to RangeToHTML. I went to the Outbox in Outlook and there was a 43MB message sitting there staring back at me. The OLE Action that Excel was waiting for Outlook to complete was rending 43MBs of HTML in a message.

The quick fix was to delete all the columns in that sheet that I didn’t want, save, close, and reopen. Fixed. As for the code, it’s tempting to use an alternative method for finding the real used range. That solves the email problem, but it doesn’t fix the root of the problem – a messed up UsedRange that’s unnecessarily bloating the file.

In the end, I decided to test the number of columns and rows in the UsedRange, and if they’re over a threshold, raise an error. That will allow the user to fix the root and rerun the procedure.

AutoHotKey Update

Have you been wondering what’s up my keyboarding sleeve lately? Too bad, I’m telling you anyway.

Insert Date

You know how pressing Ctrl+; inserts the current date in Excel? Now I can do that in any program. It’s particularly useful when I’m updating an item at goodtodo.com where I date stamp notes.

#IfWinNotActive ahk_class XLMAIN

^;::
    FormatTime, CurrentDateTime,, M/d/yyyy
    SendInput %CurrentDateTime%
    Return

I’ve already re-purposed Ctrl+semicolon in Excel, so it’s in the section that only works outside of Excel.

Stop Helping Me

The other day I accidentally pressed F1. In Excel. For the last time. I could have just disabled F1, but I thought I’d try something different. This figures out what’s in the active cell and searches for it in Firefox.

#IfWinActive ahk_class XLMAIN
f1::
    oExcel := ComObjActive("Excel.Application")
    value := oExcel.ActiveCell.Formula
    Send #2
    Sleep 50
    Send ^k
    Send %value%
    Send {Enter}
    Return

Now when I press F1, I no longer get a separate window, stealing the focus, and not being very helpful. Instead I get a Google search that’s slightly more helpful.

I had to upgrade my AHK to use the ComObjActive function. The Send #2 is because Firefox is pinned to my Windows 7 taskbar in the second location (# is the Windows key in AHK). Send ^k (control+k) moves the focus to the Firefox search textbox. If you are using Chrome, for instance, you’d want Ctrl+d because the address bar and the search bar are the same.

That’s not as nice as if I parsed the function, but it gets the job done. I tried to use the clipboard so that I could edit a cell, select a function, and press F1 to get a Google search for this function. I couldn’t get the clipboard to work reliably, no doubt because I was in edit mode. Hmmm, maybe Ctrl+C, then esc to get out of edit mode? Dangerous.

Seriously, Stop Helping Me

Don’t forget VBA help.

#IfWinActive ahk_class wndclass_desked_gsk

    f1::
    Send ^c
    StringLen, length, clipboard
    IfLessOrEqual %length%, 1
        {
        Send ^{LEFT}^+{RIGHT}
        Send ^c
        }
    Send #2
    Sleep 50
    Send ^k
    Send excel vba %clipboard%
    Send {Enter}
    Return

This copies the selection. If what it copies is more than a single character, it searches for that. If it’s one character or less, it selects the current word under the cursor and searches for that. In both cases, it puts “excel vba” before the search term. If you want to change that to Send %clipboard% site:dailydoseofexcel.com, you won’t hear me complain.

Some VBE Sugar

::ThisPath::ThisWorkbook.Path & Application.PathSeparator

Type ThisPath and it expands to ThisWorkbook.Path & Application.PathSeparator

::ppg::Public Property Get

Type ppg and it expands to Public Property Get

And Some Problems

If I type acd in a Windows Explorer address bar (or File Open or File Save dialog) it expands to the Accounting\Restricted\ folder on our main network share. That shortcut used to be acr until I figured out how common acr is in English words. The combination acd appears almost never, at least in my admittedly low brow circles. It does exist in Access’ DoCmd.OpenForm when you want to open the form as a dialog box.

The other major problem I have is writing comments in the VBE. Thankfully I don’t write a bunch of comments, but I do use the words With, If, and For a lot as I’m sure we all do. When I type them, they expand as if I were typing VBA statements. I tried to code something in AHK that would recognize a single apostrophe and ignore stuff that’s typed after it, but I never got it to work. It’s not simply that it didn’t work, it broke just about everything else in that AHK file. I was clearly over my head and deleted the whole attempt. I wish I had saved it so I could at least shown you what not to do.

Filter a Custom Class Using CallByName

Andrzej asks

Is there a way to dynamically state by which property do I want to filter/sort/unique? ppl.FilterBy(City, “Tokio”).FilterBy(LastName,”Smith”).Unique(FirstName)
Without doing select case

James Brown corrected my response by saying:

You should be able to write a generic filter function in your custom collection class based around CallByName from the object class.

Of course he’s right. Here’s how that might look. Suppose I have some sample contact data.

I could filter by any one of those properties like this

Public Property Get Filter(ByVal sProperty As String, vValue As Variant) As CContacts
   
    Dim clsReturn As CContacts
    Dim clsContact As CContact
   
    Set clsReturn = New CContacts
   
    For Each clsContact In Me
        If CallByName(clsContact, sProperty, VbGet) = vValue Then
            clsReturn.Add clsContact
        End If
    Next clsContact
   
    Set Filter = clsReturn
   
End Property

The comparison value needs to be a variant to account for all the different data types your properties could be. If you had a property of your class that was another class it could complicate things. But this saves me having to write a bunch of Filter properties. So thanks James for making that comment.

Public Sub TestCallByName()
   
    Dim clsContacts As CContacts
    Dim clsFiltered As CContacts
   
    Set clsContacts = New CContacts
    clsContacts.FillFromRange Sheet1.ListObjects(1).DataBodyRange
   
    Set clsFiltered = clsContacts.Filter("State", "Nebraska")
    Debug.Print "Nebraska: " & clsFiltered.Count
   
    Set clsFiltered = clsContacts.Filter("Active", True)
    Debug.Print "Active: " & clsFiltered.Count
   
    Set clsFiltered = clsContacts.Filter("LastPayDate", #4/10/2015#)
    Debug.Print "April 10: " & clsFiltered.Count
   
End Sub

Office 2016 Preview

From the Office blog: Office 2016 Public Preview Now Available

Intuitive data connecting and shaping capabilities. With integrated Power Query, use Excel as your personal analysis workspace by connecting to and viewing all the data around you. Take advantage of a broad range of data sources, including tables from websites, corporate data like SAP Business Objects, unstructured sources like Hadoop, and services like Salesforce. After bringing all your data together in one place, quickly shape and combine to fit your unique business needs and get to analysis in seconds

Go here for instructions on how to get the preview.

I use Oracle Virtualbox to install previews. So far, the 2016 preview has been remarkably stable, but I’m not one for taking chances.

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?

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?

Creating Tables the Right Way

And by ‘right way’ I mean the way I want. JKP commented:

I wish MSFT would put a tablename box right into the “Format as Table” dialog as it is the first thing I do after formatting a range as a table. O, and always put that checkbox on. My tables always have a header row.

I couldn’t agree more. So why not repurpose Ctrl+T to do what I want.

Sub MakeTable()
   
    Dim sh As Worksheet
    Dim sName As String
    Dim lo As ListObject, loExists As ListObject
   
    Const sSHEETSTART As String = "Sheet"
    Const sTABLESTART As String = "tbl"
   
    Set sh = ActiveSheet
   
    'Get the name of the table from the user
    sName = Application.InputBox("Enter the table name", "Table Name")
   
    'If the user didn't click Cancel
    If sName <> "False" Then
        'Start the table with 'tbl' if it doesn't already
        If Left$(sName, Len(sTABLESTART)) <> sTABLESTART Then
            sName = sTABLESTART & sName
        End If
       
        'Create the table and name it
        Set lo = sh.ListObjects.Add(xlSrcRange, ActiveCell.CurrentRegion, , xlYes)
               
        'See if that name exists on this sheet
        On Error Resume Next
            Set loExists = sh.ListObjects(sName)
        On Error GoTo 0
       
        'If the name doesn't exist
        If loExists Is Nothing Then
            lo.Name = sName
           
            'If the sheet isn't already specifically named, name it
            If Left$(sh.Name, Len(sSHEETSTART)) = sSHEETSTART Then
                On Error Resume Next
                sh.Name = Replace$(lo.DisplayName, "tbl", vbNullString)
            End If
        End If
    End If

End Sub

This code makes a lot of assumptions about how I work with tables, so it may not work for you. First, I ask for the table name. I start all my table names with tbl, so if I don’t include that the code includes it for me. Next, I create a new ListObject based on the CurrentRegion of the ActiveCell. This is different than what Excel does. If you only have one cell selected, Excel will use the CurrentRegion. If you have more than one cell selected, Excel assumes you’ve defined the range you want and uses that. I put one table on one sheet and it’s the only thing on there. Therefore, I always want everything on that sheet to be the table.

Next, I see if a table with that name already exists on the sheet. If it does, skip the whole naming part.

Finally, I change the name of the sheet if it’s still named the generic ‘Sheetx’. I drop the ‘tbl’ part from the DisplayName property and name the sheet. The error avoidance is in case there’s already a sheet with that name. In that case, the name remains unchanged.

Why the DisplayName? If you name a table tblList, you can’t name another table tblList on the same sheet. In fact, in the user interface you can’t name another table tblList in the whole workbook. But in code, you can name another table tblList as long as it’s on a different sheet. If that name already exists, ListObject.Name remains tblList, but ListObject.DisplayName is changed to tblList_1. That’s why I check for the existence of that table on the same sheet but not the whole workbook. And that’s why I use the DisplayName to name the sheet.

I should have skipped all this error checking and just put a big On Error Resume Next at the top. I probably will never have two tables with the same name, and if I accidentally did, it would just keep the default name.