Getting Range of CurrentArray In Formula
 

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
B2:D3, 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

Getting Range of CurrentArray In Formula
 

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

"Gates_72" wrote in message
...
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
B2:D3, 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

Getting Range of CurrentArray In Formula
 

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

--
Regards,
Tom Ogilvy

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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

"Gates_72" wrote in message
...
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
B2:D3, 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

Getting Range of CurrentArray In Formula
 

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


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

--
Regards,
Tom Ogilvy

"Bernie Deitrick" <deitbe @ consumer dot org wrote in
message
...
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

"Gates_72" wrote
in message
...
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
B2:D3, 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




.