User Tools

Site Tools


vba:excel:xlscolnumber

[VBA][Excel] xlsColNumber()

xlsColLetter() wandelt ein Excelspaltencode in die Spaltennummer.
Also die Umkehrfunktion zu [VBA][Excel] xlsColLetter()

download udf_xlscolletter.bas

Definition

spaltennummer = xlsColNumber(spaltencode)
Public Function xlsColNumber(ByVal iColumnLetter As String) As Long

Beispiel

?xlsColNumber("FA")
 157

Code

xlscolnumber.bas
'File         : udf_xlsColNumber.bas
'               Copyright mpl by ERB software
'               All rights reserved
'               wiki.yaslaw.info/dokuwiki/doku.php/vba/
'Environment  : VBA 2010 +
'Version      : 1.0.0
'Name         : xlsColNumber
'Author       : Stefan Erb (ERS)
'History      : 23.06.2014 - ERS - Creation
'-------------------------------------------------------------------------------
Option Explicit
 
'/**
' * Umkehrfunction zu xlsColLetter: Berchent aus einem String-Colmnidex die Position
' *
' * spaltennummer = xlsColNumber(spaltencode)
' *
' * @param  String
' * @retrun Long
'*/
Public Function xlsColNumber(ByVal iColumnLetter As String) As Long
    Const C_ASCII_DELTA = 64
    Dim str As String: str = StrReverse(UCase(iColumnLetter))
    Dim idx As Integer: For idx = 0 To Len(iColumnLetter) - 1
        xlsColNumber = xlsColNumber + 26 ^ idx * (Asc(Mid(str, idx + 1, 1)) - C_ASCII_DELTA)
    Next idx
End Function

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/xlscolnumber.txt · Last modified: 08.07.2014 15:51:54 by yaslaw