when to use the keyword "set"

**curiousGrace** · 07-28-2009, 01:31 PM

When do we use the keyword "set" in VBA? Try search in the threads, no result.

One of the examples(Walkenbach excel 2003 vba book), below:

Set rng = intersect(rng,parent.usedrange, rng)

How do i know when to use the keyword "set"?

Thanks in advance.

**DonkeyOte** · 07-28-2009, 01:36 PM

Nick Hodge has a related article which may be of interest: http://www.nickhodge.co.uk/pre2007/vba.htm

(more specifically see section re: Objects)

**shg** · 07-28-2009, 02:02 PM

There are two flavors of assignments in VBA: one for ordinary variables, which use Let, and one for object variables, which use Set.

An ordinary (string, logical, numeric) variable is one that points to the location in memory where the variable is stored.

An object variable (all the things you find in the language reference under Objects) is one that points to a structure in memory (a VTable) that in turn contains pointers to the object's properties and methods.

Please Login or Register  to view this content.

The Let in VBA is implicit and seldom seen.

EDIT: Arrays in VBA are 'ordinary' in the context above; an array in VBA is the memory address of a structure called a Safe Array that contains the data type, lower bounds, and array dimensions.

**curiousGrace** · 07-29-2009, 02:49 PM

Thank you for answering my question. I have another question pertaining to your answer.

Here is how i interpret your statement (An ordinary (string, logical, numeric) variable is one that points to the location in memory where the variable is stored.) :

Using the example you have given the "a" variable contains the address of the variable that stores the results of b+c?

Thanks in advance.

**shg** · 07-29-2009, 06:01 PM

It contains the address of a, whatever it stores. The compiler knows the data type (and therefore its length and storage format) based on its declaration. If undeclared (bad practice), it's a Variant, which contains information about its subtype (string, numeric, object, ...). See the VarType function.