Column Name?

megaxoom

New Member
Joined
May 11, 2002
Messages
11
Hi everyone,

I have written a funtion which has the following form:

Function test(rng as Range)
...

End Function

and called it by
test(A1:C10)

My question is how do we know the column name in the function test? (i.e. how can we figure out the columns are A, B, and C.

Thanks for any help
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
On 2002-05-12 20:36, megaxoom wrote:
Hi everyone,

I have written a funtion which has the following form:

Function test(rng as Range)
...

End Function

and called it by
test(A1:C10)

My question is how do we know the column name in the function test? (i.e. how can we figure out the columns are A, B, and C.

Thanks for any help

There are many ways to get to the function arguments, but try the following and see if you get anywhere

Function test(rng As Range)

MsgBox rng.Address(False, False)
MsgBox rng.Rows.Count
MsgBox rng.Columns.Count
MsgBox rng.Range("A1").Row

End Function

I don't know of any easy ways to get "A" for the first column, so it is probably best to get familiar with the R1C1 referencing method when using VBA. I never have it on the worksheet, but in VBA it is very useful.

HTH,
Jay
 
Upvote 0
Hi megaxoom and Jay:
In the worksheet address function:

=address(row_num,column_num,abs_num,a1,sheet_text)

if Ihad row number 1, column number 1, abs_number =4, then

=address(1,1,4,) will result in --> A1

I don't know if this is of any help, but I thought just in case ...

Regards!
 
Upvote 0
Hi megaxoom, and all,

How to figure out the column name using VBA is....

<pre>
Sub try()
Dim i As Integer
For i = 1 To 256
Debug.Print test(Cells(1, i))
Next
End Sub

Function test(rng As Range) As String
Dim strTmp As String
strTmp = rng.EntireColumn.Address(0, 0)
test = Mid(strTmp, 1, InStr(1, strTmp, Chr(&H3A), 1) - 1)
End Function
</pre>
 
Upvote 0
and called it by
test(A1:C10)

Sorry, I've not read it well.
Please try these again.

<pre>
Sub try2()
Debug.Print test2(Range("A1:C1"))
End Sub

Function test2(rng As Range) As String
Dim strTmp As String, intCnt As Integer, strBuf As String
For intCnt = 1 To rng.Columns.Count
strTmp = rng.Offset(, intCnt - 1).EntireColumn.Address(0, 0)
strBuf = strBuf & Mid(strTmp, 1, InStr(1, strTmp, Chr(&H3A), 1) - 1) & ","
Next
test2 = Left(strBuf, Len(strBuf) - 1)
End Function
</pre>

<HR>

<pre>
Sub try3()
Debug.Print test3("A1:C1")
End Sub

Function test3(rng As String) As String
Dim strTmp As String, intCnt As Integer, strBuf As String, rngRng As Range
Set rngRng = Range(rng)
For intCnt = 1 To rngRng.Columns.Count
strTmp = rngRng.Offset(, intCnt - 1).EntireColumn.Address(0, 0)
strBuf = strBuf & Mid(strTmp, 1, InStr(1, strTmp, Chr(&H3A), 1) - 1) & ","
Next
test3 = Left(strBuf, Len(strBuf) - 1)
End Function
</pre>
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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