Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel Class Module Issue

Status
Not open for further replies.

Aidy680

Technical User
Nov 1, 2006
138
GB
I have a class mod which contains the following code:

Does anyone know why my code falls over on the last line below:

Private oExcel As New Excel.Application

Set oExcel = Excel.Application
oExcel.Visible = True
oExcel.Calculation = xlCalculationAutomatic

I've got Excel referenced too so it's not that.

Sorry new to class mods...!
 
...sorry for your info the error I get is:

Method Calculation of object Application failed.

Any ideas...?
 



Hi,
Code:
 oExcel.Calculat[b]ion[/b] = xlCalculationAutomatic
or
Code:
 oExcel.Calculation = -4105


Skip,

[glasses] [red][/red]
[tongue]
 
Hi Skip,

The former of your two possibilities.

However that figure (error message?) does appear if I put the command into the immediate window I think....?

Hope that helps.
 
I'm not at work now but reading through other posts would it be wise to try:

Application.Calculation = xlCalculationAutomatic

Anyone?
 


Application refers to the application that you are programming in.

You set oExcel = Excel.Application already.

"The former of your two possibilities."

What about the latter?


Skip,

[glasses] [red][/red]
[tongue]
 



Try this...

Step into the sub.

Add a Watch on the value, xlCalculationAutomatic

What do you see?

Skip,

[glasses] [red][/red]
[tongue]
 
The watch tells me that it is a type Long with a value of -4105.

Are you saying that oExcel.Calculation cant be set to a Long variable?

Pls advise as I'm tearing my hair out here!
 
..if I hover over "Calculation" the tool tip says "<Type Mismatch>".

I dont understand!!
 
...and if I try setting to:

oExcel.Calculate

it errors again....
 



Calculate is a METHOD

Calculation is a PROPERTY.

Check your Excel object model.

Take a look at the CreateObject method.

Skip,

[glasses] [red][/red]
[tongue]
 
Skip I'd appreciate it if you would just tell me if it's possible to set the Calculation property of an Excel object in such a way.

If not are you saying I'd be better off starting from scratch using CreateObject rather than a class mod?
 


I'm saying that you might have to create the oExcel object using the CreateObject method instead of using the statement in your code.

Skip,

[glasses] [red][/red]
[tongue]
 
The only way I found to solve this was to delete it altogether.

The stupid thing seems to work still!
 
Application.Calculation works when there is an excel worksheet opened. After 'Set oExcel = New Excel.Application' an empty application is created, so the 'Calculation' method fails.
The same happens when there are no workbooks opened and you try to set this property in immediate window; 'Tools>Options' is not available in this case too.
You can try to modify your code, before setting 'Visible' to True add empty workbook and set 'Calculation' property. Both xlCalculationAutomatic and -4105 work.

combo
 
Apologies for the delay in replying but thanks Combo for your input.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top