VBA将Excel样式列名称（带有52个字符集）转换为原始编号(VBA convert Excel Style Column Name (with 52 charset) to original nu

VBA将Excel样式列名称（带有52个字符集）转换为原始编号(VBA convert Excel Style Column Name (with 52 charset) to original number)

我有一个c ++程序，它取一个整数并将其转换为低位和大写字母，类似于excel将列索引转换为列号但也包括小写字母。

#include <string> #include <iostream> #include <climits> using namespace std; string ConvertNum(unsigned long v) { char const digits[] = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"; size_t const base = sizeof(digits) - 1; char result[sizeof(unsigned long)*CHAR_BIT + 1]; char* current = result + sizeof(result); *--current = '\0'; while (v != 0) { v--; *--current = digits[v % base]; v /= base; } return current; } // for testing int main() { cout<< ConvertNum(705); return 0; }

我需要vba函数将其反转回原始数字。我没有很多使用C ++的经验，所以我无法弄清楚在vba中反转这个的逻辑。谁能请帮忙。

更新1：我不需要已经编写的代码，只需要一些逻辑上的帮助来反转它。我会尝试将逻辑转换为代码。

更新2：基于答案中提供的精彩解释和帮助，很明显代码没有将数字转换为通常的基数52，这是误导性的。所以我更改了功能名称，以消除未来读者的困惑。

I have a c++ program that takes an integer and convert it to lower and uppercase alphabets, similar to what excel does to convert column index to column number but also including lower case letters.

I need the vba function to reverse this back to the original number. I do not have a lot of experience with C++ so I can not figure out a logic to reverse this in vba. Can anyone please help.

Update 1: I don't need already written code, just some help in the logic to reverse it. I'll try to convert the logic into code myself.

Update 2: Base on the wonderful explanation and help provided in the answer, it's clear that the code is not converting the number to a usual base52, it is misleading. So I have changed the function name to eliminate the confusion for future readers.

最满意答案

编辑：由下面描述的代码转换为十进制的字符串格式不是标准的base-52架构。架构不包括0或任何其他数字。因此，不应使用此代码将标准base-52值转换为decimal。

好的，这是基于根据长字符串中的位置转换单个字符 。字符串是：

chSET = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"

InStr（）函数告诉我们A位于位置1 ， Z位于位置26 ， a位于位置27 。所有字符都以相同的方式转换。

我使用它而不是Asc（），因为Asc（）在大写和小写字母之间有一个间隙。

最低有效字符的值乘以52 ^ 0 下一个字符的值乘以52 ^ 1 第三个字符的值乘以52 ^ 3等。代码：

Public Function deccimal(s As String) As Long Dim chSET As String, arr(1 To 52) As String Dim L As Long, i As Long, K As Long, CH As String chSET = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz" deccimal = 0 L = Len(s) K = 0 For i = L To 1 Step -1 CH = Mid(s, i, 1) deccimal = deccimal + InStr(1, chSET, CH) * (52 ^ K) K = K + 1 Next i End Function

一些例子：

在此处输入图像描述

注意：

这不是通常编码碱基的方式。通常，base以0开头，并在任何编码值的位置允许0 。在我之前的所有UDF（）中 ，类似于这个， chSET的第一个字符是0 ，我必须使用(InStr(1, chSET, CH) - 1) * (52 ^ K)

EDIT: The character string format being translated to decimal by the code described below is NOT a standard base-52 schema. The schema does not include 0 or any other digits. Therefore this code should not be used, as is, to translate a standard base-52 value to decimal.

O.K. this is based on converting a single character based on its position in a long string. the string is:

chSET = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"

The InStr() function tells us the A is in position 1 and the Z is in position 26 and that a is in position 27. All characters get converted the same way.

I use this rather than Asc() because Asc() has a gap between the upper and lower case letters.

The least significant character's value gets multiplied by 52^0The next character's value gets multiplied by 52^1The third character's value gets multiplied by 52^3, etc. The code:

Some examples:

enter image description here

NOTE:

This is NOT the way bases are usually encoded. Usually bases start with a 0 and allow 0 in any of the encoded value's positions. In all my previous UDF()'s similar to this one, the first character in chSET is a 0 and I have to use (InStr(1, chSET, CH) - 1) * (52 ^ K)

更多推荐