Current Region through a function

[Joakim N]

I am trying to get the address of Current Region through a Function call. The function only returns the address of the cell, not current region. Isn't it possible to use Current Region within a function? It works for a Subroutine.

I have attached the examples in a file.

[Izandol]

No - this will not work in a UDF. It is like SpecialCells or CurrentArray.

[martindwilson]

what do you need it for?

[Joakim N]

what do you need it for?

I wanted to send only one cell in a header row to a table as argument and inside the function determine the complete table range from CurrentRegion. I don't want to send the complete table range as argument into the function. For the moment I use Range.End() inside the function and it works but no cell is allowed to be empty in the column I use to pick complete table range. Using CurrentRegion should solve this issue.

[shg]

If you do that, Excel will see no dependency on the body of the table, and so will not recalculate when something changes. UDFs should be passed all of the ranges they have dependencies on.

[Joakim N]

OK. You're right. I have written a lot of functions which are called from subroutines inside VBA. I wanted them also to work from a spreadheet function. However, I will rewrite them some and pass the complete table range calling them from spreadsheet function. I could have used Application.Volatile inside the function to calculate recalculate the function each time a cell changes. But to many of these will slow down the project.

Thanks