Macro variables availability

[Robert_L_Ross]

Can you establish variables and have them accessible to all macros within a
document - if you set them up in a .dot file?

I'd like to have one location to store the following variables:
Dim DataSourceFile As String
Dim DocumentTemplateFile As String
Dim CompletedFormsPath As String

DataSourceFile = "G:\GS-220\SLATE\CORRECTION_SHEETS\SCHOOL_LIST.xls"
DocumentTemplateFile = "G:\GS-220\SLATE\CORRECTION_SHEETS\Correction
Sheet.dot"
CompletedFormsPath = "G:\GS-220\SLATE\CORRECTION_SHEETS\CompletedForms\"

I'd like to have them accessible to the entire module, userforms, etc. This
way, if we move the documents around in the network (or copy them for that
matter) we only have to change the paths in one area (and I can even set up a
userform or macro to adjust them as needed).

Is this possible?

 

[Jonathan West]

Can you establish variables and have them accessible to all macros within
a
document - if you set them up in a .dot file?

I'd like to have one location to store the following variables:
Dim DataSourceFile As String
Dim DocumentTemplateFile As String
Dim CompletedFormsPath As String

DataSourceFile = "G:\GS-220\SLATE\CORRECTION_SHEETS\SCHOOL_LIST.xls"
DocumentTemplateFile = "G:\GS-220\SLATE\CORRECTION_SHEETS\Correction
Sheet.dot"
CompletedFormsPath =
"G:\GS-220\SLATE\CORRECTION_SHEETS\CompletedForms\"

I'd like to have them accessible to the entire module, userforms, etc.
This
way, if we move the documents around in the network (or copy them for that
matter) we only have to change the paths in one area (and I can even set
up a
userform or macro to adjust them as needed).

Is this possible?

Yes. Plac the declarations before the first routine in a module. If you
declare a variable using the keyword Private, the variable is available to
all routines in that module.

Private DataSourceFile As String

If you declare it with the Public keyword, it is available to all routines
in all modules.

Public DataSourceFile As String


Actually, for the purpose you describe, you are even better off using a
constant. Constants can also be module-level or global, so could be declared
like this and be available to all modules.

Public Const DataSourceFile As String =
"G:\GS-220\SLATE\CORRECTION_SHEETS\SCHOOL_LIST.xls"
Public Const DocumentTemplateFile As String =
"G:\GS-220\SLATE\CORRECTION_SHEETS\Correction
Sheet.dot"
Public Const CompletedFormsPath As String =
"G:\GS-220\SLATE\CORRECTION_SHEETS\CompletedForms\"

 

[Robert_L_Ross]

So Johnathan...what is the syntax to call to these public variables.

 

[Robert_L_Ross]

Johnathan,

I get an 'Invalid Argument' when I use the constant:
Public Sub DefinePaths()

Public Const DataSourceFile As String =
"G:\GS-220\SLATE\CORRECTION_SHEETS\SCHOOL_LIST.xls"
Public Const DocumentTemplateFile As String =
"G:\GS-220\SLATE\CORRECTION_SHEETS\Correction Sheet.dot"
Public Const CompletedFormsPath As String =
"G:\GS-220\SLATE\CORRECTION_SHEETS\CompletedForms\"
End Sub

Sub AUTONEW()

Load UserForm1

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long

' Open the database
Set db = opendatabase(DataSourceFile, False, False, "Excel 8.0")
....

I'm wondering if I put this in the document properties as a custom property,
would that work? I was able to get the DataSourceFile and CompletedFormsPath
to work that way, but not the DocumentTemplateFile. I thought maybe because
the path/file name has a space in it, but I modified the file (putting in an
underscore instead of the space) and it still didn't work.

You would think this shouldn't be as hard as it is.

 

[Jonathan West]

So Johnathan...what is the syntax to call to these public variables.

Exactly the same as to call variables or constants declared within a
rotuine.

 

[Jonathan West]

Johnathan,

I get an 'Invalid Argument' when I use the constant:

Public Sub DefinePaths()

Public Const DataSourceFile As String =
"G:\GS-220\SLATE\CORRECTION_SHEETS\SCHOOL_LIST.xls"
Public Const DocumentTemplateFile As String =
"G:\GS-220\SLATE\CORRECTION_SHEETS\Correction Sheet.dot"
Public Const CompletedFormsPath As String =
"G:\GS-220\SLATE\CORRECTION_SHEETS\CompletedForms\"
End Sub

You missed part of what I said. Public declarations are not placed inside
any routine. Place the declarations *before the first routine* in a module.

Sub AUTONEW()

Load UserForm1

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long

' Open the database
Set db = opendatabase(DataSourceFile, False, False, "Excel 8.0")
...

I'm wondering if I put this in the document properties as a custom
property,
would that work?

It would work, but there is no particular need to do it that way. Just
declare them outside routines.

I was able to get the DataSourceFile and CompletedFormsPath
to work that way, but not the DocumentTemplateFile. I thought maybe
because
the path/file name has a space in it, but I modified the file (putting in
an
underscore instead of the space) and it still didn't work.

You would think this shouldn't be as hard as it is.

Its easy once you know how!