Here's my simple-minded view of it.

1. Variables can be one of two broad types. Most are simple variables,
that can hold one (or more) values of a specific sort, such as long
integers, or strings, or dates. The other type of variable refers to
an object; the object can contain data values (of multiple types, as
Properties) and can exhibit other kinds of behaviors (via Methods).

2. You should explicitly declare both types (assuming you're setting
Option Explicit) before attempting to use them.

3. For simple variables, declaring them (via a DIM statement) is
enough. Once declared, you can start assigning values to them and
using them in your code.

4. For object variables, there are two wrinkles: a) you need to
explicitly create an instance of the object somehow, and b) you need to
use the SET statement, rather than just using the = sign, any time you
are changing what the object variable refers to.

So, for a simple variable (x) you can declare the variable (step 1) and
assign a value to it (step 2)

1 dim X as long
2 X = 102


But if you are dealing with an object, you need to do 3 things: (1)
declare the variable, (2) create the object, and (3) assign data to the
object via the object properties.

1 Dim obj as MyObjType
or
1 Dim obj as Object

2 SET obj = CreateObject("MyObjType")
or
2 SET obj = New MyObjType

3 obj.Width = 6
obj.Height = 12
obj.Color = "BLUE"
Not that the object properties are effectively simple variables; they
only take on one type of value, and you don't need to use the SET
keyword when dealing with one property at a time (unless the property
is itself an object).

If you already have an object variable that refers to an object of the
desired type, you can combine steps 2 and 3 above by making your
variable refer to the existing object. If we already have an object
variable ("bob"), we can make our new object variable refer to that
object by:

2 SET obj = bob

The CreateObject() function should always work, if the referenced
object type is registered on the machine that the code is running on.
The second version, using the "New" keyword, will require a Reference
to that object type. (From the code window, go to Tools, References
and make sure the object DLL or TLB is listed, and has a check in the
box. To add new items to the list, click the Browse... button and
navigate to the DLL or TLB file, as appropriate.)

If you are dealing with objects that are part of the Excel object model
(ranges, names, sheets, etc) you'll automatically have the Reference
you need, and you normally don't need to create an instance of the
object from scratch, you can get them by referring to the workbook,
worksheet, etc.

So you can say

Dim r as Range
SET r = ThisWorkbook.Sheets("Sheet1").Range("A3")
or
SET r = ThisWorkbook.Sheets("Sheet1").cells(3,1)


Hopefully that makes a little more sense.


Doug