The purpose is simply to make it run faster.
ActiveX dll's are run just faster than the same code in VBA.
I have done some timing tests and it can be about twice as fast.
You will loose a tiny bit of speed by generating and handling the events,
but the same would apply with the VBA code.
Thanks for showing me how to calculate the progress in VB6 and pass it to
VBA. Obvious indeed when you think about it.

RBS

"Peter T" <peter_t@discussions wrote in message
...
Hi RBS,

Unfortunately I know little about integrating VB6 & Excel. Can I ask -
what
is the purpose of controlling a loop from VB6 to perform a macro in VBA.
At
least that's what I understand from the example you posted, but I guess I
am
missing something.

In your more recent message to Chip you ask -

quote
Would it be possible to pass the loop counter (or loop counter divided by
ubound of one of the arrays) from the ActiveX dll to the Excel add-in with
the raised event?
unquote

FWIW, I changed your example as follows to loop & calc the Round in VB6
and
display the result each time in VBA, it seems to work (but for what
purpose
???)

In VB6 TestClass.cls

Option Explicit
Public Event EV()
Public d As Double
Dim lmax2 As Long

Sub TestEvent()
Dim i As Long
For i = 1 To 10
d = Round(i / lmax2, 1)

RaiseEvent EV
Next
End Sub

Public Property Get vbRound() As Double
vbRound = d
End Property
Public Property Let MaxVal(n As Long)
lmax2 = n
End Property

In VBA Class1

Option Explicit
Public WithEvents T As EventTest.TestClass
Private lmax2 As Long

Public Sub T_EV()
Static i As Long
i = i + 1
MsgBox T.vbRound, , i & "/" & lmax2
'MsgBox Round(i / lmax2, 1), , i & "/" & lmax2

End Sub

Public Sub ShowEvent(lmax1 As Long)
'other than in the module (where no events are raised)
'we need to set the object here
'----------------------------------------------------
Set T = New EventTest.TestClass
lmax2 = lmax1
T.MaxVal = lmax2
T.TestEvent
End Sub

In a normal code module: code as per orignal

(need to close XL and remake the vb dll)

Regards,
Peter T



"RB Smissaert" wrote in message
...
OK, I think I have worked this out now.
Made a very simple example like this:

In VB6:

Project EventTest (to make an ActiveX dll)
One Class called TestClass, with instance GlobalMultiUse
In the class module:

Option Explicit
Public Event EV()

Sub TestEvent()
Dim i As Long
For i = 1 To 10
RaiseEvent EV
Next
End Sub

In Excel:

One Class called Class1
In this class module:

Option Explicit
Public WithEvents T As EventTest.TestClass
Private lmax2 As Long

Public Sub T_EV()
Static i As Long
i = i + 1
MsgBox Round(i / lmax2, 1), , i & "/" & lmax2
End Sub

Public Sub ShowEvent(lmax1 As Long)
'other than in the module (where no events are raised)
'we need to set the object here
'----------------------------------------------------
Set T = New EventTest.TestClass
lmax2 = lmax1
T.TestEvent
End Sub

In a normal code module:

Option Explicit

Sub start()
Dim cls As Class1
Set cls = New Class1
cls.ShowEvent 10
End Sub

Sub test()
'to demonstrate that GlobalMultiUse still works
'and that we can run TestEvent directly as if it
'was a normal Sub
'----------------------------------------------
TestEvent
End Sub

Running start shows the events.
Running test doesn't show the events.
This is exactly how I want it as I can now run the code with or without
the events.
It all looks slightly clunky, but it seems to work fine.


RBS



"Chip Pearson" wrote in message
...
Can you change the code in the VB6 ActiveX DLL? If not, there isn't
much
you can do. If you can change the code, you could have it raise an
event
every iteration of the loop, and trap the event in your userform class
module.

In the VB6 code, declare an event like

Public Event CounterTick()

then in the loop, raise the event with code like

RaiseEvent CounterTick

In your VBA Userform code module, declare your ActiveX DLL with the
WithEvents keyword:

Public WithEvents Obj As Proj.Object

and use an event procedure to update the progress bar:

Public Sub Obj_CounterTick()
' update progress indicator
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com







"RB Smissaert" wrote in message
...
I have a simple VB6 ActiveX dll that runs a loop, comparing dates in an
array.
This dll is called by an Excel .xla add-in.
Now I would like to show the progress of this dll in a userform's
progressbar in the .xla add-in.
What would be the best way to do this?

RBS