Friday, 17 February 2012

Using the sadly broken Excel Worksheet 'CustomProperties' collection

I think the comment explains it all.

Public Function GetCustomProperty(ws As Worksheet, PropertyName As String) As Variant

' Return the value of a user-specified worksheet property created as:
p' Ws.CustomProperties.Add Name:="Report Type", Value:"Balance Sheet"
' Return value is NULL if the named property does not exist

' Nigel Heffernan 07 April 2008

' Return the value of an Excel Worksheet Custom Property
' This is necessary because Microsoft have not implemented
' CustomProperties as a VBA collection: names are not indexed
' and the numeric position is NOT the ordinal: it's the
' position of the named property in an alphabetic sort by name.
' Maybe future releases will clear this up.

Dim i As Integer

GetCustomProperty = Null

For i = 1 To ws.CustomProperties.Count
If ws.CustomProperties(i).name = PropertyName Then
GetCustomProperty = ws.CustomProperties(i).Value
Exit Function
End If
Next i

End Function

Maybe this was fixed in 2010: it's still broken in Excel 2010. Some days, I think that Aspirin should be listed under 'Programming Tools'

1 comment: