User Tools

Site Tools


vba:excel:xlscolletter

[VBA][Excel] xlsColLetter()

Wandelt eine Spaltennummer in eine Excel-Buchstabenspaltencode um.
Also die Umkehrfunktion zu [VBA][Excel] xlsColNumber()

download udf_xlscolletter.bas

Definition

spaltencode = clsColLetter(spaltennummer)
Public Function xlsColLetter(ByVal iColumnNumber As Long) As String

Beispiele

?xlsColLetter(157)
FA

Code

udf_xlsColLetter.bas
'-------------------------------------------------------------------------------
'File         : udf_xlsColLetter.bas
'               Copyright mpl by ERB software
'               All rights reserved
'               wiki.yaslaw.info/dokuwiki/doku.php/vba/functions/xlsColLetter
'Environment  : VBA 2010 +
'Version      : 1.0.1
'Name         : xlsColLetter
'Author       : Stefan Erb (ERS)
'History      : 16.06.2014 - ERS - Creation
'               23.06.2014 - ERS - Fehler rund um Z behoben
'-------------------------------------------------------------------------------
Option Explicit
 
'/**
' * Gibt den Buchstaben-Key für eine ExcelSpalte anhand einer Spaltennummer aus (Beginnend mit 1)
' *
' * spaltencode = clsColLetter(spaltennummer)
' *
' * @param  Long    Index der Spalte
' * @return String  Spaltenkey
' */
Public Function xlsColLetter(ByVal iColumnNumber As Long) As String
    Const C_ASCII_DELTA = 64
    Dim nr As Long: nr = iColumnNumber
    Do
        Dim rest As Integer: rest = nr Mod 26
        If rest = 0 Then rest = 26
        xlsColLetter = Chr(rest + C_ASCII_DELTA) & xlsColLetter
        nr = Fix((nr - 1) / 26)
    Loop While nr > 0
End Function

Discussion

Enter your comment. Wiki syntax is allowed:
If you can't read the letters on the image, download this .wav file to get them read to you.
 
vba/excel/xlscolletter.txt · Last modified: 08.07.2014 15:52:15 by yaslaw