Now that summer has arrived I’ve planned a summer sale. From July 1st up to July 10th I offer a 25% discount on both products I sell: RefTreeAnalyser
and The Excel File Remediation Utility
To get your discount, just go through the purchasing process and enter this coupon code to redeem your discount:
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.
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.
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.
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.
Type ThisPath and it expands to ThisWorkbook.Path & Application.PathSeparator
::ppg::Public Property Get
Type ppg and it expands to PublicPropertyGet
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.
I could filter by any one of those properties like this
PublicPropertyGet Filter(ByVal sProperty AsString, vValue AsVariant) As CContacts
Dim clsReturn As CContacts Dim clsContact As CContact
Set clsReturn = New CContacts
ForEach clsContact In Me If CallByName(clsContact, sProperty, VbGet) = vValue Then
clsReturn.Add clsContact EndIf Next clsContact
Set Filter = clsReturn
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.
Dim clsContacts As CContacts Dim clsFiltered As CContacts
Set clsContacts = New CContacts
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
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:
As they complete it, their answers get color-coded to match the key:
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:
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.):
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:
Just look what the Name Manager gives us, by way of comparison:
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.
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:
…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:
Range("PM[Definition " & Chr(10) & "(Experience)]").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
Selection.FormatConditions(1).StopIfTrue = True
Range("PM[Definition " & Chr(10) & "(Knowledge)]").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
Selection.FormatConditions(1).StopIfTrue = True
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:
'We have to Dim WhatIsIt as a generic Object instead of declaring as FormatCondition because DataBars screw things up.
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)
Set db = WhatIsIt
Debug.Print "Type: " & "DataBar" & vbTab & "Applies To: " & db.AppliesTo.Address
Set fc = WhatIsIt
Debug.Print "Type: " & "FormatCondition" & vbTab & "Applies To: " & fc.AppliesTo.Address & vbTab & "Formula: " & fc.Formula1
Set cs = WhatIsIt
Debug.Print "Type: " & "ColorScale" & vbTab & "Applies To: " & cs.AppliesTo.Address
Set ics = WhatIsIt
Debug.Print "Type: " & "IconSet" & vbTab & "Applies To: " & ics.AppliesTo.Address
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:
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:
…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.
If I type = then select A1 where the global name lives, note that no name comes up in the intellisense:
..but if I do the same with the local name, I get intellisense:
Now things get a little weirder. If I type =SomeName, I get two options in the intellisense:
If I select the SomeName (Workbook) option, Excel qualifies the reference with the workbook name:
…but when I push Enter, it’s still the local name that’s being referenced:
…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':
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:
What a mess. Push cancel, and you’re left with an invalid name:
Names and Collections
It turns out that the local name gets added to both the ActiveWorkbook.Names collection AND the ActiveSheet.Names Collection:
You can reference both names by index number just fine:
…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:
Here I retrieve their refersto ranges from those index numbers:
Here I try to retrieve their refersto ranges from their names:
? activeworkbook.Names(“SomeName”).RefersTo =Sheet1!$A$2
And here I try to retrieve their names from their names:
? 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!
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:
And clicking on that named range duly inserts it into my CF formula:
…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.
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:
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.