Search code examples
pythonvbalanguage-theory

Expression statements in VBA

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?

Solution

  • 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