How to set an Excel expiration date

You may want to set an expiration date for a workbook and prevent access for when creating a trial application, or simply ensuring that the application data is up to date. Unfortunately, Excel doesn’t have a built-in tools to handle this scenario, but you can use VBA to build this feature on your own. In this guide, we're going to show you how to set an Excel expiration date using VBA to prevent access to the workbook after expiration.

Download Workbook

Security of expiration date

Before starting, we should mention that VBA-based solutions are never foolproof. Excel and VBA protection methods are created to prevent the users from changing a value or a formula “accidentally”.

Anyone can stop the Workbook_Open event from running by holding the Shift key when opening the workbook. And you need to Workbook_Open event to fire your actual macro when the workbook opens. Another workaround is to break the protection of VBA and workbook with "brute force" attacks.

Although, this security method isn’t enough for an experienced user, your file will be safe for most Excel users.

Two methods for setting an expiration

There are two approaches to set an Excel expiration date. You can either set a static date regardless of when user starts to use, or set a number of days which starts when user opens the file first time. Let’s check both.

Static Expiration Date

This approach assumes that you want to prevent workbook usage after the specified date. The following code checks if the current date (Date) is greater than expiration date (exp_date). If it is, then the macro closes the workbook with a “trial expired” message.

Private Sub Workbook_Open()
  Dim exp_date As Date
  exp_date = "12/31/2020" 'update this
  If Date > exp_date Then
    MsgBox ("Trial period has expired")
    ActiveWorkbook.Close
  End If
  MsgBox ("You have " & exp_date - Date & " days left")
End Sub

Copy this code block into the ThisWorkbook object of your file and edit the exp_date variable to set a date.

Dynamic Expiration Date

Prefer using this approach if you want the expiration date to be set automatically when the user opens the workbook for the first time. This approach relies on calculating an expiration date based on the initial opening, and saving the date into a hidden a named range (ExpDate).

Th macro calculates the dynamic expiration date by summing up the current date and the constant specifies number of days until expiration (NUM_DAYS_UNTIL_EXPIRATION).

' Update this variable to set your own expiration period by number of days
Private Const NUM_DAYS_UNTIL_EXPIRATION = 30

Private Sub Workbook_Open()
  Dim ExpDate As String
  On Error Resume Next
  ExpDate = Mid(ThisWorkbook.Names("ExpDate").Value, 2)
  If Err.Number <> 0 Then
    ' Name doesn't exist.
    ' Calculate new expiration date
    ExpDate = CStr(DateSerial(Year(Now), _
      Month(Now), Day(Now) + NUM_DAYS_UNTIL_EXPIRATION))
    ' Create a named range and store the date in the named range
    ThisWorkbook.Names.Add Name:="ExpDate", _
      RefersTo:=Format(ExpDate, "short date"), _
      Visible:=False
    ' Save the file
    ThisWorkbook.Save
  End If
  ' Comapare today and expriation date
  If CDate(Now) > CDate(ExpDate) Then
    ' Expired
    MsgBox "This workbook trial period has expired.", vbOKOnly
    ThisWorkbook.Close savechanges:=False
  End If
End Sub

To use this method, copy this code block into the ThisWorkbook object, and edit the NUM_DAYS_UNTIL_EXPIRATION constant as necessary.

How to protect the VBA code

No matter which approach you choose, you need to protect your VBA code from unwanted changes. Otherwise, anyone removing the expiration date control will be able to circumvent this protection. To protect your VBA code, follow the steps:

1. Open the VBA window
2. Open Tools > VBAProject Properties
3. Activate the Protection tab
4. Check the Lock project from viewing option
5. Fill password fields
6. Click OK