Posted to microsoft.public.excel.programming
|
|
ActiveX dll with progress feedback to Excel .xla
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
|