Solved Encapsulate Function In A Class For Command Bar

dalski

Member
Local time
Today, 19:22
Joined
Jan 5, 2025
Messages
128
I have a class for a Subform; trying to encapsulate all relevant things. The MouseUp event (assigned to the class) deletes & builds a relevant right-click context menu. But if I place a Public Function InsertPageFromABill()
within the class to handle a button in the Command Bar it is not visible to the Subform. Seems you cannot reference a class' methods in the CmdBar.OnAction property with clsName.FunctionName. I am trying not build lots of classes & trying to encapsulate the relevant things to this Subform class ideally.
If I put the function in a normal module it works fine, but this does not seem the right thing to do (should encapsulate relevant things inside the class).

What is the proper encapsulation methodology here? I could build another class for the command bar but seems too messy with unnecessary classes for the sake of creating classes. Could build another class and reference the function and gain access from there but again this seems superfluous? I would think declaring the function as Friend is the way but does not seem to work.

SubForm
Code:
'+Init & Demo:
Private Sub Form_Load()
  Dim ctrl As Control
  For Each ctrl In Me.Detail.Controls
    If ctrl.ControlType = acTextBox Then
      Set mObj = New clsBillOrPage
      mObj.fInit ctrl
      mColTenderBillsAndPages.Add mObj
    End If
  Next ctrl
End Sub

Private Sub Form_Unload(Cancel As Integer)
  For Each mObj In mColTenderBillsAndPages
    mObj.Dispose
  Next mObj
End Sub

clsBillOrPage
Code:
Public WithEvents mTb As TextBox
Private mParentFrm As Form
Public Enum eBillOrPage
  Bill = 1
  Page = 2
End Enum

Public Function fInit(ctl As Control, Optional lParentFrm As Form) As clsBillOrPage
  Set mParentFrm = lParentFrm
  Set mTb = ctl
  mTb.OnMouseUp = "[Event Procedure]"
  Set fInit = Me
End Function
Public Function Dispose()
  Set mTb = Nothing
  Set mParentFrm = Nothing
End Function
Private Sub mTb_MouseUp(Button As Integer, Shift As Integer, x As Single, Y As Single)
  Dim strBarName As String
  strBarName = "dalsBillPageRcMenu"
  Dim Result As String
  Dim lConcatID As String
  lConcatID = Screen.ActiveControl.Parent.CONCAT_ID
  If Button = vbKeyRButton Then
    'Build Empty CmdBar
    Dim lBar As CommandBar
    Set lBar = ResetCustomBar("dalsBillOrPageRcMenu")
    'Assign cmdBar to frm
    Debug.Print Screen.ActiveControl.Parent.name
    Screen.ActiveControl.Parent.ShortcutMenuBar = "dalsBillOrPageRcMenu"
 
    'Is current item Bill/ Page?
    Result = IsItBillOrPage(lConcatID)
    If Result = Bill Then
      'It's a Bill; add Bill btns
      CmdBarAddBtnsBill lBar
    ElseIf Result = Page Then
      'It's a Page; add Page btns
      MsgBox "p"
    Else
      MsgBox "Dunno"
    End If
  End If
End Sub
Public Function IsItBillOrPage(lConcatenatedID As String) As eBillOrPage
  If Left(lConcatenatedID, 4) = "Bill" Then
    IsItBillOrPage = Bill
  ElseIf Left(lConcatenatedID, 4) = "Page" Then
    IsItBillOrPage = Page
  Else
    MsgBox "Unaccounted record source type; only accounting for Bill/ Page"
  End If
End Function


'************************************************************************************************
'+ RIGHT-CLICK MENU (add btn's):
'SELECTED ITEM IS BILL:
Public Function CmdBarAddBtnsBill(theCmdBar As CommandBar)
  With theCmdBar.Controls.Add(msoControlButton)
    .Caption = "+ Page"
    .OnAction = "=InsertPageFromABill()"
  End With
  With theCmdBar.Controls.Add(msoControlButton)
    .Caption = "Edit Page"
    .OnAction = "=EditThePage()"
  End With
