This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
vba:excel:xlscolnumber [08.07.2014 15:45:31] yaslaw created |
vba:excel:xlscolnumber [08.07.2014 15:51:54] (current) yaslaw [[VBA][Excel] xlsColNumber()] |
||
---|---|---|---|
Line 1: | Line 1: | ||
- | ====== [VBA][Excel] xlsColLetter() ====== | + | ====== [VBA][Excel] xlsColNumber() ====== |
- | xlsColLetter() wandelt ein Excelspaltencode in die Spaltennummer | + | xlsColLetter() wandelt ein Excelspaltencode in die Spaltennummer. |
+ | \\ Also die Umkehrfunktion zu [[vba:excel:xlscolletter]] | ||
{{:vba:excel:udf_xlscolletter.bas|download udf_xlscolletter.bas}} | {{:vba:excel:udf_xlscolletter.bas|download udf_xlscolletter.bas}} | ||
+ | |||
+ | ===== Definition ===== | ||
+ | <code>spaltennummer = xlsColNumber(spaltencode)</code> | ||
+ | <code vb>Public Function xlsColNumber(ByVal iColumnLetter As String) As Long</code> | ||
===== Beispiel ===== | ===== Beispiel ===== | ||
- | <code vb>?xlsColLetter(157) | + | <code vb>?xlsColNumber("FA") |
- | FA</code> | + | 157</code> |
+ | |||
+ | ===== Code ===== | ||
+ | <code vb 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> | ||
===== Code ===== | ===== Code ===== | ||
<code vb udf_xlscolletter.bas>'------------------------------------------------------------------------------- | <code vb udf_xlscolletter.bas>'------------------------------------------------------------------------------- |