CurrentArray Range in Formula

pagates · September 16th, 2004, 10:22 AM

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,
Paul

BrianB · October 5th, 2004, 03:53 AM

Not sure exactly what you are trying to do. Perhaps a couple of comments might help.
1. CurrentArray refers to Array Formulas.
2. The MSKB article really just says that we cannot change contents of a sheet from a UDF (just the cell that contains it).
3. A cell formula at base level is just a string, so you can examine the contents of the formula to get the range it refers to. eg.

Code:

Sub test()
    Dim MyString As String
    MyString = Range("A1").Formula
    MsgBox (MyString)
End Sub

4. If you are trying to get a range that consists of the cells containing the formula you could add each cell reference as it is found by using the Union method.

-----------------------
Regards BrianB
Most problems occur from starting at the wrong place.
Use a cup of coffee to make Windows run faster.
It is easy until you know how.