SET statement tutorial

[Daminc]

Can anyone point me in the direction of a decent tutorial on the proper way of using the SET statement please?

This:

Assigns an object reference to a variable or property.

Syntax

Set objectvar = {[New] objectexpression | Nothing}

The Set statement syntax has these parts:

Part Description
objectvar Required. Name of the variable or property; follows standard variable naming conventions.
New Optional. New is usually used during declaration to enable implicit object creation. When New is used with Set, it creates a new instance of the class. If objectvar contained a reference to an object, that reference is released when the new one is assigned. The New keyword can't be used to create new instances of any intrinsic data type and can't be used to create dependent objects.
objectexpression Required. Expression consisting of the name of an object, another declared variable of the same object type, or a function or method that returns an object of the same object type.
Nothing Optional. Discontinues association of objectvar with any specific object. Assigning Nothing to objectvar releases all the system and memory resources associated with the previously referenced object when no other variable refers to it.



Remarks

To be valid, objectvar must be an object type consistent with the object being assigned to it.

The Dim, Private, Public, ReDim, and Static statements only declare a variable that refers to an object. No actual object is referred to until you use the Set statement to assign a specific object.

The following example illustrates how Dim is used to declare an array with the type Form1. No instance of Form1 actually exists. Set then assigns references to new instances of Form1 to the myChildForms variable. Such code might be used to create child forms in an MDI application.

Dim myChildForms(1 to 4) As Form1
Set myChildForms(1) = New Form1
Set myChildForms(2) = New Form1
Set myChildForms(3) = New Form1
Set myChildForms(4) = New Form1

Generally, when you use Set to assign an object reference to a variable, no copy of the object is created for that variable. Instead, a reference to the object is created. More than one object variable can refer to the same object. Because such variables are references to the object rather than copies of the object, any change in the object is reflected in all variables that refer to it. However, when you use the New keyword in the Set statement, you are actually creating an instance of the object.

Just doesn't make enough sense to me for me to be able to learn it

[Daminc]

Oh, and another thing.

What would the SET statement be used for in a practical sense.

I would really appreciate it if someone could actually explain it in terms that I can understand rather than assuming I know everything before hand like all the bl**dy tutorials that I've found

[Dave Peterson]

If the variable represents an object (a range, a worksheet, a workbook, a
collection, a...), then you need Set.

If the variable represents a simple thingy (an integer, a string, a boolean,
a....), then you don't use Set.

If the variable has properties or methods, you know that it needs Set.

Dim wks as Worksheet

Search for Worksheet in VBA's help and you'll see all the properties and
methods.

====
Well, I can't think of an example that uses a simple variable that breaks this
rule of thumb. Maybe someone will post an example correcting me.

Daminc wrote:
>
> Oh, and another thing.
>
> What would the SET statement be used for in a practical sense.
>
> I would really appreciate it if someone could actually explain it in
> terms that I can understand rather than assuming I know everything
> before hand like all the bl**dy tutorials that I've found
>
> --
> Daminc
> ------------------------------------------------------------------------
> Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
> View this thread: http://www.excelforum.com/showthread...hreadid=501108

--

Dave Peterson

[Doug]

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

[Daminc]

Thanks, it does make a bit more sense than before. I'm going to try to apply your explanations and see if I can actually make this thing work

Does anyone know of any decent tutorials?

At the moment most of my learning seems to stem from trial and error with a bit of enlightenment from the people in excelforum.

[Daminc]

Code so far:

Please Login or Register  to view this content.

I've come across about 6 different error types trying to work this out.
Anything blatently wrong?
Any hints or nudges in the right direction

In what incidences do I use CreateObject?

[Dave Peterson]

Ranges are objects. .Value (for a single cell range) will be a simple string or
double.

Dim rngNetwork as Range
Set rngNetwork _
= Workbooks("UserForm_training").Sheets("backendinfo").Range("F11").Value

Should be:

Dim rngNetwork as Range
Set rngNetwork _
= Workbooks("UserForm_training").Sheets("backendinfo").Range("F11")

Or

Dim rngNetwork as Long 'or string
rngNetwork _
= Workbooks("UserForm_training").Sheets("backendinfo").Range("F11").Value

========
And for the workbook lines, you can't include the full path. It's just the
workbook name of that open file.

Dim wrkbkUrl1 As Workbook
Set wrkbkUrl1 = Workbooks("1 Network.xls")

On the other hand, if the workbook isn't open, you'd use:

Dim wrkbkUrl1 As Workbook
Set wrkbkUrl1 = Workbooks.open _
(filename:="P:\VBA training\Excel templates for Network stats\1 Network.xls")

