vba 如何从行、列到 Excel A1 表示法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1114628/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get from Row, Column to Excel A1 notation?
提问by Cade Roux
Given a Row and Column (As Long), how can you determine the spreadsheet notation using VBA in Excel (2007):
给定一行和一列（As Long），如何在 Excel (2007) 中使用 VBA 确定电子表格符号：
e.g.:
例如：
(R, C) = (1, 1) -> "A1"
(R, C) = (2, 1) -> "A2"
(R, C) = (2, 2) -> "B2"
Thus if you had a function:
因此，如果你有一个函数：
Function CellRef(R As Long, C As Long) As String
which provided that functionality, you could do something like:
它提供了该功能，您可以执行以下操作：
Worksheet.Range(CellRef(R1, C1) + ":" + CellRef(R2, C2)).Copy
A little background, in case this is the wrong approach to be taking: The purpose of this is that I have a master sheet which describes other worksheets in a table:
一点背景知识，以防这是错误的方法：这样做的目的是我有一个主表，它描述了表格中的其他工作表：
WorksheetName, Range etc....
This master sheet controls transformations on the sheet, but the Range value is obviously in Excel notation for convenient later use in referencing the range. However a routine to manage this table, report exceptions and ensure consistency really gets things from other sheets in row and column, so for instance it gets a row and column where it knows something is starting and ending.
这个主工作表控制工作表上的转换，但范围值显然是 Excel 表示法，以便以后在引用范围时使用。然而，管理此表、报告异常和确保一致性的例程确实从行和列中的其他工作表中获取信息，例如，它获取一行和一列，其中知道某些内容正在开始和结束。
Here's the function I ended up with:
这是我最终得到的函数：
Private Function CellRef(R As Long, C As Long) As String
CellRef = vbNullString
On Error GoTo HandleError:
CellRef = Replace(Mid(Application.ConvertFormula("=R" & R & "C" & C, XlReferenceStyle.xlR1C1, XlReferenceStyle.xlA1), 2), "$", "")
Exit Function
HandleError:
End Function
回答by Dick Kusleika
The good stuff is in the comments
好东西都在评论里
回答by Mr. Napik
http://support.microsoft.com/kb/833402is a Microsoft solution for the problem of converting numbers to letters (the tricky part of the conversion from 1,1 to A1). This actually has the beuaty of working in other applications than Excel as it relies on basic VBA.
http://support.microsoft.com/kb/833402是 Microsoft 解决将数字转换为字母（从 1,1 转换为 A1 的棘手部分）问题的解决方案。这实际上具有在 Excel 以外的其他应用程序中工作的优点，因为它依赖于基本的 VBA。
Then you add:
然后你添加：
' Converts row and column index to Excel notation, ie (3, 2) to B3.
Private Function generateExcelNotation(row As Integer, column As Integer) As String
' error handling of your choice, I go for returning an empty string
If (row < 1 Or column < 1) Then
generateExcelNotation = ""
Exit Function
End If
generateExcelNotation = ConvertToLetter(column) & row
End Function
回答by OneSkyWalker
The expression 'rngTemp.Address(False, False, , , .Cells(1, 1))' will display the address of the range rngTemp in A1 notation which does not contain $s to signify an absolute address. To get an absolute address, replace 'False, False' with ','.
表达式 'rngTemp.Address(False, False, , , .Cells(1, 1))' 将以 A1 表示法显示 rngTemp 范围的地址，该范围不包含 $s 以表示绝对地址。要获得绝对地址，请将 'False, False' 替换为 ','。
回答by RDeLisle
Here are two solutions. One with elegant generality, another simple and direct aimed at the present implementation of Excel. The first is limited only by the precision of the Integer or Long data type. The second will fail if the maximum number of columns were to increase beyond 18278, the point when column references go from three letters to four letters. Both are pure VBA with no reliance on a feature peculiar to an MS Office application.
这里有两个解决方案。一个具有优雅的通用性，另一个简单而直接的针对 Excel 的当前实现。第一个仅受 Integer 或 Long 数据类型的精度限制。如果最大列数增加到超过 18278（列引用从三个字母变为四个字母的点），则第二个将失败。两者都是纯 VBA，不依赖于 MS Office 应用程序特有的功能。
The column references are viewed as consecutive sets of base 26 numbers of a given number of digits with the alphabet serving as digits A=0,B=1,.. etc. First there are 26 single letter columns. Then 26^2 = 676 double letter columns, Then 26^3 = 17576 triple letter columns for a total of 18278 of which only 16384 are used by Excel.
列引用被视为给定数字位数的连续基数 26 组，其中字母表作为数字 A=0、B=1 等。首先有 26 个单字母列。然后 26^2 = 676 个双字母列，然后 26^3 = 17576 个三字母列，总共 18278 个，其中只有 16384 个被 Excel 使用。
A1,B1,...,Z1 (1-26, 26 columns)
A1,B1,...,Z1 (1-26, 26 列)
AA1,....,ZZ1,(27 to 702, 26^2 = 676 columns)
AA1,....,ZZ1,（27 到 702，26^2 = 676 列）
AAA1,...,XFD1 (703 to 16384, 15682 columns of 26^3 = 17576 possible with three letters)
AAA1,...,XFD1（703 到 16384，15682 列 26^3 = 17576 可能有三个字母）
This is the first solution. Currently the maximum column number is 16384 so code will work with Integer (upper limit 32767) in place of Long. If you like you can error check that column parameter C is not out of range.
这是第一个解决方案。当前最大列数为 16384，因此代码将使用 Integer（上限 32767）代替 Long。如果您愿意，您可以错误检查列参数 C 是否超出范围。
'
' A "pure" implementation limited only by precision of the Long integer data type
'
'
' The first step is to find how many letters are needed.
' the second is to translate the column index into 0..(26^n) - 1 range
' Finally render that value as a base 26 number using alphabet for digits
'
Public Function CoordToA1Cell(ByVal R As Long, ByVal C As Long) As String
Dim colRef As String
Dim cwork As Long
Dim n As Integer
'
Static e(0 To 6) As Long ' powers of 26
Static s(0 To 6) As Long ' index ranges for number of letters needed
If C <= 0 OR R <= 0 Then Exit Function
' initialize on first call
If e(0) = 0 Then ' first call
s(0) = 1
e(0) = 1
For n = 1 To UBound(s)
e(n) = 26 * e(n - 1)
s(n) = s(n - 1) + e(n)
Next n
End If
cwork = C
colRef = ""
'
' step one: discover how many letters are needed
'
n = 1
Do
If C < s(n) Then
n = n - 1
Exit Do
End If
n = n + 1
Loop
' step two: translate into 0..(26^n) - 1 interval
cwork = cwork - s(n)
'
' Step three: represent column index in base 26 using alphabet for digits
'
Do While n > 0
colRef = colRef & Chr(65 + cwork \ e(n))
cwork = cwork Mod e(n)
n = n - 1
Loop
' the final (or only) digit
colRef = colRef & Chr(65 + cwork)
CoordToA1Cell = colRef & R
End Function
This second is simple ("Quick and Dirty") and will work with current Excel. It will require serious modification if the maximum number of columns exceeds 18278 when the column reference goes from 3 to 4 letters. '
这第二个很简单（“Quick and Dirty”），并且适用于当前的 Excel。当列引用从 3 到 4 个字母时，如果最大列数超过 18278，则需要认真修改。'
Public Function CoordToA1CellQAD(ByVal R As Long, ByVal C As Long) As String
Dim colRef As String
Dim cwork As Long
If C <= 0 OR R <= 0 Then Exit Function
cwork = C
If cwork <= 26 Then
colRef = Chr(64 + cwork)
ElseIf cwork <= 26 * 26 + 26 Then
cwork = cwork - (26 + 1)
colRef = Chr(65 + (cwork \ 26)) & Chr(65 + (cwork Mod 26))
'ElseIf cwork <= 26 * 26 * 26 + 26 * 26 + 26 Then ' theoretical limit for three letters, 17576
ElseIf cwork <= 16384 Then ' actual Excel limit for columns
cwork = cwork - (26 * 26 + 26 + 1)
colRef = Chr(65 + (cwork \ 676))
cwork = cwork Mod 676
colRef = colRef & Chr(65 + (cwork \ 26)) & Chr(65 + (cwork Mod 26))
Else ' overflow
Exit Function
End If
CoordToA1CellQAD = colRef & R
End Function
