学以致用——VBA实现十进制数字转换为字母（二十六进制）（Convert a number to letters (Excel Column number to name) with VBA）

Excel列标与列名转换问题，本质上是一个十进制数和二十六进制数的转换问题。记得以前学C、C#、JAVA等编程语言时，已经做过一些相关练习了。但是，老实说，在用公式法解决这个十进制转二十六进制的问题时还真是难到我了，花了好几个小时也没有解决。

于是，求助网络，在一篇文章的启发下，先实现了VBA编程方式来解决问题。

在此总结一下经验：

1. 编程方式的思路更为清晰一些（很容易识别程序逻辑，如核心算法）

2. 调试起来方便很多。Excel自带函数法严格来讲不算调试，只是不断的试错而已。而VBE中可通过DEBUG.PRINT语句输出中间变量及计算过程，对理清思路非常有用

3. 多看看别人的代码可以少走很多弯路

代码分享：

Sub GetExcelColumnName(ByVal ExcelColumnName As String, ByVal columnNumber As Long)

    Dim dividend As Long
    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
    ExcelColumnName = columnName
    Debug.Print "Result: ColumnNumber = " & columnNumber & " <--> ColumnName = " & columnName & vbNewLine & vbNewLine
   
End Sub

测试代码：

Sub GetExcelColumnNameTest()
Dim result As String
    'For i = 676 To 678
        GetExcelColumnName result, 2147483647
       
    'Next
End Sub

测试结果：

Input: ColumnNumber = 2147483647
Calculation process:

Iteration No.1
dividend = 2147483647, modulo = (dividend - 1) Mod 26 = 22
columnName = W, dividend = Round((dividend - modulo) / 26, 0) = 82595524

Iteration No.2
dividend = 82595524, modulo = (dividend - 1) Mod 26 = 23
columnName = XW, dividend = Round((dividend - modulo) / 26, 0) = 3176750

Iteration No.3
dividend = 3176750, modulo = (dividend - 1) Mod 26 = 17
columnName = RXW, dividend = Round((dividend - modulo) / 26, 0) = 122182

Iteration No.4
dividend = 122182, modulo = (dividend - 1) Mod 26 = 7
columnName = HRXW, dividend = Round((dividend - modulo) / 26, 0) = 4699

Iteration No.5
dividend = 4699, modulo = (dividend - 1) Mod 26 = 18
columnName = SHRXW, dividend = Round((dividend - modulo) / 26, 0) = 180

Iteration No.6
dividend = 180, modulo = (dividend - 1) Mod 26 = 23
columnName = XSHRXW, dividend = Round((dividend - modulo) / 26, 0) = 6

Iteration No.7
dividend = 6, modulo = (dividend - 1) Mod 26 = 5
columnName = FXSHRXW, dividend = Round((dividend - modulo) / 26, 0) = 0

Result: ColumnNumber = 2147483647 <--> ColumnName = FXSHRXW

参考文章：

https://stackoverflow.com/questions/181596/how-to-convert-a-column-number-eg-127-into-an-excel-column-eg-aa