CODEWITHSUNDEEP
vbaexcel
joshhemphill
joshhemphill

Reputation: 592

Can you put VBA code in a bare module, outside of Function or Sub?

I'm trying to keep a Collection Class of Classes persistent while a Userform is running so that the form objects they create can still have event handlers. But if I create any classes for these in subs or functions, their respective classes and event handlers would be cleared at the end of whatever subroutine created it.

I should specify that user input determines how many classes there will be, so I can't just hard code the event handlers into the userform module.

Upvotes: 1

Views: 978

Answers (1)

JNevill
JNevill

Reputation: 50019

You can use a publicly declared dictionary to hold instances of your class that will be available to your project. You declare variables outside of a function or sub and declare them as Public for other modules and their subs/functions to be able to use them. They stay resident in memory between calls while the application is open.

Consider a class called c_gumball:

Public color As String
Public diameterInches As Double
Public Function getSize(unit As String) As Double
    Select Case unit
        Case "mm"
            getSize = diameterInches * 25.4
        Case "cm"
            getSize = diameterInches * 2.54
        Case "yd"
            getSize = diameterInches / 36
    End Select
End Function

And then a new module called m_gbmachine:

Public gumballMachine As Dictionary

Public Sub createGumbalMachine()
    gumballMachine = New Dictionary
End Sub

Public Sub addGumball(color As String, sizeInInches As Double, nameKey As String)
    Dim gb As c_gumball
    Set gb = New c_gumball
    gb.color = "green"
    gb.diameterInches = 1.2
    gumballMachine.Add Key = nameKey, gb
End Sub

Public Sub removeGumball(nameKey As String)
    gumballMachine.Remove (nameKey)
End Sub

Any module can now use m_gbmachine.gumballMachine dictionary and see what's in it. They can add gumballs using it's functions.

Perhaps in your userform you create a gumball called "gumball2" in your dictioanry and then want to get the color property of "gumball2" in the gumballMachine dictionary, you could do:

Public Sub button_Click()
    'add gumball 2 to the machine
    m_gbmachine.addGumball "green", 1.2, "gumball2"
End Sub

Public Sub someFormRoutine()
   'msgbox the color of gumball 2
   MsgBox m_gbmachine.gumballMachine("gumball2").color
End Sub

You can go deeper and change this module over to a class of it's own and have many gumball machine instances as well.

Upvotes: 2

Related Questions