Why is cell "A1" being used in this GetValue function in VBA?

Question

I'm using this function to retrieve a value from a closed workbook. In this 8th line of this code, I don't understand why "A1" is being used. What exactly is happening in that entire 8th line? I'm confused by the xlR1C1 argument as well.

Private Function GetValue(path, file, sheet, ref)

    Dim arg As String
    If Right(path, 1) <> "\" Then path = path & "\"
    If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
      Range(ref).Range("A1").Address(, , xlR1C1)
    GetValue = ExecuteExcel4Macro(arg)

End Function

Answer 1

Range().Range() Documentation here:

When applied to a Range object, the property is relative to the Range object. For example, if the selection is cell C3, then Selection.Range("B1") returns cell D3 because it’s relative to the Range object returned by the Selection property. On the other hand, the code ActiveSheet.Range("B1") always returns cell B1.

The code is using that second Range("A1") to ensure that if you have a ref range larger than one cell, it only returns the top left cell of that range. Also it would appear your other Sub called ExecuteExcel4Macro() requires an R1C1 type cell reference so the address is being converted into that type for passing the arg string into the Sub.

Answer 2

xlR1C1 is a reference style which is used to specify how formulas work. When using this style, your formulas will work and look very differently than you expect. The R1C1 specification, basically means the cells are referred to differently using row & column ordinals instead of letter names. For example, when using xlR1C1, you would access cell B2 by using =R2C2 (row2, column 2). Another Example, cell C10 could be referred to as =R10C3

As to whats happening on line 8... you are constructing a cell reference that looks like this: (Note that your cell reference will be different because it has a file path in it)

='[Myfilename.xlsx]Sheet1'!R1C1

You can use the debugger to view the string contained in the arg variable.

Answer 3

Adding Range("A1") to the code doesn't appear to do anything at all. Typing this into the immediate window results in some... unexpected results.

?Range("B3").Range("A1").Address
$B$3

Now, I would have expected that to return $A$1, but it seems that this part of the function will return the address of Range(ref).

Now, calling Range.Address with the ReferenceStyle argument would produce these results.

?Range("B3").Range("A1").Address(,,xlR1C1)
R3C2