in the Pseudo-Control-Array
|
Breakthrough in the Pseudo-Control-Array |
|
[ back to Bpca ]
( Because I who am poor at English am translating into English while using translation software,
there may be an odd expression. The mistranslation
revises it sequentially. )
General-purpose class module ( clsBpca ) Ver 4.0[ AddinBox Home (Japanese) ] [ English Home ] [ back to Bpca ]
(Jpn. 1st Edition : 17 May 2004 ) (Jpn. Last Edition : 1 Sep. 2020 ) (Eng. 1st Translation: 24 Jul. 2014 ) (Eng. Last Translation: 1 Sep. 2020 )
Event programming same as VisualBasic is enabled in VBA ,
only if you import clsBpca class module in your workbook as the Black-Box.
What you should do is ...
At first, prepare for a variable of Control-Array (clsBpca object).
Next, carry out "Rgst" method after repeating "Add" method.
After that ... , describe event procedure commonly.
Only this. ( Please check it in Sample Macro )
clsBpca supports the following controls and events, and it works in x64.
-- Controls --
Label , TextBox , CommandButton , OptionButton
CheckBox , ComboBox , SpinButton , ToggleButton
Among standard controls, ListBox/Frame/TabStrip/ MultiPage/ScrollBar is excluded
from supported controls. It is the reason that is not placed much so that these controls
becomes Control-Array.
-- Events --
Enter , Exit , BeforeUpdate , AfterUpdate
Change , Click , DblClick , KeyDown , KeyPress , KeyUp
MouseMove , MouseDown , MouseUp
SpinDown , SpinUp , DropButtonClick
FakeExit
FakeExit event was added in Ver3.0 ( 11 Oct. 2016 ).
(Note: Ver3.1 (updated by malfunction , 13 Oct. 2016 )
In the case of the focus movement to get away from Frame / MultiPage, Exit event of the source
control does not fire (it fire later). FakeExit event to become the substitute was added.
( See below for further details. )
The use of the Enter / Exit / BeforeUpdate / AfterUpdate event is possible
since clsBpca in Ver2.0 ( 11 Aug. 2014 ).
In the class module of VBA, it cannot usually receive events of Enter , Exit , BeforeUpdate
and AfterUpdate of the MsForms-Controls. It become able to receive the events by using API
(ConnectToConnectionPoint).
( See below for further details. )
The usage of this class, please refer to Sample Macro (record it in a distribution
file) and the reference guide.
Not only the Control-Array but also an event handling of the control to add
dynamically can use this class.
[ Bpca_Class_V40E.zip ] ( 168 kb ) ( It works in x64. not works in Mac. )
Bpca_Class_V40E.zip ( Distribution file )
+-- Bpca_Class_V40E.xls ( Class Definition and Sample Macro )
+-- ExportV40E ( Export file , Please import this file in your workbook. )
+-- clsBpca.cls
+-- clsBpcaCh.cls
+-- modBpcaConst.bas
+-- frmCalendarTool.frm ( cf. example of the calendar form )
If warning of "<file name> is not commonly downloaded and could be dangerous"
is given by the downloading of the file, ....
(Note)
Because Enter / Exit events implement it by Windows-API, you cannot use Ver4.0 in Mac PC.
In the case of Mac PC, please use Ver1.4 where Enter / Exit events are not implemented.
22 Jun 2004 Ver 1.0 1st version (Jpn.)
22 Jul 2014 Ver 1.4 (Jpn.)
11 Aug 2014 Ver 2.0 (Jpn.) Enter / Exit / BeforeUpdate / AfterUpdate event are supported.
11 Oct 2016 Ver 3.0 (Jpn.) FakeExit event and InitFColor/InitBColor properties are supported.
13 Oct 2016 Ver 3.1 (Jpn.) Correction of the malfunction. When Frame/MultiPage does not have
ActiveControl (ActiveControl=Nothing), it becomes the error by FakeExit processing.
1 Sep 2020 Ver 4.0 (Jpn.) Fixed a bug that a runtime error occurs when controls on a worksheet are specified.
24 Jul 2014 Ver 1.4 English 1st version
11 Aug 2014 Ver 2.0 (Eng.) Enter / Exit / BeforeUpdate / AfterUpdate event are supported.
11 Oct 2016 Ver 3.0 (Eng.) FakeExit event and InitFColor/InitBColor properties are supported.
13 Oct 2016 Ver 3.1 (Eng.) Correction of the malfunction. When Frame/MultiPage does not have
ActiveControl (ActiveControl=Nothing), it becomes the error by FakeExit processing.
1 Sep 2020 Ver 4.0 (Eng.) Fixed a bug that a runtime error occurs when controls on a worksheet are specified.
(1) It is necessary to take 3 following modules in workbook by import from
a distribution file to use this class.
(a) clsBpca and clsBpcaCh (Class module)
(b) modBpcaConst (Standard module) ( See below for further details. )
When you copy a class module in workbook,
please perform it by Import by all means.
If you perform it by "Copy & Paste" between code windows of VBE ,
Item property does not become "Default Property"
and Events of Enter etc. do not work. ( See below for further details. )
(2) clsBpcaCh class is a lower class of the clsBpca class (a user cannot use directly
this class).
(3) This class is available Excel2000 and later. (It works in x64.)
[ Link to here ]
[ Reference of Event / Method / Property ]
Within the following description, the object variable which defined clsBpca class
is described into the portion of "object".
[ Definition ]
It defines by the declaration section (module head) of a UserForm module as follows.
Private WithEvents object As clsBpca
You can define plural if you change "object".
( object )
Specify arbitrary names.
In "Initialize" event procedure, the instance of a class is created as follows.
Set object = New clsBpca
(Note) If you want to specify the controls on the worksheet, write it in < ThisWorkbook > module. ( Use Example )
[ Method ]
object .Add MsForms-Control-object
Method to register Control objects making Control-Array with clsBpca class.
You repeat "Add" method for controls to become one group and register by "Rgst" method last.
As for all controls to enroll in one "object", only the same type (TextBox, CommandButton etc.)
is possible. But controls of the different type can register when you appoint "True" in "AllType"
argument of "Rgst" method. (Rgst method is carried out after Add method. Even if controls of the
different type is mixed by Add method, it does not become the error.)
A control number is assigned to the order that you registered by Add method from 1.
This number is treated in each Event or Property as Index.
( MsForms-Control object )
Specify a control object making Control-Array.
( Note )
When specifying the controls on the worksheet,
write it in the [ Workbook_Open ] event of the ThisWorkbook module,
and use the following style ( Use Example ).
[ Worksheet Name.OLEObjects ( Control Name ).Object ]
object .Rgst Event , [ AllType ]
Method to generate Control-Array from the controls that you added by Add method.
After having repeated Add method, you carry out Rgst method.
When AllType argument is False (or omit) and controls of different type are mixed, Rgst method fails.
(Thereafter, nothing is carried out even if you call any method and property.)
( Event )
Appoint the event type that you want to fire with the total of the event type constant.
Because it is defined with an enumeration type (BPCA_Event), you can input the Event argument
by IntelliSense. (The list of Event type (BPCA_Event) constants mentions it later.)
example : object.Rgst BPCA_Change + BPCA_Click
( AllType )
When you let controls of different type (CommandButton and TextBox etc.) mix, appoint "True".
Usually omit it (Default value is False).
( Note )
When specifying the controls on the worksheet,
write it in the [ Workbook_Open ] event of the ThisWorkbook module ( Use Example ).
object .Clear
Method to perform the release of the class object.
Always carry it out by Terminate event of UserForm.
( Note )
When specifying the controls on the worksheet,
write it in the [ Workbook_BeforeClose ] event of the ThisWorkbook module ( Use Example ).
You cannot use the following method in a user side.
It is a method to send an event message from clsBpcaCh to clsBpca.
RaiseEnter , RaiseExit , RaiseBeforeUpdate , RaiseAfterUpdate
RaiseChange , RaiseClick , RaiseDblClick
RaiseKeyDown , RaiseKeyPress , RaiseKeyUp
RaiseMouseMove , RaiseMouseDown , RaiseMouseUp
RaiseDropButtonClick , RaiseSpinDown , RiseSpinUp
Exit4FakeExit
[ Property ]
object .Count [ = Integer ]
Property to acquire the number of controls registered with Control-Array of "object".
[ Read only ]
( Getting )
Return numerical value for the number of controls that you registered by Add method.
This value is the maximum of "Control Number".
object .InitFColor ( Index ) [ = Long ] ( added since Ver 3.0 )
object .InitBColor ( Index ) [ = Long ] ( added since Ver 3.0 )
For the control that you appointed in Index in Control-Array of "object", Initial-Font Color ( ForeColor )
and Initial-Background Color ( BackColor ) return.
[ Read only ]
The Initial color (Font color and Background color) are acquired from the individual control that registered
for Rgst method run time. When you handle to change the color (by an error or focus etc.) of the control, please
use it when you return it to the initial color.
With WeekBtn
.Item(Index).BackColor = .InitBColor(Index)
End With
( Index )
Appoint a control number (in the order that you added by Add method consecutive numbers
from 1).
( Getting )
About apointed control, Initial-Font Color ( ForeColor ) and Initial-Background Color ( BackColor ) return.
In the case of a non-registration number, it returns minus 1.
object .getIndex ( CtrlName ) [ = Integer ]
Property to acquire the value of Index (position number registered in collection) corresponding to
the control name from a control object registered with Control-Array of "object".
[ Read only ]
example :
Dim Index As Long
Index = WeekBtn .getIndex ( "cmdSun" )
WeekBtn .Item ( Index ) .BackColor = vbRed
( CtrlName )
Appoint a control name by string.
( Getting )
It returns a value of "Index" becoming necessary for each property from a control name.
In the case of a non-registration name, it returns minus 1.
object .EnableEvents ( Index ) [ = Boolean ]
For the control that you appointed in Index in Control-Array of "object", you enable or
disable an event.
[ Read / Write ]
When you want to disable an event temporarily, you set False just before the processing
and go back up after processing in True.
For example, you use it when you change a value of other control of the group during the
handling of Change event (Usually, change event fire for the control that changed the
value in an event).
( Index )
Appoint a control number (in the order that you added by Add method consecutive numbers
from 1).
( Setting )
About apointed control, you set a state in True ( Enable ) or False ( Disable ).
( Getting )
About apointed control, a state returns in True or False.
object .EventState [ = Variant (Array (0 to 17) by "Array function") ]
About a group of the object, Event argument of Rgst method returns with array (variant type).
[ Read only ]
( Getting )
[ 0 ] is Long type , [ 1 ～ 17 ] are Boolean type.
[ 0 ] : The value (numeric) of Event argument of Rgst method.
[ 1 to 17 ] : The state of the following events are set in True or False.
[ 1 ] Change , [ 2 ] Click , [ 3 ] DblClick
[ 4 ] KeyDown , [ 5 ] KeyPress , [ 6 ] KeyUp
[ 7 ] MouseMove , [ 8 ] MouseDown , [ 9 ] MouseUp
[ 10 ] DropBtnClick , [ 11 ] SpinDown , [ 12 ] SpinUp
[ 13 ] Enter , [ 14 ] Exit , [ 15 ] BeforeUpdate , [ 16 ] AfterUpdate
[ 17 ] FakeExit
(17 is added since Ver 3.0)
(13 to 16 are added since Ver 2.0)
(The list of Event type constants mentions it later.)
example :
object.Rgst BPCA_Change + BPCA_Click
Dim vntState As Variant
vntState = object.EventState
The contents (array) of vntState of this case are as follows.
( 3, True, True, False, False, False, False, False, False,
False, False, False, False, False, False, Flase, False, False )
object .Item ( Index ) [ = Object ] ---- Default Property ----
Property to return individual control objects constituting Control-Array of object.
[ Read only ]
(Note)
Read-Only is a limit about the reference to the control object.
It is not a meaning "not to be able to update each property" of the control object.
It is property to acquire the control object when you operate individual control of Control-Array.
You can describe the individual propertys of the object across the period after Item property.
example : object .Item (1) .Value
Because Item property is defined as Default-Property ,
you can omit ".Item" and can describe as follows. ( Q & A )
example : object (1) .Value
Item property returns a control object with a general-purpose Object type.
Therefore, IntelliSense of the property and method does not work when you type a macro code in
VBE. If IntelliSense is required, please use the next ItmXxx property.
IntelliSense does not work, but there is no problem with a manual describing properties.
(But it will become the run-time error if it is the property that the control does not implement.)
( Index )
Appoint a control number (in the order that you added by Add method consecutive numbers
from 1). You can appoint it by a control name if you use getIndex property together.
( Getting )
The control object of the number that you appointed in Index argument returns with a general-
purpose Object type.
If the number that you appointed is out of a range (1 to object.Count), it return Nothing as
an error.
object .ItmLbl ( Index ) [ = MsForms.Label ]
object .ItmCmd ( Index ) [ = MsForms.CommandButton ]
object .ItmTxt ( Index ) [ = MsForms.TextBox ]
object .ItmChk ( Index ) [ = MsForms.CheckBox ]
object .ItmOpt ( Index ) [ = MsForms.OptionButton ]
object .ItmTgl ( Index ) [ = MsForms.ToggleButton ]
object .ItmCbo ( Index ) [ = MsForms.ComboBox ]
object .ItmSpn ( Index ) [ = MsForms.SpinButton ]
Property to return individual control objects constituting Control-Array of object.
[ Read only ]
(Note)
Read-Only is a limit about the reference to the control object.
It is not a meaning "not to be able to update each property" of the control.
Because it is the property according to the control types such as Label or CommandButton,
IntelliSense works when you type a macro code in VBE.
( Index )
Appoint a control number (in the order that you added by Add method consecutive numbers
from 1). You can appoint it by a control name if you use the getIndex property together.
( Getting )
The control object of the number that you appointed in Index argument returns with control
type of MsForms corresponding to each property.
If the number that you appointed is out of a range (1 to object.Count), it return Nothing as
an error.
If a control type does not accord with a property and appointed control, it return Nothing as
an error.
[ Event ]
Private Sub object_Change ( ByVal Index As Integer )
Private Sub object_Click ( ByVal Index As Integer )
Private Sub object_DblClick ( ByVal Index As Integer, _
ByVal Cancel As MSForms.ReturnBoolean )
Private Sub object_KeyDown ( ByVal Index As Integer, _
ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer )
Private Sub object_KeyPress ( ByVal Index As Integer,
ByVal KeyAscii As MSForms.ReturnInteger )
Private Sub object_KeyUp ( ByVal Index As Integer, _
ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer )
Private Sub object_MouseMove ( ByVal Index As Integer,
ByVal Button As Integer, ByVal Shift As Integer, _
ByVal X As Single, ByVal Y As Single )
Private Sub object_MouseDown ( ByVal Index As Integer,
ByVal Button As Integer, ByVal Shift As Integer, _
ByVal X As Single, ByVal Y As Single )
Private Sub object_MouseUp ( ByVal Index As Integer,
ByVal Button As Integer, ByVal Shift As Integer, _
ByVal X As Single, ByVal Y As Single )
Private Sub object_DropButtonClick ( ByVal Index As Integer )
Private Sub object_SpinDown ( ByVal Index As Integer )
Private Sub object_SpinUp ( ByVal Index As Integer )
------ 4 following events are added since Ver 2.0 ( Q & A ) ------
Private Sub object_OnEnter ( ByVal Index As Integer )
Private Sub object_OnExit ( ByVal Index As Integer, _
ByVal Cancel As MSForms.ReturnBoolean )
Private Sub object_BeforeUpdate ( ByVal Index As Integer, _
ByVal Cancel As MSForms.ReturnBoolean )
Private Sub object_AfterUpdate ( ByVal Index As Integer )
( Note )
It becomes the Error when we define "Exit event" by Custom event because the Name conflicts
with "Exit statement".
Therefore Exit event defines it by the name of OnExit.
In addition, Enter event is defined by the name of OnEnter as a fellow of Exit, too.
------ following event is added since Ver 3.0 ( Q & A ) ------
Private Sub object_FakeExit ( ByVal Index As Integer )
The event of each controls that enrolled in object is handled all by one event procedure mentioned
above.
The format of argument of each events adds follows to the top of the argument of the normal MsForms
control.
ByVal Index As Integer ,
( Index )
A number of the control (in the order that you added by Add method consecutive numbers from 1)
that an event fired returns.
You can access the control that an event fired in Item or ItmXxx property (you appoint a number of
Index in an argument).
( other arguments )
Watch the help-document of UserForm (MsForms).
( Note )
1. About the event that you appointed to handle it in this clsBpca class, please do not describe normal
event procedure (You will carry it out double).
2. The event that you do not appoint by Rgst method does not fire even if you prepare for event procedure
in UserForm.
3. The event that you appointed by Rgst method does not fire if you do not prepare for event procedure
in UserForm.
4. The event that is not implemented by the control does not fire even if you appoint it by Rgst method.
5. The style to appoint BPCA_All by Rgst method and to prepare for procedure only by a necessary event
in UserForm is possible. However, please appoint only a necessary event for Rgst method so that it is
not overloaded.
6. FakeExit event is provided only for controls in Frame / MultiPage.
[ Link to here ]
[ Q & A ]
( a ) The omission of the Item property becomes the error.
(Symptom)
The following description that omitted Item property becomes the error.
ex. WeekBtn(Index).BackColor
It does not become the error if I describe Item property.
WeekBtn.Item(Index).BackColor
( b ) Events of Enter,Exit,BeforeUpdate,AfterUpdate do not work.
(Symptom)
Though it is appointed by Rgst method,
events of Enter,Exit,BeforeUpdate,AfterUpdate do not work.
--- Answer ( a ) & ( b ) ---
(Cause)
You performed "copy & paste" of clsBpca or clcBpcaCh class module between code windows of VBE.
(a) is clsBpca, and (b) is clsBpcaCh.
(Solution)
Please delete clsBpca or clsBpcaCh class module by "Free module".
Thereafter, please import from export file (clsBpca.cls , clsBpcaCh.cls) in the distribution file.
(Explanation)
Attribute statement (it is invisible on the code window) is written in at the procedure
of Item property and 4 events mentioned above (it is visible on the export file).
The Attribute statement lacks when you copy a class module by "copy & paste".
As a result, Default property and Events do not work.
( c ) Events of Enter,Exit may not fire in Frame control.
(Symptom)
Events of Enter,Exit may not fire when you use clsBpca for controls in Frame control.
--- Answer ( c ) ---
(Cause)
It depends on the behavior of the Enter/Exit events of VBA and is not a bug of clsBpca.
It becomes the same behavior without using clsBpca when you describe Enter/Exit events every each controls.
In addition, a similar symptom is caused in MultiPage.
The detailed commentary of the cause, please read "Tips03 : Focus changes without passing Exit event".
(Solution)
In the case of Frame, you can evade it by substituting the border line of the Label control.
(Please refer to the link mentioned above for how to make the border line by the Label control.)
In the case of MultiPage, it is necessary to examine the processing method without the Enter/Exit event.
( d ) What is FakeExit event ?
--- Answer ( d ) ---
Exit event is the event that is generally used by the error checks of input contents, but has a problem
by the control in the Frame / MultiPage control. In the case of the focus movement to get away from
Frame / MultiPage, Exit event of the source control does not fire ( it fire later ).
In the case of focus movement to get away from Frame / MultiPage control, it fire a substituted event
( FakeExit ) for the source control to deal with this problem. Specifically, in the case of Frame_Exit /
MultiPage_Exit / MultiPage_Change, it fire FakeExit event for the source control ( Frame.ActiveControl
or MultiPage.Page(x).ActiveControl ). In addition, there is no function staying a focus by [ Cancel = True ]
as it is a substitute event. Furthermore, please be careful about the original Exit event is not "not to fire"
and fire later.
( e ) How to use FakeExit event ?
--- Answer ( e ) ---
It appoint FakeExit in the Event argument of the Rgst method (it add Bpca_FakeExit) and prepare for
a description of the above FakeExit event procedure. With that alone it is available.
It is not necessary to mind which control is placed in which Frame / MultiPage in the user side. It is OK
just to perform "additional by Add method" and "practice of Rgst method" commonly. It recognize the
relations of the control and the container control in a class module automatically by appointing FakeExit
for Rgst method.
It cannot stay the focus by [ Cancel = True ] with FakeExit event. Therefore, when you use it for an error check,
only the set of the error flag, please. And it perform the last error confirmation with practice button. Of course
even other original Exit events make only the set of the error flag without using [ Cancel = True ].
It cannot fire even FakeExit event with the case of the red arrow of the chart below when you make Frame
multilayered structure ( because Frame3_Exit does not fire. ).
If it is the frame as the simple boundary line, as you can substitute it by Label control as follows, please
use Label control.
a) It place Label control of the size of the frame
b) Property setting
Caption = ""
BackStyle = fmBackStyleTransparent
SpecialEffect = fmSpecialEffectEtched
c) Setting of ZOrder
Select Label control and carry out [ Format > Order > SendToBack ] of the menu.
( f ) Can it also be used for controls on worksheet ?
--- Answer ( f ) ---
It is also available for controls on worksheet. Please refer to the usage example.
However, the Enter / Exit events do not work for controls on the worksheet.
[ Usage example ]
Please use follows (rewrite this macro in clsBpca.) or here sample macro
for reference.
You can rewrite "WeekBtn.ItmCmd(Index).xxx" in the following macro
with "WeekBtn.Item(Index).xxx". Furthermore, you omit "Item" and
can rewrite it with "WeekBtn(Index).xxx". (Item is a default property)
Private WithEvents WeekBtn As clsBpca
Private Sub UserForm_Initialize()
Set WeekBtn = New clsBpca
With WeekBtn
.Add cmdSun
.Add cmdMon
.Add cmdTue
.Add cmdWed
.Add cmdThu
.Add cmdFri
.Add cmdSat
.Rgst BPCA_Click
End With
End Sub
Private Sub UserForm_Terminate()
WeekBtn.Clear ' Clean up and Release
Set WeekBtn = Nothing
End Sub
Private Sub WeekBtn_Click(ByVal Index As Integer)
Dim vntWeekName As Variant
Dim i As Integer
vntWeekName = Array("", "Sun", "Mon", "Tue", _
Wed", "Thu", "Fri", "Sat")
If (WeekBtn.ItmCmd(Index).BackColor = vbButtonFace) Then
For i = 1 To 7
If (i = Index) Then
WeekBtn.ItmCmd(i).BackColor = &HCCFFFF 'Light Yellow
Else
WeekBtn.ItmCmd(i).BackColor = vbButtonFace
End If
Next i
Else
WeekBtn.ItmCmd(Index).BackColor = vbButtonFace
End If
MsgBox "[" & vntWeekName(Index) & _
"] button is clicked (" & Index & ")"
End Sub
As described above, it is necessary to generate clsBpca class for object
in "Set statement with New keyword" before adding it by Add method.
You can handle each event procedures of clsBpca class in the same way as
event procedures of the normal control.
Using "Index" added to the top of the argument, you can operate individual
control objects by Item or ItmXxx property.
[ Link to here ]
[ Event type constants ( modBpcaConst module) ]
Public Const BPCA2_Change As Long = 1 '[ 1]bit (ON)
Public Const BPCA2_Click As Long = 2 '[ 2]
Public Const BPCA2_DblClick As Long = 4 '[ 3]
Public Const BPCA2_KeyDown As Long = 8 '[ 4]
Public Const BPCA2_KeyPress As Long = 16 '[ 5]
Public Const BPCA2_KeyUp As Long = 32 '[ 6]
Public Const BPCA2_MouseMove As Long = 64 '[ 7]
Public Const BPCA2_MouseDown As Long = 128 '[ 8]
Public Const BPCA2_MouseUp As Long = 256 '[ 9]
Public Const BPCA2_DropBtnClick As Long = 512 '[10]
Public Const BPCA2_SpinDown As Long = 1024 '[11]
Public Const BPCA2_SpinUp As Long = 2048 '[12]
Public Const BPCA2_Enter As Long = 4096 '[13]
Public Const BPCA2_Exit As Long = 8192 '[14]
Public Const BPCA2_BeforeUpdate As Long = 16384 '[15]
Public Const BPCA2_AfterUpdate As Long = 32768 '[16]
Public Const BPCA2_FakeExit As Long = 65536 '[17]
Public Const BPCA2_All As Long = &HFFFF& '(65535 , 16bit all-ON ,FakeExit is excluded)
Public Const BPCA2_KeyDU As Long = BPCA2_KeyDown + BPCA2_KeyUp
Public Const BPCA2_KeyDPU As Long = BPCA2_KeyDown + BPCA2_KeyPress + BPCA2_KeyUp
Public Const BPCA2_MouseDU As Long = BPCA2_MouseDown + BPCA2_MouseUp
Public Const BPCA2_MouseMDU As Long = BPCA2_MouseMove + BPCA2_MouseDown + BPCA2_MouseUp
Public Const BPCA2_SpinDU As Long = BPCA2_SpinDown + BPCA2_SpinUp
Public Const BPCA2_EnterExit As Long = BPCA2_Enter + BPCA2_Exit
Public Const BPCA2_BAUpdate As Long = BPCA2_BeforeUpdate + BPCA2_AfterUpdate
Public Const BPCA2_Except_MouseM As Long = BPCA2_All - BPCA2_MouseMove
'-- Enumeration list of Event type constants --
Public Enum BPCA_Event
BPCA_Change = BPCA2_Change
BPCA_Click = BPCA2_Click
BPCA_DblClick = BPCA2_DblClick
BPCA_KeyDown = BPCA2_KeyDown
BPCA_KeyPress = BPCA2_KeyPress
BPCA_KeyUp = BPCA2_KeyUp
BPCA_MouseMove = BPCA2_MouseMove
BPCA_MouseDown = BPCA2_MouseDown
BPCA_MouseUp = BPCA2_MouseUp
BPCA_DropBtnClick = BPCA2_DropBtnClick
BPCA_SpinDown = BPCA2_SpinDown
BPCA_SpinUp = BPCA2_SpinUp
BPCA_Enter = BPCA2_Enter
BPCA_Exit = BPCA2_Exit
BPCA_BeforeUpdate = BPCA2_BeforeUpdate
BPCA_AfterUpdate = BPCA2_AfterUpdate
BPCA_FakeExit = BPCA2_FakeExit
BPCA_All = BPCA2_All
BPCA_KeyDU = BPCA2_KeyDU
BPCA_KeyDPU = BPCA2_KeyDPU
BPCA_MouseDU = BPCA2_MouseDU
BPCA_MouseMDU = BPCA2_MouseMDU
BPCA_SpinDU = BPCA2_SpinDU
BPCA_EnterExit = BPCA2_EnterExit
BPCA_BAUpdate = BPCA2_BAUpdate
BPCA_Except_MouseM = BPCA2_Except_MouseM
End Enum
[ Link to here , Jpn. site ]
(11 Aug. 2014 addition)
Because I who am poor at English am translating into English while using translation
Implementation of the event handling
by API : ConnectToConnectionPoint
software, there may be an odd expression. The mistranslationrevises it sequentially.
(Note) Because I use Windows-API, you cannot use it with Mac PC.
You cannot define Enter / Exit / BeforeUpdate / AfterUpdate events
for MsForms controls such as TextBox / CommandButton in VBA class modules.
It is a shameful and inconvenient VBA specification, but by using API (ConnectToConnectionPoint),
You can detect the occurrence of an Exit event, etc.
However, unlike a normal event procedure (VBA), the mechanism is difficult, so I will explain it
while comparing the programming of event processing by VBA and API.
------ Normal event handling in VBA ------
(a) I prepare for the Event Handler (Event procedure such as Enter, Click etc.)
for the Event Source (Control placed on UserForm or Object variable with
WithEvents). I acquire the name of the event handler based on the following rule.
Sub Event-Source-Name + Underscore + Event-Name ( arg1 , arg2 , ...... )
(i) In the case of Click event of CommandButton on UserForm (CommandButton1).
Private Sub CommandButton1_Click ( )
(ii) In the case of Exit event of TextBox on UserForm (TextBox1).
Private Sub TextBox1_Exit ( ByVal Cancel As MSForms.ReturnBoolean )
(iii) In the case of TimeOut event of clsTimer class.
Private WithEvents TimerCtrl As clsTimer
Private Sub TimerCtrl_TimeOut ( Arguments )
(b) I describe processing to carry out by the event in the procedure.
(c) VBA system take a role to link to between the fire of the event and the
practice of the event handler. (VBA links both based on the naming rule
of the event handler.)
(d) There may be several control objects becoming the Event source to one
class module (or UserForm module). Because the name of the event handler
is "Event source name + Event name", it is distinguishable.
--- Normal programing of Event handling ---
Even if there are two Event Sources in a class module ,
VBA can describe an event handler for each.
Private WithEvents TBox1 As MsForms.TextBox
Private WithEvents TBox2 As MsForms.TextBox
Private Sub TBox1_Enter()
End Sub
Private Sub TBox2_Enter()
End Sub
------ Event handling by API : ConnectToConnectionPoint ------
At first, please watch the event coding by ConnectToConnectionPoint.
(Because I only have you confirm how to use ConnectToConnectionPoint,
I do it simply.)
I prepare four TextBox on UserForm (TextBox1 - TextBox4). It receive an event of Enter , Exit ,
BeforeUpdate , AfterUpdate in a class module (clsC2CP) and output the practice situation in
Immediate Window by Debug.Print. The processing to notify UserForm of an event does not
implement it to do it simply.
[ UserForm module ]
' Because I do not implement processing to notify UserForm of
' an event, WithEvents is unnecessary.
' Therefore I define the class object with Array.
Private aryTextBox(1 To 4) As clsC2CP
Private Sub UserForm_Initialize()
Dim i As Integer
For i = 1 To 4
Set aryTextBox(i) = New clsC2CP
aryTextBox(i).Item = Me.Controls("TextBox" & i)
aryTextBox(i).Index = i
Next i
End Sub
Private Sub UserForm_Terminate()
Dim i As Integer
For i = 1 To 4
aryTextBox(i).Clear
Next i
Erase aryTextBox
End Sub
' A purpose is to check the operation of ConnectToConnectionPoint.
' Therefore I omit processing to notify UserForm of an event.
' (Debug.Print is performed in a class module.)
[ clsC2CP class module ]
(Because it is an example of ConnectToConnectionPoint, the name of the class module
makes it C2CP for short.)
I write in the red statement with an export file. (It is invisible on the code window.).
' API definition [ ConnectToConnectionPoint ]
Private Type GUID
Data1 As Long
Data2 As Integer
Data3 As Integer
Data4(0 To 7) As Byte
End Type
#If VBA7 And Win64 Then
Private Declare PtrSafe Function ConnectToConnectionPoint _
Lib "shlwapi" Alias "#168" _
(ByVal punk As stdole.IUnknown, _
ByRef riidEvent As GUID, _
ByVal fConnect As Long, _
ByVal punkTarget As stdole.IUnknown, _
ByRef pdwCookie As Long, _
Optional ByVal ppcpOut As LongPtr) As Long
#Else
Private Declare Function ConnectToConnectionPoint _
Lib "shlwapi" Alias "#168" _
(ByVal punk As stdole.IUnknown, _
ByRef riidEvent As GUID, _
ByVal fConnect As Long, _
ByVal punkTarget As stdole.IUnknown, _
ByRef pdwCookie As Long, _
Optional ByVal ppcpOut As Long) As Long
#End If
Private Cookie As Long 'pdwCookie of ConnectToConnectionPoint
'-----------------------------------------------------------
Private MyCtrl As Object 'Event Source
Private MyIndex As Integer
Private Sub ConnectEvent(ByVal Connect As Boolean)
Dim IID_IDispatch As GUID
' GUID {00020400-0000-0000-C000000000000046}
With IID_IDispatch
.Data1 = &H20400
.Data4(0) = &HC0
.Data4(7) = &H46
End With
ConnectToConnectionPoint _
Me, IID_IDispatch, Connect, MyCtrl, Cookie, 0&
End Sub
Public Property Let Index(NewIndex As Integer)
MyIndex = NewIndex
End Property
Public Property Let Item(NewCtrl As Object)
Set MyCtrl = NewCtrl
Call ConnectEvent(True) 'Start Connect Event
End Property
Public Sub Clear()
If (Cookie <> 0) Then
Call ConnectEvent(False) 'Finish Connect Event
End If
Set MyCtrl = Nothing
End Sub
'=== Event Handler ===
' Because it is procedure called by Event-Source,
' I define it in Public.
' List of VB_UserMemId
' Enter &H80018202 = -2147384830
' Exit &H80018203 = -2147384829
' BeforeUpdate &H80018201 = -2147384831
' AfterUpdate &H80018200 = -2147384832
Public Sub HookEnter()
Attribute HookEnter.VB_UserMemId = -2147384830
Debug.Print MyCtrl.Name & "(" & MyIndex & _
") [Enter] Value=" & MyCtrl.Value
End Sub
Public Sub HookExit(ByVal Cancel As MSForms.ReturnBoolean)
Attribute HookExit.VB_UserMemId = -2147384829
Debug.Print MyCtrl.Name & "(" & MyIndex & _
") [Exit] Value=" & MyCtrl.Value
End Sub
Public Sub HookBeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Attribute HookBeforeUpdate.VB_UserMemId = -2147384831
Debug.Print MyCtrl.Name & "(" & MyIndex & _
") [BeforeUpdate] Value=" & MyCtrl.Value
End Sub
Public Sub HookAfterUpdate()
Attribute HookAfterUpdate.VB_UserMemId = -2147384832
Debug.Print MyCtrl.Name & "(" & MyIndex & _
") [AfterUpdate] Value=" & MyCtrl.Value
End Sub
[ Result : Immediate window ]
TextBox1(1) [Enter] Value=
TextBox1(1) [BeforeUpdate] Value=1
TextBox1(1) [AfterUpdate] Value=1
TextBox1(1) [Exit] Value=1
TextBox2(2) [Enter] Value=
TextBox2(2) [BeforeUpdate] Value=aa
TextBox2(2) [AfterUpdate] Value=aa
TextBox2(2) [Exit] Value=aa
TextBox3(3) [Enter] Value=
TextBox3(3) [BeforeUpdate] Value=3456
TextBox3(3) [AfterUpdate] Value=3456
TextBox3(3) [Exit] Value=3456
TextBox4(4) [Enter] Value=
TextBox4(4) [BeforeUpdate] Value=qwer
TextBox4(4) [AfterUpdate] Value=qwer
TextBox4(4) [Exit] Value=qwer
TextBox1(1) [Enter] Value=1
TextBox1(1) [Exit] Value=1
(A) The name of the event handler (event procedure) is arbitrary. It is not necessary
to increase the name of the event source.
(Note)
There is no meaning there even if you add an event source name to a procedure name as a prefix.
It only contributes to improvement of the readability of the program.
(B) The point to describe processing to carry out by the event in the procedure is
the same.
(C) ConnectToConnectionPoint (API) and Attribute statement take a role
to link to between the fire of the event and the practice of the event handler.
(i) By ConnectToConnectionPoint, an object receiving the event message which an
event source sends it to is informed to an event source and it is registered.
ConnectToConnectionPoint _
Me, IID_IDispatch, Connect, MyCtrl, Cookie, 0&
I I
+-----------------------------+
Declaration to receive an event message of MyCtrl (Event Source)
in Me object (it is clsC2CP class object in the example).
(Note-1)
The object receiving an event message is called Sink object or Event-Sink. It is the
meaning of a sink (Event-Sink) catching water flowing out in sequence (event message).
(Note-2)
Actually, what's called Invoke method takes the following duty.
[1] At first, receive an event message from the event source.
[2] Next, find out the event handler for the event message.
[3] Finally, succeed processing to the found event handler.
These are invisible parts entirely from VBA user. Macro does not have the figure either.
The details, please read MSDN Library etc.
(Note-3)
This Invoke method is implemented in an Object type variable of VBA (and a class module).
Therefore you can appoint it as an argument of ConnectToConnectionPoint.
(ii) The Attribute statement gives a value to express an event type to procedure.
(cf. above sample macro). By the value, the procedure is declared as an event
handler (event procedure) receiving the event of an appointed type.
(Note)
The Attribute statement is described just under the Sub statement of the event handler
(event procedure). But the Attribute statement is invisible and you cannot describe it
on the code window. Please import after having described it on an export file.
(iii) A value to each express the event is included in various event messages
which an event source sends it to. Invoke method mentioned above finds
out procedure with a value same as the event type included in the event
message (it is for this purpose to set an event type by Attribute statement)
and succeeds processing for the procedure.
It links the event handler to the event source by the event handling using
ConnectToConnectionPoint in this way.
(D) It is only information of the event type to receive to be defined by Attribute statement.
Therefore, a control object becoming the event source by ConnectToConnectionPoint
is only one in a class module (or UserForm module).
--- Programing of Event handling by ConnectToConnectionPoint ---
When there are two event sources in a class module ......
Private TBox3 As Object
Private Cookie3 As Long
Private TBox4 As Object
Private Cookie4 As Long
ConnectToConnectionPoint _
Me, IID_IDispatch, Connect, TBox3, Cookie3, 0&
ConnectToConnectionPoint _
Me, IID_IDispatch, Connect, TBox4, Cookie4, 0&
I can describe it in an individual until the above. However ......
It is only an event type to be appointed by Attribute statement.
Therefore, a system cannot distinguish it even if I describe two event handlers.
(Which is the event handler of TBox3? Is it HookEnterA? Or is it HookEnterB?)
Public Sub HookEnterA()
Attribute HookEnterA.VB_UserMemId = -2147384830
End Sub
Public Sub HookEnterB()
Attribute HookEnterB.VB_UserMemId = -2147384830
End Sub
An Event Source is only one by the event coding by ConnectToConnectionPoint
in this way.
In the first place, you design it by the following guidance
from a beginning when you use ConnectToConnectionPoint.
"One class for one event source"
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Actually, all events supporting in clsBpca can describe only in ConnenctToConnectionPoint.
(You should only appoint the value of the event type corresponding to Change , Click etc. in
Attribute statement.)
However, it is not a desirable thing that is made all in ConnectToConnectionPoint when it
considers that general VBA user (not a senior) reads the macro code of the clsBpca class.
(Even if I explain it in this way here, it will be difficult for general VBA user to understand it.)
Therefore, in clsBpca, I make it with normal event programming about the events (except
Enter, Exit, BeforeUpdate, AfterUpdate) that normal event programming is possible.
