Excel 2010 function to test if a cell has a formula or is input (number or text)

[jerry12302]

I use Excel 2010, I have been searching for a solution to determine whether a cell contains a formula of any kind, or is just hard input (value or text.)

Isformula is not included in Excel 2010, nor is any use of Hasformula.

I tried suggested UDF solutions, such as

Function IsFormula(cell_ref As Range)
IsFormula = cell_ref.HasFormula
End Function

None have worked, I get a #NAME? Error every time, and my workbook is saved as an xlsm, macro enabled. I placed the Function in a module, under General.

Has anyone found a solution that works in Excel 2010???

 

[RoryA]

That should work as long as you have macros enabled, it's a normal module not a worksheet/ThisWorkbook module, and you didn't give the module the same name as the function.

 

[jerry12302]

That should work as long as you have macros enabled, it's a normal module not a worksheet/ThisWorkbook module, and you didn't give the module the same name as the function.

I inserted a normal module, and didn't name it, so it is still Module 1.

The macros are enabled (I ran another test macro in the same module to be sure.)

 

[Joe4]

Can you show us exactly how/where you are calling this UDF?

If right on the sheet, show use the formula you are entering.
If in VBA code, please show us the code calling it.

 

[RoryA]

Also, I assume the code is in the same workbook, not in another workbook such as your personal macro workbook? If it's in a different one, you need to include the workbook name in the function call.

 

[NoSparks]

sorry, just re-read OP

 

[jerry12302]

Can you show us exactly how/where you are calling this UDF?

If right on the sheet, show use the formula you are entering.
If in VBA code, please show us the code calling it.

It's in VBA code....

Function IsFormula(cell_ref As Range)
IsFormula = cell_ref.HasFormula
End Function

 

[jerry12302]

Also, I assume the code is in the same workbook, not in another workbook such as your personal macro workbook? If it's in a different one, you need to include the workbook name in the function call.

Yes, same workbook.

 

[RoryA]

If you type =IsF in a cell, does the function list dropdown show your function?

 

[jerry12302]

If you type =IsF in a cell, does the function list dropdown show your function?

Yes, it's all in lowercase, but it is there. I double click on it, and finish it, like =isformula(J3)

I get a #REF! error now. Similar UDF functions I tried gave me a #NAME? error.