Calculate Method of Range Class Failed

[Ken Puls]

Hello,

I'm having some issues with a subroutine that I'm working with. I've tried searching the board, but haven't come across anything.

I have an add-in with the following procedure called from my right click menu:

Public Sub Calculation_Recalc_Selection()
    Selection.Calculate
End Sub

The problem is that when I try to use it, I get a:
Run Time Error '1004'
Calculate Method of Range Class Failed

I actually had this right in the file, but changed it to an add-in as I need to share it with other people who usual manual calcuation all the time. It worked yesterday just fine, but not in the add-in version. I do know that it's calling the add-in though, as hitting debug does take me to this routine.

Other factors:
-Retruns error on protected or unprotected ranges
-Have unprotected all sheets, saved the file, and restarted Excel, but still the same issue.
-A reference is set in the file to the add-in
-Using Excel XP(2002) on Win XP

Can anyone help me out with this?

Thanks,

 

[Ken Puls]

Also, FYI:

Application.calculate runs without a problem
Activesheet.calculate also runs without issue

It is anything "range" driven that errors out.

 

[Scott Huish]

Perhaps:

Public Sub Calculation_Recalc_Selection()
ActiveWindow.Selection.Calculate
End Sub

 

[Ken Puls]

Hello,

Unfortunately no. Same errror.

What's weird is that the trigger point is going to manual calculation mode. In automatic calculation mode, it will work. Switching to manual makes it error out.

Kind of sad, since the entire point is having it for manual mode.

I'm wondering if it could be the fact that it is contained in the add-in and not native in the sheet...

EDIT: Even after uninstalling the add-in, restarting excel and flipping to manual, the selection.calculation gives the error. This is very frustrating!

 

[Zack Barresse]

Hi Ken,

Can you do something like...

With Application
    .Selection.Calculate
End With

 

[Ken Puls]

Hi firefytr,

Nope. No go there either. Even copying an example from the Calculate Method in the VBA help doesn't work! Now that's weird!

 

[Juan Pablo González]

Knowledge base:

http://support.microsoft.com/?kbid=292476

 

[Juan Pablo González]

A solution by Jim Rech (besides the one given in the KB):

http://groups.google.com.co/groups?...ailed&hl=es&lr=&ie=UTF-8&safe=off&sa=N&tab=wg

 

[Ken Puls]

Hi JPG,

Thanks for the links. I actually was pulling up the MSKB right when I got your email notification.

I am curious to know, though... if I turn off the iterations, will that cause a problem if I have 1500 formulas, many which are pulled from other formulas?

I also tried the other example that Jim Rech provided, but can't figure out how to coerce "selection" into a valid range to pass to the sub.

 

[Ken Puls]

Got it!

I needed to insert the call word to call the CalcRange macro.

All good now!

Thanks a ton!