Dim Date Help.

[Xceller]

Hi everyone,

I don't understand this logic. When I dimension a date such as the following satement:
(1)
Dim StartDate As Date
Set StartDate = Thisworkbook.Worksheets("Report").Range("A1").Value

I got a error message that says "Compile error, Object required" What does this mean? However, when a date dimensioned as Variant and without SET statement then it runs OK. Also, if I set StartDate = Thisworkbook.Worksheets("Report").Range.Value without the Dim Statement. This works as well.
(2) and (3) work , Why does (1) not work?

Thanks for your help in advance.



(2)
Dim StartDate As Variant
(3)
StartDate = Thisworkbook.Worksheets("Report").Range("A1").Value

 

[Biz]

Try

Sub Test()
Dim StartDate As Date
StartDate = ThisWorkbook.Worksheets("Report").Range("A1").Value
End Sub

Biz

 

[GTO]

Hi everyone,

I don't understand this logic. When I dimension a date such as the following satement:
(1)
Dim StartDate As Date
Set StartDate = Thisworkbook.Worksheets("Report").Range("A1").Value

I got a error message that says "Compile error, Object required" What does this mean?

It means that you need an Object to Set a reference to. A Date is not an Object, rather, its just a Value. An example of an Object would be the cell itself (that is, a Range Object). Does that make sense?

Mark

 

[Xceller]

Biz,

Thanks for your help. It your code works.
But why is it when I use the "SET" with Dim statement the code doesn't work?

Sub Test ()
Dim StartDate As Date
StartDate = Thisworkbook.Worksheets("Report").Range("A1").Value
End Sub()

I am confused as to why sometimes a Dim statement must follow by a SET statement. Yet, in this example when I incorporate the SET statement the code doesn't work.

Sub Test ()
Dim StartDate As Date
Set StartDate = Thisworkbook.Worksheets("Report").Range("A1").Value
End Sub()

 

[Xceller]

GTO, Thanks for the explanation. I think I get that a Date cannot be an object, but I try dimensioning it as Variant and it doesn't work and it says " Type mismatch ". Do you know why?

Sub Test()
Dim StartDate As Variant
Set StartDate = Thisworkbook.worksheet("Report").Value
End Sub()

 

[GTO]

GTO, Thanks for the explanation. I think I get that a Date cannot be an object, but I try dimensioning it as Variant and it doesn't work and it says " Type mismatch ". Do you know why?

Sub Test()
Dim StartDate As Variant
Set StartDate = Thisworkbook.worksheet("Report").Value
End Sub()

That is the point. A Date cannot be an Object, thus you cannot use the Set Statement and Set a reference to it. You can assign the value of the Date to a variable, as Biz showed.

 

[GTO]

PS. - Dimensioning or Declaring the variable as a Variant is simply hiding the faulty code until the Set statement attempts to execute, as a Variant could be referring to an Object.