Geek Answers Handbook

Is there a programming interface for Excel Array formulas?

For example, suppose that

{={1,2,3,4,5}}

in the cell. Is there any way to find out how many elements are in this array, and what are these elements?

Now, introducing the idea of ​​@chris neilsen, now I have the VBA function below

 Function rinfo(r As Range) As String With r.CurrentArray rinfo = .Address & ", " & .Rows.Count & ", " & .Columns.Count & ", " & .Value & ", " & .Value2 End With End Function

however, the data from it does not look reliable, namely

 $A$29, 1, 1, 1, 1

Rows.Count and Columns.Count rows make sense if they count the rows and columns used on the sheet. But, as an indication of the data in the array formula, no.

+4
source share
2 answers

Your formula occupies only one cell, and the cell can contain only a scalar value, so after calculating the cell, it will contain 1.
But you can evaluate the formula from VBA and this can give you the result of an array. If your formula is in A1, then

 Dim vArr As Variant vArr = Evaluate(Range("a1").Formula)

will result in vArr containing an array of 4 options out of 4 numbers.
There are several “quirks” to the Evaluate method: see one of my web pages for details.

+5
source

In Sub , if cl is Range and is set to a cell that is part of the array formula, then

 cl.CurrentArray

returns a range containing the array formula.

If your formula, the formula was in cells A1:E1 , and the active cell was any of the cells A1 .. E1, then

 Sub zx() MsgBox "ref = " & ActiveCell.CurrentArray.Address End Sub

will return the message ref = $A$1:$E$1

You can use .Rows.Count and .Columns.Count to get the size, and .Value values

+1
source

More articles:


All Articles