February 12, 2020

Setting cell functions in Excel, plus a sneak peak!

Filed under: Announcements,Automation — Marcus Tettmar @ 12:30 pm

Something that often gets asks is ‘Can I insert a function into Excel’ using Macro Scheduler‘s native XL functions?’.

Yes, you can. It may not be obvious from the documentation but with XLSetCell you can output any expression accepted by Excel. It doesn’t have to be a literal value.

You might set a literal value with something like this:

XLSetCell>xlH,Sheet1,2,2,560,result

In Excel you insert a function by starting the input with the ‘=’ sign, so we can do the same with XLSetCell:

XLSetCell>xlH,Sheet1,20,2,=SUM(B1:B19),result

In the same way we can force the cell to text format by preceding the input with an apostrophe:

XLSetCell>xlH,Sheet1,2,2,'560,result

These formats are standard Excel features. All we’re doing here is passing a value to Excel that it understands. But you may not have realised you can do this with Macro Scheduler’s native functions.

Coming Soon – More functions & run ANY VBA code!

A few people have requested more native XL functions. We’re working on it.

For example, a new function to set the cell colour:

RGB>50,150,50,color1
XLSetCellColor>xlH,Sheet1,13,2,color1

But how about being able to run ANY valid Excel VBA you like?

XLRunVBA>xlH,ActiveSheet.Range("B12").Interior.Color = vbRed

It could be a whole block of code:

LabelToVar>vba_code,theCode
XLRunVBA>xlH,theCode

/*
vba_code:
  Range("A1:B11").Select
  ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
  ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$B$11")
  ActiveSheet.Shapes("Chart 1").IncrementLeft +50
  ActiveSheet.Shapes("Chart 1").IncrementTop -30
*/

These features are in development. Watch them in action here:

The sky is the limit!

Note: At time of writing these functions do NOT exist in the current version. These are in development. Their names and syntax may change. Keep an eye out for updates.

Be the first to get these new features by making sure your maintenance or subscription us up to date 🙂