"Set" statement with variables?

[IanC]

Is it possible to use variables in a "Set" statement?

For example, I have the following lines:

With Worksheets("Lookup")
Set General_kV = ("Lookup").Range("General_kV")
Set General_mA = .Range("General_mA")
Set General_Timer = .Range("General_Timer")
(...etc. approx 90 other Set statements).
End With

I can recover the named ranges from the Lookup worksheet but I don't know
how (or if) it's possible to apply them to a variable in the Set statement.

What I'm hoping for is something along the lines of the following code but I
need both instances of X to be substituted with General_kV, General_mA,
General_Timer, etc.

Sub SetRanges()
Set nms = ThisWorkbook.Names
For r = 1 To nms.Count
X = nms(r).Name
If Left(nms(r).RefersTo, 8) = "=Lookup!" Then
Set X = Worksheets("Lookup").Range(X)
End If
Next
Set nms = Nothing
End Sub


Any ideas?

 

[Jim Cone]

Why do you need 90 + Set statements?
If you already have named ranges why not use them...
If Worksheets("Lookup").Range("Sludge").Cells(2, 1).Value > 100 Then
'do something
End If
--
Jim Cone
Portland, Oregon USA
http://tinyurl.com/XLCompanion

..
..
..

"IanC" <[email protected]>
wrote in message
Is it possible to use variables in a "Set" statement?

For example, I have the following lines:

With Worksheets("Lookup")
Set General_kV = ("Lookup").Range("General_kV")
Set General_mA = .Range("General_mA")
Set General_Timer = .Range("General_Timer")
(...etc. approx 90 other Set statements).
End With

I can recover the named ranges from the Lookup worksheet but I don't know
how (or if) it's possible to apply them to a variable in the Set statement.

What I'm hoping for is something along the lines of the following code but I
need both instances of X to be substituted with General_kV, General_mA,
General_Timer, etc.

Sub SetRanges()
Set nms = ThisWorkbook.Names
For r = 1 To nms.Count
X = nms(r).Name
If Left(nms(r).RefersTo, 8) = "=Lookup!" Then
Set X = Worksheets("Lookup").Range(X)
End If
Next
Set nms = Nothing
End Sub


Any ideas?

 

[Mike S]

Why do you need 90 + Set statements?
If you already have named ranges why not use them...
If Worksheets("Lookup").Range("Sludge").Cells(2, 1).Value> 100 Then
'do something
End If

Has anyone done a speed test to see if there is any appreciable
difference between using the two?

 

[GS]

Mike S laid this down on his screen :

Has anyone done a speed test to see if there is any appreciable difference
between using the two?

I agree with Jim's comments. Typically, referencing the objects
directly doesn't add to resource overhead, AND also usually results in
greater speed because VB[A} doesn't have to allocate resources required
when storing refs in variables.

 

[GS]

Jim Cone wrote :

Why do you need 90 + Set statements?
If you already have named ranges why not use them...
If Worksheets("Lookup").Range("Sludge").Cells(2, 1).Value > 100 Then
'do something
End If
--
Jim Cone
Portland, Oregon USA
http://tinyurl.com/XLCompanion

.
.
.

"IanC" <[email protected]>
wrote in message
Is it possible to use variables in a "Set" statement?

For example, I have the following lines:

With Worksheets("Lookup")
Set General_kV = ("Lookup").Range("General_kV")
Set General_mA = .Range("General_mA")
Set General_Timer = .Range("General_Timer")
(...etc. approx 90 other Set statements).
End With

I can recover the named ranges from the Lookup worksheet but I don't know
how (or if) it's possible to apply them to a variable in the Set statement.

What I'm hoping for is something along the lines of the following code but I
need both instances of X to be substituted with General_kV, General_mA,
General_Timer, etc.

Sub SetRanges()
Set nms = ThisWorkbook.Names
For r = 1 To nms.Count
X = nms(r).Name
If Left(nms(r).RefersTo, 8) = "=Lookup!" Then
Set X = Worksheets("Lookup").Range(X)
End If
Next
Set nms = Nothing
End Sub


Any ideas?

Couple things occur to me after reading your approach...

1. I'm not sure why you're using workbook level names for ranges on a
specific sheet, but I recommend you change them to sheet-level names so
they're exclusive to the sheet.

2. Once the defined name ranges are localized to Sheets("Lookup"),
your For...Next loop seems a reasonable approach on its own, but I'd
ditch loading the names into a variable since your loop can work
directly on them without the added overhead. Example...

