workbook or worksheets

[Guest]

<a
href="http://www.microsoft.com/office/com...7739c5-cb43-423a-b963-855688533e0f&sloc=en-us">Earlier</a>
I had asked about a Range problem. Eventually, I played around and solved it
simply by moving the macro from being associated to one of the worksheets to
being associated with "This Workbook."
I don't understand why this made a difference since I was referred to
specific worksheets in the code.
This begs a few questions:
1) Is it better form to associate a macro with a specific worksheet or to
the workbook?
2) If I'm associating the macor to a worksheet what are the limitations?
Thanks,
David

 

[Guest]

I am going out on a limb here and assuming that your range problem was a
named range problem? Named ranges are somewhat odd in that they can exits at
the worksheet level or at the borkbook level. Try this. Create a worksheet
with a named range on it. Now copy the worksheet. You now have two named
ranges both with the same name. The workbook version has presidence over the
worksheet version. You can confirm that for yourself by Hitting F5. Only one
of your named ranges is listed. The other is a cell range reference. That is
probably where the problem stemmed from. Assuming named ranges...

Is it better to associate code with a workbook or with a worksheet. That
depends on the scope of the function or procedure. If the function is
specific to one sheet then put it in the sheet. If the function is more
utilitarian than that and could be used by multiple sheets then put it in a
module. That is a little oversimplified but simple is good.

 

[Bob Phillips]

The workbook version has presidence over the
worksheet version.

Not always Jim. When on the worksheet that the worksheet nameis defined in,
the worksheet name takes precedence.

IMO, having the same name on different worksheets is confusing, but can be
useful. Having a worksheet name and a workbook name with the same name is
just asking for problems.

As an aside, see http://www.xldynamic.com/source/xld.Names.html

 

[Guest]

True enough on the name thing. The scope of the name is similar to a global
variable. It takes presidence unless a local variable by the same name is
also defined at which point the local name takes presidence. I was alluding
to the specific question asked where the global name was taking presidence.
Upon re-reading my answer it sure does not come across that way. Thanks for
clarifying...

 

[Tom Ogilvy]

Not sure why Jim turned this into a discussion of range names, but based on
your reference,
Your code was this:
Sub test3()
Dim str As String
str = Worksheets("Commands").Cells(2, 4)
Dim emp_range As Range
Set emp_range = Worksheets(str).Range("a1", Range("a2").End(xlDown))
For Each c In emp_range
MsgBox c.Value
Next c
End Sub

In a sheet module this is your problem - you are referring to ranges on two
different sheets in this line

Set emp_range = Worksheets(str).Range("a1", Range("a2").End(xlDown))

Thus your error.

in a sheet module, the unqualified Range("a2") refers to the sheet
containing the code - but str used in the qualification of the first range
is probably not the sheet containing the code. (also str is an excel
function, so using it as a variable is not wise) .

? typename(str(1233))
String
converts a number to a string

When you move it to the thisworkbook module Apparently it isn't a problem.
Nonetheless, as a general rule, all your code should be in general modules
(insert=>Module). Class modules such as Sheet modules and the Thisworkbook
module should be reserved for events associated with those objects. In
those cases, you can have excel enter the declaration from selecting the
object in the left dropdown at the top of the module and the event from the
right dropdown at the top of the module. Certainly this is a matter of
personal style, but I would highly recommend it to avoid problems like you
had.

--
Regards,
Tom Ogilvy

<a

href="http://www.microsoft.com/office/community/en-us/default.mspx?dg=micros
oft.public.excel.programming&mid=127739c5-cb43-423a-b963-855688533e0f&sloc=e

 

[Guest]

Thank you. It must have been using str as a variable. How stupid of me.
Thank you.
David