CODEWITHSUNDEEP
vbaexceleventsdynamicuser-controls
Apachi
Apachi

Reputation: 32

How do I effectively create controls dynamically in Excel's VBA or How do I use Application.OnTime()?

I am working on a very large VBA project in Excel at my job. We are about 1500 lines of code for just one feature and have about a dozen more features to add. Because of this, I've been trying to break everything down so that I can keep code for each feature in separate places. OOP sucks in VBA... The problem being that these controls MUST have events fired. Of course, some events (like the TextBox_AfterUpdate event) are not available when you dynamically create controls. It's a bit convoluted because of everything that is going on, so I'll break it down the best I can:

I have a class module that represents a tab for a multipage control. When a user clicks on a tab, the Userform calls this class module and THERE I have the controls created dynamically. This way I can keep the code in that class module. I have a sub that I deemed as the "AfterUpdate" sub and put code that I needed to run there. Now the problem is to get that sub to be called at the appropriate time.

So what I did is to set up a Timer of sorts to check and see if the "ActiveControl" is said textbox. If it is not, we can assume that focus has left and we can raise that event. Here's the code I'm using:

An abbreviated version of the tab creation...

Private WithEvents cmbMarketplace As MSForms.ComboBox

Public Sub LoadTab(ByVal oPageTab As Object)
    If TabLoaded Then Exit Sub

    Set PageTab = oPageTab

    Dim tmp As Object

    Set tmp = PageTab.Add("Forms.Label.1")
    tmp.Top = 6: tmp.Left = 6: tmp.Width = 48
    tmp.Caption = "Marketplace:"

    Set cmbMarketplace = PageTab.Add("Forms.ComboBox.1", "cmbMarketplace")

    ' LOAD OTHER CONTROLS '

    TabLoaded = True
    Start_Timer
End Sub

Then Start_Timer:

Public Sub Start_Timer()
    TimerActive = True
    Application.OnTime Now() + TimeValue("00:00:01"), "Timer"
End Sub

And the sub that is to be fired:

Public Sub Timer()
    If TimerActive Then
        ' DO SOME RANDOM THINGS '
        Application.OnTime Now() + TimeValue("00:00:01"), "Timer"
    End If
End Sub

Does this seem like a reasonable approach to solving the problem I'm facing? I'm open to suggestions...

That's the first problem. This seems like a lot of work to accomplish this. (I'm working on getting visual studio, but I don't know if that's going to happen)

The above code will work but the "Timer" sub will not get raised at all. I get no errors if I just run the code. Everything is created, everything works as I would hope. However, if I step through the code, I eventually will get the following error:

Cannot run the macro "...xlsm!Timer".  The macro may not be available in this workbook or all macros may be disabled.

Obviously neither of those suggestions are valid. Macros ARE enabled and the sub is in the same darn class module. I tried making it public, same problem. Tried "ClassModule1!Timer" to no avail. I'm at my wits end trying to figure this out. Thinking of having people write ALL this in the Userform or just giving up.

Does anybody have any suggestions on how to effectively break up large chunks of code? And does anybody have a clue why this sub will not run and seemingly cannot be found?

I understand that this is a confusing situation, so if you need more info or code examples or want to know why I have something set up the way I do, let me know.

Thanks!

Upvotes: 0

Views: 616

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71247

Obviously neither of those suggestions are valid. Macros ARE enabled and the sub is in the same darn class module.

There's the problem: a macro cannot be in a class module. The message is entirely correct: VBA cannot see the Timer procedure, because it's not accessible.

A class module is a blueprint for an object, VBA (or any OOP language for that matter) can't do anything with a class module, without an instance of that class - i.e. an object.

Your timer callback needs to be a Public Sub in a standard module, so that it can be called directly as a macro. Public procedures of a class modules are methods, not macros.

Depending on what ' DO SOME RANDOM THINGS ' actually stands for, this may or may not require some restructuring.

1500-liner spaghetti code can be written in any language BTW.

Upvotes: 4

Related Questions