利用空闲时间,开发了一个十进制转换为字母(相当于26进制)的函数。功能有:
1. 给定Excel列标,返回对应的列名(如,第677列对应的列名为“ZA”)(但是,Excel中的列最多为16384列,对应的列名为XFD)
2. 给定任意正长整形数值(即,1到2147483647),可返回对应的字母
3. 可通过VBE immediate窗口查看计算过程(debug print)
代码:
Function GetExcelColumnNameF(columnNumber As Integer)
Dim dividend As Integer
Dim columnName As String
Dim modulo As Integer
Dim n As Integer '循环次数
dividend = columnNumber
Debug.Print "Input: ColumnNumber = " & columnNumber
Debug.Print "Calculation process:" & vbNewLine
While (dividend > 0)
n = n + 1
modulo = (dividend - 1) Mod 26
Debug.Print "Iteration No." & n
Debug.Print "dividend = " & dividend & ", modulo = (dividend - 1) Mod 26 = " & modulo
columnName = Chr(65 + modulo) + columnName
dividend = Round((dividend - modulo) / 26, 0)
Debug.Print "columnName = " & columnName & ", dividend = Round((dividend - modulo) / 26, 0) = " & dividend & vbNewLine
Wend
GetExcelColumnNameF = columnName
Debug.Print "Result: ColumnNumber = " & columnNumber & " <--> ColumnName = " & columnName & vbNewLine & vbNewLine
End Function
调试输出(Log):
Input: ColumnNumber = 16382
Calculation process:
Iteration No.1
dividend = 16382, modulo = (dividend - 1) Mod 26 = 1
columnName = B, dividend = Round((dividend - modulo) / 26, 0) = 630
Iteration No.2
dividend = 630, modulo = (dividend - 1) Mod 26 = 5
columnName = FB, dividend = Round((dividend - modulo) / 26, 0) = 24
Iteration No.3
dividend = 24, modulo = (dividend - 1) Mod 26 = 23
columnName = XFB, dividend = Round((dividend - modulo) / 26, 0) = 0
Result: ColumnNumber = 16382 <--> ColumnName = XFB
效果图:
参考文章:
https://stackoverflow.com/questions/181596/how-to-convert-a-column-number-eg-127-into-an-excel-column-eg-aa