Wrapping controls WithEvents in classes

[jwcolby54]

Hi John,

I'm a big fan of classes and I'm enjoying your work so far.
One thing I noted was in the very beginning you have a timer class which uses an Api. I run 32 bit at home and 64 bit in the office. Had to change the dec's use it.

#If VBA7 And Win64 Then
    ' Declare for 64-bit Office
    Private Declare PtrSafe Function apiGetTime Lib "winmm.dll" Alias "timeGetTime" () As LongPtr
    Private lngStartTime As LongPtr

#Else
    ' Declare for 32-bit Office (or older VBA versions)
    Private Declare Function apiGetTime Lib "winmm.dll" Alias "timeGetTime" () As Long
    Private lngStartTime As Long

#End If

Very cool. I will put that in now. Which of course brings up having to explain compiler directives.

That change is made to both the book and the database. The DB itself is pushed to Github. And the book is too now.

GitHub desktop is really slick for helping me manage this stuff.

 

[Gasman]

It seems I may have gone wrong. To get the form to even know about the class we need a form. Inside of the form in the header we need global dim. The thing is that while we can create the class before we tell the form about the class, we can't put a reference to the class into the form until the class exists or the compile will complain that it can't find the class... obviously

So I need to stop after creating class form and get that hooked into the form. Let me do that now...

I think I launch right into another class. My bad

I have this in the form

Option Compare Database
Option Explicit
' Standard module or inside the form's code
Dim myFormHandler As ClsFrm

Private Sub Form_Open(Cancel As Integer)
    Set myFormHandler = New ClsFrm
    myFormHandler.mInit (Me)
End Sub

but getting a Type Mismatch on the mInit ?

Have gone looking and the code is on page 133? using fclsFrm ?

 

[jwcolby54]

22 years ago

I have this in the form

Option Compare Database
Option Explicit
' Standard module or inside the form's code
Dim myFormHandler As ClsFrm

Private Sub Form_Open(Cancel As Integer)
    Set myFormHandler = New ClsFrm
    myFormHandler.mInit (Me)
End Sub

but getting a Type Mismatch on the mInit ?

Good job No () around the me. myFormHandler.minit me.

The brackets() expects it to be a function returning something. Which would work but only if you were placing the returned value into a variable.

BTW what also works (if mInit is a function and returns a type clsFrm) is

Set myFormHandler = New ClsFrm.mInit(me) BECAUSE if mInit() is a function and if mInit returns a clsFrm type that syntax works. A bit obtuse though.

 

[jwcolby54]

Apologies folks but I have to go for awhile. I have a very special and special needs daughter that I have to pick up at her school. This is her last week in High School, which is going to be wrenching for her.

I will work on getting the init code into the form we created back aways. Also do not forget to clean up behind ourselves in the form's close event.

 

[Gasman]

Thank you. I now have your form working displaying the initialise and terminate.
That is me done for today. My brain hurts.

 

[moke123]

Just discovered that I have some of your stuff from a few years ago when you did a presentation for George.
Never had the chance at the time to go through it and forgot about it.

 

[jwcolby54]

Just discovered that I have some of your stuff from a few years ago when you did a presentation for George.
Never had the chance at the time to go through it and forgot about it.

LOL, lucky you. Now you can start with fresh newly minted stuff that is probably more better.

Now I gotta find the stuff where I initialize the form class and put it right under where I discuss creating the form class. That is the problem with writing a book, as well as creating a database to go along with it.

 

[Gasman]

Probably quicker for you to just add it?
Page 133 was about the button.

 

[jwcolby54]

Ok, I think I have the book cleaned up to discuss the form initializing the clsFrm. Read it for me and comment on how clear it is or is not.

 

[Gasman]

Will look at it tomorrow.

 

[Gasman]

Ok, I think I have the book cleaned up to discuss the form initializing the clsFrm. Read it for me and comment on how clear it is or is not.

Just off to bed, but thought I would download the latest.
Unable to see where the user form has code added?
I would have expected it to be after creating everything in clsFrm? After page 27 and before the Control Scanner?

 

[isladogs]

I haven't yet had a chance to look at your book but I disagree completely with the conditional compilation in post #22 by @moke123

#If VBA7 works for any 32-bit or 64-bit in A2010 or later (VBA7)
The #Else part is only needed for VBA6 i.e. A2007 or earlier

In addition, the output should be Long in either case (not LongPtr). This presumably affects lngStartTime as well

So it should be written:

#If VBA7 Then 'A2010 or later (32-bit/64-bit)
    Private Declare PtrSafe Function apiGetTime Lib "winmm.dll" Alias "timeGetTime" () As Long
    Private lngStartTime As Long
#Else 'VBA6 (A2007 or earlier
    Private Declare Function apiGetTime Lib "winmm.dll" Alias "timeGetTime" () As Long
    Private lngStartTime As Long
#End If

