Recording Excel macros for Macro Scheduler

Recording Excel macros for Macro Scheduler

Ever since we launched Macro Scheduler 15, we've been able to use XLRunCode to run Excel VBA code from within a Macro Scheduler macro.

Let's explore how we can record an Excel macro and make it execute in Macro Scheduler 15 :

Step 1.

The XLRunCode HelpFile says : In Excel, enable 'Trust Access to the VBA project object model"

Go to File/Options -> Trust Center -> Trust Center Settings -> Macro Settings. Under 'Developer Macro Settings' enable 'Trust Access to the VBA project object model'

You should only need to do this once.

Step 2.

Record your Excel macro.

In Excel :

Click "Developer Tools"

Click "Record Macro", "OK"

Now record your macro. When you're done, click "Stop Recording".

Like this :

And this is how we play back our recorded Macro :

Step 3.

Copy our Excel macro into Macro Scheduler

Click Developer Tools, Macros

Select your macro, and click Edit.

Now you can select and copy the VBA. We don't need sub, end sub, or the comments at the beginning.

Like this :

This leaves us with the following code :

Range("A1:B4").Select
Selection.Copy 
Range("E1").Select 
ActiveSheet.Paste 
Application.CutCopyMode = False 
With Selection.Interior 
.Pattern = xlSolid 
.PatternColorIndex = xlAutomatic 
.Color = 65535 
.TintAndShade = 0 
.PatternTintAndShade = 0 
End With 
With Selection.Font 
.Color = -16776961 
.TintAndShade = 0 
End With 
Selection.Font.Bold = True 
Range("C2").Select

Now we've got the code, we simply need to include it into our Macro Scheduler script. We do that using LabelToVar, and XLRunCode.

There is no need to save our Excel file and it does not need to be saved as a Macro Enabled Workbook - we simply used the built-in Macro recorder to generate our VBA code.

Let's explain LabelToVar. In the manual it says " Reads the contents of a data label into the specified variable".

In the example below we're taking the contents of the data label MyMessage and assigning it into the variable TheText. The contents of MyMessage is Hello World, so now TheText = Hello World.

LabelToVar>MyMessage,TheText
MessageModal>TheText

/*
MyMessage: Hello World
*/

We're going to do exactly the same with our VBA code. Let's not forget we need our script to open Excel first, so we can get the book handle we require. For this we can either use XLOpen to open the workbook or XLGet to assign a handle to an already open workbook :

We'll use XLOpen..

XLopen>d:\MacroExample.xlsx,1,xlH
LabelToVar>vba_code,theCode
XLRunCode>xlH,theCode
/*
vba_code:
Range("A1:B4").Select
Selection.Copy 
Range("E1").Select 
ActiveSheet.Paste 
Application.CutCopyMode = False 
With Selection.Interior 
.Pattern = xlSolid 
.PatternColorIndex = xlAutomatic 
.Color = 65535 
.TintAndShade = 0 
.PatternTintAndShade = 0 
End With 
With Selection.Font 
.Color = -16776961 
.TintAndShade = 0 
End With 
Selection.Font.Bold = True 
Range("C2").Select
*/

So now that we've recorded some VBA in Excel, copied the relevant parts to Macro Scheduler, and created a Macro Scheduler script around it, let's see it all in action :