Referring to same column that contains UDF

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
 
Upvote 0
Can I ask why you need that?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
Yes. Any ranges that are used should be passed directly, IMO.
 
Upvote 0
I second the motion. Let Excel see the dependency and recalculate only as needed.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top