Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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,

0))

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

Double

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.

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

cells.

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

Sheets(X).Select

Range("C433").Select

ActiveCell.FormulaR1C1 = "=RevisedQ(TIS, SStat, DFact, ForceC, Weights,

MATCH(ForceT, TNames, 0))"

X = X - 1

Wend

End Sub

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

Related Topics:

- Calculate Total Sum
- How to calculate IRR
- write a program that enters double data into an ArrayList and calculate the total
- Excel won't calculate my function
- Easier method to capture & paste calculated value?
- Calculating the no. of containers
- Calculating duration
- Convert string like "=123+456+789" to Long and then calculate
- calculating 'names' of text boxes in a form
- To calculate the time difference between two days
- Calculating Time in parts of an hour
- Calculating values in VB
- 2003 - ActiveSheet.Calculate Problem
- Calculate fields (Pivot Tables)
- Convert string like "=123+456+789" to Long and then calculate
- Calculating Ratios
- calculate Mean
- calculating area of a regular polygon
- calculate age
- Calculate pH of Rainwater
- calculate area
- calculate exeuction time
- JBoss Application Server is the Best Open Source Application Server
- How can i run java Application fast as VB applications
- Deploying J2EE Applications or Modules on Java System Application