array formulas and getformula UDF
I use the following code to create a UDF, getformula:
Function GetFormula(Cell As Range) As String GetFormula = Cell.Formula End Function When I use that formula to return an array formula, the forumla returned appears as a conventional formula. Example: I have a formula, =SUM(A1:B5*D1:E5), which is entered as an array formula. How come the UDF returns it without the brackets {}? Anyway to get the UDF to properly recognize an array formula? (And yes, I realize that SUMPRODUCT can be used in place of SUM as an array, and can therefore obviate this question...) |
array formulas and getformula UDF
I don't think you can, the braces are internal to Excel, to tell it to array
process the formula. As you know, you don't type them, Excel adds them as a visual cue. It must also store something at the same time to tell itself how to process it on a recalc. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Dave F" wrote in message ... I use the following code to create a UDF, getformula: Function GetFormula(Cell As Range) As String GetFormula = Cell.Formula End Function When I use that formula to return an array formula, the forumla returned appears as a conventional formula. Example: I have a formula, =SUM(A1:B5*D1:E5), which is entered as an array formula. How come the UDF returns it without the brackets {}? Anyway to get the UDF to properly recognize an array formula? (And yes, I realize that SUMPRODUCT can be used in place of SUM as an array, and can therefore obviate this question...) |
array formulas and getformula UDF
Well, this is interesting.
I recorded a macro while entering the array formula I reference below, and Excel returns the following code: Sub arraytest() ' ' arraytest Macro ' Macro recorded 8/10/2006 by David Friedman ' ' Selection.FormulaArray = "=SUM(R[-15]C[-1]:R[-11]C*R[-15]C[2]:R[-11]C[3])" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") End Sub Somewhere in there must lie the answer. "If selection is formula array, then enter the formula with brackets, else return the normal formula" etc. "Bob Phillips" wrote: I don't think you can, the braces are internal to Excel, to tell it to array process the formula. As you know, you don't type them, Excel adds them as a visual cue. It must also store something at the same time to tell itself how to process it on a recalc. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Dave F" wrote in message ... I use the following code to create a UDF, getformula: Function GetFormula(Cell As Range) As String GetFormula = Cell.Formula End Function When I use that formula to return an array formula, the forumla returned appears as a conventional formula. Example: I have a formula, =SUM(A1:B5*D1:E5), which is entered as an array formula. How come the UDF returns it without the brackets {}? Anyway to get the UDF to properly recognize an array formula? (And yes, I realize that SUMPRODUCT can be used in place of SUM as an array, and can therefore obviate this question...) |
array formulas and getformula UDF
What happens if you enter your own getformula( ) function with
CTRL-SHIFT-ENTER? Pete Dave F wrote: Well, this is interesting. I recorded a macro while entering the array formula I reference below, and Excel returns the following code: Sub arraytest() ' ' arraytest Macro ' Macro recorded 8/10/2006 by David Friedman ' ' Selection.FormulaArray = "=SUM(R[-15]C[-1]:R[-11]C*R[-15]C[2]:R[-11]C[3])" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") End Sub Somewhere in there must lie the answer. "If selection is formula array, then enter the formula with brackets, else return the normal formula" etc. "Bob Phillips" wrote: I don't think you can, the braces are internal to Excel, to tell it to array process the formula. As you know, you don't type them, Excel adds them as a visual cue. It must also store something at the same time to tell itself how to process it on a recalc. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Dave F" wrote in message ... I use the following code to create a UDF, getformula: Function GetFormula(Cell As Range) As String GetFormula = Cell.Formula End Function When I use that formula to return an array formula, the forumla returned appears as a conventional formula. Example: I have a formula, =SUM(A1:B5*D1:E5), which is entered as an array formula. How come the UDF returns it without the brackets {}? Anyway to get the UDF to properly recognize an array formula? (And yes, I realize that SUMPRODUCT can be used in place of SUM as an array, and can therefore obviate this question...) |
array formulas and getformula UDF
Interesting suggestion, there Pete.
All it does is place brackets in the formula box. What appears in the cell still doesn't have brackets. "Pete_UK" wrote: What happens if you enter your own getformula( ) function with CTRL-SHIFT-ENTER? Pete Dave F wrote: Well, this is interesting. I recorded a macro while entering the array formula I reference below, and Excel returns the following code: Sub arraytest() ' ' arraytest Macro ' Macro recorded 8/10/2006 by David Friedman ' ' Selection.FormulaArray = "=SUM(R[-15]C[-1]:R[-11]C*R[-15]C[2]:R[-11]C[3])" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") End Sub Somewhere in there must lie the answer. "If selection is formula array, then enter the formula with brackets, else return the normal formula" etc. "Bob Phillips" wrote: I don't think you can, the braces are internal to Excel, to tell it to array process the formula. As you know, you don't type them, Excel adds them as a visual cue. It must also store something at the same time to tell itself how to process it on a recalc. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Dave F" wrote in message ... I use the following code to create a UDF, getformula: Function GetFormula(Cell As Range) As String GetFormula = Cell.Formula End Function When I use that formula to return an array formula, the forumla returned appears as a conventional formula. Example: I have a formula, =SUM(A1:B5*D1:E5), which is entered as an array formula. How come the UDF returns it without the brackets {}? Anyway to get the UDF to properly recognize an array formula? (And yes, I realize that SUMPRODUCT can be used in place of SUM as an array, and can therefore obviate this question...) |
array formulas and getformula UDF
That is just VBA speak for CSE, same issue.
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Dave F" wrote in message ... Well, this is interesting. I recorded a macro while entering the array formula I reference below, and Excel returns the following code: Sub arraytest() ' ' arraytest Macro ' Macro recorded 8/10/2006 by David Friedman ' ' Selection.FormulaArray = "=SUM(R[-15]C[-1]:R[-11]C*R[-15]C[2]:R[-11]C[3])" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") End Sub Somewhere in there must lie the answer. "If selection is formula array, then enter the formula with brackets, else return the normal formula" etc. "Bob Phillips" wrote: I don't think you can, the braces are internal to Excel, to tell it to array process the formula. As you know, you don't type them, Excel adds them as a visual cue. It must also store something at the same time to tell itself how to process it on a recalc. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Dave F" wrote in message ... I use the following code to create a UDF, getformula: Function GetFormula(Cell As Range) As String GetFormula = Cell.Formula End Function When I use that formula to return an array formula, the forumla returned appears as a conventional formula. Example: I have a formula, =SUM(A1:B5*D1:E5), which is entered as an array formula. How come the UDF returns it without the brackets {}? Anyway to get the UDF to properly recognize an array formula? (And yes, I realize that SUMPRODUCT can be used in place of SUM as an array, and can therefore obviate this question...) |
array formulas and getformula UDF
To the OP, what if you first test for if cell hasarray and if it does, append
"{}" to either side of the formula? Something like (not tested.) Function GetFormula(Cell As Range) As String lb = "" ' left bracket might want to dim as string rb = "" if cell.hasarray then lb = "{" rb = "}" end if GetFormula = lb & Cell.Formula & rb End Function -- Kevin Vaughn "Bob Phillips" wrote: That is just VBA speak for CSE, same issue. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Dave F" wrote in message ... Well, this is interesting. I recorded a macro while entering the array formula I reference below, and Excel returns the following code: Sub arraytest() ' ' arraytest Macro ' Macro recorded 8/10/2006 by David Friedman ' ' Selection.FormulaArray = "=SUM(R[-15]C[-1]:R[-11]C*R[-15]C[2]:R[-11]C[3])" Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime") End Sub Somewhere in there must lie the answer. "If selection is formula array, then enter the formula with brackets, else return the normal formula" etc. "Bob Phillips" wrote: I don't think you can, the braces are internal to Excel, to tell it to array process the formula. As you know, you don't type them, Excel adds them as a visual cue. It must also store something at the same time to tell itself how to process it on a recalc. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Dave F" wrote in message ... I use the following code to create a UDF, getformula: Function GetFormula(Cell As Range) As String GetFormula = Cell.Formula End Function When I use that formula to return an array formula, the forumla returned appears as a conventional formula. Example: I have a formula, =SUM(A1:B5*D1:E5), which is entered as an array formula. How come the UDF returns it without the brackets {}? Anyway to get the UDF to properly recognize an array formula? (And yes, I realize that SUMPRODUCT can be used in place of SUM as an array, and can therefore obviate this question...) |
array formulas and getformula UDF
Bob Phillips wrote...
I don't think you can, the braces are internal to Excel, to tell it to array process the formula. As you know, you don't type them, Excel adds them as a visual cue. It must also store something at the same time to tell itself how to process it on a recalc. .... But the udf in question just returns text, and its Range arguments would have a HasArray property. "Dave F" wrote in message I use the following code to create a UDF, getformula: Function GetFormula(Cell As Range) As String GetFormula = Cell.Formula End Function .... Example: I have a formula, =SUM(A1:B5*D1:E5), which is entered as an array formula. How come the UDF returns it without the brackets {}? Anyway to get the UDF to properly recognize an array formula? .... If you want array formulas treated specially, add code to the udf. Function GetFormula(Cell As Range) As String GetFormula = Cell.Formula If Cell.HasArray Then GetFormula = "{" & GetFormula & "}" End Function |
array formulas and getformula UDF
Harlan, thanks. YOu figured it out.
"Harlan Grove" wrote: Bob Phillips wrote... I don't think you can, the braces are internal to Excel, to tell it to array process the formula. As you know, you don't type them, Excel adds them as a visual cue. It must also store something at the same time to tell itself how to process it on a recalc. .... But the udf in question just returns text, and its Range arguments would have a HasArray property. "Dave F" wrote in message I use the following code to create a UDF, getformula: Function GetFormula(Cell As Range) As String GetFormula = Cell.Formula End Function .... Example: I have a formula, =SUM(A1:B5*D1:E5), which is entered as an array formula. How come the UDF returns it without the brackets {}? Anyway to get the UDF to properly recognize an array formula? .... If you want array formulas treated specially, add code to the udf. Function GetFormula(Cell As Range) As String GetFormula = Cell.Formula If Cell.HasArray Then GetFormula = "{" & GetFormula & "}" End Function |
All times are GMT +1. The time now is 11:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com