Cannot Dimension and Excel Application 1

[MinnKota]

I am trying to Dimension an excel application

Dim objExl as excel.application

but I get the following error

"Compile Error:
User-defined type not defined"

It seems that everyone else can use this, what is my problem?

 

[RoyVidar]

In VBE, Tools | References, set a reference to the Microsoft Excel <version> Object Library.

Roy-Vidar

 

[MinnKota]

Thanks Roy,
What exactly does this do?

 

[RoyVidar]

Hmm - what level of detail?

Short one, relating to Excel.
When one needs to access objects, methods, properties and functions of Excel, there's basically two methods of such. Early and Late binding.

Early binding:
Setting a reference, like this makes the application "aware" of the existence of Excel, and you can dimension, instantiate and use all of the objects, methods and properties available to Excel in the application. This means for instance that the intellisence dropdown gives you (most of) the relevant properties and methods available when programming.

Late binding:
Then you'd declare your objcets as object

[tt]dim oxl as object[/tt]

Then use for instance the createobject (and/or getobject) method to instantiate Excel:

[tt]set oxl=createobject("Excel.Application")[/tt]

In this scenario, the application is not "aware" of Excel until the createobject line is excecuted, so for instance the intellisence dropdown will not give you any hints when programming.

So basically, setting a reference to an object library, makes the objects, methods and properties of that library available to you, both when programming and at runtime.

Roy-Vidar

 

[MinnKota]

I understand. I think that I like the early binding option!

 

[MinnKota]

I have another question.

I have seen code that dimensions the variable but then uses the createobject function...

Dim xlApp as excel.application
set xlApp = createobject("excel.application")

Is the second line necessary?

 

[RoyVidar]

No one could use

[tt]Dim xlApp as excel.application
set xlApp = new excel.application[/tt]

Some of it might be based on preferences, experience or whatever, however, take a look at for instance these two (not directly related) links on using Excel automation to form your own opinion Automation Doesn't Release Excel Object from Memory and Excel Automation Fails Second Time Code Runs

Then there's also the following INFO: Use DISPID Binding to Automate Office Applications Whenever Possib so...;-)

A lot of developers use late binding to avoid version problems, but then one often develop using early binding, then change to late binding when deploying.

Roy-Vidar