Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 10th 06, 02:55 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 2,574
Default 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...)

  #2   Report Post  
Old August 10th 06, 03:58 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 10,593
Default 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...)



  #3   Report Post  
Old August 10th 06, 04:12 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 2,574
Default 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...)




  #4   Report Post  
Old August 10th 06, 04:23 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 8,856
Default 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...)





  #5   Report Post  
Old August 10th 06, 04:36 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 2,574
Default 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...)







  #6   Report Post  
Old August 10th 06, 04:54 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 10,593
Default 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...)






  #7   Report Post  
Old August 10th 06, 06:06 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 111
Default 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...)






  #8   Report Post  
Old August 10th 06, 06:16 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 733
Default 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

  #9   Report Post  
Old August 10th 06, 09:32 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 2,574
Default 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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
GetFormula function- Need help showing formulas only- [email protected] Excel Worksheet Functions 2 January 9th 06 02:52 PM


All times are GMT +1. The time now is 07:51 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017