Jump to content
Sign in to follow this  
supersonic

_ExcelColumnLetter UDF

Recommended Posts

Hi!

I was looking for an UDF to convert MS Excel column numbers above 256 (to 16384) into column letters.

I found an older UDF written by Jos here in the forum but this one was limited to max. 256 columns.

So, I googled a bit and finally found a VB script (http://www.freevbcode.com/ShowCode.asp?ID=9264)

which I converted into AU3. May be it's useful for others too.

; ----------------------------------------------------------------------------------------------------
; Function Name:    _ExcelColumnLetter()
; Description:      Converts Microsoft Excel column number (1 - 16384) into column letter(s)
;                   [http://www.freevbcode.com/ShowCode.asp?ID=9264].
; Syntax:           _ExcelColumnLetter([$iColumn = 0])
; Parameter(s):     $iColumn - The column number to convert into column letter(s).
; Requirement(s):   None.
; Return Value(s):  Success - "A" to "XFD", @error = 0, @extended = 1, 2, 3.
;                   Failure - "", @error = 1, @extended = 0.
; ----------------------------------------------------------------------------------------------------
Func _ExcelColumnLetter($iColumn = 0)
    Switch $iColumn
        Case 1 To 26 ; Single letter columns (1 = "A" - 26 = "Z").
            Local $letter1 = Chr($iColumn + 64)
            Return SetError(0, 1, $letter1)
        Case 27 To 702 ; Double letter columns (27 = "AA" - 702 = "ZZ").
            Local $letter1 = Chr(Int(($iColumn - 1) / 26) + 64)
            Local $letter2 = Chr(Mod(($iColumn - 1), 26) + 65)
            Return SetError(0, 2, $letter1 & $letter2)
        Case 703 To 16384 ; Triple letter columns (703 = "AAA" - 16384 = "XFD" [18278 = "ZZZ"]).
            Local $letter1 = Chr(Mod(Int(($iColumn - 1 - 26 - 676) / 676), 676) + 65)
            Local $letter2 = Chr(Mod(Int(($iColumn - 1 - 26) / 26), 26) + 65)
            Local $letter3 = Chr(Mod(($iColumn - 1), 26) + 65)
            Return SetError(0, 3, $letter1 & $letter2 & $letter3)
    EndSwitch
    Return SetError(1, 0, "")
EndFunc

Please report bugs. :mellow:

Greets,

-supersonic.

Edited by supersonic

Share this post


Link to post
Share on other sites

Am bored and thought I'd mess around with the code. I love 'formulaic' challenges lol. This seems to work also:

Func _ExcelColumnLetter($iColumn = 0)
    Local $letters
    While $iColumn
        $x = Mod($iColumn, 26)
        If $x = 0 Then $x = 26
        $letters = Chr($x + 64) & $letters
        $iColumn = ($iColumn - $x) / 26
    WEnd
    Return $letters
EndFunc

It converts any size number.

Edited by Spiff59

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...