MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Application.Calculate fails

  Asked By: Rufus    Date: Sep 30    Category: MS Office    Views: 1168

I am running Windows 2000 5.00.2195 with Service Pack 4 and Excel
2000 9.0.6926 SP3.

It appears that something is wrong with automatic calculation and
Application.Calculate. I have the following formula in cell C433 of
several of my sheets:

=RevisedQ(TIS, SStat, DFact, ForceC, Weights, MATCH(ForceT, TNames,

RevisedQ is a VBA macro defined in a module attached to the workbook.
The arguments are Excel named variables, either local to the
particular sheet or global. The prototype for RevisedQ is

Public Function RevisedQ(TIS As Range, SStat As Range, DFact As
Range, ForceC As String, Weights As Range, TIndex As Integer) As

Here is the problem: The value in C433 changes when I change an input
parameter (via a drop down listbox on one of the sheets) and
automatic calculation is turned on (or when I change the input
parameter and execute Application.Calculate via a command button
click). However, the value it becomes is an incorrect value. To get
the correct value I must navigate to each sheet, click on cell C433,
then click in the address bar where the formula is displayed and
press return. This indicates that the workbook does not initially
calculate correctly.



2 Answers Found

Answer #1    Answered By: Aidyn Smith     Answered On: Sep 30

I doubt that there is anything wrong with automatic calculation. However,
it isn't all that difficult to confuse it.

Also, IIRC selection from a drop-down listbox does not flag the destination
cell as dirty to the extent that will trigger automatic calculation or cause
it to be included in a calculation. I seem to remember setting another cell
to the value of the drop-down box's target cell with a simple =n10 type of
formula, then using the second cell in my calculations. Rings a big bell,
but I haven't a clue where or when.

I also have a healthy distrust of local named ranges (i.e. the same name
defined in more than one sheet of the workbook). I'm never quite sure that
I'm really accessing the correct one.

Why not uniquely name each named range? Or possibly don't use named ranges
at all in this circumstance - merely use the A1 cell labels for the local

Answer #2    Answered By: Abbad Akhtar     Answered On: Sep 30

If C433 is the same in each sheet would something like this work for you?

Sub CFourThirtyThree()

Dim X As Integer

X = Sheets.Count

While X <> 0



ActiveCell.FormulaR1C1 = "=RevisedQ(TIS, SStat, DFact, ForceC, Weights,
MATCH(ForceT, TNames, 0))"

X = X - 1


End Sub

Didn't find what you were looking for? Find more on Application.Calculate fails Or get search suggestion and latest updates.