column number in a user defined function 1

[welldefined]

Hi,

In a cell, I will use a function I am going to write. How to get the column number of this cell in the function?

 

[combo]

Hi,
please note that ActiveCell is an active cell, you get the cell where the function is only when editing it. When the formula is recalculated (esp. after Application.Volatile), new cell can be active and used in function. Instead you can use PHV's formula or:

1. Application.Caller.Column
or, in office xp+:
2. Application.ThisCell.Column

combo

 

[GlennUK]

Hi combo,

Thiscell is not a property in Excel 97 ... when did that come in?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[combo]

Hi Glenn,
as I marked, this property was introduced in excel xp, returns the cell where the calling UDF is, as Range object.

combo

 

[GlennUK]

My concentration is not so good today, my apologies. ( I tend to skip over the green popup text adverts )

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[rmikesmith]

Combo's suggestion + Application.Volatile seems to fit the bill nicely. Using the OP's function definition:

Function TheColumn()
   Application.Volatile
   TheColumn = Application.Caller.Column
End Function

This function returns the correct column number whether it's dragged from cell to cell, a new column is inserted, etc.

Have a star.

Regards,
Mike

 

[GlennUK]

Hi rmikesmith,

please forgive before I start ( as I have misread/misunderstood 2 posts today ), but can I ask, why aren't you using =COLUMN() cell formula?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[rmikesmith]

Hi Glenn,

I didn't entertain the COLUMN() worksheet function because the OP indicated it didn't work for his purposes and seemed to want a UDF, perhaps to do more than what was detailed. I started from the assumption of a UDF that needed to be able to detect the current cell it was in, but I went all flaky there for a bit (for which PHV provided the reality check). Combo's code allows the UDF to do this. I really don't know what the OP needs this function to do and he hasn't posted lately, but he'd be the one to ask.

Regards,
Mike