References to cells and worksheets in Excel

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 d开发者_Python百科oing wrong? I just want a reference of that object, or at least a refence.

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.

As your assigning an object the line

Sheet = ActiveWorkbook.Worksheets("foobar") 

should read

Set Sheet = ActiveWorkbook.Worksheets("foobar")

In your line

Cell = ActiveWorkbook.Worksheets("foobar").Cells(i, 3).Value

you are trying to assign the value of cell (probably a string) to a cell. Take the .Value off and the assignment should work better. The Excel message you encountered is not the best: sometimes you get it when you assign variables of the wrong type.

However, the If Cell Like might not work. (Hint: the .Value has to get moved, not deleted.)

I just tried the following code and it works probably in your code make sure the ActiveWorkBook is actually the Active Work book you want(if you are working with multiple work books)

Sub test()
i = 3
If ActiveWorkbook.Worksheets("Sheet1").Cells(i, 2).Value Like ":" Then
    MsgBox "I found a colon!"
Else
    MsgBox "didn't find a colon!"
End If

Cell = ActiveWorkbook.Worksheets("Sheet1").Cells(i, 2).Value
'fails right here
If Cell Like ":" Then
    MsgBox "I found a colon!"
End If
End Sub

Use Dim MyCell as Variant (or as string) since Cell is an existing object