End Function

RightClickStuffModule - Though should be in the class I think this may be a minor exception where no point in writing this generic procedure all over the place it will be used all over the place with the exception to the Function InsertPageFromABill (this is the function I want to encapsulate as it is not generic and specific to the class.
Code:
'If menu exists; del & build new one (no exists fn)
Public Function ResetCustomBar(BarName As String) As Office.CommandBar
  On Error Resume Next
  On Error GoTo Line1
  CommandBars(BarName).Delete
Line1:
  Set ResetCustomBar = CommandBars.Add(BarName, msoBarPopup, False, True)
End Function

'If menu exists; del & build new one (no exists fn)
Public Function ResetCustomBar(BarName As String) As Office.CommandBar
  On Error Resume Next
  On Error GoTo Line1
  CommandBars(BarName).Delete
Line1:
  Set ResetCustomBar = CommandBars.Add(BarName, msoBarPopup, False, True)
End Function

Public Function InsertPageFromABill()
  Dim lBillID As String
  lBillID = Screen.ActiveControl.Parent.CONCAT_ID
  DoCmd.OpenForm "TenderPageEditF", , , , acFormAdd
End Function
 
Last edited:
Your desired syntax "clsName.FunctionName" only works with static classes, which have
Attribute VB_PredeclaredId = True
in the header. See also this.
Public methods then are available throughout the rest of the code.
 
I will give a little more context

In other languages you have what is known as Static properties and Procedures. This allows you to build a class and encapsulate and protect the functionality, but utilize the procedures without having to instantiate an instance.
Example in VB is the Math class which has all the math functions

You do not have to do something like

dim MyMath as New Math
x = myMath.log(100)

You simply do
x = Math.log(100)
notice "ClassName.ClassMethod"

In some other languages everything is a class and there is no thing like a standard module, which is kind of like the static properties.

You may ask why make a class when you can use a standard module.
1. It is more protected. You will never have a name conflict because any name of a property and method is protected in the class
2. With a class you have to access it through the class name or instance. This may provide some protection if you have standard module functions in two modules that have similar names. You are less likely to call the wrong function.
3. You have a pseudo constructor in the initialize method
4. You can actually build properties in a standard module. I have never done that, because if I am creating properties I already want the other benefits of a class over a standard module.

So you can fake static approach by predeclaring your class. This basically creates a global instance of your class immediately. See discussion on how you predeclare you class, and some easier ways if you have MZ-Tools.
 
Hey @dalski, if you are working with a popup menu or commandbar, and you don't want to handle the click using the OnAction property callback, check out this thread: How to handle a CommandBarButton click event.
• This is actually the same pattern as the TextBox right click handler you were working on in a different thread.
• The cPopup class wraps a CommandBar, holds a collection of cPopupButton instances, and exposes a method (ButtonClicked) that cPopupButton can call if a click occurs.
• Each cPopupButton class wraps a single Office.CommandBarButton, holds a reference to cPopup, handles the CommandBarButton.Click event, and calls back to cPopup.ButtonClicked.
• cPopup then raises a PopupClick event, passing the CommandBarButton back up to the object hosting cPopup, usually a Form.
This enables a form to create, show, and handle events from a CommandBar directly, without having to use CommandBarButton.OnAction to define a callback.
 
Wish I would have seen this earlier. I have a few pretty large command bars. Here is one example
It started small but I kept adding buttons. The code really became a pain to manage. With the on action you have to be precise in your naming so that the action mirrors the caption. Towards the end I figured I just needed to refactor the code because it was such a pain, but never got to it.
This would have saved a lot of time.
 
Thanks for the rapid response guys, massively appreciated.
 
This would have saved a lot of time.
Yeah, when I developed that pattern I was pretty happy, because the pattern I was using before was...
• Save and expose a temporary public reference to the owner/creator of cPopup
• Execute the .OnAction callback
• Get the .OnAction method to re-callback to the temp/public cPopup owner with results
• Destroy the temp/public cPopup owner reference
It was an ugly hack.

But what I do now is even cooler. The owner/creator of cPopup passes an ICommand to cPopupButton, whose .Click handler--rather than bubble the event back up to the owner thru cPopup--just executes the ICommand.
 
Hey @dalski, if you are working with a popup menu or commandbar, and you don't want to handle the click using the OnAction property callback, check out this thread: How to handle a CommandBarButton click event.
Thanks Mark, it's very nice & I spent over an entire weekend playing with it. But my clsBillOrPage is intrinsic to the form & more so my main endeavour is to use the .OnAction property to run different functions; which the entire purpose of classes is to contain what is relevant to that class. Yes the class is coupled with the form & bad design but I'm just a beginner & decoupling with a secondary class, only to recouple with the content of the form is above my capabilities atm & I'm very happy with a single class handling this form. In the future as my experience grows i will be able to identify complex coding patterns, but at this stage I haven't gotten far in over 2 weeks.

1 - Is it not at all possible to just reference a function in a class from the .OnAction property of the msoControlButton? It appears not. With the problem stemming from the OLE class/ property not being able to assign a class member (public function in my clsBillOrPage in this case) or my interface design (as A... below).
A - Frm itself loops controls in frm. If textbox control it hooks MouseUp event with the clsBillOrPage textbox to clsBillOrPage.
B - The clsBillOrPage OnMouseUp event builds a CommandBar. The procedure used to build the buttons is not universal. The buttons added are dependant to several factors. The actions behind the buttons are not universal (some are same buttons different actions, some are different buttons different actions...).
I want to keep everything in the clsBillOrPage. Yes an action or two may be duplicated & it may be technically better to decouple with a secondary class. But I'd then need to almost write another secondary procedure which would be dependant on the form's values. Decoupling only to couple back again. A more experienced programmer would be able to pattern this complex pattern but for someone who is new to classes & events I'm happy to be tightly coupled with this class. In the future I can improve this but atm
Though the mouseUp event builds the CommandBar, assigns the shortcut menu to the clsBillOrPage text box currently in the event. Adds the buttons & at this time the MouseUp event is the caller & still actively engaged awaiting the click of the button. The msoControl Button is pressed. Now despite the clsBillOrPage's MouseUp having called the CommandBar & it's MouseUp event still in play I get the below error. Remember putting the function to be called in a Module as Public allows the function to be found. But defeats the purpose of the class encapsulating all relevant info for it to work effectively.

1755516230963.png



Default Instance Of Class
I would imagine I'd run into problems creating a default instance of the class. I would imagine you'd have a default instance; then another instance in the form/ subform; not good!
Static properties and Procedures. This allows you to build a class and encapsulate and protect the functionality, but utilize the procedures without having to instantiate an instance.
C# inheritance & Polymorphism is intuitive. In VBA it's made so much harder. I imagine it's because it's such an old language compared to C#. Accessing the class' method here is what I'm after. I understand the problem is likely that the CommandBar is an instatiation of a separate class. I've experimented trying to set the .OnAction property of the msoControlButton in VBA as "=Parent.clsBillOrPage.FunctionName()" & many variations, from many locations.
Regarding instantiation surely the clsBillOrPage is instantiated as the CommandBar was called from the MouseUp event of the class; which is still open by the way (not end of sub). But there seems a flaw in the .OnAction property of a command bar in not letting the user access a class' method/ public/ friend function?
In this design flaw of VBA I would've thought there would be a default collection in each instance of a commandBar to reference functions/ sub's relevant to that CommandBar then at least. But NO - the only option appears to be to but the function in an external module. Defeating the entire point of classes.

Decoupling is nice but the problem I have is that I need to get external values & do different things with different buttons. Sadly the advanced decoupling is just too much for me atm.


Thanks Tom, though I would've thought it would cause me issues having a default instance declared & the entire point of classes is to encapsulate methods/ procedures/ vars I don't think I'm on the right path here in having to change these advanced settings in order just to access a class member. Was not a lot on Google on this advanced topic so it scares me; but could not help but play around; got a syntax error below.
"clsName.FunctionName" only works with static classes, which have
Attribute VB_PredeclaredId = True
in the header.
1755516557413.png
 
Last edited:
Sorry to bump thread, anyone have a solution to this? Lost nearly a week on it full-time.

I've stepped through Mark's code too many times to count. Using Mark's fine code I still get the same error. I cannot gain access to a function assigned to the .OnAction property of the cPopUpBtn from the form. The events are firing fine, & I understand the polymorphism behind them, but I do not understand why I cannot locate the function dalskiHandle() in the form. Screenshot's show the function is being recognized. I tried working it back to a string & Application.Run strNameOfFunction but no luck. Seems such a simple problem but completely at a standstill.

All I am trying to do is run different procedures for different buttons. So it makes sense to use the .OnAction property of the button. I'm just after a solution on how to run different procedures for different buttons in a CommandBar & storing them procedures in the Form behind the CommandBar.

I have changed tactics & looking to store the functions/ subs to be stored in the form & make the most of Mark's fine class in not having to create a class for each command bar then & getting the most out of Mark's coding patter. So now it makes sense to store the relevant procedures of that command bar in the form. Otherwise you would end up having to create a different cPopup class for each CmdBar (defeating the purpose of a class). But I cannot exectue/ find a fuction from the form; in which the cPopup class was instantiated.

1755778604473.png
1755778638200.png
 

Attachments

  • 1755776692669.png
    1755776692669.png
    228.3 KB · Views: 19
  • 1755776780601.png
    1755776780601.png
    242.7 KB · Views: 22
Last edited:
The OnAction command should be the name of a function that is public.
= "dalskiHandle"
not

"=Me.dalskiHandle()"
 
See example
Code:
Set myBar = CommandBars("Custom")
Set myControl = myBar.Controls _
    .Add(Type:=msocontrolButton)
With myControl
    .FaceId = 2
    .OnAction = "MySub"
End With
myBar.Visible = True

.OnAction = "MySub"
not
.OnAction = "=MySub()"
 
Thanks Pete, my main goal is to avoid public declaration & encapsulate the function within a class/ form. Typical, spent about 50 hours going through it & find a solution several minutes after posting. Honestly no exaggeration here.

I was fixated on the .OnAction property of the button, as it addresses all my needs. I put it in many different locations, sometimes it fired, sometimes it did not. I think I got encapsulation for the called function when placing the function within the cPopup class but that's no good as it requires a separate class each time for the cPopup; waste of time! Don't worry I know the click event stems from the cPopupBtn & regresses then to the cPopup class, then to the form itself.

So I assign the .Tag property of the button from the form, then do a select case in the form for the Tag returned from the inherited click event (containing the function text to run). The function to run is stored in the form. That's allowed for full encapsulation with privately declared functions within the form. This allows me to encapsulate the required functions for this instance of the class. It's not perfect by no means; means I've lost the .Tag property for something else.

Thank you all for your help & input. A bit too advanced for my experience atm :eek: .
 
Last edited:
If you handle CommandBarButton.Click event, you don't need .OnAction. If you handle Click, your execution remains within your class structure. If you use .OnAction you must call out to a standard module, and then you've lost encapsulation, and to bring execution back to where it started is WAY harder.

Post your Db if you want, and point me at where you're stuck.
 
Thanks Mark

Memory Leak
I'm experiencing a memory leak between cPopup & cPopupBtn; each use of the right-click the code loops between the cPopupBtn & cPopup +1 more increment. Either instance is not being destroyed/ btns collection. I cannot see where I'm differing from your code apart from the obvious (outside of these classes). I did wonder how you managed to avoid a memory leak when declaring a collection within the class itself as it seems similar to my problem the other week. But you have no leaks whatsoever :love:.

Learnt So Far
I understand how the clicks are handled now;
  1. Click event is handled by the wrapper around the CommandBarButton in cPopupButton with btn_Click as the event handler (a standard event). cPopupBtn is a type of StrategyHandler you helped me with the other week (thanks). It has a pointer to the cPopup class; so we can access cPopups properties/ methods - which we do with pop.ButtonClicked (a standard method).
  2. In cPopup ButtonClicked raises the user-defined event PopupClick declared and raised in cPopup.
  3. The form can use the object variable's instance of cPopup to access the user-defined Event 'PopupClick' & treat it as an event in the form FrmName_PopupClick.
Never seen being able to set the properties of an object from an external procedure before; what is the technical term for this? So powerful, surprised it does not create a circular reference & Access can handle it. The advanced constructor whilst adding to a collection also had me scratching my head & I tried to replicate it step by step in a less advanced constructor.
Form
Code:
Set mFrmCmdBar = New cPopup
mFrmCmdBar.AddBtn("Caption", True,True).Parameter = "So Cool"
cPopup
Code:
Public Function AddBtn(Caption As String, Optional Enabled As Boolean = True, _
  Optional BeginGroup As Boolean) As Object
 
  Dim lBtn As Office.CommandBarButton
  Set lBtn = Me.Popup.Controls.Add(msoControlButton)
  With lBtn
    .Caption = Caption
    .Enabled = Enabled
    .BeginGroup = BeginGroup
  End With

  'Allows consumer to modif obj's properties
  Set AddBtn = lBtn
End Function
______________________________________________________________________________________________________________________

Point Of Execution Of The Standard .OnAction Property Of A CmdBar & Why It Cannot Reference Class Members (Not Referring To Your Fine Code; Just A Standard CommandBar Wrapped In A Class):
Thought I could just wrap a standard CommandBar in a class. Use the pointer in the form to the class; assign the .OnAction property of the CommandBarButton in the form & encapsulate the functions/ subs to run (from the .OnAction property of the CommandBarButton) within the form. But did not manage this. So where does the .OnAction property of the CommandBarButton in a standard CommandBar execute from? It must not have a relative reference because if it did it would execute from the wrapped class I think.
 
Last edited:
Thought I could just wrap a standard CommandBar in a class. Use the pointer in the form to the class; assign the .OnAction property of the CommandBarButton in the form & encapsulate the functions/ subs to run (from the .OnAction property of the CommandBarButton) within the form. But did not manage this. So where does the .OnAction property of the CommandBarButton in a standard CommandBar execute from? It must not have a relative reference because if it did it would execute from the wrapped class I think
1. This is kind of Mark's whole point of this and the utility of his code and the reason for not using the OnAction. The on Action is a global call and can only find a public method in a standard module. It would be similar to going to the immediate window and typing that method in. It will never find it in a form class or other class, since these can only be executed through an instance of that class.

Even if you instantiate the command bar from the form there is no built in association. That command bar does not know it is wrapped in a class. I would be curious if you could do something like
onAction = "Forms!SomeFormName.SomeProcedure.
And execute a method from a form's class. I kind of doubt it, and that seems even worse.

So the OnAction kind of "hardwire" and that is a limitation. Imagine you have the same command bar and it is lengthy and took lots of code to build and you want to use it in multiple forms but the actions would be different for each of the forms. With Mark's approach you can build the command bar once and each form can use it trapping the custom event of his wrapper class (raised by each button click). Now each form can do different things on those actions. This is why he is getting rid of the onAction. Tedious to code and not flexible.

2. "The advanced constructor". I would not use the term constructor since VBA does not really have parameterized constructors. THere is only the initialize method of the class. These are just methods.

3. "Never seen being able to set the properties of an object from an external procedure before;". Not sure I understand this, because that is kind of the whole purpose of a class and you do it all the time.

4. As far as memory leaks, I am not even sure if you have ever created one. I am not sure if any of these things demonstrated so far are actual memory leaks. The other problems were coding issues doing thigs you did not expect like loading the event properties of the form, not a memory leak. Creating duplicate instances of objects, or leaving pointers open, is not a memory leak.
 
I would be curious if you could do something like
onAction = "Forms!SomeFormName.SomeProcedure.
And execute a method from a form's class. I kind of doubt it, and that seems even worse.

That's what had me fascinated & what I attempted earlier with Me.FunctionName() executed from the form.

So the OnAction kind of "hardwire" and that is a limitation...

Shockingly so, dumbfounded it is not a dynamic reference :eek: .

3. "Never seen being able to set the properties of an object from an external procedure before;". Not sure I understand this, because that is kind of the whole purpose of a class and you do it all the time.

I'm referring to below where 'Allows consumer to modify obj's properties. Set AddBtn = lBtn. Sure accessing a class' members & setting their values in my inexperienced mind seems normal. But the ability to add additional parameters from the consumer to a local variable in the called procedure does not seem normal (intimated by Mark's comment & further explanation 'Allows consumer to modify objects properties).
Form
Code:
Set mFrmCmdBar = New cPopup
mFrmCmdBar.AddBtn("Caption", True,True).Parameter = "So Cool"
cPopup
Code:
Public Function AddBtn(Caption As String, Optional Enabled As Boolean = True, _
  Optional BeginGroup As Boolean) As Object
 
  Dim lBtn As Office.CommandBarButton
  Set lBtn = Me.Popup.Controls.Add(msoControlButton)
  With lBtn
    .Caption = Caption
    .Enabled = Enabled
    .BeginGroup = BeginGroup
  End With

  'Allows consumer to modif obj's properties
  Set AddBtn = lBtn
End Function
I've never seen functions setting themselves before & this is a bit confusing. The AddButton function is setting itself to the lBtn.

4. memory leaks, ... leaving pointers open, is not a memory leak.

Last thing I want to do is upset someone who has helped me so much & obviously I am nowhere near your level in any area whatsoever. In the interest of my learning my interpretation from MSN is this is the definition of a memory leak - memory which is no longer required & is not released from the Stack back to the heap. The assumption obviously that the pointer is no longer in use in other references. I understanding your distinction between the other points mentioned; thank you. Pretty sure that's what I've done atm the pointers have been left open as stated earlier. I've DM'd you the db should you no doubt prove me wrong. Please don't feel obligated, I've already DM'd this to Mark & last thing I want to do is abuse either of your time; it's just for your curiosity.

1755866792504.png


Sorry to drag this out guys, a normal person would just take the .OnAction property's limitations & not question why nor spend an entire week fulltime in trying to decipher it. But being new to classes I've found the point of execution to be difficult to grasp & what things are available to me at different points during runtime. The pursuit of normality eludes.
 
Last thing I want to do is upset someone who has helped me so much & obviously I am nowhere near your level in any area whatsoever. In the interest of my learning my interpretation from MSN is this is the definition of a memory leak - memory which is no longer required & is not released from the Stack back to the heap.
If that is what is happening then yes it is a memory leak. But from what I have seen, that is not what is going on in these things that you call memory leaks. More likely you create A and then you create B. Then you destroy A. The fact that B is still hanging around is not a memory leak that is a logic / coding error. So I may not have been complete in that statement.
Sure accessing a class' members & setting their values in my inexperienced mind seems normal. But the ability to add additional parameters from the consumer to a local variable in the called procedure does not seem normal (intimated by Mark's comment & further explanation 'Allows consumer to modify objects properties).
That is just syntax and maybe you are not seeing it. But you probably have done something like that before. The addbtn returns a reference to the new created btn.
The only thing that may look different is that the object you are returning in that line you created.

This is similar syntax of something more familiar.
I want to create a query def and then add its sql.

Code:
Public Sub Demo()
  Dim qdf As QueryDef
  Set qdf = CurrentDb.CreateQueryDef("Test")
  qdf.SQL = "Select * from tPersons"
End Sub

But I can simply have it return the new created query def and modify it

Code:
Public Sub Demo()
  CurrentDb.CreateQueryDef("Test").SQL = "Select * from tPersons"
End Sub
CurrentDb.CreateQueryDef("Test") creates a query def and then immediately after that add the property
 

Users who are viewing this thread

Back
Top Bottom