I have a workbook I've been developing under XL2K in which I reference the
number of rows and columns in a named range in several VBA subroutines
within a single module:

NumRows = Range("Name_Copy").Rows.Count
NumCols = Range("Name_Copy").Columns.Count

Is there a way to make those available to all subs within my module without
having to put them in each individual subroutine?
If so, I need exact syntax please.

--
David

Declare them as public variables, before any macro, in a standard code
module

Public NumRows as Long
Public NumCols as long


Sub macro1(0
....
End Sub


etc.

--
HTH

Bob Phillips

"David" wrote in message
...
I have a workbook I've been developing under XL2K in which I reference the
number of rows and columns in a named range in several VBA subroutines
within a single module:

NumRows = Range("Name_Copy").Rows.Count
NumCols = Range("Name_Copy").Columns.Count

Is there a way to make those available to all subs within my module
without
having to put them in each individual subroutine?
If so, I need exact syntax please.

--
David

David:

I am not an expert by any means, so what I do may not be the best way. But
if I have several Subs in one module and all need to act on the same
variables, I Dim the variables at the very top of the module before the
first Sub. Be careful, though - numbers and strings may not be at
zero-value when you hit a certain Sub, as they would be if you had declared
them in that Sub. I also must confess that while I know it works for
declaring variables and setting string and number values in one Sub to carry
over into another, I have had only limited success trying to get Workbook,
Worksheet, and Range variables to Set in one Sub and carry over into
another. Referring to my first statement, I'm probably doing something
amiss; if so, I'm sure someone else here can give the best way.

Ed

"David" wrote in message
...
I have a workbook I've been developing under XL2K in which I reference the
number of rows and columns in a named range in several VBA subroutines
within a single module:

NumRows = Range("Name_Copy").Rows.Count
NumCols = Range("Name_Copy").Columns.Count

Is there a way to make those available to all subs within my module
without
having to put them in each individual subroutine?
If so, I need exact syntax please.

--
David

Bob Phillips wrote

Declare them as public variables, before any macro, in a standard code
module

Public NumRows as Long
Public NumCols as long


Sub macro1(0
...
End Sub

Ok, so how would each Sub know those were referring to my named range?
Range("Name_Copy").Rows/Columns.Count

Be easy, this is brand new to me.

--
David

Bob Phillips wrote

Declare them as public variables, before any macro, in a standard code
module

Public NumRows as Long
Public NumCols as long


Sub macro1(0
...
End Sub


etc.


OK, after preceding my subs with the Public declarations, I added these
lines to the Workbook_Open event:

NumRows = Range("Name_Copy").Rows.Count
NumCols = Range("Name_Copy").Columns.Count

and things seem to work after removing those lines from individual Subs in
my standard Module.

Is that the proper way to handle things?

--
David

Hi David,

You said you are new to this. It might be useful to look in the help
system of your Visual Basic Editor for the subjects "Declaring Variables" and
"Understanding the Lifetime of Variables". These will give you some insight
to the different types of varables, from where and how their values can be
accessed, and the duration of their value retention.

On reading these I think you'll see, for example, how it's important to
reload the 2 variables if ever something is done during a work session to the
range "Name_Copy" to change its number of rows or columns.

Best Regards,
Walt Weber

"David" wrote:

Bob Phillips wrote

Declare them as public variables, before any macro, in a standard code
module

Public NumRows as Long
Public NumCols as long


Sub macro1(0
...
End Sub


etc.


OK, after preceding my subs with the Public declarations, I added these
lines to the Workbook_Open event:

NumRows = Range("Name_Copy").Rows.Count
NumCols = Range("Name_Copy").Columns.Count

and things seem to work after removing those lines from individual Subs in
my standard Module.

Is that the proper way to handle things?

--
David

?B?V2FsdA==?= wrote

On reading these I think you'll see, for example, how it's important
to reload the 2 variables if ever something is done during a work
session to the range "Name_Copy" to change its number of rows or
columns.

I was unable to find anything in Help, but I don't plan to run any macros
after adding rows (columns will always be the same in this workbook)
without first saving the changes, closing and reopening the file.

You must have read my mind, though, because there *will* be occasions to
expand the number of rows, which is why I want to use rows.count rather
than a specific number of rows.

P.S. By "new to this", I meant declaring Public variables.

--
David

?B?V2FsdA==?= wrote

It might be useful to look in the help
system of your Visual Basic Editor for the subjects "Declaring
Variables" and "Understanding the Lifetime of Variables". These will
give you some insight to the different types of varables, from where
and how their values can be accessed, and the duration of their value
retention.

Oops, I was looking in Excel Help, not VB Help.

--
David

"Ed" wrote in message
...
David:

I have had only limited success trying to get Workbook,
Worksheet, and Range variables to Set in one Sub and carry over into
another. Referring to my first statement, I'm probably doing something
amiss; if so, I'm sure someone else here can give the best way.

Ed, this may be caused by decalring public variables in the workbook or
worksheet code modules, as they are not global to the project, they need the
calss module identifier when referring to them, such as Thisworkbook.myVar,
as they are essentialy class properties.

I'm back online, so I'll rejoin the discussion :-).

What you are doing looks workable (right or correct is subjective, so I
won't comment on that :-)). Just one point to pick up on though that you
mentioned. You said '... You must have read my mind, though, because there
*will* be occasions to expand the number of rows, which is why I want to use
rows.count rather than a specific number of rows ...'. Your approach does
not protect you against that. For example, say 'Name_Copy' refers to a range
A1:M20, then rows.count returns 20, and your variable will have 20 as its
value. However, say a new row gets inserted at row 10, then 'Name_Copy' will
then refer to A1:M21, but your variable will still have the value 20 in it.
QED - a problem. That is why it is sometimes better to recalculate such
values just prior to using them. Public variables can be good, but you still
need to know what is going on to use successfully.

--
HTH

Bob Phillips

"David" wrote in message
...
?B?V2FsdA==?= wrote

It might be useful to look in the help
system of your Visual Basic Editor for the subjects "Declaring
Variables" and "Understanding the Lifetime of Variables". These will
give you some insight to the different types of varables, from where
and how their values can be accessed, and the duration of their value
retention.

Oops, I was looking in Excel Help, not VB Help.

--
David

Bob: So if I have an assortment of subs in ThisWorkbook and Forms modules,
can I:
Sub SetRanges
Dim wkb As Workbook
Set wkb As MyWorkbook
Dim rng As Range
Set rng = MyWorkbook.ActiveSheet.Range("A1:Z100")
End Sub
and then use:
wkb.rng
in any other sub in the project to refer to that range?
Ed

"Bob Phillips" wrote in message
...

"Ed" wrote in message
...
David:

I have had only limited success trying to get Workbook,
Worksheet, and Range variables to Set in one Sub and carry over into
another. Referring to my first statement, I'm probably doing something
amiss; if so, I'm sure someone else here can give the best way.

Ed, this may be caused by decalring public variables in the workbook or
worksheet code modules, as they are not global to the project, they need
the
calss module identifier when referring to them, such as
Thisworkbook.myVar,
as they are essentialy class properties.

Hi Ed,

Not quite. In a code module (Not a sheet or thisworkbook module) try this:

Public wkb As Workbook, rng As Range

Sub SetRanges()
Set wkb As workbooks("MyWorkbook.xls")
Set rng = wkb .ActiveSheet.Range("A1:Z100")
End Sub

After running SetRanges you can then use either wbk or rng in any other sub
in the project to refer to MyWorkbook or that range.

If you only need to use those variables in code in the same code module, you
can use Dim vs. Public when declaring them above.

Best Regards,
Walt Weber


"Ed" wrote:

Bob: So if I have an assortment of subs in ThisWorkbook and Forms modules,
can I:
Sub SetRanges
Dim wkb As Workbook
Set wkb As MyWorkbook
Dim rng As Range
Set rng = MyWorkbook.ActiveSheet.Range("A1:Z100")
End Sub
and then use:
wkb.rng
in any other sub in the project to refer to that range?
Ed

"Bob Phillips" wrote in message
...

"Ed" wrote in message
...
David:

I have had only limited success trying to get Workbook,
Worksheet, and Range variables to Set in one Sub and carry over into
another. Referring to my first statement, I'm probably doing something
amiss; if so, I'm sure someone else here can give the best way.

Ed, this may be caused by decalring public variables in the workbook or
worksheet code modules, as they are not global to the project, they need
the
calss module identifier when referring to them, such as
Thisworkbook.myVar,
as they are essentialy class properties.

Oops!

Set wkb As workbooks("MyWorkbook.xls")

should be:
Set wkb = workbooks("MyWorkbook.xls")

Best Regards,
Walt Weber

"Walt" wrote:

Hi Ed,

Not quite. In a code module (Not a sheet or thisworkbook module) try this:

Public wkb As Workbook, rng As Range

Sub SetRanges()
Set wkb As workbooks("MyWorkbook.xls")
Set rng = wkb .ActiveSheet.Range("A1:Z100")
End Sub

After running SetRanges you can then use either wbk or rng in any other sub
in the project to refer to MyWorkbook or that range.

If you only need to use those variables in code in the same code module, you
can use Dim vs. Public when declaring them above.

Best Regards,
Walt Weber


"Ed" wrote:

Bob: So if I have an assortment of subs in ThisWorkbook and Forms modules,
can I:
Sub SetRanges
Dim wkb As Workbook
Set wkb As MyWorkbook
Dim rng As Range
Set rng = MyWorkbook.ActiveSheet.Range("A1:Z100")
End Sub
and then use:
wkb.rng
in any other sub in the project to refer to that range?
Ed

"Bob Phillips" wrote in message
...

"Ed" wrote in message
...
David:

I have had only limited success trying to get Workbook,
Worksheet, and Range variables to Set in one Sub and carry over into
another. Referring to my first statement, I'm probably doing something
amiss; if so, I'm sure someone else here can give the best way.

Ed, this may be caused by decalring public variables in the workbook or
worksheet code modules, as they are not global to the project, they need
the
calss module identifier when referring to them, such as
Thisworkbook.myVar,
as they are essentialy class properties.

Bob Phillips wrote

I'm back online, so I'll rejoin the discussion :-).

What you are doing looks workable (right or correct is subjective, so
I won't comment on that :-)). Just one point to pick up on though that
you mentioned. You said '... You must have read my mind, though,
because there *will* be occasions to expand the number of rows, which
is why I want to use rows.count rather than a specific number of rows
...'. Your approach does not protect you against that. For example,
say 'Name_Copy' refers to a range A1:M20, then rows.count returns 20,
and your variable will have 20 as its value. However, say a new row
gets inserted at row 10, then 'Name_Copy' will then refer to A1:M21,
but your variable will still have the value 20 in it. QED - a problem.
That is why it is sometimes better to recalculate such values just
prior to using them. Public variables can be good, but you still need
to know what is going on to use successfully.

So I found out. Routines bombed even after saving, closing and reopening
file after inserting rows. I've taken a different tack after reverting to
prior code.

Now...
I've discovered I can get the address of the first cell in a named range
with this: FirstCell=Range("<NamedRange").Cells(1).Address
This is useful in a .Resize line and to place text from my UserForm

Is there a way to reference the *last* cell in that same named range?
That would be useful in another Sub().

----
FirstCell = Range("Name_Copy").Cells(1).Address
LastCell = Range("Name_Copy").Cells(?).Address '<-- I want this!!
.....

--
David

LastCell = Range("NameCopy")(Range("NameCopy").Count).Address

--
HTH

Bob Phillips

"David" wrote in message
...
Bob Phillips wrote

I'm back online, so I'll rejoin the discussion :-).

What you are doing looks workable (right or correct is subjective, so
I won't comment on that :-)). Just one point to pick up on though that
you mentioned. You said '... You must have read my mind, though,
because there *will* be occasions to expand the number of rows, which
is why I want to use rows.count rather than a specific number of rows
...'. Your approach does not protect you against that. For example,
say 'Name_Copy' refers to a range A1:M20, then rows.count returns 20,
and your variable will have 20 as its value. However, say a new row
gets inserted at row 10, then 'Name_Copy' will then refer to A1:M21,
but your variable will still have the value 20 in it. QED - a problem.
That is why it is sometimes better to recalculate such values just
prior to using them. Public variables can be good, but you still need
to know what is going on to use successfully.

So I found out. Routines bombed even after saving, closing and reopening
file after inserting rows. I've taken a different tack after reverting to
prior code.

Now...
I've discovered I can get the address of the first cell in a named range
with this: FirstCell=Range("<NamedRange").Cells(1).Address
This is useful in a .Resize line and to place text from my UserForm

Is there a way to reference the *last* cell in that same named range?
That would be useful in another Sub().

----
FirstCell = Range("Name_Copy").Cells(1).Address
LastCell = Range("Name_Copy").Cells(?).Address '<-- I want this!!
....

--
David

Ed,

absolutely not. In this case, wkb is local to that procedure only, and is
not known to any other procedure.

One thing you should do is always use Option Explicit, this will flag
undeclared variables. You can force it by setting the Require Variable
Declaration checkbox in ToolsOptionsEditor.

--
HTH

Bob Phillips

"Ed" wrote in message
...
Bob: So if I have an assortment of subs in ThisWorkbook and Forms
modules,
can I:
Sub SetRanges
Dim wkb As Workbook
Set wkb As MyWorkbook
Dim rng As Range
Set rng = MyWorkbook.ActiveSheet.Range("A1:Z100")
End Sub
and then use:
wkb.rng
in any other sub in the project to refer to that range?
Ed

"Bob Phillips" wrote in message
...

"Ed" wrote in message
...
David:

I have had only limited success trying to get Workbook,
Worksheet, and Range variables to Set in one Sub and carry over into
another. Referring to my first statement, I'm probably doing
something
amiss; if so, I'm sure someone else here can give the best way.

Ed, this may be caused by decalring public variables in the workbook or
worksheet code modules, as they are not global to the project, they need
the
calss module identifier when referring to them, such as
Thisworkbook.myVar,
as they are essentialy class properties.

Bob Phillips wrote

LastCell = Range("NameCopy")(Range("NameCopy").Count).Address

Perfect! Thanks

--
David

I'm thinking of a situation in which code in a workbook calls forms
contained in the same workbook, and may open other workbooks that might have
code in them. (I'm working with XL 2000 and XL XP.) I generally declare my
variables at the top of Module1, before the first Sub. But I find I'm
having to Set object variables in every Sub procedure -I'd like to Set them
once at the beginning (perhaps in Form_Initialize or in the first Sub
called) and forget about them.

From what you and Walt said, I looked through Help at Public and Option
Private Module. I'm getting the idea that if I used the Option Private
Module statement, and declared the variables as Public at the top of the
module before any Sub, they would be "live" for any and all procedures
contained within that workbook's VBAProject, whether module or Form code.
But any code in another workbook would not see them, and so would not cause
confusing interactions. And the Option Explicit would tell me in any Sub in
the Project whether that variable was declared, which it should be because
it's Public.

Yes? No?
Ed

"Bob Phillips" wrote in message
...
Ed,

absolutely not. In this case, wkb is local to that procedure only, and is
not known to any other procedure.

One thing you should do is always use Option Explicit, this will flag
undeclared variables. You can force it by setting the Require Variable
Declaration checkbox in ToolsOptionsEditor.

--
HTH

Bob Phillips

"Ed" wrote in message
...
Bob: So if I have an assortment of subs in ThisWorkbook and Forms
modules,
can I:
Sub SetRanges
Dim wkb As Workbook
Set wkb As MyWorkbook
Dim rng As Range
Set rng = MyWorkbook.ActiveSheet.Range("A1:Z100")
End Sub
and then use:
wkb.rng
in any other sub in the project to refer to that range?
Ed

"Bob Phillips" wrote in message
...

"Ed" wrote in message
...
David:

I have had only limited success trying to get Workbook,
Worksheet, and Range variables to Set in one Sub and carry over into
another. Referring to my first statement, I'm probably doing
something
amiss; if so, I'm sure someone else here can give the best way.

Ed, this may be caused by decalring public variables in the workbook
or
worksheet code modules, as they are not global to the project, they
need
the
calss module identifier when referring to them, such as
Thisworkbook.myVar,
as they are essentialy class properties.