Excel - HasFormula

Asked By HK on 25-Jan-10 10:46 AM
Bob Phillips and maybe many others have provided a UDF like

Function HASFORMULA(rng As Range)
If rng.Count = 1 Then
HASFORMULA = rng.HASFORMULA
Else
HASFORMULA = CVErr(xlErrRef)
End If
End Function

Can this function be changed to show an array of TRUE/FALSE values
reflecting whether cells in a range holds formulas or not?

Hans Knudsen




Peter T replied to HK on 25-Jan-10 11:06 AM
Function GOTFORMULA(rng As Range)
' array enter with Ctr-Shift Enter
Dim arr()
On Error GoTo errH
With rng
ReDim arr(1 To .Rows.Count, 1 To .Columns.Count)
For C = 1 To UBound(arr, 2)
For r = 1 To UBound(arr)
arr(r, C) = .Cells(r, C).HasFormula
Next
Next
End With
GOTFORMULA = arr
Exit Function
errH:
GOTFORMULA = CVErr(xlErrRef)
End Function

Regards,
Peter T
HK replied to Peter T on 25-Jan-10 11:30 AM
Thank you very much.
Hans
Peter T replied to Peter T on 25-Jan-10 12:17 PM
I should of course have declared ALL the variables

Function GOTFORMULA(rng As Range)
' array enter with Ctr-Shift Enter
Dim r As Long, c As long
Dim arr()

Peter T
Rick Rothstein replied to HK on 25-Jan-10 01:43 PM
It is unclear how you intend to use the function you asked for. Would it be
enough if the function simply told you if every cell in the specified range
hold formulas or not? This function will return TRUE if every cell in the
range holds a formula and FALSE otherwise...

Function RangeHasFormulas(Rng As Range) As Boolean
Dim R As Range
For Each R In Rng
If Not R.HasFormula Then Exit Function
Next
RangeHasFormulas = True
End Function

--
Rick (MVP - Excel)
HK replied to Rick Rothstein on 25-Jan-10 02:06 PM
To Rick Rothstein
Peter T's function does exactly what I wanted.

Hans