Early alternative to ISFORMULA() function 1

[Denial]

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.

[sub][ ]—————————————————————————————————[/sub]
[sup]Engineering mathematician / analyst.[ ] See my profile for more details.[/sup]

 

[MintJulep]

Try this:

Public Function IsNumFormula(target As Range) As Variant
    If target.Count = 1 Then
        IsNumFormula = IsNumeric(Evaluate(target.Value))
    Else
        'IsNumFormula = "Error:  Argument must be a single cell."  Uncoment this line to return a message
        IsNumFormula = CVErr(xlErrValue) 'comment this line if you prefer a message
    End If
End Function

 

[Denial]

Thanks, MJ.

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+1[ ] or[ ] =RAND()[ ] should return TRUE,
whereas[ ] 3[ ] or[ ] -1.234[ ] or[ ] abcde[ ] should 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.

[sub][ ]—————————————————————————————————[/sub]
[sup]Engineering mathematician / analyst.[ ] See my profile for more details.[/sup]

 

[MintJulep]

That's actually a little easier.

Public Function CellIsFormula(target As Range) As Variant
    If target.Count = 1 Then
        CellIsFormula = Left(target.Formula, 1) = "="
    Else
        CellIsFormula = CVErr(xlErrValue)
    End If
End Function

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

 

[MintJulep]

Formulas can start with =, + or - for backwards compatibility with Lotus or Borland or one of the old spreadsheets, so this will be more robust.

Public Function CellIsFormula(target As Range) As Variant
    If target.Count = 1 Then
        Select Case Left(target.Formula, 1)
            Case "=", "+", "-"
                CellIsFormula = True
            Case Else
                CellIsFormula = False
            End Select
        Else
            CellIsFormula = CVErr(xlErrValue)
    End If
End Function

 

[Denial]

Thanks again.[ ] First works perfectly for my needs.[ ] Second (obviously) will get negative numbers wrong, so even though my specific application should not encounter negative numbers I will play it safe.

[sub][ ]—————————————————————————————————[/sub]
[sup]Engineering mathematician / analyst.[ ] See my profile for more details.[/sup]

 

[MintJulep]

Good catch on the negative numbers.

This should fix that problem.

Public Function CellIsFormula(target As Range) As Variant
    If target.Count = 1 Then
        Select Case Left(target.Formula, 1)
            Case "=", "+"
                CellIsFormula = True
            Case "-"
                CellIsFormula = Evaluate(target.Value) = target.Formula
            Case Else
                CellIsFormula = False
            End Select
        Else
            CellIsFormula = CVErr(xlErrValue)
    End If
End Function

 

[IDS]

I was looking at a non-vba formula using FORMULATEXT, but since that was introduced in 2013, the same as ISFORMULA, it's not actually much use.


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IRstuff]

I would be a bit wary of only searching for "=" since there are occasions where my own worksheets might have "=" in a cell with a value

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[Denial]

Point taken, IR.[ ] Thanks.

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 the copy and paste-copy operations 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.

[sub][ ]—————————————————————————————————[/sub]
[sup]Engineering mathematician / analyst.[ ] See my profile for more details.[/sup]

 

[3DDave]

Turning off automatic calculations should achieve that goal. A macro that runs onsave or onopen to force the calculation to manual and looks to see if a cell has the word "final" in it would do the job automatically, maybe set the contents of another cell to "manual" or "recalc" to signal the calculation status.

 

[MintJulep]

Ok, I think this is robust. Let me know if you can break it.

Public Function CellIsFormula4(target As Range) As Variant
On Error GoTo ErrorHandler

    If target.Count = 1 Then
        CellIsFormula4 = Len(target.Formula) > Len(target.Value) Or IsError(target)
    Else
        CellIsFormula4 = CVErr(xlErrValue)
    End If
    Exit Function

ErrorHandler:
    CellIsFormula4 = True
End Function

[pre]CASE Target Vale Formula IsForumla4
string abc abc abc FALSE
number 123 123 123 FALSE
formula 3 3 =1+2 TRUE
formula+ -3 -3 =1-4 TRUE
formula- 4 4 =-1+5 TRUE
formula returning error #N/A #N/A =VLOOKUP("A",N13:O17,1) TRUE
formula returning error #DIV/0! #DIV/0! =1/0 TRUE
blank 0 FALSE
- - - - FALSE
1234567 12345 12345 12345 FALSE

[/pre]

 

[Denial]

Many (MANY) years ago, I attempted to set up a systematic test to establish exactly how Excel's various ISxxx() functions responded to the various types of arguments that might be thrown at them.[ ] I have added MJ's CellIsFormula4() function to that tabulation.[ ] It passes all the tests I could think of at the time I made the spreadsheet.[ ] See attached.

[sub][ ]—————————————————————————————————[/sub]
[sup]Engineering mathematician / analyst.[ ] See my profile for more details.[/sup]

 

[Denial]

I managed briefly to get my fingertips onto a computer with Excel-2013 on it, and loaded my Testing_Is_Functions.xls spreadsheet onto it.[ ] I then added a column for the ISFORMULA() function.[ ] Comparing the results from ISFORMULA() and CellIsFormula4() the following cell contents gave different results.
.[ ] (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).

[sub][ ]—————————————————————————————————[/sub]
[sup]Engineering mathematician / analyst.[ ] See my profile for more details.[/sup]

 

[IRstuff]

hmm...

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 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[Denial]

In order to get results for the ISFORMULA function, I sent my test spreadsheet to a friend with a more recent Excel version and asked him to do the testing.[ ] The results I reported two posts up come from what he reported back to me.[ ] I have since established that he uses Excel[ ]365.

[sub][ ]—————————————————————————————————[/sub]
[sup]Engineering mathematician / analyst.[ ] See my profile for more details.[/sup]

 

[IDS]

I had a quick look at the download spreadsheet and found that for:
=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/

 

[MintJulep]

I think you're right Doug.

 

[IRstuff]

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

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 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[IDS]

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

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

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/