Here we go again, it’s Excel and sometimes I feel like Peter Pan tries to catch his own shadow.
You may not believe it, but a shadow can have some kind of grip. The day before yesterday one sales guy called me. As usual, these guys are in a hurry. Next day there is a presentation, and the most important feature in Books doesn’t seem to work like it should, he told me.
Books automates the real Excel application running as a service. Yes, sure Microsoft doesn’t suggest you to do so. For good reasons. It is problematic, but if it would be easy, everybody could do it. 😉
And certainly, it is doable.
Books can run macros, real VBA macros. Most Excel automation software vendors use different tools like Spreadsheet Gear, OpenXml, Syncfusion XlsIO or the like. None of them can do what Excel can.
So here is the myth: Books allow for configuration of macros to be run when refreshing data. Certainly there is no need to run macros, refreshing will be done automatically, when no macro is specified.
The sales man started to explain the problem with the most beloved functionality Excel offers: „I recorded a macro“.
Sure that will work… mostly. But it isn’t stable or maintainable as recorded macros always have a let’s say „open context“. They need ActiveSheet, ActiveWorkbook, ActiveCell, Selection and the like.
Nevertheless, should work out. He called it „DemoMacro“. Yes, not really innovative name, but anyway no need for it. The interesting point that catches my attention was that he added brackets after the name. So the definition of the macro name had been „DemoMacro()“. And this is what it looked like:
Range("C5").Select ActiveCell.FormulaR1C1 = "a" Range("C6").Select ActiveCell.FormulaR1C1 = "b" Range("C7").Select ActiveCell.FormulaR1C1 = "c" Range("C8").Select ActiveCell.FormulaR1C1 = "d" Range("C9").Select ActiveCell.FormulaR1C1 = "E" Range("B5:D5").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone Range("B7:D9").Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent6 .TintAndShade = -0.249977111117893 .PatternTintAndShade = 0 End With With Selection.Font .ThemeColor = xlThemeColorDark1 .TintAndShade = 0 End With Range("A1").Select
- SendKeys as there is no user interface to retrieve them
- Dialogs as there is no user to click a button
Remember the definition before, the macro had been called „DemoMacro()“ with the brackets at the end. So the macro was called, it worked… except one thing:
There had been no exception message at all, but the selections didn’t work correctly. It should look like this:
but it looked like this:
So what’s wrong here?
- macro was called, so the name is correct
- no exception raised
- printed out mostly the right things, so cell value changes worked like assumed, but setting borders and colors did not.
To be honest, actually writing the cells also didn’t work correctly in the first run. These recorded macros are just indication but no bullet prove code so it should be adopted like this at least to make it more robust:
Dim rng As Range Dim wks As WorksheetSet wks = ActiveSheet Set rng = wks.Range("c5") rng.FormulaR1C1 = "a" Set rng = wks.Range("c6") rng.FormulaR1C1 = "b" Set rng = wks.Range("c7") rng.FormulaR1C1 = "c" Set rng = wks.Range("c8") rng.FormulaR1C1 = "d" Set rng = wks.Range("c9") rng.FormulaR1C1 = "E" wks.Range("B5:D5").Select
Still the problem above. And beside the fact that the brackets in the name definition of macro catched my attention, no clue how to approach that problem.
Tried to reproduce the problem on my machine. Same results with that brackets. Tried without the brackets, so instead of naming the macro „DemoMacro()“, just „DemoMacro“ what would be my expectation anyway.
You guess it. It worked.
Any ideas why?