Re: calling from one workbook the classes and functions in another

From: Robin Hammond (rjNOrhSPAM_at_PLEASEnetvigator.com)
Date: 04/21/04 

Date: Wed, 21 Apr 2004 12:06:48 +0800

Blazingly obvious (well not really) in retrospect.

That said, having discovered the vb trick recently I'm suddenly a big fan
since it gives me a way to protect large portions of my code in a compiled
dll, and to reuse the classes across different add-ins without having to
replicate them in each add-in.

And, I got started down that route by your 2002 vba book. Doing portions in
a class while leaving menu creation and macro control in a normal add-in
makes it all pretty simple without the need for the complex interfacing.
Perhaps a digression on this in the next book...

Robin Hammond
www.enhanceddatasystems.com

"Rob Bovey" <Rob_Bovey@msn.com> wrote in message
news:uy3TNa0JEHA.3040@TK2MSFTNGP09.phx.gbl...
> Hi Mark, Robin,
>
> The trick to get around this is to instantiate the class using a
> function in the project where it resides, then have that function return a
> reference to the instantiated class. It would look something like this.
>
> ----------------------
> In the MyLibs Project
> ----------------------
> Public Function GetClass() As CWorker
> Set GetClass = New CWorker
> End Function
>
> ----------------------
> In a Different Project
> ----------------------
> Private clsAWorker As MyLib.CWorker
>
> Public Sub TestClass()
> Set clsAWorker = MyLib.GetClass
> ''' Use the class...
> End Sub
>
> --
> Rob Bovey, MCSE, MCSD, Excel MVP
> Application Professionals
> http://www.appspro.com/
>
> * Please post all replies to this newsgroup *
> * I delete all unsolicited e-mail responses *
>
>
> "Robin Hammond" <rjNOrhSPAM@PLEASEnetvigator.com> wrote in message
> news:c64jf5$7co1@imsp212.netvigator.com...
> > Mark,
> >
> > I might get corrected here, but I don't think that you can in this way.
If
> > you look at the instancing properties for your class in your source
book,
> > you have 2 options, private, or publicnotcreateable. I've just played
with
> > it and it seems that although you can create a reference to the file and
> the
> > class, you can't instantiate the class.
> >
> > What I was doing was
> >
> > 1. Rename the source VBA project to MyLibs.
> > 2. Set clsAWorker to publicnotcreateable
> > 3. Set a reference to this project from another book.
> > 4. Try something like
> >
> > Dim clsA as MyLibs.clsAWorker 'this works
> > Set clsA = new MyLibs.clsAWorker 'this fails
> >
> > Instead, there is an option if you have VB.
> >
> > Copy your cls into a VB Project for an activex dll. Set the class
> instancing
> > property to MultiUse. Create the dll. Set a reference to your dll from
> your
> > excel file, and the class can now be created as normal and referenced by
> any
> > file you want. You should also set project binary compatability in the
vb
> > project so that if you recompile the dll you don't have to reset the
> > reference every time in Excel.
> >
> > HTH,
> >
> > Robin Hammond
> > www.enhanceddatasystems.com
> >
> >
> > "mark" <anonymous@discussions.microsoft.com> wrote in message
> > news:022B7EAF-A121-47DA-8B96-12A4356E26BF@microsoft.com...
> > > I have a workbook loaded (mylibs.xls) that may become an add in. I
> also
> > have another workbook loaded (mywork.xls) that would like to use classes
> and
> > public methods defined in mylibs.
> > >
> > > mylibs contains models and classes and defines such items as:
> > > Public aworker As clsAWorker (clsAWorker is defined in mylibs)
> > > Public helpfulList As Scripting.Dictionary
> > > Public function doSomething(arg1 as integer) as integer {...}
> > (defined in a mylibs module)
> > >
> > > From within mywork I want to access the public items of mylibs but
when
> i
> > try to reference the items the VBA pre-processor errors out. How do you
> > refence and use VBA objects in an external workbook?
> > >
> > > Thanks
> >
> >
>
>

Relevant Pages

  • Re: calling from one workbook the classes and functions in another
    ... Excel from VB and C++. ... >> reference to the instantiated class. ... you can't instantiate the class. ... >>> Copy your cls into a VB Project for an activex dll. ...
    (microsoft.public.excel.programming)
  • Re: .NET CF Datagrid
    ... That did the trick, thanks very much. ... >I think you have to add a reference to the DLL in your ... >> get the option to appear when entering the ...
    (microsoft.public.windowsce.embedded)
  • Re: .NET CF Datagrid
    ... That did the trick, thanks very much. ... >I think you have to add a reference to the DLL in your ... >> get the option to appear when entering the ...
    (microsoft.public.windowsce.embedded)
  • Multiple References and Object Instantiation??
    ... this app each dll is a different form. ... reference and instantiate them to use their public properties and methods ... if one dll is trying to access another dll's ... Create a Class that instantiates all the forms from the dlls and reference ...
    (microsoft.public.dotnet.framework)
  • Multiple References and Object Instantiation??
    ... this app each dll is a different form. ... reference and instantiate them to use their public properties and methods ... if one dll is trying to access another dll's ... Create a Class that instantiates all the forms from the dlls and reference ...
    (microsoft.public.dotnet.framework.windowsforms)
Loading