Friday, September 9, 2011

VBA function- Get column letter from column number, Worksheet Exists

One often needs a Excel Column's alphabetic letter from the column number
e.g Column No.3 corresponds to ColumnLetter - C
     Column No.27 corresponds to ColumnLetter - AA

I use the following function to achieve this

Public Function ColumnLetter(ColumnNumber As Integer) As String
  If ColumnNumber > 26 Then
    ' 1st character:  Subtract 1 to map the characters to 0-25,
    '                 but you don't have to remap back to 1-26
    '                 after the 'Int' operation since columns
    '                 1-26 have no prefix letter
    ' 2nd character:  Subtract 1 to map the characters to 0-25,
    '                 but then must remap back to 1-26 after
    '                 the 'Mod' operation by adding 1 back in
    '                 (included in the '65')
    ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
                   Chr(((ColumnNumber - 1) Mod 26) + 65)
  Else
    ' Columns A-Z
    ColumnLetter = Chr(ColumnNumber + 64)
  End If
End Function


The following function will enable you to check if a worksheet already exists or not in a workbook

Public Function WorksheetExists(ByVal WorksheetName As String) As Boolean
On Error Resume Next
WorksheetExists = (xlwb.Sheets(WorksheetName).Name <> "")
On Error GoTo 0
End Function

No comments:

Post a Comment