If all users are running A2010 or later then just use:

    Private Declare PtrSafe Function apiGetTime Lib "winmm.dll" Alias "timeGetTime" () As Long
    Private lngStartTime As Long

Hope that helps

 

[jwcolby54]

Just off to bed, but thought I would download the latest.
Unable to see where the user form has code added?
I would have expected it to be after creating everything in clsFrm? After page 27 and before the Control Scanner?

The control scanner is part of clsFrm. I placed the new section directly after the last part of creating clsFrm. In fact I added a summary at the end of creating clsFrm.

I called the new section "Load clsFrm in the Form", it is a heading two level, and it is on page 29/30 in the LibreOffice document..

BTW is the sidebar showing in the pdf? If not see if you can turn it on. It is available on the left hand side when I open the PDF and it is right handy!

 

[jwcolby54]

I haven't yet had a chance to look at your book but I disagree completely with the conditional compilation in post #22 by @moke123

#If VBA7 works for any 32-bit or 64-bit in A2010 or later (VBA7)
The #Else part is only needed for VBA6 i.e. A2007 or earlier

In addition, the output should be Long in either case (not LongPtr). This presumably affects lngStartTime as well

So it should be written:

#If VBA7 Then 'A2010 or later (32-bit/64-bit)
    Private Declare PtrSafe Function apiGetTime Lib "winmm.dll" Alias "timeGetTime" () As Long
    Private lngStartTime As Long
#Else 'VBA6 (A2007 or earlier
    Private Declare Function apiGetTime Lib "winmm.dll" Alias "timeGetTime" () As Long
    Private lngStartTime As Long
#End If

If all users are running A2010 or later then just use:

    Private Declare PtrSafe Function apiGetTime Lib "winmm.dll" Alias "timeGetTime" () As Long
    Private lngStartTime As Long

Hope that helps

OK I will need guidance on this one. I don't really know whether all of this stuff will run on A2007 or not, though I suspect it will. I'm leaning to using the compiler directives.

BTW there is another version which will come up later in the book which uses fast timing, down to microseconds. More of the api calls.

Both the database as well as the pdf have been pushed to GitHub.

 

[Gasman]

The control scanner is part of clsFrm. I placed the new section directly after the last part of creating clsFrm. In fact I added a summary at the end of creating clsFrm.

I called the new section "Load clsFrm in the Form", it is a heading two level, and it is on page 29/30 in the LibreOffice document..

BTW is the sidebar showing in the pdf? If not see if you can turn it on. It is available on the left hand side when I open the PDF and it is right handy!

OK, found it, however to my way of thinking that is way to late?
On page 26 just after creating all the clsFrm entries, you say

 Save the class and open the form

The first thing that you should notice is that the events of the class fire, displaying the
“Initialize” debug statement. Remember I said that unlike regular modules, classes have
two events that fire when they instantiate. We have created sinks in the class module to
sink these events and so the debug statement will execute when we instantiate the class.
 Close the form

However all the form has at this time is the controls, no code? so that is not going to work?
You then ask to open ClsFrm and add a Close event and amend the Init event.

Yes, I can see the sidebar.

 

[Gasman]

OK I will need guidance on this one. I don't really know whether all of this stuff will run on A2007 or not, though I suspect it will. I'm leaning to using the compiler directives.

BTW there is another version which will come up later in the book which uses fast timing, down to microseconds. More of the api calls.

Both the database as well as the pdf have been pushed to GitHub.

I have one PC with 2007, so I can test for you.

 

[Gasman]

Page 32
You state

With the form in View mode look at the properties / event tab. Notice that there are two
events being raised, Before Update and On Open. Notice also that On Open is hooked
directly in frmDemoCtls but Before Update is not.

How does one do that?. I have only ever looked at properties/events in Design view?
At the moment BeforeUpdate is not being created, even as an empty event. I only have Open and Close events, both of which I have added manually under your instruction.

 

[Gasman]

Page 29
You specify

Private colCtls As Collection

and then

Private Sub Class_Initialize()
Set colCtls = New Collection
End Sub
Private Sub Class_Terminate()
Set colCtls = Nothing
End Sub

but then use

Case acComboBox
Dim lclsCtlCbo As clsCtlCbo
Set lclsCtlCbo = New clsCtlCbo
lclsCtlCbo.mInit ctl
mcolCtls.Add lclsCtlCbo, ctl.Name

so the compile balked at mcolCtls

Changing them all to mcolCtls allows a compile? So which should it be please?

Edit:
You specify this on 37

Private Sub mctlCbo_GotFocus()
mctlCbo.BackColor = cBackColorFocus
End Sub

but have not defined cBackColorFocus

 

[jwcolby54]

Apologies but I have to take my daughter Allie to school, then I have to pick up the ex and bring her home from a recovery place (knee replacement). I will be back available probably this afternoon.

 

[moke123]

but I disagree completely with the conditional compilation in post #22 by @moke123

You can blame A.I. for that. But it did work.