Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Column Name?

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!


    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  4. #4
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    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

    Hope this helps + pen pineapple apple pen!

    Masaru Kaji aka Colo - cellmasters.net

  5. #5
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    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


    Hope this helps + pen pineapple apple pen!

    Masaru Kaji aka Colo - cellmasters.net

  6. #6
    New Member
    Join Date
    May 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You guys are great.

    Thank you.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •