By using t2do.com you agree to our cookie policy, We and our partners operate globally and use cookies, for multiple purposes

Convert a column number to a letter - ColumnLetter( )


Function ColumnLetter(c)
' c is the numerical col
a = Split(Cells(1, c).Address, "$")
ColumnLetter = a(1)

End Function


Prerequisites
  • Functions
  • Parameters
  • Comments
  • variables
  • Assignment
  • VBA Split function
  • Cells collection
  • Address property
  • Arrays
  • Returning a function value

    How the function works
    The columnLetter function is passed a number of the column and returns the column letter. For example, A is column 1, Z is column 26, AA is column 27 and so on.

    Cells(1,c) will access the column passed to the function so if 27 is passed the code will become cells(1,27).

    The .address property will convert cells(1,27) row 1 column 27 to an Address, which becomes $AA$1.

    The Split VBA function will split the string $AA$1 into an array named a.

    The array will have a(0) = ""; a(1) = "AA" and a(2) = "1".

    The value in a(1) is returned which is the column converted from a number to a letter.

    This routine will fail under a number of circumstances:
  • Option Base 1 is set
  • Column passed is greater than 16K
  • Hasn't been tested on versions other than Excel 2016 but should work