When to use Set in my VBA code?

Question

To my astonishment, I realized that I could not set a range at some point of my code. And I've found nowhere any mention of this issue.

So I declared (with dim) a Range variable.

And then I do Set Myvar = Worksheets("blabla").Range("A1:C3")

And Excel throw me an error code 9, out of range...

When I put the same line at the top of my code, it works (where I have my other sets).

Is that a known spec of VBA or is there something wrong for me ?

edit:

Apparently, the problem wasn't "set", but the fact that I can select a range only when the workbook is activated.

My code: Transform_file.Worksheets("general balance").Range(Cells(startline, 1), Cells(LastCellNumber, 1)).Value = "cool"

Works only if I have Transform_file.activate before

Hence the issue of my question wasn't Set, which can apparently be used anywhere.

Possible Duplicate: http://stackoverflow.com/questions/349613/what-does-the-keyword-set-actually-do-in-vba — rajah9, Jun 24 '13 at 14:40
At a guess you've got the name of the worksheet wrong - subscript out of range means that what you're looking for isn't there — SWa, Jun 24 '13 at 17:01
...or that worksheet isn't in the currently-active workbook. — Tim Williams, Jun 24 '13 at 18:42
Apparently, the problem wasn't set, but the fact that I can select a range only when the workbook is activated. — Eagle1, Jun 25 '13 at 08:32

score 2 · Accepted Answer · answered Jun 25 '13 at 09:12

I think the problem is that you are not aware how VBA uses some common defaults and assumptions. When you use:

 Worksheets("blabla")

Excel assumes you mean this:

 ActiveWorkbook.Worksheets("blabla")

I fact it assumes even more:

 Application.ActiveWorkbook.Worksheets("blabla")

The best way to avoid these kinds of issues is to be explicit in your code by creating a workbook object when you open the workbook. Like so:

Dim wkb As Workbook
Set wkb = Workbooks.Open("blah.xls")
wkb.Worksheets("blabla")

Or if you your code refers to a worksheet that is in the same workbook as your code then you could use:

ThisWorkbook.Worksheets("blabla")

These assumptions also work for for properties of objects which is why the language has the SET command. So example when you code:

Range("A1")

What Excel will acually return is:

Range("A1").Value

This is because Value is the default property of a Range Object. The language is built this way I assume to save coding - most of the time you would wnat the Value of the range. The problem is when you want to assign the object to a variable and not it's default property which when you need to use SET. SET just says return the entire object.

Yes you are right I wasn't aware that I could only select a worksheet in an active workbook. I've modified my code so I start defining with set the workbook I'll use. However I realize that even in the case I have saved 2 workbooks in wb1 and wb2: I work first with wb1, then I need to to wb2.whatever I need first to do wb2.activate. is that right ? — Eagle1, Jun 25 '13 at 16:12

When to use Set in my VBA code?

1 Answers1