"Set" MyDate, MyNum etc

[WaterSprite]

Is the use of "Set" in a line of code outdated. In the last two days, any spreadsheet that I have with "Set" at the beginning of the line of code is not working, even though it has worked up to now for the last two or three years with that code.

Set MyDate = Worksheets("Sheet1").Range("A1").

What I get is: Code execution has been interrupted.

When I go to Debug, the highlighted line of code is always the one with "Set" at the beginning of the line. And it is different sheets, not just the same one all the time.

Should I just write MyDate = and go on without useing "Set

 

[DaveInIowa]

You must use Set when assigning the value of a reference type variable (a variable that points to an object). Making the assumption that MyDate is a Date type, which is a value type variable, not a reference type, you cannot use Set in this instance.

So to answer your question, no, the use of Set is not outdated in VBA (although it is in VB.NET). Using it just depends on what type of variable you're setting. Could you have possibly changed the data type of the MyDate variable?

 

[xlbo]

e.g.

dim myRange as range
Dim myStr as string

Set myRange = activesheet.range("A1")
myStr = "This is a string"

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[fumei]

Surely

Set MyDate = Worksheets("Sheet1").Range("A1").

is not the full line.

There is a "." at the end, with nothing after. That alone is an compile error.

Are you using Option Explicit? That would at least give you syntax error before you get things as run-time errors.

Essentially though DaveInIowa has it. If the declared variable points to something...it needs Set. If it is given a value...it does not. As in Geoff's examples.

Gerry