Excel VBA: Default Property Of A Class

Bullen’s book has better example.

Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA®

By: Stephen Bullen; Rob Bovey; John Green

Publisher: Addison-Wesley Professional


Add the following line of code on a blank line immediately following the Property Get statement.

Attribute Value.VB_UserMemId = 0

Now, your Property Get  procedure will look something like the following.  The line in bold is what you added.

Property Get Value() As Long
Attribute Value.VB_UserMemId = 0
    Value = pValue
End Property


  MultiUse = -1  'True
Attribute VB_Name = "MyCollection"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Explicit

Private m_collection As Collection

Property Get Item(ByVal id As Variant) As Variant
Attribute Item.VB_UserMemId = 0
    Set Item = m_collection(id)
End Property

Public Function NewEnum() As IUnknown
Attribute NewEnum.VB_UserMemId = -4
    Set NewEnum = m_collection.[_NewEnum]
End Function

Public Sub Add(Item As Variant, Optional key As Variant, Optional Before As Variant, Optional After As Variant)
    Call m_collection.Add(Item, key, Before, After)
End Sub

Public Property Get Count() As Long
    Count = m_collection.Count
End Property

Public Sub Remove(Index As Variant)
    Call m_collection.Remove(Index)
End Sub

Private Sub Class_Initialize()
    Set m_collection = New Collection
End Sub

Private Sub Class_Terminate()
    Set m_collection = Nothing
End Sub

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s