Save class module in form

Question

Say I want to make a bike designer program in Excel VBA. I have a class object ( cBike ) that has some default settings. Now I want to make a form (for example, the one in the picture) that can be used to change these parameters before storing the bike in the database. The (sub) storage method is in cBike .

I could save the object as a public variable in the form code, for example:

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

While this works, I have seen many people argue against using public (global) variables. I'm not quite sure why, except for the fact that if you have too many global variables, your code will be messy.

Alternatively, I could forget the object and use the values ​​from different form controls, rather than the properties of the module of the cBike class. However, this seems like a slightly awkward solution.

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

Update: I would strongly recommend that you read both the accepted answer and the one next. Both answers have some great ideas, and over them are added additional code examples that can be used for others with questions similar to mine.

Answer 1

A form is essentially a class in itself, so I would recommend creating a private property on the form to hold your Bike object. You can then transfer the existing Bike object to the form / class using the property set procedure.

There is no problem declaring a Bike item / property at the form level if it needs to be accessed by several procedures in the form. Global / public variables (declared in the module) should be used only if the object should be used by the whole 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 form controls and your class by declaring cBike properties 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 pass the value of the text field to the class if it changes. You will need a set of links to the Microsoft Forms 2.0 object library

Answer 2

Another approach may be to allow Bike to be edited. The Bike class will contain BikeEditor , which is a custom form for editing a bike object. Here is an example for the type of bike, but other properties of the bike can be done in a similar way. For BikeType , a class is used that wraps TypeOfBikeEnum .

A 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