If you have macros in Microsoft Word, Excel or Access they will be written in VBA – Visual Basic for Applications. If you wish to use this code inside Macro Scheduler you can convert this code to VBScript. However you cannot just copy the code and paste it into Macro Scheduler – or even into a VBScript file – and expect it to work, because there are some key differences:
- Objects belonging to the application are automatically exposed to VBA in that application, but don’t exist outside of it.
- VBA supports “Named Argument Syntax”. VBScript does not.
- Named Constants are automatically defined in the container application but meaningless outside of it.
So, let’s deal with each of these in turn and look at what we need to do about them.
Application Objects, Methods and Properties
Objects belonging to the container application – e.g. Excel – are automatically exposed to VBA within that application. All objects belonging to Excel can be used directly by VBA and your VBA can refer to them directly without having to create them. But outside of Excel these objects do not exist, so the code will fail – Macro Scheduler/VBScript doesn’t know what they are. You need to create them. This is done with the CreateObject command. At the very least you will need to create the “root” object which is usually the .Application object:
Set ExcelApp = CreateObject("Excel.Application")
Now you can refer to objects and properties belonging to Excel by proceeding them with the object variable:
ExcelApp.Visible = true
ExcelApp.Workbooks.Open("d:\example.xls")
ExcelApp.Workbooks.Worksheets("Sheet2").Activate
If you record a simple macro in Excel which enters some values into some cells you might see code like the following created:
Range("E4").Select
ActiveCell.FormulaR1C1 = "fred"
This code is clearly relative to the active sheet. It doesn’t specify which sheet should be used. And the Sheet object is clearly automatically exposed. To do the above outside of Excel we’d need to reference the correct Sheet object, which belongs to a Workbook object, which of course belongs to the Excel application object. So you could do:
ExcelApp.ActiveWorkbook.Sheets("Sheet1").Range("E4").FormulaR1C1 = "fred"
But you might split things up a bit with:
Set ExcelApp = CreateObject("Excel.Application")
Set MyBook = ExcelApp.Workbooks.Open("d:\example.xls")
Set MySheet = MyBook.Worksheets("Sheet1")
MySheet.Range("E4").Value = "Harry"
Note that the first three lines create references to the Application, Workbook and Worksheet objects. It’s then easier to refer directly to the objects, properties and methods belonging to each of those objects.
So where in a VBA macro inside Excel you may just see Range(“E4″).Value=”1234” consider what the Range object belongs to and remember you need to create a reference to that object, and then prefix it with the name you give to that object reference.
Each Office application includes a Visual Basic reference in the help system. In there you can see how all the objects refer to each other. E.g. if you find the help topic for the Range object you will see that it belongs to the Worksheets object.
Named Argument Syntax
VBA supports something called “Named Argument Syntax” in function calls (ArgName:=ArgValue). E.g. record a macro to sort a column in Excel and you will see something like this in the generated code:
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("F4:F1048576"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= xlSortNormal
Note Key:=Range(… and SortOn:=xlSortOnValues etc. The benefit of this system is that if a function accepts lots of optional parameters but you only need to set a few of them and leave the rest to their default values, you specify just the arguments you want to include.
However, VBScript does not support this system and instead requires all the function parameter values only in the order in which they are declared. So the above would look like this in VBScript:
MySheet.Sort.SortFields.Add MySheet.Range(F4:F1048576"), xlSortOnValues, xlAscending, xlSortNormal
As mentioned, Named Argument Syntax means that not all the parameters need to be passed to the method and because they are named can be passed in any order. So when converting to VBScript be sure to view the help for the function and find out what parameters it expects and in what order.
Named Constants
Named constants that belong to the application will also mean nothing to Macro Scheduler/VBScript. Inside of the application they belong to they are exposed to VBA. They mean something to VBA inside of the application. But take the code outside of the application and the names are meaningless.
For example the code above uses three named constants: xlSortOnValues, xlAscending and xlSortNormal. As these are declared automatically within Excel they mean something to VBA. Outside of VBA, in VBScript/Macro Scheduler they will cause errors because they are undeclared.
We need to declare these:
xlSortOnValues = 0 xlAscending = 1 xlSortNormal = 0
I know what you’re thinking – how do I know that xlSortOnValues equals 0, xlAscending is 1 and xlSortNormal is 0? Well, you could look them up. But I don’t bother doing that. I use the VBA debugger. Open up the Visual Basic Editor and hit CTRL+G to open up the “Immediate” pane – it may already be visible. Inside the “Immediate” pane type:
?xlSortOnValues
And press Enter. You’ll see the value of xlSortOnValues appear on the next line. Handy eh?
Conclusion
The key thing to remember is that your Office VBA is referring to objects that exist only within the application in question. Your Excel code can say just Range(“E5”) because it knows what a Range object is. Macro Scheduler has no idea what “Range” is unless you tell it. Once you understand that you need to create references to these objects, and look at the VBA help to understand the hierarchy of objects, the process of porting your code to VBScript should begin to make more sense.
You’ll find an example script that controls Excel with VBScript installed with Macro Scheduler. This post contains similar code. You’ll also find lots of examples in the forums. Hint: try searching for “CreateObject”.
