RC to A1 Address Conversion
2006-02-20
Excel VBA requires A1 notation to be used when making referencing spreadsheet cells and ranges. I don't think in A1 mode, and so I use these functions to allow me to use column numbers within my Excel VBA; that is, to convert from R1C1 to A1 addresses.
This function converts the column number used in RC spreadsheet addressing to the appropriate letter used in A1 addressing.
intCol - the RC column number.
"" (empty string) - returned if the input column number is not in the range of 1 to 256.
This function converts the row and column numbers used in RC spreadsheet addressing to the appropriate letter-number format used in A1 addressing. The real work of this function is performed by the C() function.
intRow - the RC row number.
"" (empty string) - returned if the input column number is not in the range of 1 to 256.
Sample usage of the C() function: Range(C(3) & "17:" & C(3) & "51").Select
To use this function, copy and paste this source code into a VBA module in the MS Word document in which you want to use the function.
Function C(ByVal intCol As Integer) As String ' ' C Function ' Convert the column number used in RC spreadsheet addressing to ' the appropriate letter used in A1 addressing. ' ' Parameters: ' intCol - the RC column number ' Return Values: ' "" (empty string) - returned if the input column number is ' not in the range of 1 to 256. ' A1 letter address - returned if the input column number was valid. ' Const MinCol = 1 Const MaxCol = 256 Const ChrOffset = 64 Const Zed = 26 If (intCol < MinCol) Or (intCol > MaxCol) Then ' The parameter is invalid; so we'll return a Null. C = "" ElseIf (intCol <= Zed) Then C = Chr(intCol + ChrOffset) Else Dim colDiv26 As Integer Dim colMod26 As Integer Dim digit_1 As Integer Dim digit_2 As Integer colDiv26 = Int(intCol / Zed) colMod26 = intCol Mod Zed If colMod26 = 0 Then digit_1 = colDiv26 - 1 digit_2 = Zed Else digit_2 = colMod26 digit_1 = colDiv26 End If C = Chr(digit_1 + ChrOffset) & Chr(digit_2 + ChrOffset) End If End Function Function RCtoA1(ByVal intRow As Long, ByVal intCol As Integer) As String ' ' RCtoA1 Function ' Convert the row and column numbers used in RC spreadsheet addressing to ' the appropriate letter-number format used in A1 addressing. The real work ' of this function is performed by the C() function. ' ' Parameters: ' intRow - the RC row number ' intCol - the RC column number ' Return Values: ' "" (empty string) - returned if the input column number is ' not in the range of 1 to 256. ' A1 letter/digit address - returned if the input column number was valid. ' Const MinRow = 1 Const MaxRow = 65536 Const MinCol = 1 Const MaxCol = 256 If (intRow < MinRow) Or (intRow > MaxRow) Or (intCol < MinCol) Or (intCol > MaxCol) Then ' One of the parameters is invalid; so we'll return a Null. RCtoA1 = "" Else RCtoA1 = C(intCol) & intRow End If End Function
Note, the template is Copyright ©2005 Christopher Rath. Permission is granted for any use of this template by others as long as this copyright statement is retained, other due and proper credit is provided to the author (e.g., don't claim you wrote this template), and the LGPL license is respected. This package is free software; you can redistribute it and/or modify it under the terms of version 2.1 of the GNU Lesser General Public License as published by the Free Software Foundation. See the LGPL licenese on the GNU.org website. This package is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE; on an “AS IS,” “WHERE IS” and “WITH ALL FAULTS” basis.