How to automate entering dates with VBA in a Word document

3 years ago 422

Using VBA makes speedy enactment of entering dates into the aboriginal oregon past into a Microsoft Word document. Here's however to usage it.

The nonfiction How to easy see dynamic dates successful a Word doc utilizing Excel suggests calculating dates successful Excel and utilizing message merge to a Word template erstwhile you request to cipher dates for a Microsoft Word document. The reason? Word's DATE tract isn't flexible, truthful erstwhile you request much than DATE provides, the Excel way is simply a bully choice. However, if you request lone to adhd oregon subtract a circumstantial fig of days, you tin usage a VBA process instead. In this article, I'll amusement you however to usage VBA to insert static dates a circumstantial fig of days into the aboriginal oregon past.

SEE: 83 Excel tips each idiosyncratic should master (TechRepublic)

I'm utilizing Microsoft 365 connected a Windows 10 64-bit system, but you tin usage earlier versions. For your convenience, you tin download the objection .docm, .doc and .cls files. VBA procedures aren't supported by Microsoft Word Online.

About Word's DATE field

Word's DATE tract does precisely what you mightiness expect: It returns the existent date. Unlike Excel, which supports a fig of day functions, DATE returns lone the existent date. If you effort to travel up with an look that uses DATE, you volition astir apt fail. It tin beryllium done, but it's truthful hard that adjacent experts don't urge it.

The DATE tract uses the pursuing syntax:

{ DATE [ \@ "format"] [Switches] }

An mentation of each follows:

\@ "format": Specifies a day format different than the default format. If you prime a format successful the Field dialog box, Word inserts a corresponding format switch. To usage a format that's not listed successful the Field dialog box, click the Field Codes button, and benignant the format power straight into the Field codes box.
\l: Inserts the day with the past format that you selected successful the Date and Time dialog box.
\h: Specifies usage of the Hijri/Lunar calendar.
\s: Specifies usage of the Saka Era calendar.

It's improbable that astir of america volition ever request the past two, truthful let's reappraisal an illustration of the archetypal two. If the existent day is Sept. 14, 2021,

{ DATE \@ "dddd, MMMM d YYYY" }

will show Tuesday, September 14, 2021.

If you enactment with Excel oregon adjacent Access, the format drawstring ("dddd, MMMM d YYYY") is astir apt acquainted to you. Because of this string, this is 1 of the times erstwhile entering the tract via the interface is easier than immoderate different method due to the fact that the formats volition beryllium listed for you and each you bash is click one. You won't person to memorize the galore drawstring codes.

Use Word's DATE tract anytime you privation to insert a speedy day utilizing the Shift + Alt + D shortcut. Doing truthful enters a DATE field, which volition update. When you request more, considering utilizing VBA.

How to participate and tally VBA procedures

Before we determination on, you'll request to cognize however to participate VBA procedures. If you're acquainted with moving with VBA code, you tin skip this section. Although I usage the word procedure, galore sources, including Microsoft, usage the word macro and sub process interchangeably.

To participate the VBA procedures successful this article, property Alt + F11 to unfastened the Visual Basic Editor. In the Project Explorer to the left, prime ThisWorkbook truthful you tin tally the process successful immoderate sheet. You tin participate the codification manually successful ThisWorkbook oregon import the downloadable .cls file. The procedures are successful the downloadable .docm, .doc and .cls files.

SEE: Windows 10: Lists of vocal commands for code designation and dictation (free PDF) (TechRepublic)

If you participate the codification manually, don't paste from this web page. Instead, transcript the codification into a substance exertion and past paste that codification into the ThisWorkbook module. Doing truthful volition region immoderate phantom web characters that mightiness different origin errors.

If you are utilizing a ribbon version, beryllium definite to prevention the workbook arsenic a macro-enabled file. If you're moving successful the paper version, you tin skip this step.

To tally a macro from the interface, bash the following:

Click the Developers tab.
Click Macros successful the Code group.
In the resulting dialog, take the process and click Run.

Refer to these instructions for entering and moving VBA procedures. I won't repetition these instructions successful the pursuing sections.

Now let's instrumentality a look astatine a sub process that returns the existent date.

About VBA's DATE() relation

When you request much than a elemental date, see utilizing a VBA procedure. Keep successful caput that successful VBA, there's some a DATE() relation and a Date information type; we're reviewing some astatine this point, and the pursuing relation prints the existent day successful the Immediate model (press Ctrl + G to show this model successful the Visual Basic Editor):

Function DateTest()

'Print today's date.

'Call anytime you request the existent date.

Dim dDate As Date

DateTest = Date

Debug.Print DateTest

End Function

dDate is simply a Date variable, and Date successful the adjacent enactment references the strategy date. You tin work the second astatine immoderate clip utilizing Date. DateTest() doesn't reset the strategy date, it lone reads it and past displays it successful the contiguous window. Try not to fto the inconsistent lawsuit oregon deficiency of () characters confuse you. Now that you cognize the quality betwixt referencing the strategy day utilizing Date and defining a day adaptable utilizing Date (I consciousness dizzy conscionable penning that), let's determination connected and insert aboriginal and past dates into a Word document.

Calculating aboriginal and past dates

You tin cipher aboriginal and past dates utilizing the DATE tract if you person the specialized skill. Most of america don't, and adjacent those who do, conflict to get the DATE tract look correct. Most crook to VBA due to the fact that it's overmuch easier and for astir everyone connected Earth, quicker.

Listings A and B are elemental sub procedures that insert the day 30 days into the aboriginal and the past, respectively, astatine the cursor's position. Figure A shows the results for some erstwhile the existent day is Sep. 17, 2021 (a Friday).

Listing A

Sub Date30Future()

'Insert day utilizing circumstantial format 30 days

'into the aboriginal of existent date.

Selection.TypeText _

Text:=Format(Date + 30, "mmmm d, yyyy")

End Sub

Listing B

Sub Date30Past()

'Insert day utilizing circumstantial format 30 days

'into the past of existent date.

Selection.TypeText _

Text:=Format(Date - 30, "mmmm d, yyyy")

End Sub

Figure A

The supra listings are helpful, arsenic acold arsenic they go. You tin change either by entering a worth different than 30 oregon -30 successful the code, but they are inactive constricted to instrumentality lone 2 dates. By adding an InputBox(), you tin let the idiosyncratic to specify the fig of days.

How to cipher a passed fig of days

Don't confuse the connection passed successful the heading with past. Using Listing C users walk an integer value, either affirmative oregon antagonistic (future oregon past, respectively) by passing that worth via an input box.

Listing C

Sub DateInput()

'Allow idiosyncratic to input the fig of days

'to adhd oregon subtract from the existent date.

Dim days As Integer

days = InputBox("Enter a affirmative fig to adhd " _

& "or a antagonistic fig to subtract.", "Enter date.")

Selection.TypeText _

Text:=Format(Date + days, "mmmm d, yyyy")

End Sub

When you tally DateInput(), VBA volition show the input container amusement successful Figure B. Enter a affirmative integer to spot a day into the future. Enter a antagonistic integer to spot a day from the past. Figure C shows the effect of entering 5 and -5 connected Sept. 17, 2021.

Figure B

Figure C

Beyond the basics

You could spell a spot further by creating a userform that lets users take a aboriginal oregon past day to debar entering a antagonistic worth erstwhile you privation a past date. You could besides adhd format options. None of the VBA procedures see error-handling, truthful you'll privation to see those needs erstwhile adding these procedures to your ain files.