views:

277

answers:

2

I need to ensure a Macro which works on Visio 2003 doesn't cause problems on lower versions of Visio: specifically because I'm writing to a property which doesn't exist on lower versions of Visio. Currently I'm doing this:

...
On Error GoTo NoComplexScriptFont:
Set cellObject = shapeObject.Cells("Char.ComplexScriptFont")
On Error GoTo ErrHandler
...
NoComplexScriptFont:
    Rem MSGBOX only for debug
    MsgBox "No Such Property"
    GoTo endsub

ErrHandler:
    Rem put in general error handling here
    GoTo endsub

endsub:
End Sub


...

Which works, but its a little messy I think. I have toyed with the idea of using 'Application.version' (Which returns '11' for Visio 2003), but I would like to avoid assumptions about what properties are available in any particular release and just test for the property itself.

What's the nice proper idiom for doing this in VBA ?

Thanks

--- Got a few answers below, my prefered solution was this one:

If shapeObject.CellExists("Char.ComplexScriptFont", 0) Then
    msgbox "Property exists"
else
    msgbox "Property does not exist"
end if
+2  A: 

I would use a wrapper function for accessing the property so that you don't mess up your normal error handling, like this:

...
Set cellObject = GetCellObject(shapeObject)
If Not cellObject Is Nothing Then
   ' Do something with cellObject
End If
...

Private Function GetCellObject(ByVal shapeObject As Object) As Object
    On Error Resume Next
    Set GetCellObject = shapeObject.Cells("Char.ComplexScriptFont")
End Function

(Note: I'm only using Object above because I don't know what type cellObject etc. is)

I often use the same technique even for properties that I know do exist, but which will raise an error under certain circumstances. For example (Excel), if I'm going to access a worksheet by name (which will raise an error if no such worksheet exists), then I'll have a wrapper function that calls Worksheets(name) and either returns a Worksheet object or Nothing:

Private Function GetWorksheet(ByVal strName as String) As Worksheet
    On Error Resume Next
    Set GetWorksheet = Worksheets(strName)
End Function

This makes for much cleaner calling code, since you can simply test the return value rather than worrying about error handling.

Gary McGill
You should have a line that reads:Set GetCellObject = Nothingbefore actually performing the .Cells("Char.ComplexScriptFont") function...with the On Error Resume Next if the cell doesn't exist that line where you're setting GetCellObject =... will get skipped, and if the calling code uses the same variable multiple times (like in a loop) the code will end up using a cell from a previous shape in the loop...hope that makes sense...
Jon Fournier
Jon - I do understand what you *mean*, but you're wrong :-) Try it. When you enter a function, the function return is implicitly initialised (to Nothing, Empty, zero, or null-string depending on the return type). I would agree that the explicit statement might add clarity, however.
Gary McGill
A: 

You can use the CellExists property of the shape object to see if a particular cell exists. You have to pass in the localeSpecificCellName, which you already seem to be using, and then you pass in an integer fExistsLocally, which specifies the scope of the search for the cell; if you specify 0 then the CellExists will return true if the cell is inherited or not...if it's 1 then CellExists will return false if the cell is inherited.

Jon Fournier
John, sorry, but the second paragraph there is nonsense. The return value of the function will *not* preserve the value from the last time it was called.
Gary McGill
Thanks ! - it works - have edited my original question to include your solution in code-snippet form.
monojohnny
Jon Fournier