1

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.

joshhemphill
  • 492
  • 6
  • 20
  • You can declare variables outside of a function or sub. Including an instance of your class(es) then they will be available to the entire project. [see here for how that works](https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/declaring-variables) – JNevill Jun 29 '18 at 13:37
  • 1
    Sounds like you just need public/module-level variables to me. – Rory Jun 29 '18 at 13:37
  • 1
    In general, it sounds like you should study up on how **scope** in object oriented programming works. I don't mean that in a condescending way, it's just a concept you should comprehend before doing any proper coding with an OOP language – Samuel Hulla Jun 29 '18 at 13:40
  • As for the "User input determines how many classes" you can declare a dictionary at the module/project level and then just add/remove instances of your existing class into it as needed. – JNevill Jun 29 '18 at 13:40
  • Read this to see an example for dynamically defining the events of the checkboxes: https://stackoverflow.com/questions/37407974/dynamically-define-the-events-of-the-checkboxes-in-vba – AcsErno Jun 29 '18 at 13:52
  • @JNevill Thanks. Just to make sure, will my userform object event handlers still work down in subclasses? – joshhemphill Jun 29 '18 at 13:57
  • I don't think "subclass" means what you think it does. As for OOP, I agree with @Rawrplus here. With properly written OOP code, a UserForm wouldn't be spawning any other forms. Look into *Model-View-Presenter* implementations, like [here](https://superuser.com/a/1335170/165271). – Mathieu Guindon Jun 29 '18 at 14:03
  • Probably, yes. It's difficult to say and is dependent on how you build it. Once you get your head wrapped around OOP (even it's very minimal implementation in VBA) this stuff will make a lot more sense. – JNevill Jun 29 '18 at 14:03
  • @MathieuGuindon Yes, I agree my userform is going to be horribly inefficient, but I'm going this route since from what I can tell, it's not possible to have objects and sub-objects, and then to deep copy in VBA. If I could do that and still get the event handlers for all the objects to work, then all this wouldn't be necessary. – joshhemphill Jun 29 '18 at 14:15
  • 1
    @Robofan I'd love to show you otherwise, but you're giving us no code to chew on... – Mathieu Guindon Jun 29 '18 at 14:18
  • @MathieuGuindon What part of the code would be relevant? I have a mess of code that wouldn't be helpful in dumping here. And I haven't added anything to the classes yet. – joshhemphill Jun 29 '18 at 15:00
  • @MathieuGuindon: Not "overboard", just with extreme enthusiasm :-) – AJD Jun 29 '18 at 22:48

1 Answers1

2

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.

JNevill
  • 46,980
  • 4
  • 38
  • 63