Now that you are convinced of VBA’s potential and how useful they can be, it’s time we learned how to use them. This is important since running Macros is not as straightforward as running python or JavaScript.

No scopes are provided within Automation Anywhere for VBA like it is for VBScript, Python or JavaScript in Automation 360.

You can only run macros present in a macro-enabled workbook, i.e., you can add the macros during execution then execute them, nor can you simply run a VBA script and expect it to work, so things can get a little tricky when working with xlsx or xls files.

What About VBScript?

While there is a scope provided for VBScript, working with it is nowhere as convenient as coding in VBA.

VBScript is a subset of VBA which means we can perform excel automation with it, however it comes with several limitations. You can’t use Labels in VBScript and you have to declare constants for excel Enums like xlUp or xlDown.

We will explore Labels and Enums in a moment so don’t panic.

There isn’t much of a community support for VBScript either. For every topic on VBScript, you will find at least 10-20 posts on VBA. This means it will take less time to develop solutions on VBA than it will with VBScript.

That being said, if the automation doesn’t involve anything too complex, we can think of VBScript. I’d recommend sticking with VBA and the reason I say that is because you can convert VBA to VBScript.

VBScript is a subset of VBA after all, and once you are confident working with VBA then converting them to VBScript will be a cakewalk.

Can we Run Macros on Non Macro-Enabled Workbooks?

Like I’ve mentioned before, if you want to run VBA code on excel workbooks, that can only be done if the workbook contains the macro you want to run.

However, workbooks can only contain macros if it’s macro-enabled.

Quite the predicament, right?

But then again, I wouldn’t be writing this if I didn’t have a solution for this now would I?

The Answer is Pretty Straightforward

If you can’t run macros on a workbook because it isn’t macro-enabled, then you can play it smart and create a macro-enabled workbook that will house a library of macros you want to run.

This template can be treated as a dependency and stored in the Control Room, which gets downloaded to the Bot Runner Machine during execution.

“But TCT, I Want to Run Macros on Other Files!”

And you will.

Macros aren’t very demanding – all they require is a roof over their head. Once they receive that, they will work with anyone.

They’ve got to pay that rent somehow right?

What’s That Supposed to Mean?

Macros can work on other workbooks as well.

Subroutines accept parameters, even if it doesn’t return anything.

What, that’s news to you? Go back and read the Introduction!

.

.

.

.

.

Where were we?

Ah yes, all we have to do is reference the file we want and the macro will do its magic.

Sub openWb(filePath As String)
Dim wb As Workbook
Set wb = Workbooks.Open(filePath)
End Sub

Quite the game changer, ain’t it?

With this, you can run macros on other workbooks!

Can I Run Macros on Static Files?

You can, but it wouldn’t make sense to run macros on csv, tsv or even text files, unless you are fetching data from it. Also, it’s not like you can format/highlight cells, or create charts or graphs with them. They are called static files for a reason.

If you want to query data from it, try using the CSV/TXT Actions instead. If the file is large, then connect to it like it were a database using Database Actions.

Don’t mix VBA into it.

If you are really curious, then use you can use ActiveX Objects or ADO in short, with VBA to work with delimited files. Here is a tutorial on that.

WiseOwlTutorials has some really great tutorials on VBA and Python so I’d recommend checking out his channel.

If you are looking specifically for Python, then Corey Schafer’s channel is the place to start.

Using Macros the Right Way

Okay, so we can run macros, but what is the right way to go about developing them?

What happens if you don’t develop them properly?

All I need is to surround the Macro Actions with Error Handlers in Automation Anywhere right?

That ought to take care of it.

News Flash – It Won’t.

When a macro is triggered, Automation Anywhere waits until she finishes execution.

If the macro encounters an error, it generates a dialogue box which looks something like this:

The comments are quite hurtful, but you want to know what’s even more hurtful?

The headache it generates. No not the error message, but the message box itself.

Manual intervention is required to close these message boxes, because it originates from VBA. This is also why it errors from Macros can’t be handled. Even if the macro runs into an error, Automation Anywhere assumes that she is still at it and will diligently wait until she finishes.

How To Handle VBA Errors

Ok I lied.

Well sort of, but not really.

VBA Errors can be handled, but it has to be handled within VBA itself. Don’t worry, I’ll walk you through that, and also present you an example of the Labels and Enums as promised.

We don’t have any Try-Catch blocks in VBA. The entire block is preceded by what’s called On Error Statements. There are four types of Error Statements, out of which only two look useful (to me).

Sub ActivateSheet()
Dim Sh As Worksheet
Dim rowCount As Long
  On Error Resume Next
  Set Sh = ThisWorkbook.Worksheets("IDontExist")
  Sh.Activate
  rowCount = Sh.Range("A"& Sh.Rows.Count).End(xlUp).Row
  Sh.Activate
End Sub

xlUp is whats called an Enum. Knowing what they are right now isn’t really important, but it will be when we switch over to VBScript. For now, you can think of them as Integers Constants.

You can see the values they hold when you run VBA in Debug Mode:

On Error Resume Next skips the error, plain and simple.

This is useful only if you are absolutely sure about the sort of error you will run into. In our example, we will run into an error if we try to activate a sheet that doesn’t exist.

Actually, it will error out as soon as we set it to the Worksheet Object, but you get the point.

The way this is handled is like so:

Sub ActivateSheet()
Dim Sh As Worksheet
Dim rowCount As Long
  On Error Resume Next
  'Error happens here
  Set Sh = ThisWorkbook.Worksheets("ISaidIDontExist")
  Sh.Activate
  rowCount = Sh.Range("A"& Sh.Rows.Count).End(xlUp).Row
  If Err.Number <> 0 Then
  MsgBox "The Sheet does not exist."
  End If
