VBA .HasFormula

Question

If I enter the number 4 into a cell, the VBA property of the cell, .HasFormula, correctly return 'False'.

But, if I enter =2+2, which in my book is NOT a formula, since it contains no functions at all, the property suddenly returns 'True'?!?

I my book, entering =2+2, is still the same as entering a constant...

...it is as if the .HasFormula just checks if the cell start with '=', which is just plain stupid, since we could do that ourselves with Left(Cell.Value,1)!

Why use a full built-in property for something as stupid as just a test for '='?!?

Anyway...

My question is: How to TRUELY find out if a cell has a formula (i.e. a function) in it?

Thanks,

Dan

Answer 1

I hear you all...

...but, it really sounds to me, like "oh, someone is attacking our beloved Excel, and we better come up with a rational theoretical academic defense".

But, it really doesn't make sense that '=2' is a formula - not even with a desperate, "but what if it points to a cell with the value 2"???

In that case it is a completely different thing, since the cell now contain a reference to another cell.

However, your also right: Creating your own little UDF is very simple...

It fact, it only requires two rules:

1. Does the formula contains a function()
2. Does the formula contains reference to another cell(s)

If either of them is meet, it TRULY is a formula, and the UDF only takes up 5 lines of code :-)

Thank you all for your feedback...

Answer 2

BTW: If I enter '=2', the .HasFormula will also return 'True', even that the cell doesn't need to be re-calculated, thus the .HasFormula really only test if the cell begins will '='.

So, it is not just remonstrations from my part - it REALLY is stupid!

Answer 3

>> ..   But, if I enter =2+2, which in my book is NOT a formula

Hi.  The "=" formula is the Sum formula.

Answer 4

Thanks, Tom - I get your point...

Your example will return 'True', since it contains a function - but it (kind of) makes sense that .HasFormula looks at the 'calculation requirement' of the cell...

Next step will be to find a way to test if a cell .HasFunction :-)

Answer 5

Dan,

Despite your remonstrations, Hasformula appears to be related to if the cell needs to be calculated.  In the =2+2, the cell has to be calculate.    How about  =(16^(-1/3))*pi()    by your definition it uses a function, but it is in fact a constant (1.246741870791)

If you want to create you own definition, then you can create your own function that parses the value of the cell and makes the correct determination using your rules.  That would be the only way.

--

Regards,

Tom Ogilvy