Dim n as Object, wksSource As Worksheet
Set wksSource = ThisWorkbook.Sheets("Lookup")'just for clarity
For Each n in wksSource.Names
If Left$(n.RefersTo, 8) = "=Lookup!" Then
With wksSource.Range(n)
'do stuff
End With
End If
Next n


Also, if you have a specific list of named ranges that you want to work
with then you could store them into a delimited string and use the
InStr() function to validate that n is one you want, OR you could just
iterate the string var in a loop and only operate on the desired named
ranges in your list.

HTH

 

[IanC]

Hi Jim

Depending on the combination various ComboBoxes on a form, differing ranges
from the Lookup sheet are copied to the Checklist sheet.

As the same named ranges are used in multiple combinations, my code is
shorter (and easier to write) by adding 90+ Set statements then using
General_kV.Copy .Range("A6")
instead of using
Worksheets("Lookup").Range("General_kV").Copy .Range("A6")
in several locations.

I'm simply trying to reduce the code further.

 

[IanC]

Hi Garry

Speed isn't really an issue. Even on the slowest laptops we have the time
taken to process the code is negigible.

--
Ian
--

Mike S laid this down on his screen :

Has anyone done a speed test to see if there is any appreciable
difference between using the two?

I agree with Jim's comments. Typically, referencing the objects directly
doesn't add to resource overhead, AND also usually results in greater
speed because VB[A} doesn't have to allocate resources required when
storing refs in variables.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

 

[IanC]

Hi Garry

Speed isn't really an issue. Even on the slowest laptops we have the time
taken to process the code is negigible.

--
Ian
--

Mike S laid this down on his screen :

Has anyone done a speed test to see if there is any appreciable
difference between using the two?

I agree with Jim's comments. Typically, referencing the objects directly
doesn't add to resource overhead, AND also usually results in greater
speed because VB[A} doesn't have to allocate resources required when
storing refs in variables.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

 

[IanC]

Hi Garry

About your 2 points below.

1. I wasn't aware there is such a distinction as workbook level names &
sheet-level names. How do you determine which they are and change between
them?
I use Insert > Name > Define... to create the names. There isn't an option
to specify workbook or worksheet names.

2. I appreciate the pointer that I don't need to use a variable, but if the
names are localised to the sheet would I need to add this line?

If Left$(n.RefersTo, 8) = "=Lookup!" Then

I only put that in because there are 3 names referring to an external
workbook and needed to work only with the Lookup sheet names.

Where you added the line
'do stuff
the stuff I need to do is the equivalent of
Set X = Worksheets("Lookup").Range(X)
I suppose in your code this would be
Set n = Worksheets("Lookup").Range(n)

This is really the problem I'm having. All the rest is interesting, but not
essential.

 

[IanC]

Hi Jim, Mike & Garry

Thanks for all your comments which were interesting to read, but didn't
address the central question. While some of your comments may be valid, and
may serve to make my eventual code run faster or use less resources, the
main question remains:

Can I define set statements using something like
Set X = Worksheets("Lookup").Range(X)
where X is automatically replaced with the name of a range?

If it's possible, then how? If not the specifics, perhaps you could point me
in the right direction.
If it's NOT possible, please put me out of my misery

Many thanks.

 

[Scossa]

shorter (and easier to write) by adding 90+ Set statements then using
    General_kV.Copy .Range("A6")
instead of using
    Worksheets("Lookup").Range("General_kV").Copy .Range("A6")
in several locations.

I'm simply trying to reduce the code further.

No SET statment required if use named ranges, try:

[General_KV].Copy .Range("A6")

Bye!
Scossa

 

[GS]

Hi Garry

About your 2 points below.

1. I wasn't aware there is such a distinction as workbook level names &
sheet-level names. How do you determine which they are and change between
them?
I use Insert > Name > Define... to create the names. There isn't an option to
specify workbook or worksheet names.

How local names (sheet-level) are defined in this dialog is they are
preceeded by the sheetname and an apostrophe. So...

