Excel Training Message Board › The Most Useful Macro I Have Ever Written
|A former member||
My Fellow Excellencies:
Here's an example of a tiny visual basic program. I am hoping that it will help people who want to experiment with VBA (Visual Basic for Applications) and can play with this as a first step.
BACKGROUND:: I keep a dream journal in Excel. I am often half awake when I write in it (my other life is dreams -- see http://dreamreplay.co....
So what I need is a simple way of entering today's day and date into a cell. It's not quite as easy as it seems.
You might think that this will work
that gives you today's date and time
and if you type in
you get just the date.
BUT you get exactly what it says.
The problem here is that this is a dynamic entry: it refreshes. That cell will update tomorrow morning and the next day and so on. In other words, you don't get the date when you created the entry, you always get today's date. The Now() and Today() functions are "dynamic". I want a static entry.
So you need the very slightest of changes to a macro so that you hold on to the date that you actually created the entry.
MACRO: Here's the macro (which I tie to keyboard shortcut ctrl-shift-D for easy typing
' Keyboard Shortcut: Ctrl+Shift+D
Dim TempDate As Date
TempDate = Date
ActiveCell = Format(tempDate, "ddd") & " " & TempDate
I put the dynamic value into a temporary variable, tempDate, and then move that variable (with formatting) into the active cell. Without this switch, you are stuck with the dynamic value.
Format(tempDate, "ddd") & " " & tempDate
allows me to display the date two different ways: firstly as the 3 letter day of the week, Wed, and then as month/day/year: 3/26/2014
So today's date is displayed as Wed 3/26/2014.
All I have to do is press Ctrl Shift D and Excel tells me the day of the week and the date. That's not trivial before your first cup of coffee kicks in.
David Jenkins, M.S.
PS If you can settle for the date on its own, you can use the shortcut ctrl; (control and semi-colon) which produces a static entry but doesn't give you the day of the week (often vital when I look back).
PS Feedback welcome: there must be many ways to do this.
PS You can write a comparable macro for the time