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

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