Range("A1").Value = 123
Range("A1") = 123

Unfortunately, VBA does not give you a simple mechanism by which you can specify a property to be the default.  VBA does, however, support default properties. You just have to jump through a few hoops to get there.  This page describes the steps to specify a default property in your classes.

Note: This works only in Excel 2000 and later.  It will not work in Excel 97.

Suppose you have a class named CMyClass with the following code:

'''''''''''''''''''''
' Private Variables
'''''''''''''''''''''
Private pValue As Long
Private pName As String

'''''''''''''''''''''
' Property Procedures
'''''''''''''''''''''
Property Get Value() As Long
    Value = pValue
End Property
Property Let Value(V As Long)
    pValue = V
End Property

Property Get Name() As String
    Name = pName
End Property
Property Let Name(V As String)
    pName = V
End Property
 

Without a default property, the following code will fail with an error 438 -- Object does not support this property of method. 

Dim MyClass As CMyClass
Set MyClass = New CMyClass
MyClass = 123
 

To make the Value property the default, follow these steps:

1) Save your workbook.

2) Open the CMyClass module in the VBA Editor.

3) From the File menu, choose Remove CMyClass.

4) When prompted to export the file first, choose YES. Save the module.

5) Open the exported file in Notepad, or your favorite text editor. (It is a simple text file with a cls extension.)

6) In Notepad, find your Property Get procedure.  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
 

7) Save the file in Notepad, and exit Notepad.

8) In VBA, choose Import File and select the file that you just modified.

That's it.  You can delete the exported file if you want -- it is no longer needed.

Note that you will not see the "Attribute" statement in the VBA editor. The editor reads and processes Attribute statements, but does not display them, nor does it allow them to be entered in the editor.

Now, the following code will work fine.

Dim MyClass As CMyClass
Set MyClass = New CMyClass
MyClass = 123
Debug.Print MyClass
 

Because you added the Attribute statement to the Property Get procedure, the compiler recognizes this as the default property.