Custom Property Object

When you write VBA procedures, you often need to store specific information regarding a worksheet. Excel offers many ways to preserve information for later use. For example, you can store information in worksheet-level range names or hidden worksheets, or you can write it directly to the registry. The fourth, and newest, method is storing information using custom properties. A CustomProperty object can store information with a worksheet or SmartTag. Use the Add method of the CustomProperties collection to add custom property information and return a CustomProperty object. You must specify the name and value of the custom property. The following example procedure demonstrates how to store student names and scores shown below as custom properties.

StLident Name_Scores

Ann Mat luck_133

Tonya Walsh 149

Sub StoreScores()

Dim mySheet As Worksheet

Dim custPrp As CustomProperty

Dim i As Integer

Dim rng As Range

Dim totalCount As Integer

Set mySheet = Application.WorkbooksCSpecial.xls"). _ Worksheets("Speech")

' find out if custom properties exist If mySheet.CustomProperties.Count > 0 Then ' Display custom properties totalCount = mySheet.CustomProperties.Count For i = 1 To totalCount

With mySheet.CustomProperties(l)

Debug.Print .Name & vbTab; .Value Set rng = mySheet.Range("A:A").Find(what:=.Name) ' Delete the custom property If Not rng Is Nothing Then .Delete End With

Next End If mySheet.Activate Cells(2, 1).Select Do While ActiveCell <> ""

If Not IsEmpty(ActiveCell) Then

Set custPrp = mySheet.CustomProperties.Add( _ Name:=ActiveCell.Text, _ Value:=ActiveCell.Offset(0, 1).Text) Debug.Print custPrp.Name & vbTab & custPrp.Value ActiveCell.Offset(1, 0).Select End If

Loop

If mySheet.CustomProperties.Count > 0 Then ' Display custom properties

For i = 1 To mySheet.CustomProperties.Count With mySheet.CustomProperties(i)

Debug.Print .Name & vbTab; .Value End With

Next End If End Sub

0 0

Post a comment