I went off on a rant again, so if you want to get straight to the meat and potatoes, click here.

VBA is pretty powerful, but today will explore something that isn’t nearly as powerful but just as useful. This might seem like a waste of time, but that’s because my ability to persuade others is second to none.

The ‘something’ we will explore today is called Visual Basic Script.

It’s a subset of VBA and works quite well on its own. Of course, it comes with limitations since it’s a subset i.e., it doesn’t possess all features of VBA, because if it did then we’d call it VBA.

An Incredibly Brief History of VBScript

VBA is the bonsai tree from which VBScript sprang forth.

Microsoft decided to trim and prune VBA in the hopes that it would evolve into a language that could enhance web pages i.e., make stuff dance around the webpage as you interact with it.

Only problem is Microsoft didn’t realize that the Bonsai tree had to be carefully pruned. They went at it like it were a piñata and lo and behold – VBScript.

If that wasn’t bad enough, the only browser that supported VBScript was Internet Explorer.

Microsoft ditched both of them.

You: “I Have Several Questions, and I’ve Listed Them Below.”

#1 Isn’t VBScript dying?

#2 Why waste time learning something that won’t be relevant in the next five years?

#3 If it’s just like VBA, then why not use VBA instead?

#1 Yes, VBScript is dying, but so are you.

.

.

.

Ok, I guess emotional blackmail isn’t going to work here so let’s try that again.

Addressing Your Concerns

#1 Yes, VBScript is dying, but it’s not dead. It’s still has some fight left so give it a chance to prove itself before it passes on into the great beyond.

#2 Every language you learn has a chance of going the way of the COBOLosaurus. Nothing in life is certain, and you can be certain of that.

VBScript is still used in systems administration wherever PowerShell is not installed. It works really well with excel spreadsheets and databases.

#3 You’ve missed the point.

You don’t have to learn VBScript.

What you will learn is to transform VBA code into VBS and leverage it properly in Automation Anywhere.

I prefer showing how they can be useful, as opposed to wording them out and boring you to death. Examples breathe life into the abstract, not extensive documentation.

You: “Sorry, But I’m Not Convinced Yet.”

You drive a hard bargain.

Visual Basic Script, or VBS for short, is a subset of Visual Basic for Applications which means you won’t have to learn anything new since the syntax is pretty much the same, with minor modifications.

This also means you can’t access functionalities like named arguments or labels. Also, converting complex VBA codes to VBScript is a bad idea so banish the thought.

There isn’t a proper IDE for VBScript, so don’t even think about converting complex VBA code to VBS. VBSEdit is the only one I was able to find online, and it’s a paid software no one pays for because no one uses VBScript anymore.

I still use it though, since it lets you run VBS as long as you are willing to wait 3..4…5 seconds each time for it to run.

No I didn’t lose my marbles (at least not yet) and you will understand what I meant by that after downloading it.

I know I was supposed to sell you on why VBScript is useful but you have to understand why people are hesitant to learn it in the first place.

Flawed reasoning at work, and that’s because I happen to love red herrings. Maybe that’s why they booted me off the Presales team. Oh yeah, I was in Presales before all of this happened.

Now I automate stuff because I love getting my hands dirty with technical stuff, and blog about it while I’m at it because I still have a little bit of that presales inside of me.

The reason I stated that VBScript is “useful” is because Automation Anywhere provides a scope for us to run VBS code with. This means we can either import a library of Functions from a text file or store it within the scope itself.

And What Do Functions Return?

Still You: “I Dunno”

Anything you want.

Think of it as an oven which bakes delicious cookies for us, except these cookies are inedible and occupy virtual space.

Using this we can skip the extra steps we had to implement in VBA and focus on just the function logic.

What Did We Do in VBA Again?

We had to modify the script to write data back to the template, which the bot would then pick up and analyze to determine the macro’s execution status. The modification involved wrapping the entire script within a Error Handler block using On Error Statements. Go ahead and check it out if you want to refresh your memory.

