Macro variables availability

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
Wed Aug 01 18:41:05 CDT 2007


"Robert_L_Ross" <RobertLRoss@discussions.microsoft.com> wrote in message
news:3E9E3D67-B463-4ED4-BD00-45C948E0A191@microsoft.com...
> 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\"

--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup

RobertLRoss
Thu Aug 02 10:56:02 CDT 2007

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

"Jonathan West" wrote:

>
> "Robert_L_Ross" <RobertLRoss@discussions.microsoft.com> wrote in message
> news:3E9E3D67-B463-4ED4-BD00-45C948E0A191@microsoft.com...
> > 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\"
>
> --
> Regards
> Jonathan West - Word MVP
> www.intelligentdocuments.co.uk
> Please reply to the newsgroup
>
>
>

RobertLRoss
Thu Aug 02 11:14:01 CDT 2007

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" wrote:

>
> "Robert_L_Ross" <RobertLRoss@discussions.microsoft.com> wrote in message
> news:3E9E3D67-B463-4ED4-BD00-45C948E0A191@microsoft.com...
> > 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\"
>
> --
> Regards
> Jonathan West - Word MVP
> www.intelligentdocuments.co.uk
> Please reply to the newsgroup
>
>
>

Jonathan
Thu Aug 02 11:46:31 CDT 2007


"Robert_L_Ross" <RobertLRoss@discussions.microsoft.com> wrote in message
news:5F6BC927-2BA8-42FC-B9DB-C295B84292F7@microsoft.com...
> 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! :-)


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup

Jonathan
Thu Aug 02 11:42:51 CDT 2007


"Robert_L_Ross" <RobertLRoss@discussions.microsoft.com> wrote in message
news:40721BB5-8304-4641-B219-B51DD6BB33AF@microsoft.com...
> 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.


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup