Having trouble calling FormulaText from VBA

[JenniferMurphy]

A long time ago, I wrote a GetFormula UDF that does what the FormulaText function now does. But my code has the additional advantage that it will optionally display the address of the cell being referenced. So instead of just "=C7*D7" I get "E7: =C7*D7".

I am trying to convert it to use the FormulaText function, but it keeps getting a #VALUE error. Here's the code:

Function GetFormula(pCell As Range, Optional pAddrSw As Boolean = True) As String

GetFormula = Application.WorksheetFunction.formulatext(pCell)   'This gets a #VALUE error

'GetFormula = pCell(1).FormulaArray      'Get the formula in the cell
'If pCell(1).HasArray Then               'If it's an array formula,
'  GetFormula = "{" & GetFormula & "}"     '.add the {}s
'End If

If pAddrSw Then   'If switch is on, add the cell address and leading quote, if any
  GetFormula = pCell(1).Address(0, 0) & ": " & pCell(1).PrefixCharacter & GetFormula
End If

End Function

The previous code that worked is shown commented out. What's wrong with the first statement that gets the error?

Thanks

 

[bebo021999]

Function GetFormula(pCell As Range, Optional pAddrSw As Boolean = True) As String
    If pAddrSw Then   'If switch is on, add the cell address and leading quote, if any
        GetFormula = pCell.Address(0, 0) & ": " & pCell.Formula
    Else
        GetFormula = pCell.Formula
    End If
End Function

 

[Peter_SSs]

What's wrong with the first statement that gets the error?

Not all worksheet functions are available to the vba WorkSheetFunction object.
See List of worksheet functions available to Visual Basic

You could also note that that function does not appear in the intellisense list

 

[Peter_SSs]

Another way of writing the function

Function GetFormula(pCell As Range, Optional pAddrSw As Boolean = True) As String
  GetFormula = IIf(pAddrSw, pCell.Address(0, 0) & ": ", "") & pCell.Formula
End Function

 

[JenniferMurphy]

Not all worksheet functions are available to the vba WorkSheetFunction object.
See List of worksheet functions available to Visual Basic

You could also note that that function does not appear in the intellisense list

View attachment 63235

Very helpful, thanks.

 

[JenniferMurphy]

Another way of writing the function

Function GetFormula(pCell As Range, Optional pAddrSw As Boolean = True) As String
  GetFormula = IIf(pAddrSw, pCell.Address(0, 0) & ": ", "") & pCell.Formula
End Function

Perfect. Clean and compact. Thanks

 

[Peter_SSs]

You're welcome.