{"id":2995,"date":"2020-02-12T12:30:59","date_gmt":"2020-02-12T12:30:59","guid":{"rendered":"https:\/\/www.mjtnet.com\/blog\/?p=2995"},"modified":"2020-02-12T12:44:46","modified_gmt":"2020-02-12T12:44:46","slug":"setting-cell-functions-in-excel-plus-a-sneak-peak","status":"publish","type":"post","link":"https:\/\/www.mjtnet.com\/blog\/2020\/02\/12\/setting-cell-functions-in-excel-plus-a-sneak-peak\/","title":{"rendered":"Setting cell functions in Excel, plus a sneak peak!"},"content":{"rendered":"

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

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

You might set a literal value with something like this:<\/p>\n

\r\nXLSetCell>xlH,Sheet1,2,2,560,result\r\n<\/pre>\n
<\/p>\n
In Excel you insert a function by starting the input with the ‘=’ sign, so we can do the same with XLSetCell:<\/p>\n
\r\nXLSetCell>xlH,Sheet1,20,2,=SUM(B1:B19),result\r\n<\/pre>\n
<\/p>\n
In the same way we can force the cell to text format by preceding the input with an apostrophe:<\/p>\n
\r\nXLSetCell>xlH,Sheet1,2,2,'560,result\r\n<\/pre>\n
<\/p>\n
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.<\/p>\n
Coming Soon – More functions & run ANY VBA code!<\/h1>\n
A few people have requested more native XL functions. We’re working on it. <\/p>\n
For example, a new function to set the cell colour:<\/p>\n
\r\nRGB>50,150,50,color1\r\nXLSetCellColor>xlH,Sheet1,13,2,color1\r\n<\/pre>\n
<\/p>\n
But how about being able to run ANY<\/strong> valid Excel VBA you like?<\/h2>\n
\r\nXLRunVBA>xlH,ActiveSheet.Range(\"B12\").Interior.Color = vbRed\r\n<\/pre>\n
<\/p>\n
It could be a whole block of code:<\/p>\n
\r\nLabelToVar>vba_code,theCode\r\nXLRunVBA>xlH,theCode\r\n\r\n\/*\r\nvba_code:\r\n  Range(\"A1:B11\").Select\r\n  ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select\r\n  ActiveChart.SetSourceData Source:=Range(\"Sheet1!$A$1:$B$11\")\r\n  ActiveSheet.Shapes(\"Chart 1\").IncrementLeft +50\r\n  ActiveSheet.Shapes(\"Chart 1\").IncrementTop -30\r\n*\/\r\n<\/pre>\n
<\/p>\n
These features are in development. Watch them in action here:<\/p>\n