Yaslaw.Info

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== [VBA][Excel] xlsColLetter() ======

Wandelt eine Spaltennummer in eine Excel-Buchstabenspaltencode um.
\\ Also die Umkehrfunktion zu [[vba:excel:xlscolnumber]]

{{:vba:excel:udf_xlscolletter.bas|download udf_xlscolletter.bas}}

===== Definition =====
<code>spaltencode = clsColLetter(spaltennummer)</code>
<code vb>Public Function xlsColLetter(ByVal iColumnNumber As Long) As String</code>

===== Beispiele =====
<code vb>?xlsColLetter(157)
FA</code>

===== Code =====
<code vb 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</code>
Yaslaw.Info

User Tools

Site Tools

Page Tools
