 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Word / Programming / August 2007Macro 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? > Can you establish variables and have them accessible to all macros within > a [quoted text clipped - 19 lines] > > 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\"  SignatureRegards Jonathan West - Word MVP www.intelligentdocuments.co.uk Please reply to the newsgroup So Johnathan...what is the syntax to call to these public variables. > > Can you establish variables and have them accessible to all macros within > > a [quoted text clipped - 42 lines] > Public Const CompletedFormsPath As String = > "G:\GS-220\SLATE\CORRECTION_SHEETS\CompletedForms\" > 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. SignatureRegards Jonathan West - Word MVP www.intelligentdocuments.co.uk Please reply to the newsgroup 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. > > Can you establish variables and have them accessible to all macros within > > a [quoted text clipped - 42 lines] > Public Const CompletedFormsPath As String = > "G:\GS-220\SLATE\CORRECTION_SHEETS\CompletedForms\" > Johnathan, > > I get an 'Invalid Argument' when I use the constant: > Public Sub DefinePaths() > [quoted text clipped - 5 lines] > "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() > [quoted text clipped - 11 lines] > 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 [quoted text clipped - 4 lines] > > You would think this shouldn't be as hard as it is. Its easy once you know how! :-) SignatureRegards Jonathan West - Word MVP www.intelligentdocuments.co.uk Please reply to the newsgroup |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.