Getting Range of CurrentArray In Formula

[Gates_72]

Hi All,

This may fall under the MSKB213199, but I wanted to see
if anybody knew of a way to do this.

We have a function that looks through the worksheet for
all occurrences of that function (grouping the parameters
of that function for an OPC call).

I have no trouble finding the cells that contain the
function with that name. However, what I want to do is
find the selected range of each of those function calls
(top left and bottom right cells).

I tried to use the CurrentArray property of the cell that
is found to contain the function, but it only gives me a
range containing that cell, not the entire range.

For example, if the function is found in an array from
B23, the CurrentArray.Rows.Count = 1 (same for
columns). That gives an offset of (0, 0), which makes
the first cell = last cell = B2.

Any ideas? I am trying to get around the problem with
assuming the shape and size of the formula array.

Thanks,
G72

 

[Bernie Deitrick]

G72,

I'm not sure why, but when you use the currentarray, it only works in
functions called from subs. It won't work in functions called from
worksheets.

I.E., this will work:

Sub Test()
MsgBox CAAddress(Range("B2"))
End Sub

Function CAAddress(incell As Range) As String
CAAddress = incell.CurrentArray.Address
End Function

but using

=CAAddress(B2)

in another cell won't work. Go figure....

HTH,
Bernie
MS Excel MVP

 

[Tom Ogilvy]

for a multicell array formula the UDF can use application.Caller to return
the "array" of cells containing the function.

 

[Gates_72]

Hi Tom,

Thanks - that works for the UDF that is calling the
function. My problem is that I need to know the array of
all calls of that function when a single call runs.

The UDF is a complicated, re-entrant call that groups OPC
items for a single OPC SyncRead. We gather all the tags
into one call, and keep track of the cell address of that
tag. Finding each call is easy enough, and telling that
it HasArray is no problem. The problem is that if it
HasArray, I cannot find a way to know the size of that
array.

Thanks,
G72