End Sub

If the process runs successfully, the Error Number remains zero.

While this is somewhat useful, we won’t always be building simple macros. For anything complex, we have to rely on another Error Statement which is capable of handling multiple errors and report it back to the main automation.

I’ve used a Message Box to report the error in the example, but I’ll show you how we can inform Automation Anywhere that an error has occurred while running the macro AFTER I’m done with this.

On Error, Go Somewhere~

In VBA, we have Labels.

You can think of them as checkpoints your automation navigates to when it loses all its lives battling against the final boss. It allows your code to jump to another section within the same Subroutine/Function.

If you want to return the error message to Automation Anywhere, or create customized error messages you know could popup, then labels come in handy.

Its represented like so:

Sub ActivateSheet()
Dim Sh As Worksheet
  On Error GoTo ErrorHandler
  Set Sh = ThisWorkbook.Worksheets("AreYouEvenListeningToMe")
  Sh.Activate

ErrorHandler:
  If Err.Number <> 0 Then
  MsgBox "The Sheet does not exist."
  End If
End Sub

The Label can be added anywhere, but since it handles errors, I have positioned it towards the end of the subroutine. Our work is not done – we have to modify this some more for it to work as expected.

First, we have to understand that VBA doesn’t like leaving any line of code unexecuted. This means, we have to explicitly inform VBA to leave them alone.

To put things into perspective, if the sheet exists, then VBA will not only activate it, but it will also enter the ErrorHandler Block. This can be handled by added Exit Sub before the Label.

Sub ActivateSheet()
Dim Sh As Worksheet
  On Error GoTo ErrorHandler
  Set Sh = ThisWorkbook.Worksheets("Helloooo")
  Sh.Activate
Exit Sub
ErrorHandler:
  If Err.Number <> 0 Then
  MsgBox "The Sheet does not exist."
  End If
End Sub

You thought we were done?

Oh no no no.

We haven’t customized any errors until now…but why customize them in the first place?

Let me show you why.

As you can see, VBA Error messages are quite descriptive.

I mean, that’s exactly what would cross my mind if I were to access a worksheet that didn’t exist – word for word.

“Oh yeah, Subscript out of range! Gosh no wonder it didn’t work!”

With Labels, you can handle the errors we are likely to encounter and substitute the default descriptions with something that actually makes sense:

Sub ActivateSheet()
Dim Sh As Worksheet
  On Error GoTo ErrorHandler
  Set Sh = ThisWorkbook.Worksheets("IKnowYouAreReadingThis")
  Sh.Activate
Exit Sub
ErrorHandler:
  If Err.Number = 9 Then
  MsgBox "The Sheet does not exist."
  End If
End Sub

We know that VBA spits out an error code of 9 when accessing sheets that don’t exist, but this is a problem because error code 9 isn’t unique to just this scenario.

This is a problem…actually it isn’t.

You just have to get a little fancy with your VBA and Raise Exceptions wherever necessary.

Sub ActivateSheet()
Dim Sh As Worksheet
Dim containsSheet As Boolean

    containsSheet = False
    On Error GoTo ErrorHandler
    For Each Sh In ThisWorkbook.Worksheets
    If Sh.Name = "OkFineIgnoreMeThen" Then
        containsSheet = True
    End If
    Next Sh
    
    If Not containsSheet Then
    Err.Raise Number:=100 + vbObjectError, description:="The sheet does not exist!"
    End If

Exit Sub
ErrorHandler:
  MsgBox Err.description
  
End Sub

Using the Raise Method, we can create custom error messages for specific scenarios, as opposed to creating a one size fits all error message for each error code.

Reporting Back To Automation Anywhere

Now that we know how to handle errors in VBA, we will look at how those errors can be passed back to Automation Anywhere.

Since we are running Subroutines, we can’t return anything. What we can do, is make changes to the Workbook Template, either by coloring cells, or by add text to detail the execution results, which Automation Anywhere can pick up and validate.

Sub ActivateSheet()
Dim Sh As Worksheet
Dim containsSheet As Boolean

    containsSheet = False
    On Error GoTo ErrorHandler
    For Each Sh In ThisWorkbook.Worksheets
    If Sh.Name = "OkFineIgnoreMeThen" Then
        containsSheet = True
    End If
    Next Sh
    
    If Not containsSheet Then
    Err.Raise Number:=100 + vbObjectError, description:="The sheet does not exist!"
    End If

  Sh.Range("A1").Interior.Color = vbGreen
  Sh.Range("B1") = "Success"

Exit Sub
ErrorHandler:
  Sh.Range("A1").Interior.Color = vbRed
  Sh.Range("B1") = Err.description
  
End Sub

We can either retrieve the color or the description which will tell us about the macro’s activity.

It would also be smart to create another Subroutine which clears the cells right before the main macro executes. We can get even fancier with it and code in a Subroutine that improves performance.

In short, there is a lot we can do to not only handle VBA errors, but also improve its performance.

As much as I’d love to explain how that works, we have already breached the 2000 word limit and there is a good chance that only TCT has made it this far.

In Conclusion

Phew! That was a lot to cover.

As you can tell, VBA is quite cumbersome to work with, but everything becomes smooth once you get a hang of it. I know not many use VBA because of his supposed malicious intent, but give it a try.

He doesn’t bite.

Also, here is a special bonus for those of you who managed to make it towards the end.

We will cover VBScript next week, so you better brush up on your VBA skills by then or else you won’t understand a damn thing.