×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Early alternative to ISFORMULA() function

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

Try this:

CODE --> VBA

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 

RE: Early alternative to ISFORMULA() function

(OP)
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.

 —————————————————————————————————
Engineering mathematician / analyst.  See my profile for more details.

RE: Early alternative to ISFORMULA() function

That's actually a little easier.

CODE --> VBA

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.

RE: Early alternative to ISFORMULA() function

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

CODE --> VBA

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 

RE: Early alternative to ISFORMULA() function

(OP)
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.

 —————————————————————————————————
Engineering mathematician / analyst.  See my profile for more details.

RE: Early alternative to ISFORMULA() function

Good catch on the negative numbers.

This should fix that problem.

CODE --> VBA

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 

RE: Early alternative to ISFORMULA() function

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/

RE: Early alternative to ISFORMULA() function

(OP)
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.

 —————————————————————————————————
Engineering mathematician / analyst.  See my profile for more details.

RE: Early alternative to ISFORMULA() function

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.

RE: Early alternative to ISFORMULA() function

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

CODE --> VBA

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 

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

 

RE: Early alternative to ISFORMULA() function

(OP)
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.

 —————————————————————————————————
Engineering mathematician / analyst.  See my profile for more details.

RE: Early alternative to ISFORMULA() function

(OP)
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).

 —————————————————————————————————
Engineering mathematician / analyst.  See my profile for more details.

RE: Early alternative to ISFORMULA() function

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

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: 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

(OP)
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.

 —————————————————————————————————
Engineering mathematician / analyst.  See my profile for more details.

RE: Early alternative to ISFORMULA() function

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/

RE: Early alternative to ISFORMULA() function

I think you're right Doug.

RE: Early alternative to ISFORMULA() function

Quote:

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: 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

Quote:

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/

RE: Early alternative to ISFORMULA() function

In a compiled language the assignment to PI is to create a numeric constant; the other is a pointer to variable.

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close