Since I haven't written on here for a while, I thought I'd put up a quick post with some code to convert an Excel column letter reference to a number or the reverse: convert column letters to a number. This is a good candidate for your Excel utility module.

This function uses recursion, but there isn't any risk of stack overflow because there will at most `log`

recursions, where _{26}(n)`n`

is the column number. It also uses the relatively unknown VBA `\`

operator, which does integer division (rounding down). Input validation is left as an exercise. I don't do any because I only ever call this function with constant values, which are pretty hard to get wrong.

' converts column letter to number or vice versa Public Function Col(c As Variant) As Variant Dim x As Variant If VarType(c) = vbLong Or VarType(c) = vbInteger Then x = Chr(Asc("A") + (c - 1) Mod 26) If c > 26 Then Col = Col((c - 1) \ 26) & x Else Col = x End If ElseIf VarType(c) = vbString Then x = Asc(Right(c, 1)) - Asc("A") + 1 If Len(c) > 1 Then Col = x + Col(Left(c, Len(c) - 1)) * 26 Else Col = x End If Else Col = Null End If End Function

Excel versions 2007 and later support 16,384 columns, so a good test is with column 16,384, which you can verify is XFD by opening a new Workbook and pressing `Ctrl+Right`.

' Executed in the immediate window ?Col(16384) XFD ?Col("XFD") 16384

## No comments:

## Post a Comment