Convert XL Column Number 2

[VulcanJedi]

I'm trying to discern the appropriate function for Converting the XL ColumnNumber into the Alpha counterpart.
I've written a few approaches and of course googled but seem to encounter issues when the column is at the inflection points (when the column ends in a Z or A)
i've tried multiple examples but usually end up w/ say column AZ being incorectly returned at A@.

Tranpkp

 

[SkipVought]

Hi,

May I ask the reason? What are you trying to accomplish?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[rmikesmith]

tranpkp,

Try the following function:

Function ColumnNumToAlpha(ByVal ColNum As Integer) As String
Dim CellAddress As String
Dim Pos1 As Integer
Dim Pos2 As Integer

   CellAddress = Cells(1, ColNum).Address
   Pos1 = InStr(1, CellAddress, "$", vbTextCompare)
   Pos2 = InStr(Pos1 + 1, CellAddress, "$", vbTextCompare)
   ColumnNumToAlpha = Mid$(CellAddress, Pos1 + 1, Pos2 - Pos1 - 1)
   
End Function

Keep in mind there is no code to check that the column number is valid so it's up to you to add that or ensure the calling procedure doesn't pass an invalid value.


Regards,
Mike

 

[PHV]

A one-line way:

ColumnNumToAlpha = Split(Columns(ColNum).Address(False, False), ":")(0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VulcanJedi]

Wow that is brilliant PHV, took me a second to digest all the stuff going on in that one liner.
Should have mentioned I was doing this in VBS and not VBA and was hoping to do in a function w/o XL necessarily instantiated yet if possible...but that is terrific and works 100%

Tranpkp

 

[rmikesmith]

PHV,

Nice one. I'll tuck that away for future reference.


tranpkp,

Here is a function that doesn't use Excel's object model but does require integer division (\), modulus (mod) and the Chr() funciton:

Function ColumnNumToAlpha(ByVal ColNum As Integer) As String
Dim iDiv As Integer
Dim iMod As Integer

   If ColNum > 256 Then Exit Function
   
   iDiv = ColNum \ 26
   iMod = ColNum Mod 26
   
   If iMod > 0 Then
     ColumnNumToAlpha = Chr(iDiv + 64) & Chr(iMod + 64)
   Else
     ColumnNumToAlpha = Chr(iDiv - 1 + 64) & "Z"
   End If
   
End Function

There is an explicit boundary check on the input parameter, since otherwise the function will return invalid results or generate an error.


Regards,
Mike

 

[VulcanJedi]

Mike, fails on 26
PHV, you'll most definately get a star but I still hoping someone helps the more the mathematical response w/ need of XL if possible (I know it sounds dumb since I'll inevitably need to use this return value in XL)

Tranpkp

 

[SkipVought]

"since I'll inevitably need to use this return value in XL"

WHY? You can always reference a column by either its number or alpha value.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[xlbo]

...is exactly what I was going to say. Why bother jumping through hoops when you don;t need to...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hence, my original question to you...

"May I ask the reason? What are you trying to accomplish?"

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[rmikesmith]

tranpkp,

Skip & Geoff raise a valid point. However, I present the following corrected function if for no other reason than I felt compelled to make it work:

Function ColumnNumToAlpha(ByVal ColNum As Integer) As String
Dim sTmp As String
Dim iDiv As Integer
Dim iMod As Integer


   If ColNum < 1 Or ColNum > 256 Then Exit Function
   
   iDiv = ColNum \ 26
   iMod = ColNum Mod 26
   sTmp = ""
   
   If iMod = 0 Then
     iDiv = iDiv - 1
     If iDiv > 0 Then sTmp = Chr(iDiv + 64)
     ColumnNumToAlpha = sTmp & "Z"
   Else
     If iDiv > 0 Then sTmp = Chr(iDiv + 64)
     ColumnNumToAlpha = sTmp & Chr(iMod + 64)
   End If
   
End Function

Regards,
Mike

 

[xlbo]

Mike - thats about as good a reason as you can get....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[VulcanJedi]

I guess more of the same I just wanted to know too.
That appears to work thx Mike/PHV.

Tranpkp