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

Question

I use this function to retrieve a value from a closed book. In this eighth line of this code, I don’t understand why “A1” is used. What exactly is going on throughout this 8th line? I am also confused by the argument xlR1C1.

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, this property refers to a Range object. For example, if the selection is cell C3 , then Selection.Range("B1") returns cell D3 because it is relative to the Range object returned by the Selection property. ActiveSheet.Range("B1") code, on the other hand, always returns cell B1 .

This code uses the second Range("A1") to make sure that if you have a ref range that exceeds one cell, it only returns the top left cell of that range. Also, for your other Sub , called ExecuteExcel4Macro() , you need a reference to a cell of type R1C1 so that the address is converted to that type to pass the arg string to Sub .

Answer 2

xlR1C1 is a reference style that is used to indicate how formulas work. Using this style, your formulas will work and look different than you expect. The R1C1 specification basically means that cells are referenced differently using row and column numbers instead of letter names. For example, when using xlR1C1, you will access cell B2 with =R2C2 ( =R2C2 , column 2). Another example, cell C10 can be called =R10C3

As for what happens on line 8 ... you create a link to the cell that looks like this: (Note that the link to the cell will be different because it has a file path)

 ='[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 look like anything at all. Entering this in the direct window leads to some unexpected results.

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

Now I expected 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 a ReferenceStyle will produce these results.

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