Since we can’t use Labels, we have to rely on On Error Resume Next.

On Error Resume Next

And like we did in VBA, we can check if an error has occurred.

Function ReviveCobol()
On Error Resume Next
  Set xlObj = CreateObject("CobolLivesMatter.Application")
  If Err.Number <> 0 Then
    ReviveCobol = Err.Description
End If
End Function

Remember, we can invoke VBScript Function from Automation Anywhere, which means we can return results back to Automation Anywhere. The way that’s done is by assigning the Return Value to the Function Name.

We can also raise errors just like we did in VBA, but since we are dealing with simple VBScripts, that won’t be necessary.

Parameterized VBScript Functions

If your function must accept a single parameter, then its pretty straightforward. Simply pass in a String Variable like so:

Function Message(str_parameter)
  MsgBox str_parameter
End Function

If it has to accept multiple parameters, you can’t pass in the arguments like so:

Function formatColumn(str_wbPath, str_sheetName,str_columnIndex,str_format)

For it to work with Automation Anywhere, you have to use a List Variable.

Only a single List argument should be provided. The items from the List are referenced by index, so keep that in mind as you go about developing your script.

Function formatColumn(lst_args)
	Set xlObj = CreateObject("Excel.Application")
	xlObj.visible = False

On Error Resume Next
	Set xlFile = xlObj.Workbooks.open(lst_args(0))
	Set shName = xlFile.Worksheets(lst_args(1)) 
	xlObj.ActiveSheet.Columns(lst_args(2)).Select
	xlObj.Selection.NumberFormat = lst_args(3)
	xlObj.ActiveSheet.Range("A1").Select

	xlFile.Save
	xlFile.Close
	xlObj.Quit
	
If Err.Number <> 0 Then
	formatColumn = Err.Description
	
Else
	formatColumn = "Success"
End If

As you might have probably noticed by now, I haven’t initialized any variables and that’s because I haven’t specified Option Explicit On. The complier will treat each variable as a Variant without me having to worry about it.

You can do this in VBA as well, but its better to leave it on since VBA code can get pretty complex and treating everything as a Variant will not only make it harder to understand the code, but it also consumes more space.

Enums in VBScript

Enums are constants that we didn’t have to worry about while writing VBA code. Operations that you might be fairly acquainted with like xlUp, xlDown etc can’t be directly used in VBScript – we need enums to use them.

We usually assign enums to constant variables like so:

const xlUp = -4162
rowCount  = Sh.Range("A" & Sh.Rows.Count).End(xlUp).Row

But where can I find them?

There are two ways of fetching the enum values. First is by browsing through the Microsoft Docs, and the other is by adding a Breakpoint, running VBA in Debug Mode, and Hovering your mouse over the enum of interest.

I prefer the latter since I usually develop VBA before converting them to VBS.

And that concludes our short introduction to VBScript Functions.

Visual Basic “SCRIPT”

If you don’t want VBS to return anything back to the Automation Anywhere, then you can create a Script instead, but I’d recommend sticking with Functions since it can be designed to customize error messages.

Scripts return “true” if everything goes well, and “bot error” when it runs into an error. The errors can’t be customized, and sometimes it won’t catch the error and return true, so its not reliable.

When using Scripts, you can only pass both String and List Variables. If you have to pass in multiple parameters, you can use add items to a List Variable…

Or use the String Variable instead. Interested to know how?

"Hello" "World" "!"

You have to add them together but delimit them with spaces.

Its not necessary to surround the parameters in quotation marks. The only time that is necessary is when the parameters themselves contain spaces like File Paths for example.

In Conclusion

VBScript might not be as great as VBA, but its still easy to integrate with Automation Anywhere than it is with VBA. I’ve just recently started exploring Custom Packages, and I’ll be putting out a series of articles on that pretty soon.