Groups > comp.lang.basic.visual.misc > #864

Re: What is a class?

From	Schmidt <sss@online.de>
Newsgroups	comp.lang.basic.visual.misc
Subject	Re: What is a class?
Date	2012-02-29 21:39 +0100
Organization	Aioe.org NNTP Server
Message-ID	<jim2ed$n4k$1@speranza.aioe.org> (permalink)
References	(15 earlier) <jihl1e$m6v$1@dont-email.me> <jiiiqg$q7a$1@speranza.aioe.org> <jiilag$d7r$1@dont-email.me> <jike52$h28$1@speranza.aioe.org> <jilr22$1kh$1@dont-email.me>

Show all headers | View raw

Am 29.02.2012 19:33, schrieb GS:

[regfree usage of COM-Dlls in VBA using DirectCOM.dll]
> Olaf, this changes my entire approach in several ways.

Yep, I know that there's already many XL-VBA-users, which do
use (mostly) the SQLite-wrapper from the RichClient-lib
(some of them also doing that regfree).

...often for InMemory-DBs, to massage Data per SQL - and
then they use the SQLite-Recordsets Rs.GetRows(...) to
put the Results of "Selects" out in a fast and convenient
way, back into XL-Ranges.


If you want to try that out, you will have to put the
RichClient-Dll as well as its companion-SQLite-Dll into
the same WorkBookPath (for example into the path which
already contains the example I've posted a link to before).

Then please change the modDirectCOM-Modules content
to a more "sophisticated" version which now should
contain this code here:

Option Explicit

Declare Function LoadLibrary Lib "kernel32" Alias "LoadLibraryA" _
                  (ByVal lpLibFileName As String) As Long
Declare Function GetInstanceEx Lib "DirectCom" _
                  (StrPtr_FName As Long, StrPtr_ClassName As Long, _
                  ByVal UseAlteredSearchPath As Boolean) As Object
Declare Function GETINSTANCELASTERROR Lib "DirectCom" () As String


Private Sub EnsureDirectComDllPreLoadingAndCheckPath(Path As String)
Static hLib As Long
   If Right(Path, 1) <> "\" Then Path = Path & "\"
   'preload DirectCOM.Dll (only once) into the Excel-Process
   If hLib = 0 Then hLib = LoadLibrary(Path & "DirectCOM.dll")
End Sub

Function CreateWrapperInstanceRegFree(ByVal WorkBookPath As String)
   EnsureDirectComDllPreLoadingAndCheckPath WorkBookPath

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

Function CreateSQLiteCnnRegFree(ByVal WorkBookPath As String)
   EnsureDirectComDllPreLoadingAndCheckPath WorkBookPath

   Set CreateSQLiteCnnRegFree = GetInstance(WorkBookPath & _
                                "vbRichClient4.dll", "cConnection")
End Function

Function GetInstance(DllFileName As String, ClassName As String)
   On Error Resume Next
   Set GetInstance = GetInstanceEx(StrPtr(DllFileName), _
                                   StrPtr(ClassName), True)
   If GetInstance Is Nothing Then
     On Error GoTo 0: Err.Raise vbObjectError, , GETINSTANCELASTERROR
   End If
End Function


Then you can already try out SQLite-InMemory-DBHandling inside your
XL-Sheet (when you add an additional Button CommandButton1 there):

Here's the Button-EventHandler which demonstrates SQLite-stuff:

Private Sub CommandButton1_Click()
Dim Cnn As Object '<- we use the regfree created Objects LateBound
   Set Cnn = CreateSQLiteCnnRegFree(ActiveWorkbook.Path)
   Cnn.CreateNewDB '<- without params creates a yet empty InMemory-DB

   'so we create a DemoTable now
   Dim SQL$
   SQL = "Create Table T(ID Integer Primary Key, Txt Text, Dbl Double)"
   Cnn.Execute SQL

   'and now we add a few Records into it
   Dim i As Long, Rs As Object '<- LateBound-usage here as well
   Set Rs = Cnn.OpenRecordset("Select * From T Where 0")
   For i = 1 To 20
     Rs.AddNew
     Rs!ID = i
     Rs!Txt = "SomeText_" & i
     Rs!Dbl = 20 / i
   Next i
   Rs.UpdateBatch

   'finally we perform a Select on the just added InMemory-Table-Data
   '(thereby filtering for "uneven IDs" per SQLs Mod-Operator '%')
   Set Rs = Cnn.OpenRecordset("Select * From T Where (ID % 2)")

   'and now with one command, we beam the result into an XL-range
   '(from a given "TopLeft-Cell")
   With Range("F7").Resize(Rs.RecordCount, Rs.Fields.Count)
     .Value = Rs.GetRows(, , , True)
   End With

   'or also with RowHeaders (with a few formattings on the 1st Row)
   With Range("J7").Resize(1, Rs.Fields.Count)
     .Font.Bold = True
     .HorizontalAlignment = xlCenter
   End With
   With Range("J7").Resize(Rs.RecordCount + 1, Rs.Fields.Count)
     .Value = Rs.GetRowsWithHeaders(, , , True)
   End With
End Sub


Olaf

Back to comp.lang.basic.visual.misc | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

What is a class? Peter Nolan <peter.nolan40@gmail.com> - 2012-02-24 04:02 -0800
  Re: What is a class? "Auric__" <not.my.real@email.address> - 2012-02-24 14:06 +0000
    Re: What is a class? Peter Nolan <peter.nolan40@gmail.com> - 2012-02-25 03:07 -0800
      Re: What is a class? Helmut_Meukel <Helmut_Meukel@bn-hof.invalid> - 2012-02-25 14:19 +0100
      Re: What is a class? "Auric__" <not.my.real@email.address> - 2012-02-25 21:26 +0000
        Re: What is a class? Jim Mack <no-uce-ube@mdxi.com> - 2012-02-25 18:58 -0500
          Re: What is a class? GS <gs@somewhere.net> - 2012-02-25 19:39 -0500
          Re: What is a class? "Auric__" <not.my.real@email.address> - 2012-02-26 03:09 +0000
            Re: What is a class? Jim Mack <no-uce-ube@mdxi.com> - 2012-02-25 22:35 -0500
              Re: What is a class? GS <gs@somewhere.net> - 2012-02-25 22:55 -0500
                Re: What is a class? "Farnsworth" <nospam@nospam.com> - 2012-02-25 23:27 -0500
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-26 01:30 -0500
                Re: What is a class? "Auric__" <not.my.real@email.address> - 2012-02-26 04:41 +0000
                Re: What is a class? ralph <nt_consulting64@yahoo.net> - 2012-02-25 23:05 -0600
                Re: What is a class? "Henning" <computer_hero@coldmail.com> - 2012-02-26 11:24 +0100
                Re: What is a class? ralph <nt_consulting64@yahoo.net> - 2012-02-25 23:03 -0600
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-26 01:26 -0500
                Re: What is a class? "Stuart McCall" <smccall@myunrealbox.com> - 2012-02-27 04:15 +0000
                Re: What is a class? ralph <nt_consulting64@yahoo.net> - 2012-02-27 00:48 -0600
                Re: What is a class? "Mayayana" <mayayana@invalid.nospam> - 2012-02-27 08:57 -0500
                Re: What is a class? "Stuart McCall" <smccall@myunrealbox.com> - 2012-02-27 19:19 +0000
                Re: What is a class? "Stuart McCall" <smccall@myunrealbox.com> - 2012-02-27 19:12 +0000
                Re: What is a class? "Bob Butler" <bob_butler@cox.invalid> - 2012-02-27 06:06 -0800
                Re: What is a class? "Stuart McCall" <smccall@myunrealbox.com> - 2012-02-27 19:24 +0000
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-27 11:01 -0500
                Re: What is a class? ralph <nt_consulting64@yahoo.net> - 2012-02-27 11:07 -0600
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-27 13:10 -0500
                Re: What is a class? Schmidt <sss@online.de> - 2012-02-27 19:09 +0100
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-27 13:29 -0500
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-27 14:43 -0500
                Re: What is a class? Schmidt <sss@online.de> - 2012-02-27 22:14 +0100
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-27 16:45 -0500
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-27 18:23 -0500
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-27 23:26 -0500
                Re: What is a class? Schmidt <sss@online.de> - 2012-02-28 13:54 +0100
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-28 08:37 -0500
                Re: What is a class? ralph <nt_consulting64@yahoo.net> - 2012-02-28 09:19 -0600
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-28 10:42 -0500
                Re: What is a class? Schmidt <sss@online.de> - 2012-02-29 06:47 +0100
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-29 09:39 -0500
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-29 13:33 -0500
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-29 14:43 -0500
                Re: What is a class? Schmidt <sss@online.de> - 2012-02-29 21:39 +0100
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-29 16:15 -0500
                Re: What is a class? Schmidt <sss@online.de> - 2012-02-29 23:20 +0100
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-28 00:16 -0500
                Re: What is a class? Jim Mack <no-uce-ube@mdxi.com> - 2012-02-27 13:26 -0500
                Re: What is a class? "Stuart McCall" <smccall@myunrealbox.com> - 2012-02-27 19:33 +0000
                Re: What is a class? Jim Mack <no-uce-ube@mdxi.com> - 2012-02-27 15:50 -0500
                Re: What is a class? Helmut_Meukel <Helmut_Meukel@bn-hof.invalid> - 2012-02-26 11:02 +0100
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-26 14:23 -0500
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-26 17:09 -0500
            Re: What is a class? ralph <nt_consulting64@yahoo.net> - 2012-02-25 22:21 -0600
              Re: What is a class? "Auric__" <not.my.real@email.address> - 2012-02-26 04:51 +0000
                Re: What is a class? ralph <nt_consulting64@yahoo.net> - 2012-02-25 23:10 -0600
        Re: What is a class? Peter Nolan <peter.nolan40@gmail.com> - 2012-02-26 04:11 -0800
          Re: What is a class? Helmut_Meukel <Helmut_Meukel@bn-hof.invalid> - 2012-02-26 16:37 +0100
          Re: What is a class? "Auric__" <not.my.real@email.address> - 2012-02-26 18:36 +0000
            Re: What is a class? ralph <nt_consulting64@yahoo.net> - 2012-02-26 14:11 -0600
            Re: What is a class? Peter Nolan <peter.nolan40@gmail.com> - 2012-02-27 03:11 -0800
              Re: What is a class? "Auric__" <not.my.real@email.address> - 2012-02-27 16:33 +0000
                Re: What is a class? Jim Mack <no-uce-ube@mdxi.com> - 2012-02-27 13:33 -0500
                Re: What is a class? Peter Nolan <peter.nolan40@gmail.com> - 2012-02-28 04:07 -0800
    Re: What is a class? Peter Nolan <peter.nolan40@gmail.com> - 2012-02-25 03:33 -0800
    Re: What is a class? Peter Nolan <peter.nolan40@gmail.com> - 2012-02-25 04:19 -0800
  Re: What is a class? "Ivar" <ivar.ekstromer000@ntlworld.com> - 2012-02-24 15:15 +0000
    Re: What is a class? GS <gs@somewhere.net> - 2012-02-24 14:52 -0500
      Re: What is a class? "Ivar" <ivar.ekstromer000@ntlworld.com> - 2012-02-24 23:10 +0000
        Re: What is a class? GS <gs@somewhere.net> - 2012-02-24 19:46 -0500
          Re: What is a class Way off Topic "Ivar" <ivar.ekstromer000@ntlworld.com> - 2012-02-25 02:20 +0000
            Re: What is a class Way off Topic GS <gs@somewhere.net> - 2012-02-25 16:57 -0500
        Re: What is a class? Helmut_Meukel <Helmut_Meukel@bn-hof.invalid> - 2012-02-25 11:36 +0100
    Re: What is a class? Peter Nolan <peter.nolan40@gmail.com> - 2012-02-25 03:35 -0800
  Re: What is a class? Jim Mack <no-uce-ube@mdxi.com> - 2012-02-24 15:23 -0500
    Re: What is a class? "Auric__" <not.my.real@email.address> - 2012-02-25 01:33 +0000
    Re: What is a class? Peter Nolan <peter.nolan40@gmail.com> - 2012-02-25 03:09 -0800
  Re: What is a class? "Mayayana" <mayayana@invalid.nospam> - 2012-02-24 20:05 -0500

csiph-web