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.

 

[Rick Rothstein]

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

It is not clear from your pictures what the problem was. What did my formula display for dates and what did it display for the times? For those dates and times, what showed in your Formula Bar? Lastly, what did you want to see for those values... what was in the cell or what was in the Formula Bar (those two values are different for me, hence my questions)?

 

[JenniferMurphy]

It is not clear from your pictures what the problem was. What did my formula display for dates and what did it display for the times? For those dates and times, what showed in your Formula Bar? Lastly, what did you want to see for those values... what was in the cell or what was in the Formula Bar (those two values are different for me, hence my questions)?

Gosh, and I went to so much trouble to make them crystal clear!

I've uploaded new graphics that I hope are clearer. I also uploaded the workbook itself. The workbook does not include the UDF. I keep all my UDFs in a personal add-in, so you'll need to put it where the workbook can find it.

https://www.dropbox.com/sh/ifnqgy7j9...QBvvdwsZa?dl=0

The Test Sheet graphic is a snap shot of the actual worksheet, which you now have, so you probably don't need the graphic. The important rows are 27 & 28. Both C27 and C28 contain "06:00:00". That's what is in the Formula Bar. C27 is formatted as "hh:mm:ss", C28 as "h:mm am/pm". The GetFormula returns "0.25" for both.

The Properties Table graphic is from a Word document that reports the results from the MsgBox statement in the UDF. A24 & A25 report what I said about the contents of the cells.

Is that clearer?

 

[Rick Rothstein]

Gosh, and I went to so much trouble to make them crystal clear!

I've uploaded new graphics that I hope are clearer. I also uploaded the workbook itself. The workbook does not include the UDF. I keep all my UDFs in a personal add-in, so you'll need to put it where the workbook can find it.

https://www.dropbox.com/sh/ifnqgy7j9...QBvvdwsZa?dl=0

The Test Sheet graphic is a snap shot of the actual worksheet, which you now have, so you probably don't need the graphic. The important rows are 27 & 28. Both C27 and C28 contain "06:00:00". That's what is in the Formula Bar. C27 is formatted as "hh:mm:ss", C28 as "h:mm am/pm". The GetFormula returns "0.25" for both.

The Properties Table graphic is from a Word document that reports the results from the MsgBox statement in the UDF. A24 & A25 report what I said about the contents of the cells.

Is that clearer?

I'm not sure what the difference, but when I first wrote my function for you, dates and times in a cell showed the underlying numeric value for them rather than the date and time themselves, so I wrote the code to do that (which it did on my system, but only half did on yours)... but now, dates and times in the Formula Bar are the same as in the cell. As I said, I am not sure what is different now than back then, but no matter... I believe this minor change (highlighted in red below) will produce the result you are looking for... formatted numeric values (dates, times, currencies, etc.) will be displayed as to what is in the cell, no matter what is in the Formula Bar...

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.Text
  End If
End Function

 

[JenniferMurphy]

I'm not sure what the difference, but when I first wrote my function for you, dates and times in a cell showed the underlying numeric value for them rather than the date and time themselves, so I wrote the code to do that (which it did on my system, but only half did on yours)... but now, dates and times in the Formula Bar are the same as in the cell. As I said, I am not sure what is different now than back then, but no matter... I believe this minor change (highlighted in red below) will produce the result you are looking for... formatted numeric values (dates, times, currencies, etc.) will be displayed as to what is in the cell, no matter what is in the Formula Bar...

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.Text
  End If
End Function

Rick,

Unless there is something I don't understand, always a distinct possibility, I want the UDF to return what I see in the Formula Bar, regardless of the formatting. Neither Cell.Text not Cell.Value will do that. That's why I included both of these values in the MsgBox string and showed them in the Properties Table document.

I have uploaded a new graphic to the Dropbox folder that shows the results for Cell.Text. And I renamed the other one to show that it has the data for Cell.Value.

I originally started on this endeavor to be able to display a formula, so, in the light of that objective, this is not a big deal. But now that I have gotten this far, I would like to see if Excel/VBA is capable of doing the whole job. As far as I can see, neither Cell.Text not Cell.Value can do it by themselves.

Did I misunderstand something?

-jm

 

[JenniferMurphy]

I think I have it figured out. For data values (not formulas), the Formula Bar is affected by the formatting.

If I enter "0.25", the Formula Bar will show "0.25". But if I then change the formatting to "h:mm", the cell will show "6:00" and the Formula Bar will show "06:00:00".

Similarly, if I enter "6 am", the cell will show "6:00 am" and the Formula Bar will show "06:00:00". If I then change the formatting to Number, both the cell and the Formula Bar will show "0.25".

I will have to think about what I really want it to do. (sigh)

 

[JenniferMurphy]

I have uploaded 3 new files to Dropbox:

https://www.dropbox.com/sh/ifnqgy7j9...QBvvdwsZa?dl=0

GetFormula UDF.xlsm: A macro-enabled workbook containing 1 sheet and 2 UDFs. It has the GetFormula UDF I have been working on with a tweak to allow me to pass it a second parameter to control whether it uses Cell.Value or Cell.Text and a GetProperties UDF to return as many of the properties as I could of the Cell parameter so I could see if there might be a way to show exactly what is in the Formula Bar. It looks like there is not.

GetFormula Results (cell.value).jpg: A graphic of the worksheet if GetFormula is called using Cell.Value. This seems to work the best. It only messes up on a few date values.

GetFormula Results (cell.text).jpg: A graphic of the worksheet if GetFormula is called using Cell.Text. This doesn't work as well. It messes up on the date data as well as some currency and general numeric data.

I think I'll have to leave it at that. It works for formulas and array formulas and that was what I was primarily interested in.

If anyone has a suggestion for how to get the last few data types to work, let me know.

Here's the final UDF. It's basically as Rick proposed it.

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

Thanks, Rick

 

[Tetra201]

The following code resolves the "0.25 vs 6:00" issue, but it is far from being perfect:

Function GetFormula(Cell As Range) As String
  If Cell.HasArray Then
    GetFormula = "{" & Cell.FormulaArray & "}"
  ElseIf Cell.HasFormula Then
    GetFormula = Cell.Formula
  Else
    If VarType(Cell) = 5 And ( _
       (InStr(1, Cell.NumberFormat, "h") > 0) Or _
       (InStr(1, Cell.NumberFormat, "m") > 0) Or _
       (InStr(1, Cell.NumberFormat, "s") > 0)) Then
        GetFormula = Format(Cell.Value, "General Date")
    Else
        GetFormula = Cell.PrefixCharacter & Cell.Value
    End If
  End If
End Function

Issues:
- Custom formats with \h, \m, \s
- Integer numbers formatted as time
- Numbers between 1 and 61
- Format change is not a recalculation event