vba:excel:xlscolnumber
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision | Last revision Both sides next revision | ||
|
vba:excel:xlscolnumber [08.07.2014 15:48:49] yaslaw [[VBA][Excel] xlsColLetter()] |
vba:excel:xlscolnumber [08.07.2014 15:51:23] yaslaw [Beispiel] |
||
|---|---|---|---|
| Line 3: | Line 3: | ||
| {{: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.txt · Last modified: 08.07.2014 15:51:54 by yaslaw
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: GNU Free Documentation License 1.3
