Running macros in Excel from c#

Here we go again, it’s Excel and sometimes I feel like Peter Pan tries to catch his own shadow.

catchingshadows

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”.

what

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

I love the beauty of the simplicity of recorded macros :-D.

Honestly, I doesn’t matter what it actually does, it only shall show that running macros is possible and allows for pretty much everything that macros can do.

Certainly there are limitations. When running as a service, it is prohibited to use

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:

Excel-macro-correct-execution

but it looked like this:

Excel-macro-incorrect-execution

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?

Running macros in Excel from c#

holgerleichsenring