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 replacing
```
debug.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 with wscript.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]