How to automate entering dates with VBA in a Word document

Utilizing VBA makes fast work of coming into dates into the longer term or previous right into a Microsoft Phrase doc. This is how you can use it.


Illustration: Lisa Hornung, Getty Photos/iStockPhoto

The article The right way to simply embody dynamic dates in a Phrase doc utilizing Excel suggests calculating dates in Excel and utilizing mail merge to a Phrase template when you want to calculate dates for a Microsoft Phrase doc. The explanation? Phrase’s DATE area is not versatile, so once you want greater than DATE supplies, the Excel route is an efficient alternative. Nonetheless, when you want solely so as to add or subtract a selected variety of days, you need to use a VBA process as an alternative. On this article, I am going to present you how you can use VBA to insert static dates a selected variety of days into the longer term or previous.

SEE: 83 Excel ideas each person ought to grasp (TechRepublic)

I am utilizing Microsoft 365 on a Home windows 10 64-bit system, however you need to use earlier variations. To your comfort, you may obtain the demonstration .docm, .doc and .cls recordsdata. VBA procedures aren’t supported by Microsoft Phrase On-line.

About Phrase’s DATE area

Phrase’s DATE area does precisely what you would possibly anticipate: It returns the present date. In contrast to Excel, which helps a variety of date capabilities, DATE returns solely the present date. In case you attempt to give you an expression that makes use of DATE, you’ll most certainly fail. It may be achieved, but it surely’s so troublesome that even specialists do not advocate it.

The DATE area makes use of the next syntax:

{ DATE [ @ “format”] [Switches] }

An evidence of every follows:

  • @ “format”: Specifies a date format aside from the default format. If you choose a format within the Area dialog field, Phrase inserts a corresponding format swap. To make use of a format that is not listed within the Area dialog field, click on the Area Codes button, and sort the format swap instantly into the Area codes field.
  • l: Inserts the date with the final format that you simply chosen within the Date and Time dialog field.
  • h: Specifies use of the Hijri/Lunar calendar.
  • s: Specifies use of the Saka Period calendar.

It is unlikely that almost all of us will ever want the final two, so let’s overview an instance of the primary two. If the present date is Sept. 14, 2021,

{ DATE @ “dddd, MMMM d YYYY” }

will show Tuesday, September 14, 2021.

In case you work with Excel and even Entry, the format string (“dddd, MMMM d YYYY”) might be acquainted to you. Due to this string, this is without doubt one of the instances when coming into the sector through the interface is less complicated than every other methodology as a result of the codecs can be listed for you and all you do is click on one. You will not need to memorize the various string codes.

Use Phrase’s DATE area anytime you wish to insert a fast date utilizing the Shift + Alt + D shortcut. Doing so enters a DATE area, which can replace. While you want extra, contemplating utilizing VBA.

The right way to enter and run VBA procedures

Earlier than we transfer on, you may have to know how you can enter VBA procedures. In case you’re accustomed to working with VBA code, you may skip this part. Though I take advantage of the time period process, many sources, together with Microsoft, use the time period macro and sub process interchangeably. 

To enter the VBA procedures on this article, press Alt + F11 to open the Visible Primary Editor. Within the Venture Explorer to the left, choose ThisWorkbook so you may run the process in any sheet. You possibly can enter the code manually in ThisWorkbook or import the downloadable .cls file. The procedures are within the downloadable .docm, .doc and .cls recordsdata.

SEE: Home windows 10: Lists of vocal instructions for speech recognition and dictation (free PDF) (TechRepublic)

In case you enter the code manually, do not paste from this net web page. As an alternative, copy the code right into a textual content editor after which paste that code into the ThisWorkbook module. Doing so will take away any phantom net characters which may in any other case trigger errors.

In case you are utilizing a ribbon model, be sure you save the workbook as a macro-enabled file. In case you’re working within the menu model, you may skip this step.

To run a macro from the interface, do the next:

  1. Click on the Builders tab.
  2. Click on Macros within the Code group.
  3. Within the ensuing dialog, select the process and click on Run.

