## Early alternative to ISFORMULA() function

## Early alternative to ISFORMULA() function

(OP)

I am looking for a way to detect whether an Excel cell contains a numerical value or a formula that returns a numerical value. In later versions of Excel the ISFORMULA() function would do exactly what I need, but this function is not offered in the version I am using (2010). I would prefer a solution that does not involve VBA, but will use VBA if necessary.

_{ —————————————————————————————————}

^{Engineering mathematician / analyst. See my profile for more details.}

## RE: Early alternative to ISFORMULA() function

## CODE --> VBA

## RE: Early alternative to ISFORMULA() function

Your suggestion points out to me that my request was subtly ambiguous.

Primarily I am seeking a Boolean UDF that will return TRUE if the single cell passed into it is a formula, and will return FALSE if it is anything else.

Thus

=sin(pi())or=2+1or=RAND()should return TRUE,whereas

3or-1.234orabcdeshould return FALSE.If it makes things any easier, it would suffice if the test applied was that the first character in the cell should be "=", but I suspect that would be impossible to detect in VBA.

_{ —————————————————————————————————}^{Engineering mathematician / analyst. See my profile for more details.}## RE: Early alternative to ISFORMULA() function

## CODE --> VBA

You can use either formula in the sheet, to return a Boolean, or you can use them with conditional formatting.

## RE: Early alternative to ISFORMULA() function

## CODE --> VBA

## RE: Early alternative to ISFORMULA() function

_{ —————————————————————————————————}^{Engineering mathematician / analyst. See my profile for more details.}## RE: Early alternative to ISFORMULA() function

This should fix that problem.

## CODE --> VBA

## RE: Early alternative to ISFORMULA() function

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Early alternative to ISFORMULA() function

TTFN (ta ta for now)

I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg

FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm

## RE: Early alternative to ISFORMULA() function

Luckily in my present context this will not arise. I am developing a spreadsheet for a sports club to use when it is organising tournaments. The workbook has two parallel columns of text data (people's names). The first column has the participants' names in alphabetical order, and the second one dis-arranges the names into a randomised order based in a column of random numbers [generated using

=RAND()]. So, initially, every time any change is made to the worksheet the randomised order re-randomises. There comes a time when this re-randomising has to stop, so that rosters etc can be printed and posted. I told the club that the simplest way to stop the re-randomising is to use thecopyandpaste-copyoperations to convert all RAND() functions to their current values. I intend to use this new UDF in an updated version of the spreadsheet to ensure that the conversion is properly and fully done._{ —————————————————————————————————}^{Engineering mathematician / analyst. See my profile for more details.}## RE: Early alternative to ISFORMULA() function

## RE: Early alternative to ISFORMULA() function

## CODE --> VBA

## RE: Early alternative to ISFORMULA() function

_{ —————————————————————————————————}^{Engineering mathematician / analyst. See my profile for more details.}## RE: Early alternative to ISFORMULA() function

. (Just a single, lonely decimal point)

'55

'1.98D09 (but not '1.98E09)

Fred

=""

=T(5)

=NA()

=Fred(A4) (ie a non-existent function)

=SQRT(-1)

=(A4=A9) (where A4 did not equal A9)

(I didn't think to test the case where A4 did equal A9)

In all these cases I think ISFORMULA got it wrong and CellIsFormula4 got it right (although a few are arguable).

_{ —————————————————————————————————}^{Engineering mathematician / analyst. See my profile for more details.}## RE: Early alternative to ISFORMULA() function

hmmm... Excel 2021 had the same results as CellIsFormula4 for all those cases.

TTFN (ta ta for now)

I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg

FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm

## RE: Early alternative to ISFORMULA() function

_{ —————————————————————————————————}^{Engineering mathematician / analyst. See my profile for more details.}## RE: Early alternative to ISFORMULA() function

=PI()+1

or just =PI()

The Excel ISFormula function says it is a formula, but CellIsFormula4 says it isn't.

It seems to me that any short formula returning a floating point value will be found to be not a formula by CellIsFormula4

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Early alternative to ISFORMULA() function

## RE: Early alternative to ISFORMULA() function

Agree. Note that "=3.141592654" returns TRUE for both IsFormula and CellsFormula4, but it's not strictly a formula, since no calculation is involved.

TTFN (ta ta for now)

I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg

FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm

## RE: Early alternative to ISFORMULA() function

On that basis =A1 wouldn't be a formula.

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Early alternative to ISFORMULA() function

The original goal seems to be to detect if formulas have been pasted or numeric constant; simply turning off recalc eliminates the need to do the copy/paste and to detect it was done correctly.

It's a shame Excel doesn't have a method to lock formula results separately from locking formulas.