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
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=501108

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

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

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

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
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=501108

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

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

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

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

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

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


--
Daminc
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=501108

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

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

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