Store global variable in Excel even when closed

I have a question and I don't know if it has an answer.

What I would like to do is track the usage of a macro inside a global variable for an entire day. I want this macro to only be capable of running once a day. I choose if I run it in the morning, noon or evening.

So for example:

Today we are on the 13/01/2022. In the morning I ran this macro and closed the workbook. I want to not be able to run this macro before the 14th (a different day), even if I reopen the Workbook on the 13th in the afternoon, I press the macro button and it won't run.

So I want this macro to check if it was run today and then if the answer is yes to not run, but if the answer is no to run. Can this be done ? I thought about creating a global variable was_run, but how can I store its value even when the workbook is closed and reopen again ? Is this possible ?

Thank you for your help !

Solution 1:

Please, try the next way:

Sub myMacroRunningOnlyOncePerDay()
  Const myApp As String = "My Daily Variable", Sett As String = "Settings"
  Const strDate As String = "myDailyValue"
    
   Dim RegValue As String, NoRun As Boolean
   RegValue = GetSetting(myApp, Sett, strDate, "No value")
   If RegValue <> "No value" Then
        If IsNumeric(RegValue) Then
            If CLng(RegValue) = CLng(Date) Then NoRun = True
        End If
   End If
   If NoRun Then Exit Sub
   SaveSetting myApp, Sett, strDate, CStr(CLng(Date))
   MsgBox "It runs..."
End Sub

It should be good to place the constants (Registry keys) on top of the module (in the declarations area). In this way you can use them from different Subs.