Syntax to reference object in excel

  • Thread starter Thread starter Robert
  • Start date Start date
R

Robert

Would anyone happen to know the correct syntax to reference an object that
resides in a file outside of excel?

Here's what I'm trying to do: I've created a class module named Person. I
want to move it out of the spreadsheet it is currently in, and into a file
named ncd_ac.xla. I then want to make the Person class module globally
available in the spreadsheet, and I want to create an instance of Person.

This is basically the same thing as saying #include stdio.h in C. How does
one reference code externally in Excel (v. 10)?

tia,
Robert
 
Robert said:
I've created a class module named Person. I
want to move it out of the spreadsheet it is currently in, and into a file
named ncd_ac.xla. I then want to make the Person class module globally
available in the spreadsheet, and I want to create an instance of
Click to expand...
Person.

With the .xls open and active and the .xla also open but not the active
workbook, go to the Visual Basic Editor, choose Tools, References and
the name of the .xla's VBA project (named VBAProject by default) should
appear in the list just below those already checked. Check the project.
It must have a different name to the one in the .xls e.g. they can't
both be named VBAProject. The class name can now be declared either by
fully qualifying it with the VBA project name (e.g. VBAProject.Person)
or the VBA project name may be omitted.

You class needs to be PublicNotCreatable. This means the .xls will not
be able to instantiate the class; you need a way for the .xla to serve
an instance of the class to the .xls. So, add a standard .bas module
(yuk!) to the .xla project and add a single function e.g.

Public Function GetPerson() As Person
Set GetPerson = New Person
End Function

Jamie.

--
 
Back
Top