Thread: ActiveX dll with progress feedback to Excel .xla

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" <bartsmissaert@blueyonder.co.uk> wrote in message
news:%238YmktzTFHA.2456@TK2MSFTNGP10.phx.gbl...
>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" <chip@cpearson.com> wrote in message
news:er633C0TFHA.3012@TK2MSFTNGP14.phx.gbl...
> 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" <bartsmissaert@blueyonder.co.uk> wrote in message
> news:%238YmktzTFHA.2456@TK2MSFTNGP10.phx.gbl...
>>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" <chip@cpearson.com> wrote in message
news:er633C0TFHA.3012@TK2MSFTNGP14.phx.gbl...
> 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" <bartsmissaert@blueyonder.co.uk> wrote in message
> news:%238YmktzTFHA.2456@TK2MSFTNGP10.phx.gbl...
>>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" <chip@cpearson.com> wrote in message
news:er633C0TFHA.3012@TK2MSFTNGP14.phx.gbl...
> 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" <bartsmissaert@blueyonder.co.uk> wrote in message
> news:%238YmktzTFHA.2456@TK2MSFTNGP10.phx.gbl...
>>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" <bartsmissaert@blueyonder.co.uk> wrote in message
news:eyFVg0CUFHA.3840@tk2msftngp13.phx.gbl...
> 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" <chip@cpearson.com> wrote in message
> news:er633C0TFHA.3012@TK2MSFTNGP14.phx.gbl...
>> 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" <bartsmissaert@blueyonder.co.uk> wrote in message
>> news:%238YmktzTFHA.2456@TK2MSFTNGP10.phx.gbl...
>>>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" <chip@cpearson.com> wrote in message
news:er633C0TFHA.3012@TK2MSFTNGP14.phx.gbl...
> 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" <bartsmissaert@blueyonder.co.uk> wrote in message
> news:%238YmktzTFHA.2456@TK2MSFTNGP10.phx.gbl...
>>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
>
>

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" <chip@cpearson.com> wrote in message
news:er633C0TFHA.3012@TK2MSFTNGP14.phx.gbl...
> 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" <bartsmissaert@blueyonder.co.uk> wrote in message
> news:%238YmktzTFHA.2456@TK2MSFTNGP10.phx.gbl...
>>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" <bartsmissaert@blueyonder.co.uk> wrote in message
news:eA9rwmDUFHA.2768@tk2msftngp13.phx.gbl...
> 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" <chip@cpearson.com> wrote in message
> news:er633C0TFHA.3012@TK2MSFTNGP14.phx.gbl...
> > 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" <bartsmissaert@blueyonder.co.uk> wrote in message
> > news:%238YmktzTFHA.2456@TK2MSFTNGP10.phx.gbl...
> >>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
news:ODTr9ANUFHA.2124@TK2MSFTNGP14.phx.gbl...
> 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" <bartsmissaert@blueyonder.co.uk> wrote in message
> news:eA9rwmDUFHA.2768@tk2msftngp13.phx.gbl...
>> 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" <chip@cpearson.com> wrote in message
>> news:er633C0TFHA.3012@TK2MSFTNGP14.phx.gbl...
>> > 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" <bartsmissaert@blueyonder.co.uk> wrote in message
>> > news:%238YmktzTFHA.2456@TK2MSFTNGP10.phx.gbl...
>> >>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
news:ODTr9ANUFHA.2124@TK2MSFTNGP14.phx.gbl...
> 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" <bartsmissaert@blueyonder.co.uk> wrote in message
> news:eA9rwmDUFHA.2768@tk2msftngp13.phx.gbl...
>> 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" <chip@cpearson.com> wrote in message
>> news:er633C0TFHA.3012@TK2MSFTNGP14.phx.gbl...
>> > 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" <bartsmissaert@blueyonder.co.uk> wrote in message
>> > news:%238YmktzTFHA.2456@TK2MSFTNGP10.phx.gbl...
>> >>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 VB>VBA 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

OK, another option.
It doesn't surprise me you can do this as I know you can control Excel from
VB6, similar as Word can Control Excel, so why wouldn't this work with a
user form?

