I'm trying to use CurrentRegion for the first time, and it appears it behaves differently when I use it in a sub than when I use it in a function.

This:
Sub SelectCurrentRegion()
    Range("c5").Activate
    MsgBox (ActiveCell.Address)
    MsgBox (ActiveCell.CurrentRegion.Rows.Count)
    MsgBox (ActiveCell.CurrentRegion.Columns.Count)
    ActiveCell.CurrentRegion.Select
End Sub

Tells me what I want to hear - that the CurrentRegion around C5 has 22 rows and 4 columns. The correct region is selected, not matter where in the worksheet is active before I run the macro.

BUT this:
Function TableLookup()
    Range("c5").Activate
    MsgBox (ActiveCell.Address)
    MsgBox (ActiveCell.CurrentRegion.Rows.Count)
    MsgBox (ActiveCell.CurrentRegion.Columns.Count)
    ActiveCell.CurrentRegion.Select
    'TableLookup = "foo"
End Function

Will tell me that the active cell's address is whatever cell I've put the formula in, not C5. Even if I do start it in C5, it tells me that there's just one column and one cell in C5's current region.

Now, the end goal of this function isn't just to select regions, but I can't move on until I learn what I'm doing wrong. Can what I want to do even be done?

The sub procedure is called from anywhere and the first line moves the activecell to the c5, this is the point where cell c5 is said to "get focus". The cell then continues to be retained as the activecell in focus until the end of the code.

The function (udf or user defined function) executes when you place a cell lets say "d20" into edit mode, this cell is said to "get focus". you type in the function "=tablelookup()" and press enter, the action of pressing enter means that before the cell can "lose focus" it must recalculate the udf, the first line of the udf makes the activecell c5 however because the code in the udf has not finished, the focus is returned to the cell d20 straight after the line completes the execution.

hope this helps.

cheers

Matt
-------------------------------------------------------

So, basically, the answer is, "you can't do that"? It's fine if it is, I just want to be sure I'm reading it correctly.