[ September 15 2016 ]      AddinBox site moved from [ DION ] to [ SAKURA Internet ].  
      In addition, the old site remains until October 2017.
      New URL of this page http://addinbox.sakura.ne.jp/Breakthrough_P-Ctrl_Arrays_Eng_ref.htm
 
      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 2.0
      ( Ver 3.0 is released in New Site. )
  (Jpn. 1st Edition : 17 May 2004 )
  (Jpn. Last Edition : 11 Aug. 2014 )
  (Eng. 1st Translation: 24 Jul. 2014 )
  (Eng. Last Translation: 11 Aug. 2014 )

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
          The use of the Enter / Exit / BeforeUpdate / AfterUpdate event is possible
          since clsBpca (Ver2.0).
 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.


    ( Ver 3.0 is released in New Site. )

    [ Bpca_Class_V20E.zip ]   ( 143 kb )      ( It works in x64. not works in Mac. )
            Bpca_Class_V20E.zip    ( Distribution file )
                +--  Bpca_Class_V20E.xls      ( Class Definition and Sample Macro )
                +--  ExportV20E    ( Export file , Please import this file in your workbook. )
                            +--  clsBpca.cls
                            +--  clsBpcaCh.cls
                            +--  modBpcaConst.bas

        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 Ver2.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 (Japanese)
        22 Jul 2014        Ver 1.4    (Japanese)
        11 Aug 2014       Ver 2.0    (Japanese)   Enter / Exit / BeforeUpdate / AfterUpdate event are supported.
        24 Jul 2014        Ver 1.4    English 1st version
        11 Aug 2014      Ver 2.0    English version    Enter / Exit / BeforeUpdate / AfterUpdate event are supported.


(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



[ 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.


    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).


    object .Clear

        Method to perform the release of the class object.
        Always carry it out by Terminate event of UserForm.


    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



[ 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 .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 16) 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 ` 16 ] are Boolean type.

        [ 0 ]  :  The value (numeric) of Event argument of Rgst method.
        [ 1 to 16 ]  :  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

        (13 to 16 is 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 )


    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.


    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.


[ 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.



[ 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_All As Long = &HFFFF&    '(65535,16bit all-ON)

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_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 ] [ News of the site move ]

(11 Aug. 2014 addition)
     Implementation of the event handling
              by API : ConnectToConnectionPoint

(Note) Because I use Windows-API, you cannot use it with Mac PC.

I will explain a normal event handling in VBA and an event handling by API
with a comparison.

------ 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.



[ AddinBox Home (Japanese) ]  [ English Home ]  [ back to Bpca ]  AddinBox_Banner


AddinBox ( K.Tsunoda in Japan ) CopyRight(C) 2014 Allrights Reserved.