Convert Column Numbers to Column Letters
Navigating and iterating over values in a spreadsheet is one of the most common things a VBA programmer has to do. And while doing this you will find needs to refer columns by their letter or by their number. However there is no inbuilt way of switching between the two in Excel.
This function takes a column number and converts it to a column letter. So the number 3 will be converted to the letter “C” and so on.
To use it, pass the col_num argument the number you want to convert to a letter.
If you pass a number that cant be converted to letter (a decimal number, a negative number or a number over the maximum number of columns in Excel) it will return an empty string.
Function ColNumToLet(col_num As Integer) As String ' Purpose --> Converts a column number to its equivalent letter ' Arguments --> col_num (Integer) ' - The number to be converted into a column letter(s). ' Returns --> (String) ' - Returns a string containing the letter(s) if found ' - Returns an empty string if there is any kind of error ' Created by --> Carl Nixon, Excel-Expert.co.uk ' Set up variables ' ================ ' Holds the calculated letter(s) Dim column_letter As String ' Default the calculated letter(s) to a blank string column_letter = "" ' Check safe to run ' ================= ' If number is outside the permitted number of columns then exit If col_num < 0 Or col_num > 16384 Then GoTo Finished ' If column number is not a whole number then exit If Int(col_num) <> col_num Then GoTo Finished ' Convert number to letter(s) ' =========================== ' Build a range using the column number then split it ' back out so the column letter can be extracted column_letter = Split(Cells(1, col_num).Address, "$")(1) ' Finish up ' ========= Finished: ' Return the calculated column letter ColNumToLet = column_letter End Function
All of the code featured on this site is available to download on my GitGub page. Click here to visit it.