My app is using Excel spreadsheet.
User has a choice of preselecting some of the sheet's columns.
Now:
Excel is using letters as the visual indexing, of the columns but when
programming the corresponding number (A -->1, B -->2..., AA-->28, AB-->29
etc) must be used..
What will be the best method of converting those letters into numbers?
Not sure about "best", but the functions below should work well past any
thing you may want to handle. The ToNumber function (which is the one that
addresses your question) will convert letter combination up to BRUTMHYHIIZO
(which is converts to 9999999999999999). Likewise, the ToAlpha function
(which is the inverse of the ToNumber function) will accept values up to
9999999999999999 (which is converts to BRUTMHYHIIZO).
Rick
Function ToNumber(Value As String) As Variant
Dim x As Integer
If Format$(Value, "@@@@@@@@@@@@") > "BRUTMHYHIIZO" _
Or Value Like "*[!A-Za-z]*" Then
ToNumber = -1
Else
ToNumber = CDec(0)
For x = Len(Value) To 1 Step -1
ToNumber = ToNumber + _
(Asc(UCase$(Mid$(Value, x, 1))) - 64) * _
26 ^ (Len(Value) - x)
Next
End If
End Function
Function ToAlpha(ByVal Value As Variant) As String
Dim AsciiValue As Variant
If Len(Value) > 16 Or Value Like "*[!0-9]*" Then
ToAlpha = "###"
Else
Value = CDec(Value)
Do While Value > 0
AsciiValue = CDec(64 + Value - 26 * Int(Value / 26))
If AsciiValue = 64 Then AsciiValue = 90
ToAlpha = Chr$(AsciiValue) & ToAlpha
Value = Int(Value / 26)
If AsciiValue = 90 Then Value = Value - 1
Loop
End If
End Function
