formula result a 0 instead of TRUE (2002)

Topic

New Reply

I have the following formula:

=IF(trans_opt=1,AND(starty=B4,(INT((B4-starty)/yspace))=(B4-starty)/yspace),AND(startx=A4,(INT((A4-startx)/xspace))=(A4-startx)/xspace))

It checks a number in trans_opt (it is 1 or 2), based on that it performs any of 2 AND functions. It should return a TRUE or FALSE value.

The problem is that no matter what the input cells, it always returns a “0”. I have checked every term of the equation by going to cell edit mode, selecting and pressing F9 checking for partial results, and know when the formula should return FALSE or TRUE.

And the strangest thing is that the formula used to display the result as FALSE or TRUE until today, when I started editing it to modify it. Result as TRUE or FALSE worked before!

The cell that has the formula is formated as “General”. But I havent changed the formating since I started. The only thing that happened , was that several times I had errors with the parenthesis and received the syntax error message and a couple of times accepted the suggested solution. Other than that, I cant think of any other anomalous thing.

Help please!

Reply | Quote

Replies

Check the following:

• Select Tools | Options…
• Activate the Transition tab.
• Make sure that the check box labeled “Transition formula evaluation” in the “Sheet options” section is cleared.
• Click OK.
  [/list](If the check box was clear already, check it, then click OK, open the Options dialog again, clear the check box and click OK)

  Does this help?

Reply | Quote

I am sorry about this post.

When I went back to my formulas and macros, something clicked in my head…

The problem is related to a macro that disables recalculation of the spreadsheet:

Private Sub setnewparam_Click()
‘
Worksheets(“gpft”).EnableCalculation = False
‘
Dim userinput As Integer
‘
userinput = newmaxx.Value
Sheets(“hidden_data”).Range(“finishx”).Value = userinput
‘
Worksheets(“gpft”).Calculate
‘
End Sub

I didnt realize that by executing this macro I was turning recalc off.

I solved with adding at the end of the sub:

Worksheets(“gpft”).EnableCalculation = True
Worksheets(“gpft”).Calculate

Does this sound reasonable?

Reply | Quote

I don’t understand why turning off calculation would cause a boolean (TRUE/FALSE) value to be displayed as a number, but it certainly will result in the displayed result being incorrect, or at least suspect.

Reply | Quote

I am not sure.

I thought that F9 would recalc the spreadsheet. So in the process, I tried recalculating with F9 several times. But obviously …

Worksheets(“gpft”).EnableCalculation = False

cannot be overiden with F9.

I aslo had several delete and copy and paste on the cell containing the formula, with the recalc disabled. Maybe that has something to do with the “0” displayed.

Reply | Quote

VBA help says about EnableCalculation “When the value of this property is False, you cannot request a recalculation.” Perhaps you were thinking about the effect of Application.Calculation = xlManual. This will prevent automatic calculation, but still allows recalculation by pressing F9.

Reply | Quote

excellent observation and help!

Reply | Quote

excellent observation and help!

Reply | Quote

VBA help says about EnableCalculation “When the value of this property is False, you cannot request a recalculation.” Perhaps you were thinking about the effect of Application.Calculation = xlManual. This will prevent automatic calculation, but still allows recalculation by pressing F9.

Reply | Quote

I am not sure.

I thought that F9 would recalc the spreadsheet. So in the process, I tried recalculating with F9 several times. But obviously …

Worksheets(“gpft”).EnableCalculation = False

cannot be overiden with F9.

I aslo had several delete and copy and paste on the cell containing the formula, with the recalc disabled. Maybe that has something to do with the “0” displayed.

Reply | Quote

I don’t understand why turning off calculation would cause a boolean (TRUE/FALSE) value to be displayed as a number, but it certainly will result in the displayed result being incorrect, or at least suspect.

Reply | Quote

Check the following:

• Select Tools | Options…
• Activate the Transition tab.
• Make sure that the check box labeled “Transition formula evaluation” in the “Sheet options” section is cleared.
• Click OK.
  [/list](If the check box was clear already, check it, then click OK, open the Options dialog again, clear the check box and click OK)

  Does this help?

Reply | Quote