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

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

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

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

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" wrote in message
...
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
...
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

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
...
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" wrote in message
...
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
...
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