When did I last work on this Excel Program?

[Mackeral]

I wanted an easy and quick way to remember via VBA when I last worked on a Spread Sheet, and this is what I came up with.

Function D(Optional Arg = "")
    ' Most Recent Date Updated. _
        IF Arg = "" Then Display date, or _
        if ARG <> "" Then set to today's date.
    ' 3/15/21 Created. WML
    
    If Arg = "" Then
        D = Name_Get("Data_Date_Updated")
        D = Format(Date + Time, "mm/dd/yy hh:ss")
    Else
        D = Format(Date + Time, "mm/dd/yy hh:ss")
        Call Name_Put("Data_Date_Updated", D)
    End If
    
End Function ' D

Function Name_Get(Name_To_Get, Optional Output_Named_Value)
    ' Get a Value from NAMES.
    ' 11/12/09 Added Name_Verify Call.
    ' 4/30/12 Added "Optional_Value" to call. WML
    ' 8/9/13 Changed to Select. WML
    ' 8/30/13 Changed Select to UCase(Name_Get). Wml
    ' 6/26/15 Added Strip_Quotes call & took out Boolean testing. WML
    ' 2/3/19 Test for "Name_to_Get" = "". WML
    
     Prog = "Name_Get"
    
    If Not Name_Verify(Name_To_Get) Then
        If Len(Name_To_Get) Then
            Call Msg_Err(Prog, "Name is not currently identified", Name_To_Get, True)
        Else
            Msg = "No NAME specified!"
            Call Msg_Err(Prog, Msg)
        End If
        
    
    Else
        ' So Decode what you get.
        Name_Get = Names(Name_To_Get).Value
        Name_Get = Strip_Quotes(Mid(Name_Get, 2, 99))
        
        If False Then
            Select Case UCase(Name_Get)
            
                Case "=""TRUE""", "=TRUE"
                    Name_Get = True
                    
                Case "=""FALSE""", "=FALSE"
                    Name_Get = False
                    
                Case Empty_String
                    Name_Get = ""
                      
            End Select
        End If
    
    End If
 
End Function ' Name_Get()

Sub Name_Put(ByVal Name_Label, ByVal Name_Value)
    ' Put a Value into NAMES.
    ' Added call to verify Name_Label. 11/13/09 WML
    ' 11/27/14 Changed. WML
    ' 1/25/19 Changed to "RefersTo:="=""""". WML
    ' 10/11/19 Added replacement of " " with "_" in "Name_toLookup". WML
    ' 5/3/20 Replace "New_Value" if it is empty with """". WML
    ' 1/13/21 Added ByVal to "Name_Label". WML
    
    Call Substitute(Name_Label, " ", "_") ' <-- Added 10/11/19
    If New_Value = "" Then New_Value = """"    ' <-- 5/3/20
    
    If Not Name_Verify(Name_Label) Then
        ActiveWorkbook.Names.Add Name:=Look_Up, RefersTo:="="""""
    End If
    
    If Name_Value = "" Then Name_Value = """"""
    ActiveWorkbook.Names(Name_Label).Value = Name_Value
    
End Sub ' Name_Put

Function Name_Verify(Name_To_Verify) As Boolean
    ' Make sure "Name_To_Verify" is in NAMES in the ActiveWorkbook.
    ' 9/19/17 New Code from _
      "stackoverflow.com/questions/40488819/vba-find-names-in-activeworkbook-names"
    ' 12/8/17 Reworked. WML
    ' 11/1/19 Replaces "Name_Exists". WML
    ' 1/13/21 Reworked. WML
    
    On Error Resume Next
    Set Check = ActiveWorkbook.Names(Name_To_Verify)
    On Error GoTo 0

    Name_Verify = Len(Check) > 0
        
End Function ' Name_Verify

 

[6StringJazzer]

Are you asking a question or just sharing a tip?

I did not peruse the code but I found a problem in the first few lines:

    If Arg = "" Then
        D = Name_Get("Data_Date_Updated")
        D = Format(Date + Time, "mm/dd/yy hh:ss")

The second assignment will wipe out the first one.

Also I would generally recommend stronger typing. Nothing is declared.

 

[rhombus4]

Can't you just use workbook close event and add =now() to a cell just before it closes

 

[Mackeral]

You caught a mistake I didn't catch.

The first Format should have been "D" rather than "Date+Time".

I was putting this out as something that might be of general use rather than a question.

So thanks for noticing it for me.