Excel references and Intellisense with my own Class Module

[Rob G]

Hi,

I am learning about Class Modules and trying to implement them correctly, so
pardon my naviete on some of this stuff.

I've written code (in a regular module) in MS Access and that uses
Automation to enter data and format it on an Excel sheet. The code works
just fine now but as I an trying to consolidate and clean everything up I
thought I would put it all in a Class Module. So far it is working fine...

I am using an Excel Object in the Class Module, but I also need reference to
the same Object in the regualr module code. That too is working fine. The
problem is that I can no longer use the Excel Intellisense since I am not
directly using it as a object. Intellisense may, to some, be considered a
crutch, but I find it extremely useful for learning.

Is there a way to have a reference to an Excel object in my Class Module and
still use the Intellisense?
If not, how could I pass the same Excel Object back and forth from the Class
Module to the Module so I could be sure I am working with the same Object.
Is that with a Set Property?

Thanks for your help and advice.

-Rob

 

[onedaywhen]

I am using an Excel Object in the Class Module, but I also need reference to
the same Object in the regualr module code. That too is working fine. The
problem is that I can no longer use the Excel Intellisense since I am not
directly using it as a object. Intellisense may, to some, be considered a
crutch, but I find it extremely useful for learning.

Is there a way to have a reference to an Excel object in my Class Module and
still use the Intellisense?

Are you using early bound (declared as Excel.Application and
instantiated with the New keyword) or late bound (declared as Object
and instantiated with the CreateObject/GetObject function)? You only
get IntelliSense with early bound. The usual approach is to develop
early bound and change to late bound just before deployment.

how could I pass the same Excel Object back and forth from the Class
Module to the Module so I could be sure I am working with the same Object.
Is that with a Set Property?

You want a Property Get. For example, assuming early bound:

' <In class module MyClass>
Private m_ExcelApp As Excel.Application
Public Property Get ExcelApp() As Excel.Application
Set ExcelApp = m_ExcelApp
End Property
' </In class module MyClass >

Private m_Inst As MyClass
Sub Test()
m_Inst.ExcelApp.Calculate ' IntelliSense here
End Sub

--

 

[Rob G]

Onedaywhen,

Thanks so much for your answer. You solved the problem and I learned
something new as well!

I was using the Early Bound and then instantiating it with the Create or
Get. But the main problem, I think, was that I didn't declare the Property
Get ExcelApp as an Excel.Application object. I just left it blank (assuming
I was OK with that.

I really appreciate the help.

-Rob