Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!aioe.org!.POSTED!not-for-mail
From: Schmidt <sss@online.de>
Newsgroups: comp.lang.basic.visual.misc
Subject: Re: What is a class?
Date: Wed, 29 Feb 2012 06:47:15 +0100
Organization: Aioe.org NNTP Server
Lines: 105
Message-ID: <jike52$h28$1@speranza.aioe.org>
References: <e20a08e0-5d12-4ec8-bb47-c8d415c71d60@hs8g2000vbb.googlegroups.com> <XnsA003488305D80auricauricauricauric@88.198.244.100> <a2936af3-3147-423b-8a74-262ddd837428@db5g2000vbb.googlegroups.com> <XnsA00492FC86A25auricauricauricauric@88.198.244.100> <m_WdnZ1BdOYv6NTSnZ2dnUVZ_jydnZ2d@giganews.com> <XnsA004CD148B656auricauricauricauric@88.198.244.100> <cLCdnTOc3IH0NdTSnZ2dnUVZ_tudnZ2d@giganews.com> <jicag2$87f$1@dont-email.me> <5odjk75idm6fgl98vbmabap2djd38nvum5@4ax.com> <jicjab$6ta$1@dont-email.me> <6sD2r.204924$WX2.187127@newsfe28.ams2> <jig9cq$val$1@dont-email.me> <jiggst$rbl$1@speranza.aioe.org> <jigmdl$k11$1@dont-email.me> <jigroc$q1h$1@speranza.aioe.org> <jihl1e$m6v$1@dont-email.me> <jiiiqg$q7a$1@speranza.aioe.org> <jiilag$d7r$1@dont-email.me>
NNTP-Posting-Host: jN9YdZHS8FAZD9RowC1efQ.user.speranza.aioe.org
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
X-Complaints-To: abuse@aioe.org
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:11.0) Gecko/20120222 Thunderbird/11.0
X-Notice: Filtered by postfilter v. 0.8.2
Xref: x330-a1.tempe.blueboxinc.net comp.lang.basic.visual.misc:860

Am 28.02.2012 14:37, schrieb GS:

>  I can live with the speed of my function 'as is', but for
> larger amounts of data it would be nice to use your code with a DLL.
> The wrench in the works is that this will commonly be used with VBA,
> and so is why I went with a Collection. If I had a DLL then I could
> carry it around on a memstick and use it wherever. Of course, on my
> main machine I have dhRichClient and so no need for a separate DLL.
> It's unfortunate that VBA requires external COMs be registered!

Hah - and so we finally found a good use-case for DirectCOM.dll
on your side <g>.

Please check the following out - it is using a "two-step-approach"
(sounds already complicated, but is not... ;-))

First, we have to move everything that needs speed - into
a separate VB-Dll-Project - in practise that means:
The compare-routine (without the XL-Range-stuff which
writes the results into the OutColumn - instead a vOut()
VariantArray is returned "ByRef")...

Then, only there, within this small ActiveX-Dll (which I named
MyWrapper.dll, having only a small Class, named: cMyClass),
there's a reference to dhSortedDictionary (the smaller,
a bit faster version of the RC-based cSortedDictionary).

Within this new VB6-Dll we now only need to ensure, that
the externally referenced Class (cSortedDictionary
from dhSortedDictionary.dll) is created regfree per
DirectCOM (one normal Dll-Declare - one Call with the
correct Path and we're done).

That's it already on the VB6-end (the VB6-Code for
MyWrapper.dll is included in the Zip).

So now we have an ActiveX-Dll, which creates its
external Helper (cSortedDictionary) already regfree
and also contains the fast Compare-Routine, which
we now were able to compile even to native code.

Now XL comes into play - which will work only with
your new Wrapper cMyClass and will not know anything
about any sorting Dictionaries - these are already
"encapsulated out of the way".

So what remains is (to complete the regfree-hierarchy,
and my above mentioned "two steps"), to create your
cMyClass regfree also in XL ... *and* latebound (As Object),
so that XL not even knows (and gets confused by) the
Interface-Reference of your cMyWrapper.dll.

Here's the DirectCOM related code-part which ensures
the regfree Loading/Instancing:

'*** In a VBA-Module
Declare Function LoadLibrary Lib "kernel32" Alias "LoadLibraryA" _
                  (ByVal lpLibFileName As String) As Long
Declare Function GetInstance Lib "DirectCom" Alias "GETINSTANCE" _
                  (FName As String, ClassName As String) As Object

Function CreateWrapperInstanceRegFree(ByVal WorkBookPath As String)
Static hLib As Long
If Right$(WorkBookPath, 1) <> "\" Then WorkBookPath = WorkBookPath & "\"

'preload DirectCOM.Dll (only once) into the Excel-Process
If hLib = 0 Then hLib = LoadLibrary(WorkBookPath & "DirectCOM.dll")

Set CreateWrapperInstanceRegFree = GetInstance(WorkBookPath & _
                                    "MyWrapper.dll", "cMyClass")
End Function


And this is the hollowed out Original VBA-Function, which
now delegates the main-task to your Wrapper-Class:

Function FilterMatches2&(vFilterRng(), vCheckRng(), _
                          OutColumnName As String, _
                          Optional ByVal Mode As FilterMode, _
                          Optional ByVal DupesAllowed As Boolean, _
                          Optional ByVal TypeComparison As ExpectedTypes)

Dim Wrp As Object, vOut()
'this instantiates cMyClass (a VB6-Compiled Wrapper)
  Set Wrp = CreateWrapperInstanceRegFree(Application.ActiveWorkbook.Path)

   FilterMatches2=Wrp.FilterMatches2(vFilterRng,vCheckRng,vOut, Mode, _
                                       DupesAllowed, TypeComparison)

   Columns(OutColumnName).ClearContents

   If FilterMatches2 > 0 Then
     With Range(OutColumnName & "1").Resize(FilterMatches2, 1)
       .Value = vOut
       .NumberFormat = "0000000000000" '..optional
       .EntireColumn.AutoFit '..optional
     End With
   End If
End Function

Please check it out - here's a complete example
including a MatchTest.xls with a 50000er test-set.
www.datenhaus.de/Downloads/XLRegFreeCOM.zip

Olaf