Defined name "General_kV" would be entered as "Lookup!General_kV" to
make it sheet-level. This type of defined name will appear in the list
in exactly the same fashion. So.., names in the list without the active
sheet's name prepended are global (workbook-level) defined names. Use
of global names is discouraged as much as possible when a local defined
name can work. One main reason is that when you copy sheets to other
workbooks the global defined names follow along and could cause name
conflicts if the same name was defined in the target workbook. Local
defined names do not behave this way as they are unique to the sheet
they were defined on. Also, local defined names are reusable on many
sheets within a workbook whereas global defined names are unique only
to the workbook they are defined in (and so is why name conflicts can
occur when we copy/move sheets to another workbook.

2. I appreciate the pointer that I don't need to use a variable, but if the
names are localised to the sheet would I need to add this line?

If Left$(n.RefersTo, 8) = "=Lookup!" Then

I only put that in because there are 3 names referring to an external
workbook and needed to work only with the Lookup sheet names.

So then, are you saying the three defined names don't exist in the same
workbook your code is in? Your code suggests that they do as you define
your set statement to 'ThisWorkbook.Names', which suggests these are
defined in the workbook containing your code. Your comment is
confusing!

'n' is a required object var for use in the For Each loop used to
iterate the names collection so you can dtermine if the defined name
refs a range on Sheets("Lookup"). If the names were local to the sheet
then you would only have to iterate the names collection for
Sheet("Lookup") for the desired names (or sheet names if that's what
they ref).

**Again, it's confusing due to your previous comment about an external
workbook.

Where you added the line
'do stuff
the stuff I need to do is the equivalent of
Set X = Worksheets("Lookup").Range(X)
I suppose in your code this would be
Set n = Worksheets("Lookup").Range(n)

No, in my code 'n' refers to the defined name object in the names
collection. You should use a different var to hold a range object ref
(such as you're already doing with 'X'), but I would make it a bit more
descriptive about what it refers to (ie: rngTemp), especially if you're
reloading it with numerous values in the same procedure. Otherwise, 'n'
is only a temp object var used to hold a ref to each name in the names
collection for the purpose of stepping through each name in the names
collection. So, to answer your Q with respect to my code sample you
would use:
Set X = wksSource.Range(n.Name) to set an object var to the range
defined by that name's Name property.

 

[IanC]

Thanks, Scossa.

That did the trick, though I don't understand why. It doesn't work without
[ ] round the name, not does it work with just General_kV. I need to specify
[Lookup!General_kV].Copy and this works fine.

Over the course of this thread I've also come to realise that when I create
all these Set statements, I am using more system resources - resources that
are not released when I close the workbook. No wonder I kept getting a
message saying there are insufficient resources the display the workbook
fully after a period of development work on the workbooks!

Presumably your method doesn't reserve system resources in this manner, so I
shouldn't enounter the same problem.

Thanks for all you help.

--
Ian
--

shorter (and easier to write) by adding 90+ Set statements then using
General_kV.Copy .Range("A6")
instead of using
Worksheets("Lookup").Range("General_kV").Copy .Range("A6")
in several locations.

I'm simply trying to reduce the code further.

No SET statment required if use named ranges, try:

[General_KV].Copy .Range("A6")

Bye!
Scossa

 

[IanC]

Hi Garry

How local names (sheet-level) are defined in this dialog is they are
preceeded by the sheetname and an apostrophe. So...

Defined name "General_kV" would be entered as "Lookup!General_kV" to make
it sheet-level. This type of defined name will appear in the list in
exactly the same fashion. So.., names in the list without the active
sheet's name prepended are global (workbook-level) defined names. Use of
global names is discouraged as much as possible when a local defined name
can work. One main reason is that when you copy sheets to other workbooks
the global defined names follow along and could cause name conflicts if
the same name was defined in the target workbook. Local defined names do
not behave this way as they are unique to the sheet they were defined on.
Also, local defined names are reusable on many sheets within a workbook
whereas global defined names are unique only to the workbook they are
defined in (and so is why name conflicts can occur when we copy/move
sheets to another workbook.

If I understand correctly - When I define a name, I can define it as MyRange
or Lookup!MyRange and both will work, but by using Lookup!MyRange I could
have another MyRange in another worksheet (eg Checklist!MyRange) without any
conflict?

So then, are you saying the three defined names don't exist in the same
workbook your code is in? Your code suggests that they do as you define
your set statement to 'ThisWorkbook.Names', which suggests these are
defined in the workbook containing your code. Your comment is confusing!

My comment is confusing because I'm confused. My VBA programming is all
self-taught (with a lot of help from this forum), but much of it is trial
and error. I find solutions that work, but they are not necessarily the
preferred methods.

As far as the range naming goes, if I go to the "Define Name" dialog,
"General_kV" is shown as referring to "=Lookup!$A$120:$C$128". One of the
"external" names is "Equipment" and is shown as referring to "='C:\[Test
Equipment List.xls]Sheet1'!$A$5:$A$30". When I hit F5 in the workbook to
bring up the list of used names, "General_kV" appears, but "Equipment"
doesn't. I defined both from within my workbook.

Hopefully you are no longer confused as I don't know how else to explain it
:-(

No, in my code 'n' refers to the defined name object in the names
collection. You should use a different var to hold a range object ref
(such as you're already doing with 'X'), but I would make it a bit more
descriptive about what it refers to (ie: rngTemp), especially if you're
reloading it with numerous values in the same procedure. Otherwise, 'n' is
only a temp object var used to hold a ref to each name in the names
collection for the purpose of stepping through each name in the names
collection. So, to answer your Q with respect to my code sample you would
use:
Set X = wksSource.Range(n.Name) to set an object var to the range defined
by that name's Name property.

What I had been looking for was something whereby I could parse the list of
names and automatically create Set statements such that in my line...

Set X = Worksheets("Lookup").Range(X)

....both instances of X would be replaced by the range name. It looks like
this isn't possible, but it's academic now as Scossa has provided a neat
solution that does away with the Set statements altogether.

Thanks for all your input. It's been a useful learning experience.

 

[GS]

IanC explained on 8/20/2010 :

Hi Garry

How local names (sheet-level) are defined in this dialog is they are
preceeded by the sheetname and an apostrophe. So...

Defined name "General_kV" would be entered as "Lookup!General_kV" to make
it sheet-level. This type of defined name will appear in the list in
exactly the same fashion. So.., names in the list without the active
sheet's name prepended are global (workbook-level) defined names. Use of
global names is discouraged as much as possible when a local defined name
can work. One main reason is that when you copy sheets to other workbooks
the global defined names follow along and could cause name conflicts if the
same name was defined in the target workbook. Local defined names do not
behave this way as they are unique to the sheet they were defined on. Also,
local defined names are reusable on many sheets within a workbook whereas
global defined names are unique only to the workbook they are defined in
(and so is why name conflicts can occur when we copy/move sheets to another
workbook.

If I understand correctly - When I define a name, I can define it as MyRange
or Lookup!MyRange and both will work, but by using Lookup!MyRange I could
have another MyRange in another worksheet (eg Checklist!MyRange) without any
conflict?
Correct!

So then, are you saying the three defined names don't exist in the same
workbook your code is in? Your code suggests that they do as you define
your set statement to 'ThisWorkbook.Names', which suggests these are
defined in the workbook containing your code. Your comment is confusing!

My comment is confusing because I'm confused. My VBA programming is all
self-taught (with a lot of help from this forum), but much of it is trial and
error. I find solutions that work, but they are not necessarily the preferred
methods.

As far as the range naming goes, if I go to the "Define Name" dialog,
"General_kV" is shown as referring to "=Lookup!$A$120:$C$128". One of the
"external" names is "Equipment" and is shown as referring to "='C:\[Test
Equipment List.xls]Sheet1'!$A$5:$A$30". When I hit F5 in the workbook to
bring up the list of used names, "General_kV" appears, but "Equipment"
doesn't. I defined both from within my workbook.

Hopefully you are no longer confused as I don't know how else to explain it
:-(

No, in my code 'n' refers to the defined name object in the names
collection. You should use a different var to hold a range object ref (such
as you're already doing with 'X'), but I would make it a bit more
descriptive about what it refers to (ie: rngTemp), especially if you're
reloading it with numerous values in the same procedure. Otherwise, 'n' is
only a temp object var used to hold a ref to each name in the names
collection for the purpose of stepping through each name in the names
collection. So, to answer your Q with respect to my code sample you would
use:
Set X = wksSource.Range(n.Name) to set an object var to the range defined
by that name's Name property.

What I had been looking for was something whereby I could parse the list of
names and automatically create Set statements such that in my line...

Set X = Worksheets("Lookup").Range(X)

...both instances of X would be replaced by the range name. It looks like
this isn't possible,

Also correct! In the case of 'Set' X is an object. In the case of
'Range(X)' X is a string. No-no supreme!

but it's academic now as Scossa has provided a neat
solution that does away with the Set statements altogether.

Exactly what others have been saying as well. Good for Scossa to give

 

[IanC]

Hi Garry

Exactly what others have been saying as well. Good for Scossa to give it
to you the way he did!<g>

I have to admit I'd been using Set statements because it avoids a lot of
typing <bg>

That said, Scossa's solution of
[General_KV].Copy .Range("A6")
is a lot shorter than
Worksheets("Lookup").Range("General_kV").Copy .Range("A6")
and as I already have
General_KV.Copy .Range("A6")
in place, extending this to
[Lookup!General_kV].Copy .Range("A6")
isn't a great deal of work, especially with copy/paste.