Optimal means of obtaining cell address column letter from column index and column index from column letter

Question

Typically the accepted approach is to do the following

Number to Letter

public function numberToLetter(ByVal i as long) as string
  Dim s as string: s = cells(1,i).address(false,false)
  numberToLetter = left(s,len(s)-1)
end function

Letter to Number

Public Function letterToNumber(ByVal s As String) As Long
  letterToNumber = Range(s & 1).Column
End Function

However neither of these are particular optimal, as in each case we are creating an object, and then calling a property accessor on the object. Is there a faster approach?

Answer 1

Summary

The core thing to realise is that the lettering system used in Excel is also known as Base26. NumberToLetter is encoding to Base26 from decimal, and LetterToNumber is decoding from Base26 to decimal.

Base conversion can be done with simple loops and

Function base26Encode(ByVal iDecimal As Long) As String
  if iDecimal <= 0 then Call Err.Raise(5, "base26Encode" ,"Argument cannot be less than 0")
  if iDecimal >= 16384 then Call Err.Raise(5, "base26Encode" ,"There are only 16384 columns in a spreadsheet, thus this function is limited to this number.")
  Dim s As String: s = ""
  Do
    Dim v As Long
    v = (iDecimal - 1) Mod 26 + 1
    iDecimal = (iDecimal - v) / 26
    s = Chr(v + 64) & s
  Loop Until iDecimal = 0
  base26Encode = s
End Function

Function base26Decode(ByVal sBase26 As String) As Long
  sBase26 = UCase(sBase26)
  Dim sum As Long: sum = 0
  Dim iRefLen As Long: iRefLen = Len(sBase26)
  For i = iRefLen To 1 Step -1
    sum = sum + (Asc((Mid(sBase26, i))) - 64) * 26 ^ (iRefLen - i)
  Next
  base26Decode = sum
End Function

Performance

I tested the performance of these functions against the original functions. To do this I used the stdPerformance class of stdVBA.

The code used for testing is as follows:

Sub testPerf()
  Dim cMax As Long: cMax = 16384
  With stdPerformance.Measure("Encode Original")
    For i = 1 To cMax
      Call numberToLetter(i)
    Next
  End With
  With stdPerformance.Measure("Encode Optimal")
    For i = 1 To cMax
      Call base26Encode(i)
    Next
  End With
  With stdPerformance.Measure("Decode Original")
    For i = 1 To cMax
      Call letterToNumber(base26Encode(i))
    Next
  End With
  With stdPerformance.Measure("Decode Optimal")
    For i = 1 To cMax
      Call base26Decode(base26Encode(i))
    Next
  End With
End Sub

The results for which are as follows:

Encode Original: 78 ms
Encode Optimal: 31 ms
Decode Original: 172 ms
Decode Optimal: 63 ms

As shown this is a slightly faster approach (2-3x faster). I am fairly surprised that object creation and property access performed so well however.