Results 1 to 8 of 8
  1. #1
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts

    Programming Excel w/VBA

    Hey Y'all,

    Often when programming in VBA you may want to convert a Column reference from A1 notation, e.g. column "AB" to R1C1 notation, e.g. column 28. Very handy in loops and such.

    If your cursor is in the appropriate cell/column you can easily get this by using Immediate window and typing ?Activecell.column and pressing Enter.
    Immediate window.JPG
    However, if you aren't there and don't want to keep moving your cursor around here's a little function you can use to "do the math".
    Code:
    '                         +-------------------------+             +----------+
    '-------------------------|     lColLtrToNum()      |-------------| 10/10/13 |
    '                         +-------------------------+             +----------+
    'Arguments: Column Reference as String
    '       Ex: "bba"
    
    Public Function lColLtrToNum(zCol As String) As Long
    
    'Function returns zero (0) if the column reference
    'given is out of range.
    
    'Note: If the cursor is in the cell you want the number
    '      for simply typing ?Activecell.column in the
    '      Immediate window will return the correct number.
    
       Dim iColLen  As Integer
       Dim iCnt     As Long
       Dim zCurChar As String
       
       zCol = UCase(zCol)
       iColLen = Len(zCol)
       
       If iColLen = 0 Or _
          iColLen > 3 Then Exit Function
       
       For iCnt = 1 To iColLen
          zCurChar = Mid(zCol, iCnt, 1)
          If zCurChar < Chr(65) Or _
             zCurChar > Chr(90) Then
            lColLtrToNum = 0
            Exit Function
          Else
            lColLtrToNum = lColLtrToNum + _
               (Asc(zCurChar) - 64) * (26 ^ (iColLen - iCnt))
          End If
       Next iCnt
       
    '*** Check to see if zCol value is greater than this
    '*** version of Excel supports
       If lColLtrToNum > Columns.Count Then lColLtrToNum = 0
        
    End Function   'lColLtrToNum
    Place the code in your Personal.xls file and then you can call it from the Immediate window (as shown above) or from a spreadsheet cell in any workbook. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Maybe I am missing something.
    But why all the work? It can be done as a 1 line function:
    Code:
    Function lColLtrToNum(zCol As String) As Long
      lColLtrToNum = Range(zCol & "1").Column
    End Function
    But I am not sure of the need for the function at all. It is just another something to have to remember the name. In the immediate window you could just use:
    ?[bba1].column

    or in excel:
    =Column(bba1)

    All you need to do is append a number to the column string you are interested in getting.
    Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    RetiredGeek (2013-10-10)

  4. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Steve,

    Thanks, you just blew an hours work.

    My only excuse is I wasn't firing on all cylinders ( or cells ) still on Moroccan time.

    However, I did have fun solving the problem "my way".
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,830
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi RG

    ..or, as I have often included in my replies, you can use this form..
    zCol = [axy1].column
    ..or like this
    Cells(5, [bq1].column)
    etc etc.

    zeddy

  6. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Although of course Cells(5, "bq") is easier than Cells(5, [bq1].column)
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,830
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi Rory

    ..spot on of course.
    I was trying to give an example where column numbers are often used.
    ..for those who like using things like Cells(2, 137) etc.

    zeddy

  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Hey Rory & Zeddy,

    I find it very interesting, being the very old programmer I am, that a function would accept such a wide range of formats for it's arguments. Kudos to the MS guys who programmed this! Flexibility is a very good thing. Thank you both for expanding my horizons once again.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,830
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi RG

    and the opposite function for Steve's post#2

    Code:
    Function colLetter(z)
    colLetter = Split(z.Address, "$")(1)
    End Function
    Use it like..
    =colLetter(anycell)
    where anycell can be the range name of a cell or point to the actual cell.

    zeddy

Posting Permissions

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