align-toparrow-leftarrow-rightbackbellblockcalendarcamerachatcheckchevron-downchevron-leftchevron-rightchevron-small-downchevron-small-leftchevron-small-rightchevron-small-upchevron-upcircle-with-crosscrosseditfacebookglobegoogleimagesinstagramlocation-pinmagnifying-glassmailmoremuplabelShape 3 + Rectangle 1outlookpersonplusImported LayersImported LayersImported Layersshieldstartwitteryahoo

Excel Training Message Board › The Most Useful Macro I Have Ever Written

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
=Now()
that gives you today's date and time
and if you type in
=Today()
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
Sub RecordDate()
' Keyboard Shortcut: Ctrl+Shift+D
Dim TempDate As Date
TempDate = Date
ActiveCell = Format(tempDate, "ddd") & " " & TempDate
End Sub
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.
The formatting:
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.
Best wishes
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
Powered by mvnForum

Sign up

Meetup members, Log in

By clicking "Sign up" or "Sign up using Facebook", you confirm that you accept our Terms of Service & Privacy Policy