=========
Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with.



Daminc wrote:
>
> Code so far:
>
> Code:
> --------------------
> Option Explicit
>
>
> Public Sub UserForm_Initialize()
>
> Dim rngNetwork As Range
> Dim strNetwork1 As Range
> Dim strNetwork2 As Range
> Dim strNetwork3 As Range
> Dim wrkbkUrl1 As Workbook
> Dim wrkbkUrl2 As Workbook
> Dim wrkbkUrl3 As Workbook
>
> Set rngNetwork = Workbooks("UserForm_training").Sheets("backendinfo").Range("F11").Value
> Set strNetwork1 = Workbooks("UserForm_training").Sheets("backendinfo").Range("I11").Value
> Set strNetwork2 = Workbooks("UserForm_training").Sheets("backendinfo").Range("I12").Value
> Set strNetwork3 = Workbooks("UserForm_training").Sheets("backendinfo").Range("I13").Value
> Set wrkbkUrl1 = Workbooks("P:\VBA training\Excel templates for Network stats\1 Network.xls")
> Set wrkbkUrl2 = Workbooks("P:\VBA training\Excel templates for Network stats\2 Network.xls")
> Set wrkbkUrl3 = Workbooks("P:\VBA training\Excel templates for Network stats\3 Network.xls")
>
>
> 'P:\VBA training\Excel templates for Network stats\UserForm_training\backendinfo
> End Sub
> --------------------
>
> I've come across about 6 different error types trying to work this
> out.
> Anything blatently wrong?
> Any hints or nudges in the right direction
>
> --
> Daminc
> ------------------------------------------------------------------------
> Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
> View this thread: http://www.excelforum.com/showthread...hreadid=501108

--

Dave Peterson

[Daminc]

Do you know what? That is one of the clearest explainations that I've come across.

At the moment I've got:

• Introductory Visual Basic (P.K.McBride)
• Excel 2000 Bible
• Excel VBA Macro Programming (Richard Shepherd)
• The VBA help files
• Tutorials from across the web

and none of them gives a clear explaination

I shall check out the book you've mentioned.

Cheers Dave

[Dave Peterson]

If you can get to a library/bookstore, you may want to take a copy of Debra's
list.

Then you can pick out the book that seems to fit you best.

Daminc wrote:
>
> Do you know what? That is one of the clearest explainations that I've
> come across.
>
> At the moment I've got:
>
>
> - Introductory Visual Basic (P.K.McBride)
> - Excel 2000 Bible
> - Excel VBA Macro Programming (Richard Shepherd)
> - The VBA help files
> - Tutorials from across the web
>
>
> and none of them gives a clear explaination
>
> I shall check out the book you've mentioned.
>
> Cheers Dave
>
> --
> Daminc
> ------------------------------------------------------------------------
> Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
> View this thread: http://www.excelforum.com/showthread...hreadid=501108

--

Dave Peterson

[Daminc]

I've printed of a copy of those books and I sent a copy to my boss just in case

[Daminc]

With regards to:

Set wrkbkUrl1 = Workbooks.open _
(filename:="P:\VBA training\Excel templates for Network stats\1 Network.xls")

can I attach a name (i.e. wrkbkUrl1) to a workbook without opening it?

I want to use it later on for example:

If x=1 then wrkbkUrl1.open
elseif
x=2 then wrkbkUrl2.open
end if

or something like that?

I though it might be :

Set wrkbkUrl1 = Workbooks(filename:="P:\VBA training\Excel templates for Network stats\1 Network.xls")

but it doesn't work

[Dave Peterson]

if x = 1 then
set wrkbkurl1 = workbooks.open(filename:="P:\VBA training....\....xls")
elseif xl = 2
set wkrkburl2 = workbooks.open(filename:="yourother path here")
end if

Daminc wrote:
>
> With regards to:
> > Set wrkbkUrl1 = Workbooks.open _
> > (filename:="P:\VBA training\Excel templates for Network stats\1
> > Network.xls")
> can I attach a name (i.e. wrkbkUrl1) to a workbook without opening it?
>
> I want to use it later on for example:
>
> If x=1 then wrkbkUrl1.open
> elseif
> x=2 then wrkbkUrl2.open
> end if
>
> or something like that?
>
> I though it might be :
>
> Set wrkbkUrl1 = Workbooks(filename:="P:\VBA training\Excel templates
> for Network stats\1 Network.xls")
>
> but it doesn't work
>
> --
> Daminc
> ------------------------------------------------------------------------
> Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
> View this thread: http://www.excelforum.com/showthread...hreadid=501108

--

Dave Peterson

[Daminc]

Cheers Dave