Expression statements in VBA

Question

I was recently trying to write an Excel macro, and I needed to determine whether a particular worksheet existed. My weapon of choice for scripting is Python, and my VBA skills are admittedly poor. So, in good pythonic style, I took the Easier to Ask Forgiveness than Permission approach and and wrote something like this:

Debug.Print MyWorkbook.Worksheets(TabName).Name  ''Check for an index error
If Err.Number <> 0 Then
    ''Code to add the sheet to the workbook

This worked great until I tried to eliminate the print statement. I changed the first line to:

MyWorkbook.Worksheets(TabName).Name

And suddenly I started getting "Object doesn't support this property or method" errors. As a Python programmer this surprised me, I do similar things in Python all the time.

I was curious so I did a little reading. I was able to find documentation about expression statements like the above in Python, but not in VBA. Are expression statements simply not allowed in VBA? If so, is there a reason why they aren't? Does anyone know where I can read about this?

If you assign the worksheet name to a variable, does that solve your issue? — Marc, Apr 18 '12 at 04:02

score 2 · Accepted Answer · edited May 23 '17 at 12:21

The short answer is "no". VBA is imperative all the way down. In general, expressions aren't legal standalone statements. You'll notice that many expressions won't even "compile":

'Won't "compile"
(42+99)

In your specific example, there's a little more going on. VBA sees the MyWorkbook.Worksheets(TabName).Name on this line the way you're intending, as an expression that returns a value:

'Fine: gets value of `Name` property
Debug.Print MyWorkbook.Worksheets(TabName).Name

VBA sees the same code on this line:

'Error: tries to call `Name` as method
MyWorkbook.Worksheets(TabName).Name

as an attempt to call the (non-existent) Name method on a worksheet object. (Name is a property of Worksheet.) Thus the error message, which makes more sense now that you know what is going on.

As far as reading more about this, you could see here:

https://stackoverflow.com/a/1545024/58845

There's a link there to a VBA language spec, which presumably covers this sort of thing more precisely, although I didn't check:

http://msdn.microsoft.com/en-us/library/dd361851%28PROT.10%29.aspx

score 0 · Answer 2 · answered Apr 18 '12 at 22:10

In VBA, u cannot write a line of variable without actually using it. Like @Marc said, u just need to assign the name to another variable.

Besides, normally I use the following function to check:

Public Function CheckWorksheetExists(wsNams As String) As Boolean

    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Thisworkbook.Worksheets(wsName)
    CheckWorksheetExists = err.Number = 0

End Function

Expression statements in VBA

2 Answers2