Column Letters instead of Numbers

The Problem

Sometimes the most intuitive or simplest way to refer to a range is to use column letters instead of column numbers.   The need is driven home when trying to refer to ranges of Columns.   Unfortunately, VBA doesn't offer the option to omit the <row> entry when specifying ranges, although it allows ignoring the <column> entry.  

• rng(rows(5), rows(7)).Select ' Works fine
• rng(Columns(3), Columns(5)).Select ' Fails

The Solution

After a number of tries to get the column letter(s) using the column number, mainly using an algorithm that turned Base 10 into Base 26, using all letters instead of 1-9.  IOW, 1=A, 2=B ... 26 = Z, 27 = AA etc. Then I happened upon this method: 

Function ColLtrFromNum(ColNum As String, _
                       Optional wkbk As Workbook, _
                       Optional wksht As Worksheet) As String
    On Error GoTo ColLtrFromNum_Error
    If wksht Is Nothing Then
        If wkbk Is Nothing Then
            Set wksht = ThisWorkbook.Worksheets(1)
        Else
            Set wksht = wkbk.Worksheets(1)
        End If
    End If
    If ColNum >= 1 And ColNum <= wksht.Columns.Count Then
        ColLtrFromNum = wksht.Cells(1, CInt(ColNum)).Address(1, 0)
        ColLtrFromNum = Left(ColLtrFromNum, InStr(ColLtrFromNum, "$") - 1)
    Else
ColLtrFromNum_Error:
        ColLtrFromNum = "#ERROR"
    End If
    On Error GoTo 0
End Function
'****************************
' Test Sub
Sub textCLFM()
    Dim v, w, x, y, z, a, b
    v = ColLtrFromNum("JHOI")
    w = ColLtrFromNum("15,767")
    x = ColLtrFromNum(0)
    y = ColLtrFromNum(57.5999)
    z = ColLtrFromNum(" 57 ")
    a = ColLtrFromNum _
           (ThisWorkbook.Worksheets(1).Columns.Count)
    b = ColLtrFromNum("")
End Sub

Explanatory Notes

The ColNum  parameter is declared as a String instead of a numeric data type to allow returning an error of "#VALUE" instead of having the function fail to complete.  This also allows for external error checking and use as a worksheet function.

The purpose of the Optional Workbook and Worksheet parameters, coupled with the opening IF structure, is to facilitate use with Excel 2007/2010, workbooks from earlier releases, and those pesky worksheets that have been copied/moved from earlier releases into Excel 2007/2010 workbooks.

The CInt function prevents errors from numeric strings.  Some may choose CDbl  as a safety in case MicroSoft moves to a worksheet more than twice as wide as present, something I don't think will happen soon.

Uses

Four uses come to mind, mainly because A1 notation is easier for people, i.e.  Range( ColLtrFromNum(<col>) & <row>) versus Range(Cells(<row1>,<col1>)) 

• Coding a Range description
• When using the Worksheet Indirect function, e.g. the following returns what is five columns to the right.
  =Indirect(ColLtrFromNum(Column()+5 & Row())
• In a Watch so that you can see the address and the value, e.g. ColLtrFromNum(colnumSource) &  rownumSource ": " &  Cells(rownumSource,colnumSource).Value
• When reporting the location of a value, e.g. in a message box for trouble shooting where column letters are not part of the design
  MsgBox "Error cause by value at cell " ws.Name & "!" & ColLtrFromNum(colnumSource) &  rownumSource &  ": " &  ws.Cells(rownumSource,colnumSource).Value