six demon bag
Wind, fire, all that kind of thing!
2014-06-07
Translate VBA to VBScript
Since I'm seeing lots of questions like "how can I do FOO in Excel/Word/... with VBScript" I thought I'd post some guidelines on how to approach this kind of task.
In general, VBA and VBScript are quite similar, so most of the time you're better off recording a VBA macro and translate that to VBScript than writing the whole thing in VBScript from the get go. There are some notable differences between the two languages, though, which you need to observe when translating VBA to VBScript.
Obviously the most notable difference is that in VBScript you need to create an instance of your Office application to begin with:
Set xl = CreateObject("Excel.Application")
whereas with VBA the application is already running, as it provides the environment for your macro.
Beware that Office applications, unlike many other objects you use in a VBScript, do not automatically terminate when the script ends. You need to manually terminate them
xl.Quit
otherwise they'll keep running (usually unnoticed, unless you made them visible). A way to work around this issue and make Office applications terminate automatically with the script is to wrap instance creation and termination in a custom class:
Class Excel Public obj_ Private Sub Class_Initialize Set obj_ = CreateObject("Excel.Application") End Sub Private Sub Class_Terminate For Each wb In obj_.Workbooks wb.Saved = True 'discard unsaved changes wb.Close 'close workbook Next obj_.Quit End Sub End Class Set xl = New Excel Set wb = xl.obj_.Workbooks.Add '...
There are no implicit objects in VBScript. You must have a handle to each object whose methods or properties you want to access. For instance, a VBA statement
ActiveWorkbook.Close
implicitly uses the
Application
object, making this the effective statement:Application.ActiveWorkbook.Close
In VBScript you need to explicitly provide the application object you created before:
xl.ActiveWorkbook.Close
VBScript doesn't recognize VBA constants. You either have to define them in your script:
Const xlTop = -4160 xl.Selection.VerticalAlignment = xlTop
or you have to use the numeric value of the constant:
xl.Selection.VerticalAlignment = -4160
VBScript doesn't support named arguments in method calls. Instead of
ActiveWorkbook.SaveAs Filename:="some.xlsx", FileFormat:=xlWorkbookDefault, CreateBackup:=False
you have to provide all arguments in the correct order. Optional arguments that you want to leave at default values can remain empty, and a trailing list of empty arguments may be left out completely:
xl.ActiveWorkbook.SaveAs "some.xlsx", 51, , , , False
While the above should cover what you'll normally encounter with recorded macros, there are some other notable differences that you'll probably have to deal with when translating hand-written macros, particularly:
VBScript doesn't support typed variables. Variable definitions like
Dim foo As String
will raise an
Expected End of Line
error. You need to remove the type from the definition:Dim foo
There's no
debug.Print
statement in VBScript. You need to replace statements writing debug output to the Immediate Window with your own debug output routines. In many cases simply replacingdebug.Print "some text"
with
WScript.Echo "some text"
will suffice.
A word of warning, though: since
WScript.Echo
raises a popup window when you run the script withwscript.exe
(the default) you need to take into consideration what the rest of the code actually does. If for instance your debug statement is inside a loop it's a good idea to write the output to a log file or the eventlog instead, because otherwise you might end up spending a lot of time clicking away debug messages.Error handling in VBScript is more restricted than it is in VBA. You can't jump to line labels on errors in VBScript. Instead of
On Error Goto LABEL 'do things that might fail On Error Goto 0 ' 'more code ' LABEL: 'error handling routine Resume
you must use
On Error Resume Next
to enable error handling and handle errors inline:On Error Resume Next 'do things that might fail If Err Then 'error handling routine End If On Error Goto 0 ' 'more code '
Posted 15:26 [permalink]