====== [VBA][Excel] xlsColNumber() ======
xlsColLetter() wandelt ein Excelspaltencode in die Spaltennummer.
\\ Also die Umkehrfunktion zu [[vba:excel:xlscolletter]]
{{:vba:excel:udf_xlscolletter.bas|download udf_xlscolletter.bas}}
===== Definition =====
spaltennummer = xlsColNumber(spaltencode)
Public Function xlsColNumber(ByVal iColumnLetter As String) As Long
===== Beispiel =====
?xlsColNumber("FA")
157
===== Code =====
'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 =====
'-------------------------------------------------------------------------------
'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