CurrentRegion - Doesn't work when function referenced in worksheet cell

[BlaineG]

CurrentRegion is most handy in many cases, however I have (sadly) just discovered, it apparently has limitations.
I have boiled this down to its simplest form for posting purposes. This is not my real code, but it demonstrates the challenge.

If the function (below) is called from another function or subroutine, or, if it is simply run "stand alone" it does what I expect, it targets cell C3, expands the range, and the resultant address is displayed as B3:D6 (no problem).

However, if the function is used by inserting the formula "=MyFun()" into a cell in the spreadsheet, the CurrentRegion seems to be completely ignored and the function displays, and returns, simply "C3" (ignoring the ".CurrentRegion" portion of the s1 assignment).

Can someone share why this happens? What am I missing?
Thank you!!


Function MyFun() As String
Dim s1 As String
s1 = Worksheets("SHEET1").Range("C3").CurrentRegion.Address
MsgBox s1
MyFun = s1
End Function



-- removed inline image ---

 

[venkat1926]

Sheet1

*	A	B	C	D	E
1	1	1	1	1	1
2	1	1	1	1	1
3	1	1	1	1	1
4	1	1	1	1	1
5	1	1	1	1	1
6	1	1	1	1	1

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


the abolve is sam'le data

now try the funcion. your fiunction does not have arguments

Function MyFun(c As Range) As String
Dim s1 As String
MyFun = c.CurrentRegion.Address


End Function

Sub test()


Dim  add As String
add = MyFun(Range("A1"))
MsgBox add
End Sub

now try to test the macro test with this data and see what you ge.

 

[BlaineG]

Perhaps my question was not properly worded.

venkat1926 suggestion above does not work either. Bottom line is, "CurrentRegion" does not work, in any function, whether it has arguments or not, WHEN, the function is called from/referenced in, a worksheet cell.

My question as to "why" was more for being able to extrapolate other functions that may also "not work" when referenced from a worksheet cell.

Perhaps the question should be "Is there a comprehensive list of functions that (by design) do not work when referenced from a worksheet cell?" so I can avoid using them.

Thank you!

 

[Colin Legg]

Hi,

The general principal behind this is a VBA function called from a worksheet range should only return a value to the range which called it and cannot modify the Excel environment. So, for example, a function which tries to put the value 1 in another cell using the Range.Value property would not work. This makes sense because otherwise you could play absolute havoc with your UDFs. As a result of this, quite a few properties and methods in the Excel object model are disabled when the function is called from a range. Some of them are obvious, some of them are not. One which catches people out quite often is the Range.SpecialCells() method (which is disabled). Another of note is the Range.Find() method which was disabled up to Excel 2000 but was then enabled in Excel 2002 onwards by Microsoft due to popular demand.

 

[Colin Legg]

That said, I just checked and CurrentRegion seemed to work for me.

This in a public module:

Public Function foo(ByVal rng As Range)
    foo = rng.CurrentRegion.Address
End Function

Then this formula in cell B2
=foo(B1)


There's a note in the helpfile that CurrentRegion doesn't work when the worksheet is protected.

 

[Colin Legg]

I just checked it more thoroughly and it doesn't return the entire currentregion as BlaineG said.

 

[RoryA]

Yep - currentarray doesn't either IIRC.

 

[BlaineG]

Thanks everyone.
Colin, I totally agree with you (no buts), personally, as a general rule, I would never want to reference a function in a cell formula that modified other cells (ouch).

I was just hoping there was a definitive list of specific functions to avoid using in code that you may want to reference in a cell formula.
Not that I don't, I guess the answer lies in one's exhaustive testing. Such a list could however, save a good bit of time and hassle by not having to go back and develop a workaround to an otherwise perfectly functioning bit of code.

On the up side, my toolbox now has a really nice MyCurrentRegion function that works ALL the time

'preciate it folks!
BlaineG

 

[Colin Legg]

Generally speaking any Excel object model methods and let (write) properties will be disabled. There are exceptions to this such as Range.Find() which I mentioned above. Get (read) properties will generally be enabled. If you try to use a 'disabled' member then it would usually result in the UDF returning an error value. Range.CurrentRegion/CurrentArray are different in that respect because they return a value but I'd discourage their use in a UDF anyway because they would confuse Excel's dependency tree. I feel some testing and a blog post coming on.

 

[RoryA]

And although Find works from 2002 onwards, FindNext does not.