#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 88
Default Scope of Variables

I pose a question for a better understanding of variables within my
spreadsheet.
I have declared within "ThisWorkbook"

Public Sub Workbook_Open()
Public ver, Report, Week, Period
End Sub

Within Module 1, Sub Report1
I have given the variabes values
Ver = 4
Report = "Report Week 1.xls"
Week = 1
Period = 1

call Reports

Sub Reports

Routine that calls upon the variables mentioned above,
eg. Workbooks.Add
ActiveWorkbook.SaveAs Filename:="D:\Store Returns\Reports\" &
Report, _
FileFormat:=xlNormal, CreateBackup:=False

More code

Windows("Poor Store Returns Ver " & ver & " 2003.xls").Activate


but when run the variables are empty. Can anyone please help me to better
understand where I'm going wrong.

Regards
Lee


  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default Scope of Variables

Try this,

Paste into a module and you'll see how the values for the variable are
passed to the testvariables sub.

Public ver As Long, Report As String, Week As Long, Period As Long
Sub SetVariables()
ver = 4
Report = "Report Week 1.xls"
Week = 1
Period = 1
testvariables
End Sub

Sub testvariables()
MsgBox ver
MsgBox Report
MsgBox Week
MsgBox Period
End Sub

Mike

"leerem" wrote:

I pose a question for a better understanding of variables within my
spreadsheet.
I have declared within "ThisWorkbook"

Public Sub Workbook_Open()
Public ver, Report, Week, Period
End Sub

Within Module 1, Sub Report1
I have given the variabes values
Ver = 4
Report = "Report Week 1.xls"
Week = 1
Period = 1

call Reports

Sub Reports

Routine that calls upon the variables mentioned above,
eg. Workbooks.Add
ActiveWorkbook.SaveAs Filename:="D:\Store Returns\Reports\" &
Report, _
FileFormat:=xlNormal, CreateBackup:=False

More code

Windows("Poor Store Returns Ver " & ver & " 2003.xls").Activate


but when run the variables are empty. Can anyone please help me to better
understand where I'm going wrong.

Regards
Lee


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 88
Default Scope of Variables

I pasted the code into Module 1 which placed into the Declarations section
Public ver as long ect

I ran the testvariables and all values came back as either blank or zero

Regards


"Mike H" wrote:

Try this,

Paste into a module and you'll see how the values for the variable are
passed to the testvariables sub.

Public ver As Long, Report As String, Week As Long, Period As Long
Sub SetVariables()
ver = 4
Report = "Report Week 1.xls"
Week = 1
Period = 1
testvariables
End Sub

Sub testvariables()
MsgBox ver
MsgBox Report
MsgBox Week
MsgBox Period
End Sub

Mike

"leerem" wrote:

I pose a question for a better understanding of variables within my
spreadsheet.
I have declared within "ThisWorkbook"

Public Sub Workbook_Open()
Public ver, Report, Week, Period
End Sub

Within Module 1, Sub Report1
I have given the variabes values
Ver = 4
Report = "Report Week 1.xls"
Week = 1
Period = 1

call Reports

Sub Reports

Routine that calls upon the variables mentioned above,
eg. Workbooks.Add
ActiveWorkbook.SaveAs Filename:="D:\Store Returns\Reports\" &
Report, _
FileFormat:=xlNormal, CreateBackup:=False

More code

Windows("Poor Store Returns Ver " & ver & " 2003.xls").Activate


but when run the variables are empty. Can anyone please help me to better
understand where I'm going wrong.

Regards
Lee


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 88
Default Scope of Variables

My mistake when I run the Setvariables routine the message boxes work,
however how can I set the variables in one subroutine and access from another
if they are in separte modules

"leerem" wrote:

I pasted the code into Module 1 which placed into the Declarations section
Public ver as long ect

I ran the testvariables and all values came back as either blank or zero

Regards


"Mike H" wrote:

Try this,

Paste into a module and you'll see how the values for the variable are
passed to the testvariables sub.

Public ver As Long, Report As String, Week As Long, Period As Long
Sub SetVariables()
ver = 4
Report = "Report Week 1.xls"
Week = 1
Period = 1
testvariables
End Sub

Sub testvariables()
MsgBox ver
MsgBox Report
MsgBox Week
MsgBox Period
End Sub

Mike

"leerem" wrote:

I pose a question for a better understanding of variables within my
spreadsheet.
I have declared within "ThisWorkbook"

Public Sub Workbook_Open()
Public ver, Report, Week, Period
End Sub

Within Module 1, Sub Report1
I have given the variabes values
Ver = 4
Report = "Report Week 1.xls"
Week = 1
Period = 1

call Reports

Sub Reports

Routine that calls upon the variables mentioned above,
eg. Workbooks.Add
ActiveWorkbook.SaveAs Filename:="D:\Store Returns\Reports\" &
Report, _
FileFormat:=xlNormal, CreateBackup:=False

More code

Windows("Poor Store Returns Ver " & ver & " 2003.xls").Activate


but when run the variables are empty. Can anyone please help me to better
understand where I'm going wrong.

Regards
Lee


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default Scope of Variables

leerem,

Once the variables are declared public in this way they can be passed
between different modules. Try it, put the testvariables() sub I gave you in
a new module and you'll find it works.

Mike

"leerem" wrote:

My mistake when I run the Setvariables routine the message boxes work,
however how can I set the variables in one subroutine and access from another
if they are in separte modules

"leerem" wrote:

I pasted the code into Module 1 which placed into the Declarations section
Public ver as long ect

I ran the testvariables and all values came back as either blank or zero

Regards


"Mike H" wrote:

Try this,

Paste into a module and you'll see how the values for the variable are
passed to the testvariables sub.

Public ver As Long, Report As String, Week As Long, Period As Long
Sub SetVariables()
ver = 4
Report = "Report Week 1.xls"
Week = 1
Period = 1
testvariables
End Sub

Sub testvariables()
MsgBox ver
MsgBox Report
MsgBox Week
MsgBox Period
End Sub

Mike

"leerem" wrote:

I pose a question for a better understanding of variables within my
spreadsheet.
I have declared within "ThisWorkbook"

Public Sub Workbook_Open()
Public ver, Report, Week, Period
End Sub

Within Module 1, Sub Report1
I have given the variabes values
Ver = 4
Report = "Report Week 1.xls"
Week = 1
Period = 1

call Reports

Sub Reports

Routine that calls upon the variables mentioned above,
eg. Workbooks.Add
ActiveWorkbook.SaveAs Filename:="D:\Store Returns\Reports\" &
Report, _
FileFormat:=xlNormal, CreateBackup:=False

More code

Windows("Poor Store Returns Ver " & ver & " 2003.xls").Activate


but when run the variables are empty. Can anyone please help me to better
understand where I'm going wrong.

Regards
Lee


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I change a Name scope from local to Global? Henry Excel Discussion (Misc queries) 7 April 9th 10 10:36 PM
How can I change a Name scope from local to Global? Henry Excel Discussion (Misc queries) 0 September 23rd 08 07:24 PM
Not at all clear on use of variables and/or object variables JMay-Rke Excel Discussion (Misc queries) 11 July 4th 08 06:36 PM
Scope priviledges for locked/unlocked cells Jerry W. Lewis Excel Discussion (Misc queries) 2 July 30th 07 04:06 PM
Scope of range names Printing of Range Name References Excel Discussion (Misc queries) 1 October 26th 05 09:37 PM


All times are GMT +1. The time now is 01:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"