Software Salariman: Converting VBA Programs to VBScript using SED

Writing VBScript programs can be a pain without an IDE with early-binding to ensure that the name and arguments of procedures are correct and you have start the Microsoft Script Debugger to debug your code separately from your editor. How very 90s! On the other hand, MS-Office has an IDE for writing and debugging VBA programs. What's more, since VBScript is very similar to VBA (and VB), it can be simpler to write a VBA program first, export it as a BAS file then convert it to VBS file (still very 90s but slightly less painful).

Below is a simple text converter using sed (the Unix stream editor), followed by an explanation of each rule. To use these rules, copy and save them into a file such as bas2vbs.sed, then run it using sed -f bas2vbs.sed <file>.bas > <file>.vbs. You should be familiar with sed regular expressions to follow the rules. The conversion isn't exhaustive or perfect but it works well enough for me.

/^Attribute/d
/^Declare/d
/^Option Base/d
s/ As [^,)]*//g
s/Next [^ ]*/Next /
s/^'Entry //

General Syntactic Conversions

/^Attribute/d

An exported VBA file has one or more lines of metadata such as Attribute VB_Name="<module>". These metadata lines aren't supported in VBScript, so this rule deletes them.

/^Declare/d and Sleep

The Windows Scripting Host (WSH / WScript) includes a useful function called Sleep() which has an equivalent function in kernel32.dll. To make the kernel32.dll function visible in VBA, you declare it like this:

Declare Sub Sleep Lib "kernel32" (ByVal dwMiliseconds As Long)

then use it like this:

Sleep(1000)

VBScript doesn't support the Declare keyword, so that declaration is deleted. When the VBSscript script is run, the call to Sleep(n) function calls WScript.Sleep(n).

/^Option Base/d

In VBA, you can specify the default lowest index value for your variables. Coming from programming in C-style languages, I habitually define Option Base 0 so that my arrays start from index 0. In VBSript, array indexes start from 0 and the Option Base declaration is not supported, so that declaration is deleted from the VBS code.

s/ As [^,)']*//g

If you declare the type of your variables, the VBA IDE can auto-complete the class constants, properties or methods relating to your variables using early-binding. If you use Option Explicit, you have to declare variable names but all VBScript variables are type Variant so the type name after the variable is not required. This sed rule deletes the type name from statements (e.g. Dim x as type1, y as type2 becomes Dim x, y) and procedure definitions (e.g. Sub f (ByVal a as type3, ByVal b as type4) becomes Sub f (ByVal a, Byval b)).

s/Next [^ ]*/Next /

VBA allows you to specify the control variable to advance in a For...Next statement, e.g. the variable i in For i ... Next i. Specifying the control variable after Next is optional but I use it to make loops (especially nested ones) easier to read. VBScript doesn't allow a control variable after the Next keyword, so the variable string has to be deleted.

Inconsistently, the control variable is allowed after Next in For Each...Next statements in VBA and VBScript!

Environment or Library Conversions

s/New \([^ ]*\)/CreateObject("\1")/

You can create new ActiveX objects in VBA using the New keyword. This keyword isn't available in VBScript so you have to use the CreateObject() function instead. The rule just takes the string after the New keyword and makes it an argument for the CreateObject function. However, the two strings aren't necessarily the same (see below).

You can create new ActiveX objects in VBA using the New keyword. This keyword is available in VBSCript (though it's not listed) to create new instances of classes but you have to use the CreateObject() to create an ActiveX object in VBScript.

s/Shell32.Shell/Shell.Application/

A VBA project may include the Microsoft Shell Controls and Automation library in VBA to interact with the Windows Shell. To use the Windows Shell in VBA, you could write Set ws = New Shell32.Shell (the namespace for this library is Shell32 and Shell represents Windows Shell class). Since the New keyword is not available in VBScript, the CreateObject rule and this rule would write Set ws = CreateObject("Shell.Application"), where Shell is the name of the automation server and Application is type of object to create.

Convenience Conversion

s/^'Entry //

A VBScript module requires some entry point that takes command-line arguments or to run a procedure, for example DoSomething WScript.Arguments(0). You can't add this statement by itself into a VBA module (all statements have to be within a procedure) so this convenience rule takes any comment starting with 'Entry and removes that prefix, leaving you with a VBS statement.

Software Salariman

2010-11-27

Converting VBA Programs to VBScript using SED