Referring to same column that contains UDF

[MrKowz]

Hey all,

I'm writing a UDF for a user on the boards here, and I have the need for the UDF to identify what column it is located in. Is there a way to do this without requiring an additional argument in the code?

Thanks in advance!

 

[Damon Ostrander]

Hi MrKowz,

Inside a UDF you can use Application.Caller.Column to give the column number the UDF is being called from.

For example, if you put the code

MsgBox "I'm in column " & Application.Caller.Column

in your UDF it will show a message box that shows the column when the UDF calculates.

Keep Excelling.

Damon

 

[RoryA]

Can I ask why you need that?

 

[MrKowz]

Thanks Damon!

Rorya,

I needed to call the column it was in because the code had to look at every cell above it to find where the last non-blank ("") entry was.

It was for this post. The code I produced is in post# 13. (I hadn't had the Application.Caller.Column code yet, so the code I supplied was dependent on both where the data and where the formula is placed.)

In essence, the code had to look at a column of time (seconds), and determine if at least 160 second had passed. If it did, then it had to identify a peak (high) or trough (low) in the data. I would have done this with native formulas, but it was hitting the maximum nested statements.

 

[RoryA]

With UDFs you should always aim to pass the relevant ranges as direct arguments to the function. Using hardcoded range references or Offset etc, means you need to make the function volatile and that should be avoided if at all possible.

 

[MrKowz]

With UDFs you should always aim to pass the relevant ranges as direct arguments to the function. Using hardcoded range references or Offset etc, means you need to make the function volatile and that should be avoided if at all possible.

Aye, I agree. In a sense this code has the arguments passed for the column without being explicitly stated - the non-stated reference always begins on the same row as FirstTimeRange, will always be in the same column as the formula, and will always end in the cell above the formula's current cell. My thought was to reduce the chance of human error when entering the formula, because the user would have to remember to use Absolute references for the first cell in the range, and relative references for the second cell ($C$2:C3). I will, however, be recoding it for the user to include Application.Caller.Column so that it isn't reliant on being next to the data.

Just to further my expertise and programming ethics, given this particular UDF, would you still reccomend that it requires the argument of the range above it?

Thanks for the insight!

 

[RoryA]

Yes. Any ranges that are used should be passed directly, IMO.

 

[shg]

I second the motion. Let Excel see the dependency and recalculate only as needed.

 

[MrKowz]

Understood, thanks guys!

 

[MrKowz]

Quick, easy question (because I've honestly never taken the time to learn it and it is going to be applicable to rewriting the code I mentioned in this post). When given a range object, what is the easiest way to get the last cell in that range? I usually use rng.resize(1,0) to get the first cell.