It looks a bit more complex than the event method, so I think I will stick
with that for now.
Did you do any timing test?

RBS


"Peter T" <peter_t@discussions> wrote in message
news:%23kXjWRvUFHA.2468@TK2MSFTNGP10.phx.gbl...
>> 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 VB>VBA 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
>
>

I'm also well aware you can automate Excel from VB6, what surprised me is
that you can simply pass a vba userform as an object and thereafter control
it in vb. Doing some things with objects requires qualifying references all
the way back to parent application.

Timing - not shown in the posted example I ran virtually the same VB6
routine (proc1) from a third commandbutton in the vba form. vba was barely
a tad faster which in itself doesn't prove much. The routine doesn't do
anything other than update the form a few times, and perhaps a few micro
seconds are lost in setting up the reference to the dll and passing the form
object. I didn't time the Event method.

I think far more important than which method is to minimise the number of
progress updates. That's what generally takes a disproportionate amount of
time in loops. Eg, in the example I posted the caption is only updated 100
times in the loop of 12 million. I've seen progress examples in this ng with
updates in every loop, lengthening a routine from a few seconds to a coffee
break.

For your purposes it might be worthwhile comparing "Event driven" vs.
"direct control" with real life scenarios. However I doubt there would be
any significant difference if doing only a small number of updates, choice
would be dictated by other factors - as in simplest.

Regards,
Peter T


"RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
news:eMcQiHxUFHA.3840@tk2msftngp13.phx.gbl...
> OK, another option.
> It doesn't surprise me you can do this as I know you can control Excel
from
> VB6, similar as Word can Control Excel, so why wouldn't this work with a
> user form?
>
> It looks a bit more complex than the event method, so I think I will stick
> with that for now.
> Did you do any timing test?
>
> RBS
>
>
> "Peter T" <peter_t@discussions> wrote in message
> news:%23kXjWRvUFHA.2468@TK2MSFTNGP10.phx.gbl...
> >> 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 VB>VBA 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
> >
> >
>

> I think far more important than which method is to minimise the number of
> progress updates.

Good point. My max number of updates isn't that much, maybe up to about
20.000,
so not really critical, but I will see if I can gain some time by doing it
less often.

RBS


"Peter T" <peter_t@discussions> wrote in message
news:ejE86txUFHA.1896@TK2MSFTNGP14.phx.gbl...
> I'm also well aware you can automate Excel from VB6, what surprised me is
> that you can simply pass a vba userform as an object and thereafter
> control
> it in vb. Doing some things with objects requires qualifying references
> all
> the way back to parent application.
>
> Timing - not shown in the posted example I ran virtually the same VB6
> routine (proc1) from a third commandbutton in the vba form. vba was
> barely
> a tad faster which in itself doesn't prove much. The routine doesn't do
> anything other than update the form a few times, and perhaps a few micro
> seconds are lost in setting up the reference to the dll and passing the
> form
> object. I didn't time the Event method.
>
> I think far more important than which method is to minimise the number of
> progress updates. That's what generally takes a disproportionate amount of
> time in loops. Eg, in the example I posted the caption is only updated 100
> times in the loop of 12 million. I've seen progress examples in this ng
> with
> updates in every loop, lengthening a routine from a few seconds to a
> coffee
> break.
>
> For your purposes it might be worthwhile comparing "Event driven" vs.
> "direct control" with real life scenarios. However I doubt there would be
> any significant difference if doing only a small number of updates, choice
> would be dictated by other factors - as in simplest.
>
> Regards,
> Peter T
>
>
> "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
> news:eMcQiHxUFHA.3840@tk2msftngp13.phx.gbl...
>> OK, another option.
>> It doesn't surprise me you can do this as I know you can control Excel
> from
>> VB6, similar as Word can Control Excel, so why wouldn't this work with a
>> user form?
>>
>> It looks a bit more complex than the event method, so I think I will
>> stick
>> with that for now.
>> Did you do any timing test?
>>
>> RBS
>>
>>
>> "Peter T" <peter_t@discussions> wrote in message
>> news:%23kXjWRvUFHA.2468@TK2MSFTNGP10.phx.gbl...
>> >> 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 VB>VBA 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
>> >
>> >
>>
>
>