C(), RCtoA1()

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.

Syntax

C(ByVal intCol As Integer) As String

This function converts 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.

RCtoA1(ByVal intRow As Long, ByVal intCol As Integer) As String

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.

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 address - returned if the input column number was valid.

Example

Sample usage of the C() function: Range(C(3) & "17:" & C(3) & "51").Select

Source code

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.
'
' (c) 2006 Christopher Rath
'
'   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.
'
' (c) 2006 Christopher Rath
'
'   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

Copyright & Licensing

Note, the code is Copyright ©2006 Christopher Rath. Permission is granted for any use of this code 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 code), 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.