calling from one workbook the classes and functions in another

[Guest]

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

 

[Robin Hammond]

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

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?

 

[Rob Bovey]

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]

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]

Hi Robin,

Yes, in our next book we're devoting several chapters to working with
Excel from VB and C++. It's not likely to hit the book stores until next
January, though.

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

 

[Guest]

Both of your solutions are appreciated as they are applicable to different circumstances.

I was unaware that I needed to first manually set a reference to the workbook that contained the methods that I want to call. Since I define - Private clsAWorker As MyLib.CWorker to explicitily reference the MyLib workbook in code - and since the workbook that i am referencing is loaded in Excel the manual reference step is not expected

Thanks for your help

Mark

----- Robin Hammond wrote: ----

Mark

I might get corrected here, but I don't think that you can in this way. I
you look at the instancing properties for your class in your source book
you have 2 options, private, or publicnotcreateable. I've just played wit
it and it seems that although you can create a reference to the file and th
class, you can't instantiate the class

What I was doing wa

1. Rename the source VBA project to MyLibs
2. Set clsAWorker to publicnotcreateabl
3. Set a reference to this project from another book
4. Try something lik

Dim clsA as MyLibs.clsAWorker 'this work
Set clsA = new MyLibs.clsAWorker 'this fail

Instead, there is an option if you have VB

Copy your cls into a VB Project for an activex dll. Set the class instancin
property to MultiUse. Create the dll. Set a reference to your dll from you
excel file, and the class can now be created as normal and referenced by an
file you want. You should also set project binary compatability in the v
project so that if you recompile the dll you don't have to reset th
reference every time in Excel

HTH

Robin Hammon
www.enhanceddatasystems.co

I have a workbook loaded (mylibs.xls) that may become an add in. I als

have another workbook loaded (mywork.xls) that would like to use classes an
public methods defined in mylibs

Public aworker As clsAWorker (clsAWorker is defined in mylibs
Public helpfulList As Scripting.Dictionar
Public function doSomething(arg1 as integer) as integer {... (defined in a mylibs module

try to reference the items the VBA pre-processor errors out. How do yo
refence and use VBA objects in an external workbook