Compute Excel Column Number from Integer Column NumberExcel VBA Programming |   | How can I compute any Excel column name from "A" to "ZZ" from an integer column number? This article will show you how.
I used to do a lot of VBA programming in Microsoft Word. I haven't done that in about four years, but occasionly still do VBA in Excel. In an application that I am working on, I save the contents of a spread sheet in XML in a database transaction table. In some cases, I am called upon to replicate the original spreadsheeet from the database and in addition add a variable number of columns to the output spreadsheet. The code that I wrote several years ago worked fine until the user added enough columns to exceed 26. Computing a column name from A to Z can be computed by the following line of code.
Where i is a parameter passed into the short function and is zero based. However, when "i" exceeds 25, then the function no longer works. Obviously, you could do one or more tests for the value of "i" and compute names from A to Z, or AA to AZ, or BA to BZ, etc. That would take 26 such tests and would really be kinda kludgy. The following code will work for 26 * 26 columns and returns values from A to ZZ.
''' <summary>
''' Return string Excel column name for passed integer
''' column number.
'''
'''
''' <returns>string
''' <remarks>
Private Function GetNextCol(ByVal i As Integer) As String
Const A As String = "A"
Dim firstInt As Integer = (i - 1) \ 26
Dim rounded As Integer = firstInt * 26
Dim secondAlpha As String = Chr((i - rounded - 1) + Asc(A))
Dim firstAlpha As String = String.Empty
If firstInt > 0 Then
firstAlpha = Chr(firstInt - 1 + Asc(A))
Else
firstAlpha = secondAlpha
secondAlpha = String.Empty
End If
Return (firstAlpha & secondAlpha).ToUpper
End Function
|
Maybe this will save you some panic moments when your code no longer works simply because the user added columns to spreadsheets. Thanks to my friend and co-worker David Perry for the algorithm. I just coded it.
Have you tried our newest product, Visual Class Organizer? You'll be amazed how easy it is to keep the code in your code windows organized. TRY IT FREE FOR 30 DAYS BY CLICKING HERE.
If you are developing in C# and haven't tried CSharpCompleter, you are wasting valuable time typing hundreds of braces {} daily needlessly. Try CSharpCompleter for 30 DAYS FREE.
| Ask a Question, or give your feedback on my articles or products by going to the KnowDotNet Forum or by clicking on My Blog. |  |
|
