ActiveX Checkboxes losing automation

[gravanoc]

I have a set of ActiveX checkboxes created through a script. It works fine, but if an unrelated script executed at a later time were to be ended prematurely via an error and hitting the End button rather than typing in a workaround, it will wipe out the events tied in through my class module and WithEvents. What's more, it seems like the only way to preserve their automation status is to manually run the code. If I tie this to another ActiveX control like a command button, it will fire the creation of the checkboxes, but all public variables will fail to initialize and they will not have automation. Is there anything I'm overlooking?

 

[GWteB]

From what you describe, I suspect that you are not pre-compiling the code.
Stopping the code in the event of a compile error (so this is a completely different error than a run-time error) does indeed result in the loss of data that is in the computer's memory.
It is recommended to always compile your VBA project beforehand and to also use the Option Explicit in every module.
Compiling your project will check the syntax and if Option Explicit is also used you will immediately get a warning about non-existent or unreachable variables, objects and procedures.

 

[gravanoc]

From what you describe, I suspect that you are not pre-compiling the code.
Stopping the code in the event of a compile error (so this is a completely different error than a run-time error) does indeed result in the loss of data that is in the computer's memory.
It is recommended to always compile your VBA project beforehand and to also use the Option Explicit in every module.
Compiling your project will check the syntax and if Option Explicit is also used you will immediately get a warning about non-existent or unreachable variables, objects and procedures.

Is using the Debug menu's Compile different from the Compile on Demand option? I'm not certain, but that is turned on in my options. I did try compiling the code, but unfortunately it still refuses to automate the checkboxes. I agree about OE, and do use it in my modules, but I decided to turn on the options to enforce it, nonetheless. I'm not sure if it was related to this option change, but another strange error occurred while working on this issue. The checkboxes are named after the cell's address they are located in, and this non-contiguous range is named Stations. A subset of this range is called CurrentStations and is used to determine which cells to highlight for UI convenience because each checkbox has an owner, corresponding to one of the workbook's tabs. The 1004 error kept breaking on a line:

.RefersToRange = optSht.Range(newRng.Address)

It said that the naming convention was incorrect for something in that line, or that it was a duplicate of another variable in scope. Neither seems to be true. Any idea what could cause this? I was able to make it accept the code by removing "optSht.", but it later stopped on the edited code and complained again.

 

[Gokhan Aycan]

I agree with compiling the code and Option Explicit (also enforced it through options). iirc compiling won't help with Application.Run, Application.OnTime and the sort if you have any.

Now I am not sure it would be the same (I am nowhere as knowledgeable as some here), but in my last (and only real) project in the error handling code I call the global initialization with Application.OnTime (i.e., Application.OnTime Now + TimeValue("00:00:02"), "mdlButtons.Recover_Routine" and then END the execution. Works for me. Would this kind of approach be applicable to your code?

 

[gravanoc]

Ahh, thank you very much Gokhan. I tried:

Application.OnTime Now + 0.0001, "CreateCheckBoxes"

It succeeded! Strangely, I earlier tried to use a variation of this by utilizing a function with the Timer command, and passing a pause of 1 second. That, didn't work, but this did. Thanks again!

 

[Gokhan Aycan]

Timer afaik pauses as much and continue executing the code. Application.OnTime starts a new execution chain so to speak. So actually the one with error ends, and then this one starts. Glad it worked

 

[GWteB]

@gravanoc, glad your problem has been resolved.

@Gokhan Aycan, pretty nice workaround, although I prefer to use as few ActiveX elements as possible on my worksheets. Nevertheless, I will definitely be able to use this one day

Is using the Debug menu's Compile different from the Compile on Demand option?

Compile VBAProject from the menu compiles the entire project, the on demand option compiles as needed during run-time. The latter option dates back to a time when computers could be relatively slow. Enabling this option could potentially increase performance a bit back then, with today's computers that doesn't have any noticeable impact. Excel has evolved quite a bit since then, the VBE on the other hand, barely.

Consider the code below, within different modules (with some absolutely unintentional typos ). With Compile on Demond option enabled and not performing a Compile VBAProject, running ProgramEntry results in a compile error when entering the FigureThisOut procedure, meanwhile without any warning giving us 0 where 16 was expected, caused by the lack of the Option Explicit statement. So the final program flow, usually depending on the interaction with the user, determines if and when the compiler errors out, which should make a coder quite uncomfortable.

Regarding your additional issue, provided optSht and newRng are valid objects it should work. Hard to tell what's causing it without seeing the code involved and the contents of the names collection.

Module1

Public Sub ProgramEntry()
    MsgBox Square(4)
    FigureThisOut "OnceMore"
End Sub

Public Sub CallMe()
End Sub

Module2

Public Function Square(ByVal argNumber As Long) As Long
    Squared = argNumber ^ 2
End Function

Public Sub FigureThisOut(ByVal argTxt As String)
    Ca11Me
End Sub