Help Please - Global Vars

[graviz]

I'm trying to improve my programming skills and I decided to try and use a global variable. When I run my code I receive the error "Invalid outside procedure" I know it must be something simple but I just can't figure it out.

Option Compare Database
Dim ExcelTemplate As String
ExcelTemplate = "\\Mer2-corpfs1\dnsc\Resource Management\Auto Reports\JBR\JBR Template.xls"

Public Function DoAll()
'Create connection to Database
Dim cnn As ADODB.Connection
Dim MyRecordset As New ADODB.Recordset
Dim MySQL As String
Dim MySheetPath As String
'Variables to refer to Excel and Objects
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
Set cnn = CurrentProject.Connection
MyRecordset.ActiveConnection = cnn
'SQL statements to extract the data required for the report - From a query
MySQL = "SELECT * From "
MySQL = MySQL & "05_Output_JBR_All_Types"
MyRecordset.Open MySQL
' Tell it location of actual Excel file
MySheetPath = ExcelTemplate
'Open Excel and the workbook
Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(MySheetPath)
'Make sure excel is visible on the screen
Xl.Visible = True
XlBook.Windows(1).Visible = True
'Define the sheet in the Workbook as XlSheet
Set XlSheet = XlBook.Worksheets("Actual")
' Create column names in the spreadsheet
' For iCol = 1 To MyRecordset.Fields.Count
' XlSheet.Cells(2, iCol).Value = MyRecordset.Fields(iCol - 1).Name
' Next
' 'Insert the Recordset in the excel sheet starting at specified cell
XlSheet.Range("A2").CopyFromRecordset MyRecordset
'Clean up and end with worksheet visible on the screen
MyRecordset.Close
Set cnn = Nothing
Set Xl = Nothing
XlBook.Save
XlBook.Close
Set XlBook = Nothing
Set XlSheet = Nothing
End Function

 

[gemma-the-husky]

this line

ExcelTemplate = "\\Mer2-corpfs1\dnsc\Resource Management\Auto Reports\JBR\JBR Template.xls"

is not WITHIN a procedure (sub or function)

if you want it hard coded just do


CONST ExcelTemplate = "\\Mer2-corpfs1\dnsc\Resource Management\Auto Reports\JBR\JBR Template.xls"

instead of the dim and assignment

other wise put the assignment INSIDE the function

 

[graviz]

this line

ExcelTemplate = "\\Mer2-corpfs1\dnsc\Resource Management\Auto Reports\JBR\JBR Template.xls"

is not WITHIN a procedure (sub or function)

if you want it hard coded just do


CONST ExcelTemplate = "\\Mer2-corpfs1\dnsc\Resource Management\Auto Reports\JBR\JBR Template.xls"

instead of the dim and assignment

other wise put the assignment INSIDE the function

Thank you very much! This is just what I needed.

 

[gemma-the-husky]

just one other thing

in a module, if you dim a variable, the variable is local to that module.

you can retrieve the value with a function

so

'this variable cannot be accessed outside the module
dim myvar as long

'but it can be read by a function
function readmyvar() as long
readmyvar = nz(myvar,0)
end function

'and it can be set by another function or sub
sub setmyvar(newval as long)
myvar = newval
end function

this may seem longwinded, but it adds robustness to an app - as you cannot inadvertently modify a global variable by directyl referring to it.


alternatively, you can make a variable (or a const) PUBLIC in scope, simply by using the word public

'this variable can now be accessed outside the module
public myvar as long

'or with your const
public const mypath = "E: /whatever"

'now the variable can be set directly, from anywhere in the whole of your app
myvar = newvalue

the former is a bit more long winded, but is similar to what happens inside classes - where you have to use property get, and property let statements to achieve similar functionality within classes - as the variables inside a class are private to the class (I think).

 

[graviz]

just one other thing

in a module, if you dim a variable, the variable is local to that module.

you can retrieve the value with a function

so

'this variable cannot be accessed outside the module
dim myvar as long
 
'but it can be read by a function
function readmyvar() as long
readmyvar = nz(myvar,0)
end function
 
'and it can be set by another function or sub
sub setmyvar(newval as long)
myvar = newval
end function

this may seem longwinded, but it adds robustness to an app - as you cannot inadvertently modify a global variable by directyl referring to it.


alternatively, you can make a variable (or a const) PUBLIC in scope, simply by using the word public

'this variable can now be accessed outside the module
public myvar as long
 
'or with your const
public const mypath = "E: /whatever"
 
'now the variable can be set directly, from anywhere in the whole of your app
myvar = newvalue

the former is a bit more long winded, but is similar to what happens inside classes - where you have to use property get, and property let statements to achieve similar functionality within classes - as the variables inside a class are private to the class (I think).

It appears I have much to learn. The main reason I wanted to set it up as I did was the path will be called in many different places and I would like just to have to change one line versus 20+.

 

[gemma-the-husky]

well, you are doing it the right way - just set it as a const, but make it a public const - then you can "see" it from anywhere

the "hiding" of variables is a technical point really - although if you have a lot of them, you need to keep track of exactly what you use them all for.

in time though, you may decide that rather than having to hard code a file path - you want to be able to change the path, without having to get at the code.

then having a dim is necessary, so that you can change it dynamically at run-time.

and from there you probably start by typing in the full path you need - but then you realise it may be more user-friendly to have a file picker, that can let you select the file you want interactively.