Excel "IsFunction" function needed

[Imagineer]

I am using excel '97. (Yes, I know it is old but for various reasons, we can't upgrade this specific machine).

Anyway, what I basically need is an "IsFunction" function or the functional equivalent. Where I pass it a reference and it will tell me if that reference contains a function. I have tried to TYPE function but it seems to always resolve the reference first. I've tried cell("Contents",a1) but again, the reference was resolved first. I've tried various combinations of direct and indirect but haven't stumbled upon the right solution.

Example below:
[tt]
A B
1 8 =IsFunction(A1) Result=0
2 =princ*5% =IsFunction(A1) Result=1
[/tt]

The current thought is somehow getting the "string" that is in the cell and testing for an equal sign. Alas, still haven't found a way to get at the underlying string instead of its resolution.

TIA,
Mark

Imagineer

 

[GlennUK]

You can use a named formula, utilising an Excel 4 macro function if you really want to.

Do menu command Insert/Name/Define, and choose a suitable name like TestFormula, and in the Refers to box, type this formula ...
=GET.CELL(48,INDIRECT("rc[-1]",FALSE))
and press OK.
This named formula tests the cell to left of where this named formula is used, so if you type =TestFormula in cell B2, it will return TRUE if cell A2 has a formula, and FALSE if A2 is not a formula.

Glenn.

 

[rmikesmith]

Mark,

If you're not averse to using a little VBA, the following user defined function should work for you:

Function IsFunction(ByVal OneCell As Range) As Boolean

  IsFunction = Not (OneCell.Text = OneCell.Formula)
  
End Function

HTH
Mike