vba:excel:xlscolnumber
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
vba:excel:xlscolnumber [08.07.2014 15:48:49] yaslaw [[VBA][Excel] xlsColLetter()] |
vba:excel:xlscolnumber [08.07.2014 15:51:54] (current) yaslaw [[VBA][Excel] xlsColNumber()] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| ====== [VBA][Excel] xlsColNumber() ====== | ====== [VBA][Excel] xlsColNumber() ====== | ||
| - | xlsColLetter() wandelt ein Excelspaltencode in die Spaltennummer. Also die Umkehrfunktion zu [[vba:excel:xlscolletter]] | + | 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>'------------------------------------------------------------------------------- | ||
vba/excel/xlscolnumber.1404827329.txt.gz · Last modified: 08.07.2014 15:48:49 by yaslaw
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: GNU Free Documentation License 1.3
