Can my GetFormula UDF automatically detect array formulas?

[JenniferMurphy]

Some time ago I wrote a little UDF to display the formula in a cell. It works great, but I discovered that it does not detect array formulas. I added an optional parameter so I could get it to add the "{}".

Function GetFormula(Cell As Range, Optional arraysw = "") As String
   GetFormula = Cell.Formula
   If UCase(arraysw) = "ARRAY" Then GetFormula = "{" & GetFormula & "}"
End Function

This was a poor solution. It would be great if it could do it automatically. So I scanned the Cell properties and discovered Cell.HasArray and modified the code thus:

Function GetFormula(Cell As Range) As String
   GetFormula = Cell.Formula
   If Cell.HasArray Then GetFormula = "{" & GetFormula & "}"
End Function

This seems to handle array formulas, but then I noticed that it doesn't handle strings that look like formulas ('=x-1). After a little more research, I added code to check for VarType 8:

Function GetFormula(Cell As Range) As String
  GetFormula = Cell.Formula
  If Cell.HasArray Then
    GetFormula = "{" & GetFormula & "}"
  ElseIf VarType(Cell) = 8 Then
    GetFormula = "'" & Cell.Text
  End If
End Function

This seems to work. Can anyone find any holes in the logic?

This is for Office 2007. I understand that Office 2013 has a FormulaText function that may make this unnecessary.

 

[Tetra201]

Your UDF will precede any string with a single quote -- even if it was not explicitly entered. Also, if the string was entered as ="Test", the UDF will still return 'Test

 

[JenniferMurphy]

Your UDF will precede any string with a single quote -- even if it was not explicitly entered. Also, if the string was entered as ="Test", the UDF will still return 'Test

Good catch.

How about this?

Function GetFormula(Cell As Range) As String
  GetFormula = Cell.Formula
  If Cell.HasArray Then         'If it's an array formula,
    GetFormula = "{" & GetFormula & "}" 'Add the curly braces
  End If
  If Cell.HasFormula Then       'If it really is a formula,
    Exit Function                 'We're done
  End If
  If VarType(Cell) = 8 Then     'If it's text,
    GetFormula = "'" & Cell.Text  'Prefix a single quote
  End If
End Function

Here are some results:

C/R	C	D	E	F
3	Date	GetFormula	GetFormula^2	Comments
4	10	10	=getformula(C4)	Literal number assigned the name "X"
5	11	=X+1	=getformula(C5)	Expression
6	0.53488	=23/43	=getformula(C6)	Expression
7	X+1	="X+1"	=getformula(C7)	Expression
8	0.53	0.534883720930233	=getformula(C8)	Formatted number
9	X+1	'X+1	=getformula(C9)	Literal expression
10	3.5	'3.5	=getformula(C10)	Literal number
11	3.5	3.5	=getformula(C10)	Number
12	02/14/17	42780	=getformula(C11)	Date
13	6:00 am	0.25	=getformula(C12)	Time
14	0	{=SUM(0+(C4:C13=D4:D13))}	=getformula(C14)	Array formula
15			=getformula(C15)	Empty
16		'	=getformula(C16)	Spaces

<tbody>
</tbody>

 

[JenniferMurphy]

Arrgghh. It looks like it needs more work.

What's the difference between Cell.FormulaArray & Cell.Formula?

As far as I can tell, they both return the exact same data whether the cell contains an array formula, a regular formula, or, for that matter, pretty much anything else.

 

[RoryA]

FormulaArray only differs from Formula when setting a formula - i.e. it array-enters it for you. Reading the formula will be no different either way.

 

[JenniferMurphy]

FormulaArray only differs from Formula when setting a formula - i.e. it array-enters it for you. Reading the formula will be no different either way.

That makes sense. I didn't think about setting it -- just reading it.

Seems like another, albeit minor, failure by the M$FT developers. Since they have 2 properties, they could have returned the curly braces for the array version. To me, that would be more accurate & complete. (sigh)

 

[JenniferMurphy]

According to this page, https://msdn.microsoft.com/en-us/library/office/gg278470.aspxhttps://msdn.microsoft.com/en-us/library/office/gg278470.aspx, the VarType function returns about 19 results including different codes for different number types. But when I execute VarType against a Cell parameter, I get "5" (double) for both integers and floating point numbers.

 

[Rick Rothstein]

Lightly tested, but I think this may return what shows in the Formula Bar for the cell in question...

Function GetFormula(Cell As Range) As String
  If Cell.HasArray Then
    GetFormula = "{" & Cell.FormulaArray & "}"
  ElseIf Cell.HasFormula Then
    GetFormula = Cell.Formula
  Else
    GetFormula = Cell.PrefixCharacter & Cell.Value
  End If
End Function

 

[RoryA]

Seems like another, albeit minor, failure by the M$FT developers. Since they have 2 properties, they could have returned the curly braces for the array version. To me, that would be more accurate & complete. (sigh)

But then we would either have to add the {} when writing to it, or have the read and write use different values. I'd rather have it the way it is, to be honest.

 

[JenniferMurphy]

Lightly tested, but I think this may return what shows in the Formula Bar for the cell in question...

Function GetFormula(Cell As Range) As String
  If Cell.HasArray Then
    GetFormula = "{" & Cell.FormulaArray & "}"
  ElseIf Cell.HasFormula Then
    GetFormula = Cell.Formula
  Else
    GetFormula = Cell.PrefixCharacter & Cell.Value
  End If
End Function

Pretty darn close. Good work.

I added this to your code to display as many properties as I could think of.

Dim msg As String
msg = "Address = " & Application.Caller.Address & vbCrLf & _
      "Cell.Address = " & Cell.Address & vbCrLf & _
      "Length = " & Len(Cell) & vbCrLf & _
      "Cell.HasFormula = " & Cell.HasFormula & vbCrLf & _
      "Cell.HasArray = " & Cell.HasArray & vbCrLf & _
      "Cell.Formula = " & Cell.Formula & vbCrLf & _
      "Cell.FormulaArray = " & Cell.FormulaArray & vbCrLf & _
      "Cell.Value = " & Cell.value & vbCrLf & _
      "Cell.Text = " & Cell.Text & vbCrLf & _
      "Cell.PrefixCharacter = " & Cell.PrefixCharacter & vbCrLf & _
      "VarType(Cell) = " & VarType(Cell) & vbCrLf
MsgBox msg, , "GetFormula"

Then I created a spreadsheet to test as many cases as I could think of and summarized that in a properties table. I've uplaoded both to this drop box folder:

https://www.dropbox.com/sh/ifnqgy7j9qnpq0u/AADwafLmyS5KvpXtQBvvdwsZa?dl=0

Your code handles everything I could throw at it except for 2 having to do with time. Curiously it handled dates OK.