VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
END
Attribute VB_Name = "Constructor"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Private myName as String

Private Sub Class_Initialize()
    myName = Cstr(Int((100) * rnd + 1))
End Sub

Public Property Get Name() as String
    Name = myName
End Property

Public Property Get PieceOfMe(Optional value as String) As Constructor
    Attribute Item.VB_UserMemId = 0
    myName = value
    Set PieceOfMe = Me
End Property

Option Explicit

' vba4all.com
Public Enum TestType
    eStatic
    eInstance
    [_Min] = eStatic
    [_Max] = eInstance
End Enum

Sub Main()

    ' Debug.Print Constructor
    ' *NOTE  : fails because Constructor's default GET returns an instance of Constructor
    ' Constructor.Name = "newName"
    ' *NOTE2  : Explicit assignment of the .Name property not possible (removed to improve the class encapsulation)
    '         : To assign a new name you have to use the default property i.e Set T = T("New Name")

    Dim testOption As TestType
    Dim i As Long

    ' * I am only presenting 2 tests so the Enum thingy is a bit of an overkill
    For i = TestType.[_Min] To TestType.[_Max]
        testOption = i

        ' reset static defualt
        Set Constructor = New Constructor

        ' Singleton pattern - as a VBA static class
        If testOption = eStatic Then
            Debug.Print "***STATIC"

            ' whenever calling this it will always return the defualt (random number)
            Debug.Print Constructor.Name, "Default call to Constructor.Name"
            ' unless it has been changed by one of the following calls throughout the life of the Excel application
            '   *this call pretty much means that if you ever change the default this call will always reflect the last change

            '  ****
            '   *** SETTING THE DEFAULT OR AN INSTANCE BY CALLING THE CLASS NAME AND PASSING PARAMETERS
            Set Constructor = Constructor("foo")
            Debug.Print Constructor.Name, "Set Constructor = Constructor(""foo"") and debug call Constructor.Name"

            '   ** however, you can reset the Constructor.Name (ask for a new, default by calling
            Set Constructor = New Constructor
            Debug.Print Constructor.Name, "Set Constructor = new Constructor -> [reset] assigned a new random"

        ' Working with INSTANCES
        Else
            Debug.Print "***INSTANCES"

            Debug.Print Constructor.Name, "Constructor.Name -> Current static default [reseted]"

            ' create an new variable of Test type
            Dim T As Constructor

            ' Assing to the currently stored value
            Set T = Constructor
            Debug.Print T.Name, "Set T = Constructor -> Should equal the current default"

            ' Create a new Constructor instance ( grabs the new random number )
            Set T = New Constructor
            ' or
            'Dim T1 As New Constructor  - same behaviour (grabs new random) but one line declaration
            Debug.Print T.Name, "Set T = new Constructor - > new random for this instance"
            ' *NOTE - it's possible that this number is the same as previous because the Random Range is 100

            ' *** PARAMETARISED CONSTRUTOR
            ' new instance calling the static name and passing a parameter
            ' that changes the default as well as the instance
            Dim T1 As Constructor
            Set T1 = Constructor("fooInst")

            Debug.Print T1.Name, "T1 = Constructor(""fooInst"") -> assigned through the static class"
            Debug.Print Constructor.Name, "Constructor.Name -> static is now equal to what T1 is"

            ' another separate instance using NEW Constructor
            Dim T2 As New Constructor
            Debug.Print T2.Name, "Dim T2 as New Test = just to verify, a new instance via a NEW Constructor"
            Debug.Print Constructor.Name, "Constructor.Name -> did NOT change, still equal to T1 due to the last assignment"

            ' changing instance via another instance
            Set T1 = T2
            Debug.Print T1.Name, "T1 result of: Set T1 = T2"
            Debug.Print T2.Name, "T2 result of: Set T1 = T2 -> T1 and T2 are equal"

            ' changing instance via another instance + PARAMETER
            Set T1 = T2("both")
            Debug.Print T1.Name, "T1.Name -> due to: Set T1 = T2(""both"")"
            Debug.Print T2.Name, "T2.Name -> due to: Set T1 = T2(""both"") - > T1 and T2 are now equal as T1 points to the T2"
            Debug.Print Constructor.Name, "Constructor.Name -> did NOT change, still equal to itself because it hasn't been used by other instances"

        End If
    Next

End Sub

VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
END
Attribute VB_Name = "MultipleParams"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Private myName as String
Private myNumber as Long

Private Sub Class_Initialize()
    myName = Cstr(Int((100) * rnd + 1))
End Sub

Public Property Get Name() as String
    Name = myName
End Property

Public Property Get PieceOfMe(Optional newName as String, Optional theNumber as Long) As MultipleParams
    Attribute PieceOfMe.VB_UserMemId = 0
    myName = newName
    myNumber = theNumber
    Set PieceOfMe = Me
End Property

Sub Main()

    Dim multiParams As MultipleParams
    Set multiParams = MultipleParams("the Name", 1)

End Sub

VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
END
Attribute VB_Name = "ParamArrayConstructor"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Private Sub Class_Initialize()
    myName = Cstr(Int((100) * rnd + 1))
End Sub

Public Property Get Name() as String
    Name = myName
End Property

Public Property Get PieceOfMe(paramArray arr() as Variant) As ParamArrayConstructor
    Attribute PieceOfMe.VB_UserMemId = 0
    ' handle the paramArray
    Dim v as Variant
    For each v in arr
        Debug.? v
    Next
    Set PieceOfMe = Me
End Property

Sub Main()

    Dim param As ParamArrayConstructor
    Set param = ParamArrayConstructor("oh", "my", "lord", "paramarray", "works too!")

End Sub