Macro to convert date to text

  • Thread starter Thread starter Mr. Clean
  • Start date Start date
M

Mr. Clean

Excel 2003 VB - I need to add code to a macro that compares today's date to
the last time this macro was run, and provides the number of days different
between the two dates.

Can anyone help?
 
Add the following code at the beginning of your existing code. It will
create an hidden defined name called "LastRun" containing the date on
which the code was executed.

Dim D As Date
Dim S As String
Dim Nm As Name
On Error Resume Next
Set Nm = ThisWorkbook.Names("LastRun")
If Err.Number <> 0 Then
ThisWorkbook.Names.Add "LastRun", Int(Now), False
Else
S = Nm.RefersTo
S = Mid(S, 2)
D = CDate(S)
Debug.Print "Last Run: " & D, "Days between: " & Int(Now) - D
End If
ThisWorkbook.Names.Add "LastRun", Int(Now), False

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
If it were me, I'd add a named range that contains the last time the macro
was run. Each time the macro is run, I'd pull that value and do the
calculation. After the calculation is done, I'd reset the named range. If
you need more info on this, come back and let us know.
 
Thanks for the VERY swift reply. This was my first post.

I need one more thing. I need to write a text message into a cell that
tells the user that the last time this macro was run was more than 90 days
ago.
 
Back
Top