Lettering in Macros

Our Nosy Dog, Alex

Visual Basic for Applications, embedded in the Microsoft Office Suite, is one of the most unknown power tools available for office automation.  (Check out a primer on Excel VBA here)

One of the most powerful aspects is the ability to loop through contents and perform an action on every row or column in a range.

Some functions in Excel, however, doesn't use the column number as an identifier.  Instead, they use the normal spreadsheet nomenclature of column letter plus row number. So, the upper-left cell in a spreadsheet, row 1,  column 1, is called "A1".

If you are trying to loop through columns using a for loop, this will cause you a bit of a headache.

I found a solution that is elegant!

VBA

Function Letter(num as integer) as string
     letter =split(Cells(1, num ).Address, "$")(1)
End Function 

Dot Net
Function Letter(byref ws as Worksheet, byval Num as Integer) as String
        Dim a() As String = Split(CType(ws.Cells(1, Input), Range).Address, "$")
        Return a(1)
End Function