View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips Bob Phillips is offline
external usenet poster
 
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...)