Column Name?

[megaxoom]

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

[Jay Petrulis]

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

[Yogi Anand]

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!

[Colo]

Hi megaxoom, and all,

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

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

[Colo]

and called it by
test(A1:C10)

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

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

---

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

[megaxoom]

You guys are great.

Thank you.