public variable not being recognized in separate workbook module?

Question

I'm trying to declare a public variable strFileDate that I would like to store an input box value to be later referenced when another workbook (separate wb) is opened and a macro is run within that new wb. I declare a public variable as so:

Public strFileDate as string 
Option Explicit
Sub Update_Data()
  Dim strFileDate As String: strFileDate = InputBox("Enter Folder Date (mm.dd.yy)", Default:=Format("mm.dd.yy"))
  ...
end sub

Option Explicit
Sub Open_Separate_File    
  Dim basepath as string: basepath = "C:\...."
  Dim strFileName as string: strFileName = "test_file_"
  Thisworkbook.SaveCopyAs basepath & strFileName & strFileDate & ".xlsx" 
End Sub

whenever I open up the workbook containing Open_Separate_File, it says that strFileDate is not defined, and I assume it's due to the Option Explicit label at the top of both modules. Is there a way around this without omitting Option Explicit?

I would give this a read; https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/understanding-scope-and-visibility — BigBen, Mar 04 '20 at 16:01
@BigBen it's saying that non-private variable declaration is automatically in the public scope, so is it `option explicit` causing this error? In that article, I don't see that declaration — NidenK, Mar 04 '20 at 16:03
"If you declare a module-level variable as public, it's available to all procedures in the project." Emphasis on "*in the project*." — BigBen, Mar 04 '20 at 16:04
@BigBen ah, gotcha, missed that part. So since this variable is trying to be declared in a separate workbook module, I need to declare it, again? Only reason why I am trying to avoid this, is that I don't want the user to have to type in a date in an input box, twice — NidenK, Mar 04 '20 at 16:05
@bigben would I just declare it like `Global strFileDate as string`? — NidenK, Mar 04 '20 at 16:07
[This](https://stackoverflow.com/questions/32046869/excel-vba-workbook-level-variable-declaration) may be helpful, but I would not go this route myself. — BigBen, Mar 04 '20 at 16:10
@BigBen I went this route, but its still not functioning. I may just be SOL and have the user input the date, twice — NidenK, Mar 04 '20 at 16:12

score 2 · Accepted Answer · answered Mar 04 '20 at 16:30

2

In general, you can't rely on variables retaining their value longer than a macro's run-time. All sorts of Excel actions can clear saved variables, such as saving a workbook, and you'll never be able to access them across workbooks without explicitly using get/set macros (which might not work anyway, due to the variable values possibly getting cleared).

The best way to retain this sort of data is with the SaveSetting and GetSetting functions.

In your first workbook, you could save the user-entered value saved, with something like this:

Dim strFileDate As String
strFileDate = InputBox("Enter Folder Date (mm.dd.yy)", Default:=Format("mm.dd.yy"))
SaveSetting "ExampleAppName", "ExampleSectionName", "FileDate", strFileDate

In your second workbook, you would access that value, with something like this:

Dim strFileDate As String
strFileDate = GetSetting("ExampleAppName", "ExampleSectionName", "FileDate")

If you want to clean it up after you're finished running whatever's in the second workbook, you would delete it with:

DeleteSetting "ExampleAppName", "ExampleSectionName", "FileDate"

answered Mar 04 '20 at 16:30

Josh Eller

2,045
6
11

when you say `ExampleAppName`, `ExampleSectionName`, and `FileDate`, do you mean `workbook_name`, `module name`, and I'm still confused about `FileDate`? – NidenK Mar 04 '20 at 16:37
Good approach but, a) what if the user doesn't have access to write to the registry and b) two people try to access and save at the same time? – Kostas K. Mar 04 '20 at 16:45
@NidenK Those are just examples, you can use whatever strings you'd like (generally, it would be something descriptive of what your app is called). It just has to be the exact same set of strings for both setting/getting the data. `FileDate` can also be any string you want, it's just a description of what data you're storing. – Josh Eller Mar 04 '20 at 17:23
@KostasK. If you're running this on Windows, you just need permissions to read/write `HKEY_CURRENT_USER`. I've never seen a group permissions so restrictive to disallow that, seeing as many Windows applications rely on it. In terms of two people doing it at the same time, it's specific to the current user on a given machine; as far as I know, Windows doesn't ever let you have a user logged onto the same machine twice. – Josh Eller Mar 04 '20 at 17:28
@JoshEller I added in that stuff, and I'm still getting a `variable not defined` error when the other workbook opens and the module is about to run; this brings me back to my original problem – NidenK Mar 04 '20 at 17:33
@NidenK Well, what variable isn't defined? – Josh Eller Mar 04 '20 at 17:33
@JoshEller `strFileDate` in the workbook module that I'm calling upon (the second workbook) – NidenK Mar 04 '20 at 17:37
@NidenK Add `Dim strFileDate as String` then, as shown in the answer. If you're using that variable in only one sub, put it at the top of that sub. If you're using it across multiple subs, put it at the top of the module (below `Option Explicit`). – Josh Eller Mar 04 '20 at 17:40
that did it, I just needed to place `strFileDate` in between `Option Explicit` and the `Sub` Thanks a bunch! – NidenK Mar 04 '20 at 17:49

public variable not being recognized in separate workbook module?

1 Answers1