Thanks a lot, macro recorder!

I think it's pretty well known that the Excel macro doesn't always record code when you'd like it to.  I know that there's been several times I've recorded something to get a syntax, and it's been an empty stub after I'm done.  This one was a new one to me though...

I tried to record the creation of a conditional format in 2007 and here's what I got:

[vb]Sub Macro4()
'
' Macro4 Macro
'

'
Selection.FormatConditions.Add Type := xlExpression, Formula1 :=
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub[/vb]

The first line was even highlighted red. Seriously, it could get the rest, but not the formula?

4 thoughts on “Thanks a lot, macro recorder!

  1. The 2007 recorder comes up short for most features which were added or significantly changed in 2007. Shapes are completely absent from the recorder, while charts (which are comprised of shapes) lacks any of the formatting inherited from the shapes.

    I'm not surprised that you tripped over conditional formatting. Have you tried macro recording of pivot tables? (I haven't, I'm just interested to hear.)

  2. I recorded a basic pivot last week, and also a pivot filter and both worked fine, but I agree, they have really been blasé about breaking the macro recorder. And of course, I am sure they ave no intention of fixing it.

  3. I could not reproduce this bug with my install of Excel 2007 sp2, perhaps it only occurs for particularly long or complex formulae?
    syntax in VBA for this is essentially the formula wrapped in double quotes as a string, and with normal escape procedures to double up other quotes to make them literal.
    eg:
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= "=A1=""hello world"""

  4. Jon/Bob... yes, Pivot Tables seem to work fine for my needs. I've frequently recorded PT actions in 2007 and always got code.

    Adam, the formula that I was using was hugely long, so maybe that's it. The irony is that recording a macro to enter the same formula in a cell gives you the code. So it's not like it can't do it, it just buggers up when it's part of the cf.

    Oh, and fwiw, I'm running SP2 as well.

Leave a Reply

Your email address will not be published. Required fields are marked *