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

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

Chip, thanks for that.
I just got a copy of VB6 and I made the .dll so I can alter the code.
Will do it like that.

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

Chip,

I can't work this quite out.
The instancing of the class in the ActiveX dll (there is only one class with
a number of Subs, nil else) is GlobalMultiuse.
This means I don't have to declare or set the dll object, but I can directly
use it's Subs.
I understand what I should do in VB6, but I have some trouble what to do in
Excel.

Public WithEvents Obj As Proj.Object

I understand this goes at the top (declarations) of the userform.
My dll is referenced in Excel (Tools, References) as VBMatchup.
The Sub I want to run is called CompareArrayDates.
So, as the VB6 class is instanced as globalmultiuse I can just do
in a VBA Sub:
CompareArrayDates arg1, arg2, arg3 etc.

What in your line above are Obj, Proj and Object?

Public Sub Obj_CounterTick()
' update progress indicator
End Sub

Where does this go? Can it be in a normal module or do I have to make a
class module?

The VB6 side is fine after putting in the code as you suggested.
It compiles fine as well.
Would you know any worked out example of this particular scenario?
I have never used custom events, so this is a bit new.
Thanks again for the help.

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

OK, I have got a bit further with this and this is what I got now:

In VB6:

A project VBMatchup
A class module called MatchupArray (MatchupArray.cls)
At the top of the class module code: Public Event CounterTick()
A Function in the class module called Function CompareArrayDates.
This will compare the dates in 2 arrays and return True if successfull.
In a loop in this function:
RaiseEvent CounterTick
The class has the instancing GlobalMultiUse.


In Excel:

At the declaration part of a Userform module:
Public WithEvents VBM As VBMatchup.MatchupArray
An event procedure in this same Userform module:

Public Sub VBM_CounterTick()
MsgBox "test"
End Sub

It compiles and runs, but no messagebox "test"
I have tried with adding:
Set VBM = New VBMatchup.MatchupArray
in the Sub UserForm_Initialize()
but that made no difference.

Is the trouble the GlobalMultiUse instancing in the VB6 class?
Any suggestions where I might be going wrong?


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

Forgot to say that I run MatchupArray in a Sub in normal code module.

RBS


"RB Smissaert" wrote in message
...
OK, I have got a bit further with this and this is what I got now:

In VB6:

A project VBMatchup
A class module called MatchupArray (MatchupArray.cls)
At the top of the class module code: Public Event CounterTick()
A Function in the class module called Function CompareArrayDates.
This will compare the dates in 2 arrays and return True if successfull.
In a loop in this function:
RaiseEvent CounterTick
The class has the instancing GlobalMultiUse.


In Excel:

At the declaration part of a Userform module:
Public WithEvents VBM As VBMatchup.MatchupArray
An event procedure in this same Userform module:

Public Sub VBM_CounterTick()
MsgBox "test"
End Sub

It compiles and runs, but no messagebox "test"
I have tried with adding:
Set VBM = New VBMatchup.MatchupArray
in the Sub UserForm_Initialize()
but that made no difference.

Is the trouble the GlobalMultiUse instancing in the VB6 class?
Any suggestions where I might be going wrong?


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

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

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

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

I put the Round in the dll and it speeded things up a bit more again.
I don't need the Property Let as the max is available in the dll from the
ubound of the array.

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

I put the Round in the dll and it speeded things up a bit more again.
I don't need the Property Let as the max is available in the dll from the
ubound of the array.

RBS

Yes, I knew the Property Let MaxVal was redundant, it was only to show how
to put it there if required.

I was interested to see the "Event" driven method you had devised with
Chip's suggestion. From your original question it seems the main purpose of
all this is to update progress of a VB6 function on an Excel vba form. I've
been playing, seems it's possible to update the vba form directly from VB6 -

'in a VB6 Project named "vb2vba_Update"
'code in class named "clsTest"

Option Explicit

'Dim oVBAform As Object
'
'Public Property Set UsrFrm(oUF As Object)
'''/oVBAform = a Userform from VBA
'If Not oUF Is oVBAform Then
'Set oVBAform = oUF
'End If
'End Property

Public Sub Proc1(oVBAform As Object)
Dim i As Long
Dim nUpper As Long
Dim dPcent As Double
Dim nProgess As Long

nUpper = 12345678
dPcent = nUpper / 100

nProgess = 1

oVBAform.Caption = nProgess & "0%"

For i = 1 To nUpper
'do stuff
If i nProgess * dPcent Then
'Debug.Print i, nProgess, nProgess * dPcent
oVBAform.Caption = nProgess & "%"
nProgess = nProgess + 1
If nProgess Mod 10 = 0 Then
oVBAform.BackColor = 16777215 * Rnd
oVBAform.Repaint
End If
End If
Next

oVBAform.Caption = "Done"
oVBAform.CommandButton1.BackColor = 16777215 * Rnd
oVBAform.CommandButton1.Caption = "Close Me Now"

End Sub

'code in an Excel vba Userform
'with two commandbuttons

Option Explicit

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub CommandButton2_Click()
Dim oVB6 As New vb2vba_Update.clsTest

oVB6.proc1 Me
Set oVB6 = Nothing

End Sub

Private Sub UserForm_Activate()

Me.CommandButton1.Caption = "Quit"
Me.CommandButton2.Caption = "vb6 Proc1"

End Sub

In VB6, name the project and class as described above, run with F5 or
Ctrl-F5
Then, in Excel set a reference to "vb2vba_Update" which should appear in the
list, and run the form.

Normally, would probably want to declare and set object references in both
vb & vba at global level. To simplify in this example, I've kept all ref's
at procedure level. But I've left some commented stuff at top of clsTest,
which could be called from (say) the vba form initialize event.

I'm pretty new at this VBVBA stuff, so please nobody assume this is the
correct way of doing things, or that writing to the vba form direct from vb6
is better than the Event driven method. However it seems to work, somewhat
to my surprise!

Regards,
Peter T

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?
I can do all this in the add-in, but it would be a bit nicer to get this
directly from the dll.

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