What is wrong here...?

[ErikJan]

Code: Select all

Dim Tg
Set Tg = Sheets("Data_Calculated").Range("A1")
.
.
.
Dim R As Range
.
R = Tg.Offset(1, i)

After this I'm calling a sub that required a "Range" variable as argument, hence my attempt to put 'Tg' (what I believed was a range as I use it like that in code) in R

[HansV]

Since R is an object variable, you should use the keyword Set, just like you did with Tg:

Set R = Tg.Offset(1, i)

[ErikJan]

Update - I tried and it works - thank you!

And if I have a subroutine which looks like:

Sub T(Rn as Range...)

Could I then call it like T R

if I have used Set for R

(and yes, I never fully grapsed that Set stuff ;-))

[HansV]

Could I then call it like T R

if I have used Set for R

Yes, that is correct. Or, alternatively,

Call T(R)

The basic rule is: don't use Set for variables of a "simple" data type such as String, Long, Date or Boolean:

Dim strName As String
strName = "Erik Jan"

Do use Set for variables that refer to an object with properties, methods and/or events:

Dim wshData As Worksheet
Set wshData = Worksheets("My Data")

The following may seem confusing:

Dim strName As String
strName = Range("A1")

A Range is an object, with properties such as Font and Locked, and methods such as Copy and ClearContents. But when we try to assign a range to a String variable, VBA will automatically assign the Value of that range to the String variable. Value is the so-called default property of a Range object. The line

strName = Range("A1")

is actually shorthand for

strName = Range("A1").Value

When I use

Dim rngCell As Range
Set rngCell = Range("A1")

the declaration of rngCell as a Range and the use of Set make it clear to VBA that you don't want to assign the value of the cell to rngCell, but the cell object with all its properties.

[ErikJan]

I did know this once and read about it many times but I keep on forgetting it. Thank you for a very nice explanation!