What is the TYPE of an Excel R1C1 reference?

Question

I have created some VBA functions to use with an Excel spreadsheet. Right now, the parameters to the functions have type String so I have to pass my column reference as a string.

However, I much prefer the R1C1 notation (been using that since Multiplan) but I can't figure out how to pass a column reference to a VBA function with that notation. In other words, I want to define a function so I can call it in the following way

=foo(C[-2])

so I'm looking for right type for the following function

function foo(ColRef as WhatTypeGoesHere) ...

Actually I you change the reference style of the application to xlR1C1, your formula will pass c[-2] as a range, but you can't have it both ways. — nutsch, Dec 13 '13 at 15:27
Apparently I can have it both ways (although I don't want it both ways). The application is in R1C1 mode....always. I was trying to create a VBA function to find the location of the last populated cell in a specified column and I was able to define function foo(col as string) and then call it like foo("B") which returned the last cell with a value in column B (aka Column C2). But rather than calling foo("B") I would like to call foo (C2) as an example. — David, Dec 13 '13 at 15:32
`which returned the last cell with a value in column B` You can use [this](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) in a function — Siddharth Rout, Dec 13 '13 at 15:34
@SiddharthRout I know how to get the result (the function I built works). The issue is how to CALL that function using R1C1 notation rather than passing a column STRING name — David, Dec 13 '13 at 15:45
Yeah, this looks like the way to go --- wasn't expecting to have to do this and was surprised that google searches didn't have examples. Thank you. — David, Dec 13 '13 at 16:39

Siddharth Rout · Accepted Answer · 2013-12-13T16:09:19.750

This will explain which one to use :)

Dim ws As Worksheet

Sub Sample()
    Set ws = ThisWorkbook.Sheets("Sheet1")

    Debug.Print foo1("B")         '<~~ Col Name
    Debug.Print foo2(Range("B1")) '<~~ Range
    Debug.Print foo3(2)           '<~~ Col Number
    Debug.Print foo4("R1C2")      '<~~ RC Notation
End Sub

Function foo1(ColRef As String) As Long
    foo1 = ws.Cells(ws.Rows.Count, ColRef).End(xlUp).Row
End Function

Function foo2(ColRef As Range) As Long
    foo2 = ws.Cells(ws.Rows.Count, ColRef.Column).End(xlUp).Row
End Function

Function foo3(ColRef As Integer) As Long
    foo3 = ws.Cells(ws.Rows.Count, ColRef).End(xlUp).Row
End Function

Function foo4(ColRef As String) As Long
    Dim MYAr
    MYAr = Split(ColRef, "C", , vbTextCompare)
    foo4 = ws.Cells(ws.Rows.Count, Val(MYAr(UBound(MYAr)))).End(xlUp).Row
End Function

Screenshot

enter image description here

EDIT:

What is to be noted is that "C[-2]" is not Column B it is an offset from the current cell. Here is another example which can handle different RC notations. Note, I am not doing any error handling but I am sure you can handle that. The objective of this code is to show how to find the last row using RC notation

Dim ws As Worksheet

Sub Sample()
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Debug.Print foo4("C[-2]")      
    Debug.Print foo4("RC[-2]")
    Debug.Print foo4("R[-1]C[-2]")
End Sub

Function foo4(ColRef As String) As Long
    Dim MYAr, sTmp As String
    Dim colNo As Integer
    Dim rng As Range

    '~~> I am assuming that you will pass a `C`. If not then
    '~~> Add an error handling here
    MYAr = Split(ColRef, "C", , vbTextCompare)

    If InStr(1, MYAr(UBound(MYAr)), "[") Then
        tmpAr = Split(MYAr(UBound(MYAr)), "[")(1)
        tmpAr = Split(tmpAr, "]")(0)
        colNo = Val(Trim(tmpAr))
    Else
        colNo = MYAr(UBound(MYAr))
    End If

    Set rng = ActiveCell.Offset(, colNo)

    foo4 = ws.Cells(ws.Rows.Count, rng.Column).End(xlUp).Row
End Function

Ah ---- so even though I want to use RC style, I still have to pass the value as a string and then extract the pieces myself? I understand that. I had hoped that there was a "type" that represented the RC stuff so I could use them without enclosing in strings. Thank you --- I will test this ASAP — David, Dec 13 '13 at 16:37
Correct. There is no specific type. You can pass it as a `String` or `Range` — Siddharth Rout, Dec 13 '13 at 16:38

AMissico · Answer 2 · 2013-12-13T23:16:19.020

1

In your case, use a Range.

You can specify any data type and Excel will implicitly convert as needed. It really depends on what the function does. For instance, if the function works with a group of cells or single cell, then use Range, if the function strips out characters, use a String.

edited Dec 13 '13 at 23:16

answered Dec 13 '13 at 15:23

AMissico

21,470
7
78
106

I do know I can ignore the type and I'll get a variant and in fact if I do this, I'm able to see the actual column number in a field called column (although I haven't figured out the syntax to get at it, col.column doesn't seem to work). But I'd like to avoid using variant types in the first place. – David Dec 13 '13 at 15:47
In Excel to VBA, everything is a `Variant`. In your case, use a `Range`. – AMissico Dec 13 '13 at 23:16

What is the TYPE of an Excel R1C1 reference?

2 Answers2