Using VBA to Convert a Column Number To A Letter

Sometimes while manipulating data in Excel you’ll have a integer which represents the column number, but need the column letter.

The function below can translate that number to the column letter required.  This function has been tested and works to 702, column ‘ZZ’.

Function ConvertToLetter(iCol As Integer) As String
'-----------------------------------------------------------------------------
' Procedure : ConvertToLetter
' Author    : Niall - Zypher.co.uk
' Purpose   : Convert a number to a column letter
'             Tested to 256 columns, assumed to work until 702 (ZZ)
'-----------------------------------------------------------------------------
'
    If iCol <= 26 Then
        ' Columns A-Z
        ConvertToLetter = Chr(iCol + 64)
    Else
        ConvertToLetter = Chr(Int((iCol - 1) / 26) + 64) & _
                          Chr(((iCol - 1) Mod 26) + 65)
    End If
End Function

As always, any questions or enquiries let us know via the comments section.

Add worksheets to Excel using VBA

Adding worksheets to Excel is quite simple. For example, to add a Worksheet before the active sheet (default unless stated otherwise), name it "MyWorksheet" and have it become the active sheet, you would use code similar to below;

Sub Add_New_Worksheet()
    ' Add a new worksheet in front of the active sheet
    Worksheets.Add().Name = "MyWorksheet"
End Sub

If we want to add a new Worksheet as the last Worksheet in the active workbook and name it "MyWorksheet" we would use;

Sub Add_As_Last_Worksheet()
    ' A a named worksheet to the end
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "MyWorksheet"
End Sub

The Add Method (with regards to the Worksheet Object) has an After Variant as well as an Before Variant. You can only use one of the options, either the Before or After Variant, or omit the Argument altogether. If we do omit the Before and After Variants Excel places the Worksheet before the current active Sheet.

the example below shows how to add more then one Worksheet, the code below adds 2 new worksheets;

Sub Add_n_Worksheets()
    ' Insert 2 worksheets after the last current worksheet
    Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=2
End Sub

The last Variant available is the Type Variant. The Type specifies the sheet type. The choices available are listed below;
XlSheetType constants:

• xlWorksheet
• xlChart
• xlExcel4MacroSheet
• xlExcel4IntlMacroSheet

If you are inserting a sheet based on an existing template, specify the path to the template (Recording a macro is best for this). The default value is xlWorksheet.

Create a Microsoft Word Document with VBA

The below code is a basic example on how to create a Microsoft Word document.  This is sometimes useful if want to create a report showing your data outside of Excel.

Private Sub CreateWordDoc()
'-----------------------------------------------------------------------------
' Procedure : CreateWordDoc
' Author    : Matthew - Zypher.co.uk
' Date      : 12/04/2007
' Purpose   : Create a MS Word document using earling binding
'             Requires a reference to 'Microsoft Word ??.? Object Library'
'-----------------------------------------------------------------------------
'
    Dim objWord As Word.Application
    Dim doc As Word.Document

    'Create Word doc object
    Set objWord = CreateObject("Word.Application")

    With objWord
        ' Ensure the MS Word object is visible
        .Visible = True

        ' Add a new word document and save the file prior to adding text
        Set doc = .Documents.Add
        doc.SaveAs "C:\Your\File\Directory\Filename.doc(x)"

        ' Or open an existing document
        'Set doc = wrdApp.Documents.Open("C:\Foldername\Filename.doc")
    End With

    'Construct document
    With objWord.Selection
        ' Set the font type
        .Font.Name = "Trebuchet MS"
        ' Set the font size
        .Font.Size = 16

        ' Set the format, depending on the value of i
        For i = 1 To 50
            Select Case i
                Case Is < 10
                    ' Set the font size
                    .Font.Size = 12
                    ' Set font to bold
                    .Font.Bold = True
                    ' Align the text to the right of the page
                    .ParagraphFormat.Alignment = wdAlignParagraphRight

                Case Is < 20
                    ' Set the font size
                    .Font.Size = 8
                    ' Turn off bold
                    .Font.Bold = False
                    ' Align the text to the right of the page
                    .ParagraphFormat.Alignment = wdAlignParagraphLeft

                Case Is < 30
                    ' Set the font size
                    .Font.Size = 10
                    ' Turn on bold
                    .Font.Bold = True
                    ' Align the text to the right of the page
                    .ParagraphFormat.Alignment = wdAlignParagraphRight

                Case Is < 40
                    ' Set the font size
                    .Font.Size = 6
                    ' Turn off bold
                    .Font.Bold = False
                    ' Align the text to the center
                    .ParagraphFormat.Alignment = wdAlignParagraphCenter

                Case Else
                    ' do nothing
            End Select

            ' Add text
            .TypeText "Here is an example test line, #" & i _
                & " - Font size is " & .Font.Size
            ' Move to the next line
            .TypeParagraph

        Next i

    End With

    ' Save the file
    doc.Save
    ' Bring the MS Word window to the front
    doc.Activate

End Sub

As always, if you have any questions do let us know.