Generic Way to Determine if Invoking a Property Throws an Error

Question

Say you have one slide with one chart on it, and you run this code(in a version of Office later than 2007):

Dim pptWorkbook As Object
Dim result As Object

Set pptWorkbook = ActivePresentation.slides(1).Shapes(1).Chart.ChartData.Workbook
Set result = pptWorkbook.ContentTypeProperties

You will generate an error:

Application-defined or object-defined error

I believe this is because "Smart tags are deprecated in Office 2010."(Source), Generally to avoiding this sort of issue from throwing an error and exiting your VBA you can take one of two different approaches:

//Method 1
If OfficeVersion <= 2007
    Set result = pptWorkbook.ContentTypeProperties

//Method 2
On Error Resume Next // or GOTO error handler
Set result = pptWorkbook.ContentTypeProperties

Method one requires that you know the specific reason why the property would cause an error, which is easy in this case but may not be as easy with other properties. Method two requires that you use some form of error handling to deal with the error AFTER the fact, my understanding of most other Microsoft languages is that is typically discouraged(example, another example). Is this standard practice in VBA?

In VBA, is there any other way to determine whether a property of an object would throw an error if invoked, BEFORE invoking that property, and without knowing the specifics of that invoked property?

This may help you: freevbcode.com/ShowCode.asp?ID=2441&NoBox=True — Sorceri
@Sorceri I believe the property in this example does exists, however invoking that property results in an error being thrown. — David Rogers
Basically no, as far as I know you have to anticipate, trap and deal with the error; hence the age old way of determining if a worksheet exists: On error resume next, if err.number <> 0 sheet exists, resume 0 etc — Absinthe

TheSilkCode TheSilkCode · Accepted Answer · 2017-01-17T05:09:52

What I like to do for this situation is create a separate function that checks if the property exists and returns a Boolean. In this case it would look something like this:

Public Function CheckIfExists(targetObj As Object) As Boolean
Dim testObj As Object
On Error GoTo failedTest:
Set testObj = targetObj.ContentTypeProperties
CheckIfExists = True
Exit Function
failedTest:
CheckIfExists = False
End Function

Which would return false if that property causes an error and true if not-

Then modify your sub to be:

Public Sub FooSub()
Dim pptWorkbook As Object
Dim result As Object

Set pptWorkbook = ActivePresentation.slides(1).Shapes(1).Chart.ChartData.Workbook
If CheckIfExists(pptWorkbook) Then
    Set result = pptWorkbook.ContentTypeProperties
End If

... rest of your code or appropriate error handling...

Hope this helps, TheSilkCode

Generic Way to Determine if Invoking a Property Throws an Error

1 Answers