I am having problems with the range object in VBA – I expect the following code to have the range to be the actual range “A17” but it is actually the value in “A17”.
And this is not what I want, as I want to pass the actual range to other subs and functions.
Sub test()
Dim rng As Range
Set rng = Worksheets("Sheet1").Range("A17")
MsgBox rng
End Sub
2
rng is the “actual” range. It follows from the next operation:
Set rng = Worksheets("Sheet1").Range("A17")
The Range() method results the range, and the Set command provides object assignment (without Set rng gets the value of the range as explained below).
Since MsgBox requires the first argument to be a string, Excel automatically apply the default property Value to rng. So, the equivalent (actual) code is:
MsgBox rng.Value
You can test this:
MsgBox rng.Address
to ensure the address of rng.
The Range’s Value property is a default property. For this reason, MsgBox rng evaluates as MsgBox rng.Value.
I use Application.Goto to test my Ranges. It will bring you to and select your range.
Application.Goto rng
Make sure that you type your range when you are passing them to other procedures to avoid unexpected behaviors caused the default property. Read About Default Properties
Sub Test(rng As Range)
I more or less see your problem: in order to try to solve it, I started debugging your piece of code and I didn’t find the answer either, as you can see in the following screenshot: (the “Row” equals “17” but I didn’t find any property, yielding “A17” or “$A$17”)
My next screenshot is in fact more a question towards Rotabor: you mention Address being the property the question author needs to look for, but a range does not have that property, according to the watch window, you see here. How can a person find out such a property exists?
No “Address”:
… but “Rng.Address” does have a value:
1