creating Arrays from addin functions

  • Thread starter Thread starter Steve Lloyd
  • Start date Start date
S

Steve Lloyd

Can anyone help me in returning a formula array from a worksheet function.

I have a worksheet function that successfully populates an array and works
fine provided that the formula is entered using Ctrl + Shift + Enter.
However, this is far too complicated for the users and i want the array to
be populated vertically starting at the cell where the formula was entered.
sounds simple enough but is giving me great headaches.

In short when they type =ArrayFunction(CellReference) i want the array to
start in that cell and populate the results vertically with the seperate
values in each cell.

Confused ?? me too !

any help is much appreciated.

Thanks

Steve.
 
Steve,

Does this help

Function ArrayFunction(cell As Range)
Dim ary
ary = Range(cell, Cells(Rows.Count, cell.Column).End(xlUp))
'rest of code
End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Can anyone help me in returning a formula array from a worksheet function.

I have a worksheet function that successfully populates an array and works
fine provided that the formula is entered using Ctrl + Shift + Enter.
However, this is far too complicated for the users and i want the array to
be populated vertically starting at the cell where the formula was entered.
sounds simple enough but is giving me great headaches.

In short when they type =ArrayFunction(CellReference) i want the array to
start in that cell and populate the results vertically with the seperate
values in each cell.
Click to expand...
...

This is possible, but it's damned awkward. More user training would be better.
But here goes. Do you mean something like the following very simplified task?

ArrayFormula(cellref) returns cellref*{1;10;100;1000}, so a 4 row by 1 column
array result, and you want it automatically entered into a range of that size
even when the user just presses [Enter].

If so, then you could use a SheetChange event handler to do it.


Private Sub Workbook_SheetChange( _
ByVal Sh As Object, _
ByVal Target As Excel.Range _
)
'------------------------------------------
Dim x As Variant, n As Long, c As Range

Application.EnableEvents = False
Application.ScreenUpdating = False

For Each c In Target
If Not (Intersect(c.Offset(1, 0), Target) Is Nothing And _
Intersect(c.Offset(0, 1), Target) Is Nothing) Then Exit For

x = Evaluate(c.Formula)

If IsArray(x) Then
On Error Resume Next
n = UBound(x, 2)
On Error GoTo CleanUp

If n > 0 Then
c.Resize(UBound(x, 1), n).FormulaArray = c.Formula

Else
c.Resize(1, UBound(x, 1)).FormulaArray = c.Formula

End If

End If

Next c

CleanUp:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
 
Back
Top