Preserve Class Module in form

Question

Let's say I want to make a bike designer program in Excel VBA. I have a Class Object (cBike) which has some default settings. Now I want to make a form (like the one in the image) which can be used to change these settings, before storing the bike to the database. The method (sub) for storing, is located in cBike.

I could preserve the object as a public variable in the form code, like this:

Public objBike As cBike 
Public Sub StoreBikeToDatabase()   
 'database storing code goes here 
End Sub

While that would work, I have seen a lot of people arguing against the use of public (global) variables. I am not quite sure why, except for the fact that if you have too many global variables, your code will be a mess.

Alternatively I could forget the object, and use the values from the different form controls, rather than the properties of the Class Module cBike. However this seems like a bit of a clumsy solution.

My question is this: Which of the above solutions are the best, if any? And if none of them, then what should I do instead?

Update: I would strongly suggest that you read both the accepted answer, and the one by dee further down. Both answers has some great ideas and dee's post additonally holds some comprehensive code examples which can be used for others with questions similar to mine.

Answer 1

Another approach could be to let the Bike be editable. The Bike class will contain a BikeEditor which is a user form for editing of bike object. Here is example for bike type but the other bike properties it could be done in a similar way. For the BikeType a class is used which wraps the TypeOfBikeEnum.

Bike

Private m_editor As BikeEditor
Private m_bikeType As BikeType

Private Sub Class_Initialize()
    Set m_editor = New BikeEditor
    Set m_bikeType = New BikeType
End Sub

Public Property Get TypeOfBike() As BikeType
    Set TypeOfBike = m_bikeType
End Property

Public Property Set TypeOfBike(ByVal vNewValue As BikeType)
    Set m_bikeType = vNewValue
End Property

Public Sub Edit()
    m_editor.Initialize Me
    m_editor.Show
End Sub

BikeType

Public Enum TypeOfBikeEnum
    [_First]
    Unknown = 1
    MountainBike = 2
    StreetBike = 3
    OfficeBike = 4
    MoonBike = 5
    [_Last]
End Enum

Private m_type As TypeOfBikeEnum

Private Sub Class_Initialize()
    m_type = Unknown
End Sub

Public Property Get TypeValue() As TypeOfBikeEnum
    TypeValue = m_type
End Property

Public Property Let TypeValue(ByVal vNewValue As TypeOfBikeEnum)
    m_type = vNewValue
End Property

Public Function GetBikeTypeNames() As VBA.Collection
    Dim enumVal As Long, name As String
    Set GetBikeTypeNames = New VBA.Collection
    For enumVal = TypeOfBikeEnum.[_First] To TypeOfBikeEnum.[_Last]
        name = GetBikeTypeName(enumVal)
        If name <> "" Then _
            GetBikeTypeNames.Add name, CStr(enumVal)
    Next enumVal
End Function

Public Function GetBikeTypeName(typeOfBikeValue As TypeOfBikeEnum) As String
    Select Case typeOfBikeValue
        Case TypeOfBikeEnum.Unknown
            GetBikeTypeName = "Unknown"
        Case TypeOfBikeEnum.MountainBike
            GetBikeTypeName = "MountainBike"
        Case TypeOfBikeEnum.StreetBike
            GetBikeTypeName = "StreetBike"
        Case TypeOfBikeEnum.OfficeBike
            GetBikeTypeName = "OfficeBike"
        Case TypeOfBikeEnum.MoonBike
            GetBikeTypeName = "MoonBike"
        Case Else
            GetBikeTypeName = ""
    End Select
End Function

BikeEditor

Private m_bikeToEdit As Bike

Public Sub Initialize(bikeToEdit As Bike)
    Set m_bikeToEdit = bikeToEdit
    Dim bikeTypeName
    For Each bikeTypeName In m_bikeToEdit.TypeOfBike.GetBikeTypeNames
        Me.bikeTypesComboBox.AddItem bikeTypeName
    Next
    Me.bikeTypesComboBox.ListIndex = m_bikeToEdit.TypeOfBike.TypeValue - 1
End Sub

Private Sub CancelCommandButton_Click()
    Unload Me
End Sub

Private Sub SaveCommandButton_Click()
    If Me.bikeTypesComboBox.ListIndex > -1 Then
        m_bikeToEdit.TypeOfBike.TypeValue = Me.bikeTypesComboBox.ListIndex + 1
    End If
    Unload Me
End Sub

Module

Sub test()
    Dim bk As Bike
    Set bk = New Bike

    Dim bt As BikeType
    Set bt = New BikeType
    bt.TypeValue = OfficeBike

    Set bk.TypeOfBike = bt
    bk.Edit
End Sub

Answer 2

A form is essentially a class in itself, so I would recommend creating a Private Property in the form to hold your Bike object. You can then pass the existing Bike object into the form/class through a Property Set routine.

There is no problem with declaring the Bike member/property at form level if it needs to be accessed by several routines within the form. Global/Public variables (declared in a module) should only be used if the object needs to be used by the entire project.

'Private Member of this Form/Class
Private mBike As cBike

'Pass the existing object into this Form/Class
Public Property Let Bike(ByVal obj As cBike)

    Set mBike = obj

End Property

You can effectively create a dynamic link between the form controls and your class by declaring the properties of cBike as follows:

Private WithEvents mTextBox1 As MSForms.TextBox

Public Property Set TextBox1(ByVal obj As MSForms.TextBox)

    Set mTextBox1 = obj

End Property

This means that you will not need to keep passing the value of the textbox to the class should it change. You will need a reference set to Microsoft Forms 2.0 Object Library