0

I need to store a date as a global variable. I thought I was doing this but the watch shows it resetting after the sub ends. I've looked at countless global variable articles and responses to questions, nothing seemed to work.

So, just how do I stop this from happening in my VBA code as I need to call the variable in later subs? 

Option Explicit

Public strDate As Variant

Sub SelectDate()
    strDate = InputBox("Select Report Date (mm/dd/yy)", "Report Date", Format(Now() - 1, "mm/dd/yy"))

    If IsDate(strDate) Then
        strDate = Format(CDate(strDate), "mm/dd/yy")
    Else
        MsgBox "Valid Date Format Required"
    End If
End Sub
0m3r
  • 12,286
  • 15
  • 35
  • 71
BBA Hunter
  • 1
  • 3
  • change `Dim strDate As Variant` to `Public strDate as Variant` That will make it global and stick after the routine executes. – Scott Holtzman Nov 13 '18 at 18:30
  • I remember Mathieu Guindon had a really good answer a month or two ago about scope which would be worth posting here. looking for it... – Marcucciboy2 Nov 13 '18 at 18:42
  • @Scott Holtzman : Nope, already tried that. I tried using global, public, and anything else I could try. I wonder is the issue is 64bit vba? It's the whole reason I am rewriting the spreadsheet, doesn't work correctly in 64 bit office. – BBA Hunter Nov 13 '18 at 18:43
  • Usually class-level variables only lose their values when you surprise Excel more than you should ([a nasty example](https://stackoverflow.com/q/6089178/11683)). Can you reproduce the problem in a blank file? – GSerg Nov 13 '18 at 18:48
  • @GSerg : Yes, if you have excel 64 bit, add a button, copy the code above into it and watch the strDate variable. Use Public, Dim, Etc...for declaring the variable, doesn't matter. Assign it to "This.Workbook" or to a new module, doesn't matter. As soon as you F8 through the steps, the variable resets at the 'End Sub' – BBA Hunter Nov 13 '18 at 18:55
  • Hmm it doesn't apply as well as I remembered, but it might help? https://stackoverflow.com/questions/51898947/create-and-assign-variables-on-workbook-open-pass-it-to-worksheet-change/51899356#51899356 – Marcucciboy2 Nov 13 '18 at 18:56
  • It's tricky because according to [declaring variables in VBA7](https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/declaring-variables) it should work the same. – Scott Holtzman Nov 13 '18 at 19:02
  • *Is this* in a class module (Worksheet or Workbook)? – Comintern Nov 13 '18 at 19:04
  • 2
    @BBAHunter It goes to "Out of context" in the Watch window, but it actually retains its value - it's just that the Watch window cannot show it anymore because the sub has ended. If you `? strDate` from the Immediate window, it's still there. – GSerg Nov 13 '18 at 19:04
  • @GSerg : If I understand you correctly, the variable is retaining it's value but in a different sub, the watch window won't show it. If that is the case, is there a particular method to call the value into a different sub? Thanks! – BBA Hunter Nov 13 '18 at 19:10
  • A `Dim` statement *can't* possibly declare anything *global*, by definition. Also whether a public field is a global variable or a public instance field (i.e. instance state) depends on the type of module it's declared in: please [edit] your post with your answer to @Comintern's question - is that code written in a *standard module* (.bas) or not? – Mathieu Guindon Nov 13 '18 at 19:58
  • @ Mathieu Guindon : Edited to show I did in fact try "Public" instead of "Dim". The code is stored in the workbook itself. At this point I am thinking I need to figure out how to call the global sub's variable data in each other sub I need to use the data in. If this is the case, I will know how to be more helpful to anyone else that may have the same questions. – BBA Hunter Nov 13 '18 at 20:21
  • 1
    @BBAHunter The variable's value is available for all subs. You just can't see that in the Watch window because there is no code currently executing, and the Watch window displays "" in that case. The variable is fine, you don't need to do anything. – GSerg Nov 13 '18 at 20:43

0 Answers0