Creation & Implementation of VB6 DLL in VBA to raise events

[Trip]

Hello All,

I'm struggling a bit and would appreciate any direction anyone might be
able to provide...

I created the following simple ActiveX DLL in VB6, registered the DLL
in windows, and included the references in the two workbooks (described
below). This code resides in "Class1" of the DLL:
____________________________________________________
Public Event DataRecived(Price As String, MarketIndex As Byte)

Public Sub DateRec(Price As String, MarketIndex As Byte)

RaiseEvent DataRecived(Price, MarketIndex)
MsgBox "data received by DLL: " & Price & ", " & MarketIndex

End Sub
___________________________________________________

I then created a workbook to send data via the DLL. I placed the
following code in Sheet1..
__________________________________________________
Public WithEvents SendData As DataEvent.Class1

Sub DataSend()

Set SendData = New DataEvent.Class1
Call SendData.DateRec("12345", 1)
Set SendData = Nothing

End Sub
_________________________________________________

and I created a second workbook to receive the data. This code was
placed in Sheet1 of this workbook:
_______________________________________________
Public WithEvents ReceiveData As DataEvent.Class1

Sub initialize()

Set ReceiveData = New DataEvent.Class1

End Sub

Sub ReceiveData_DataRecived(Price As String, MarketIndex As Byte)

MsgBox Price & ", " & MarketIndex

End Sub
_______________________________________________

After initializing the the "ReceiveData" object in the "data receiving"
workbook, when I raise the event in the "data sending" workbook by
issuing the command:
Call SendData.DateRec("12345", 1)
the DLL manages to give me the shown msgbox, indicating that it is in
fact receiving the data. Yet, the second workbook does not respond to
the:
RaiseEvent DataRecived(Price, MarketIndex)
issued by the DLL. It is as though the event never fired.

Any ideas as to why??

TIA

Trip

 

[NickHK]

If I understand you correctly, you are misunderstanding the way these DLL
object work in VB/VBA.
Each of your :
Public WithEvents SendData As DataEvent.Class1
creates a separate instance of the class. As such there is no connection
between the instances, so your attempt to trigger an event in a different
instance will not work.

Depending on what you are trying to achieve, you could pass a reference to
the recieving WB to your DLL class. Although it can't raise a custom event,
it could then interact with that WB.

Or maybe you can work with GetObject on the send data instance.

NickHK

 

[Trip]

Ahhhh! ok, I see. Thanks Nick! Well, I've got to do a bit more
research and then it's back-to-the-drawing-board.

Any further suggestions you may have in how to implement this would be
great.

Thanks!

Trip

 

[NickHK]

What are you trying to achieve ?

Consider that worksheets modules are basically class modules, with some
signatures already written. That means you can add your own Public routines
and call them from another worksheet. e.g.
However, routines in a .bas module may be sufficient.

<Worksheet(1)>
Public Function Calc(Num1 As Long, Num2 As Long) As Long
Calc = Num1 + Num2
End Function

Public Function ChangeMe(WS As Worksheet, Num2 As Long) As Long
WS.Range("A1").Value = Num2
End Function
</Worksheet(1)>

<Worksheet(2)>
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox Worksheets(1).Calc(10, 10)
Call Worksheets(1).ChangeMe(Me, 100)
End Sub
</Worksheet(2)>

NickHK