Excel

Check cell for a formula

Ease of Use

Easy

Version tested with

2000, 2002, 2003 

Submitted by:

Zack Barresse

Description:

This function will return a Boolean (True/False) logical return depending if the cell referenced holds a valid Excel recognized formula. 

Discussion:

This User Defined Function (UDF) helps in the fact that it will return a Boolean-type (True/False) return. This can be used in any Excel formula AND Conditional Formatting (CF). If CF was desired to be used, one would need to make use of an old Excel4Macro (e.g. GET.CELL) and use named ranges. They were extremely cumbersome, hard to maintain and very hard to troubleshoot. With a UDF such as this, it takes the complication out of the matter and you can use this function right in the CF formula. 

Code:

instructions for use

			

Option Explicit Function ISFORMULA(cel As Range) As Boolean ISFORMULA = cel.HasFormula End Function

How to use:

  1. Copy code.
  2. From Excel, press Alt + F11
  3. Select desired file on left*
  4. Insert | Module
  5. Paste code on right
  6. Press Alt + Q
  7. Save progress
  8. Enter as a normal function, =IsFormula(A1)
  9. *If no pane exists on left, press Ctrl + R
 

Test the code:

  1. Enter any value in cell A1.
  2. Enter any formula in A2 (i.e. =1+1).
  3. Enter in B1: =IsFormula(A1)
  4. Copy down to B2.
 

Sample File:

IsFormulaEx.zip 5.93KB 

Approved by mdmackillop


This entry has been viewed 121 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express