Thanks a lot, macro recorder!

Posted on June 26th, 2009 in Excel, General, I hate it when... by Ken Puls

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:

PLAIN TEXT
Visual Basic:
  1. Sub Macro4()
  2. '
  3. ' Macro4 Macro
  4. '
  5.  
  6. '
  7. Selection.FormatConditions.Add Type := xlExpression, Formula1 :=
  8. Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  9. With Selection.FormatConditions(1).Font
  10. .Bold = True
  11. .Italic = False
  12. .ThemeColor = xlThemeColorDark1
  13. .TintAndShade = 0
  14. End With
  15. With Selection.FormatConditions(1).Interior
  16. .PatternColorIndex = xlAutomatic
  17. .Color = 255
  18. .TintAndShade = 0
  19. End With
  20. Selection.FormatConditions(1).StopIfTrue = False
  21. End Sub

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

4 Comments

4 Responses to 'Thanks a lot, macro recorder!'

Subscribe to comments with RSS or TrackBack to 'Thanks a lot, macro recorder!'.

  1. Jon Peltier said,

    on June 27th, 2009 at 1:46 pm

    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. Bob Phillips said,

    on July 1st, 2009 at 11:31 pm

    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. AdamV said,

    on July 5th, 2009 at 9:49 am

    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. Ken Puls said,

    on July 5th, 2009 at 11:28 am

    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.

Post a comment