References to cells and worksheets in Excel

Question

I have a problem that I don't understand at all:

i = 150
If ActiveWorkbook.Worksheets("foobar").Cells(i, 3).Value Like "*:*" Then
  MsgBox "I found a colon!"
End If

As you might guess, the sheet foobar has at position (150, 3) a cell containing a colon, thus the message box is shown.

Now I want to do this:

i = 150
Cell = ActiveWorkbook.Worksheets("foobar").Cells(i, 3).Value 'fails right here
If Cell Like "*:*" Then
  MsgBox "I found a colon!"
End If

Here it gives me an error saying "Object variable or With block variable not set. In fact saying:

Sheet = ActiveWorkbook.Worksheets("foobar")

gives a similar message. Why? What am I doing wrong? I just want a reference of that object, or at least a refence.

Answer 1

Bottomline:

• When handling objects, use Set.
• When handling primitive data (integers, longs, strings*, variants*) you do not use it.

Sheet, Workbook, Range, are objects. Therefore, you need to use Set when assigning them to variables.

A Range.Value returns a Variant (that can be a long, a string, etc.) So, you cannot use Set.

==========================

Now, about your error message... I'd say then that maybe before in your code, Cell is being declared as object. Try use another variable name, or check the Cell variable type.

To check this, right click on it and then click in 'Definition'. Have it declared as Variant might fix the problem (be aware of side effects it might cause, though).

==========================

*I know these types aren't 'primitive'; I used as an example here for the sake of explanation's cleanliness.