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]