Refer to those directions for coming into and operating VBA procedures. I will not repeat these directions within the following sections.

Now let’s check out a sub process that returns the present date.

About VBA’s DATE() perform

While you want greater than a easy date, think about using a VBA process. Remember the fact that in VBA, there’s each a DATE() perform and a Date knowledge sort; we’re reviewing each at this level, and the next perform prints the present date within the Speedy window (press Ctrl + G to show this window within the Visible Primary Editor):

Perform DateTest()

    ‘Print in the present day’s date.

    ‘Name anytime you want the present date.

    Dim dDate As Date

    DateTest = Date

    Debug.Print DateTest

Finish Perform

dDate is a Date variable, and Date within the subsequent line references the system date. You possibly can learn the latter at any time utilizing Date. DateTest() would not reset the system date, it solely reads it after which shows it within the quick window. Strive to not let the inconsistent case or lack of () characters confuse you. Now that you already know the distinction between referencing the system date utilizing Date and defining a date variable utilizing Date (I really feel dizzy simply writing that), let’s transfer on and insert future and previous dates right into a Phrase doc.

Calculating future and previous dates

You possibly can calculate future and previous dates utilizing the DATE area when you’ve got the specialised ability. Most of us do not, and even those that do, battle to get the DATE area expression appropriate. Most flip to VBA as a result of it is a lot simpler and for nearly everybody on Earth, faster.

Listings A and B are easy sub procedures that insert the date 30 days into the longer term and the previous, respectively, on the cursor’s place. Determine A reveals the outcomes for each when the present date is Sep. 17, 2021 (a Friday).

Itemizing A

Sub Date30Future()

    ‘Insert date utilizing particular format 30 days

    ‘into the way forward for present date.

    Choice.TypeText _

        Textual content:=Format(Date + 30, “mmmm d, yyyy”)

Finish Sub

Itemizing B

Sub Date30Past()

    ‘Insert date utilizing particular format 30 days

    ‘into the previous of present date.

    Choice.TypeText _

        Textual content:=Format(Date – 30, “mmmm d, yyyy”)

Finish Sub

Determine A


  Use the VBA procedures to enter dates 30 days previous and future right into a Phrase doc.

The above listings are useful, so far as they go. You possibly can alter both by coming into a price aside from 30 or -30 within the code, however they’re nonetheless restricted to return solely two dates. By including an InputBox(), you may enable the person to specify the variety of days.

The right way to calculate a handed variety of days

Do not confuse the phrase handed within the heading with previous. Utilizing Itemizing C customers go an integer worth, both optimistic or detrimental (future or previous, respectively) by passing that worth through an enter field.

Itemizing C

Sub DateInput()

    ‘Enable person to enter the variety of days

    ‘so as to add or subtract from the present date.

    Dim days As Integer

    days = InputBox(“Enter a optimistic quantity so as to add ” _

        & “or a detrimental quantity to subtract.”, “Enter date.”)

    Choice.TypeText _

        Textual content:=Format(Date + days, “mmmm d, yyyy”)

Finish Sub

While you run DateInput(), VBA will show the enter field present in Determine B. Enter a optimistic integer to see a date into the longer term. Enter a detrimental integer to see a date from the previous. Determine C reveals the results of coming into 5 and -5 on Sept. 17, 2021.

Determine B


  Enter a optimistic or detrimental integer to return a future or previous date, respectively.

Determine C


  Run DateInput() twice, coming into 5 after which -5 to see these dates when the present date is Sept. 17, 2021.

Past the fundamentals

You might go a bit additional by making a userform that lets customers select a future or previous date to keep away from coming into a detrimental worth once you need a previous date. You might additionally add format choices. Not one of the VBA procedures embody error-handling, so you may wish to think about these wants when including these procedures to your individual recordsdata.

Additionally see

Recent Articles


Related Stories

Leave A Reply

Please enter your comment!
Please enter your name here

Stay on op - Ge the daily news in your inbox