Timebombing A Workbook

This page describes how to limit the functionality of a workbook after a period of time.

Introduction

There may be circumstances in which you want to limit the functionality of a workbook after a specified period of time, such as distributing a demonstration or trial version of an application. Excel doesn't lend itself well to doing this. Nearly any limitation on a workbook can be circumvented by an experienced user. That said, there are a numbers thing you can do limit the usable life of a workbook, called time-bombing as workbook.

Unfortunately, no VBA-based time-bombing method is foolproof. A skilled user can quite easily circumvent the time-bomb code and get full access to the workbook, even after it has expired. The code on this page also requires that the user has macros enabled. If macros are disabled, the code will not work. (See Ensuring Macros Are Enabled for code to force the user to enable macros.) All that said, the time-bomb methods presented here are probably "good enough". They will not prevent an experienced (and dishonest) user from circumventing the protection, but the code will work for the vast majority of Excel users. All protection mechanisms in Excel are really intended to prevent the user from accidentally changing a value or some code. The security just isn't strong enough to provide real protection of proprietary information or information or code with intellectual property value. For that level of security, you should be writing your code in Visual Basic 6 or VB.NET. See Creating A COM Add-In for details about creating a COM Add-In and Automation Add Ins A Function Libraries for details about creating an Automation Add-Ins.

A quick note regarding security in Excel: Security of any sort in Excel is extremely weak. There are many password breakers available on the internet. I use PassWare's VBAKey and ExcelKey that can crack Excel and VBA Project passwords effortlessly, usually in a matter of seconds. In most cases, the password assigned to an object (a sheet, workbook, or VBA Project) isn't the only password that will succeed in opening the object. It is one of many mathematically related passwords, any one of which will work. For example, in some tests of VBAKey, VBAKey will return not the password that I actually used to protect the project, but a seemingly random string of characters that will pass successfully through the password validation logic.

As I tell all of my commercial clients, password protection, or protection of any sort, should be viewed as a method to keep the honest user from accidentally changing or deleting something he shouldn't. The security is by no means sufficient to protect proprietary data or code or code with intellectual property value. The same goes for any sort of registry based key storage or settings. A skilled user can easily run RegEdit and see the values in the registry. The same hold true for macro based security. Not only can the VBA password be easily broken, but nearly all macro based security relies on macros being enabled in the first place and that the Application's EnableEvents setting is True. It is a one-liner to circumvent either of those two restrictions.
The bottom line is that if you have valuable code and you need more than "honest person" security, then VBA is not sufficient. You should create a COM Add-In in VB6 or a Shared Add-In or an Excel Workbook in VB.NET and Visual Studio Tools For Office.

All of the procedures desribed on this page use the constant:

Private Const C_NUM_DAYS_UNTIL_EXPIRATION = 30

You should change the value of this constant to number of days after the first usage of the workbook that the workbook should be disabled. The value provided in the is 30 days. On the 31st day after the first time the workbook is opened, it will become unusable.

You can download an example workbook here.

TimeBomb With A Defined Name

This procedure, TimeBombWithDefinedName, uses a hidden defined name to store the expiration date. The first time the workbook is opened, that defined name will not exist and will be created by the code. (Be sure that you delete this name when you are done with your own development work on the workbook.) When the defined name is created, it uses the C_NUM_DAYS_UNTIL_EXPIRATION constant to calculate the expiration date. When workbook is opened after the first time, the code reads the name ExpirationDate and tests that value against the current date.

Sub TimeBombWithDefinedName()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' TimeBombWithDefinedName
' This procedure uses a defined name to store this workbook's
' expiration date. If the expiration date has passed, a
' MsgBox is displayed and this workbook is closed.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim ExpirationDate As String

On Error Resume Next
ExpirationDate = Mid(ThisWorkbook.Names("ExpirationDate").Value, 2)
If Err.Number <> 0 Then
    '''''''''''''''''''''''''''''''''''''''''''
    ' Name doesn't exist. Create it.
    '''''''''''''''''''''''''''''''''''''''''''
    ExpirationDate = CStr(DateSerial(Year(Now), _
        Month(Now), Day(Now) + C_NUM_DAYS_UNTIL_EXPIRATION))
    ThisWorkbook.Names.Add Name:="ExpirationDate", _
        RefersTo:=Format(ExpirationDate, "short date"), _
        Visible:=False
End If

''''''''''''''''''''''''''''''''''''''''''''''''''''''
' If the today is past the expiration date, close the
' workbook. If the defined name didn't exist, we need
' to Save the workbook to save the newly created name.
''''''''''''''''''''''''''''''''''''''''''''''''''''''
If CDate(Now) > CDate(ExpirationDate) Then
    MsgBox "This workbook trial period has expired.", vbOKOnly
    ThisWorkbook.Close savechanges:=False
End If

End Sub

TimeBomb With Suicide

A more extreme version of timebombing code is to permanently delete the workbook once the expiration date has passed. I generally wouldn't recommend this approach, but it is certainly possible.

CAUTION: This code will permanently delete (not Recycle) the workbook that contains the code. Once deleted, there is no way to get the workbook back. Be sure you that when you are testing you have another copy of the code.

However you store and calculate the expiration date, you can use the code below to delete the workbook containing the code.

Sub CommintSuicide()
    With ThisWorkbook
        Application.DisplayAlerts = False
        If .Path <> vbNullString Then
            .ChangeFileAccess xlReadOnly
            Kill .FullName
        End If
        ThisWorkbook.Close SaveChanges:=False
    End With
End Sub

TimeBomb To Read-Only

This procedure, TimeBombMakeReadOnly, uses a defined name, just as the previous procedure did, but rather than closing the workbook, it makes the workbook read-only.

Sub TimeBombMakeReadOnly()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' TimeBombMakeReadOnly
' This procedure uses a defined name to store the expiration
' date and if the workbook has expired, makes the workbook
' read-only.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim ExpirationDate As String
Dim NameExists As Boolean

On Error Resume Next
ExpirationDate = Mid(ThisWorkbook.Names("ExpirationDate").Value, 2)
If Err.Number <> 0 Then
    '''''''''''''''''''''''''''''''''''''''''''
    ' Name doesn't exist. Create it.
    '''''''''''''''''''''''''''''''''''''''''''
    ExpirationDate = CStr(DateSerial(Year(Now), _
        Month(Now), Day(Now) + C_NUM_DAYS_UNTIL_EXPIRATION))
    ThisWorkbook.Names.Add Name:="ExpirationDate", _
        RefersTo:=Format(ExpirationDate, "short date"), _
        Visible:=False
    NameExists = False
Else
    NameExists = True
End If

''''''''''''''''''''''''''''''''''''''''''''''''''''''
' If the today is past the expiration date, make the
' workbook read only. We need to Save the workbook
' to keep the newly created name intact.
''''''''''''''''''''''''''''''''''''''''''''''''''''''
If CDate(Now) >= CDate(ExpirationDate) Then
    If NameExists = False Then
        ThisWorkbook.Save
    End If
    ThisWorkbook.ChangeFileAccess xlReadOnly
End If

End Sub

TimeBomb With Registry

This procedure, TimeBombWithRegistry, stores the expiration date in the System Registry. To use this code, you must include the modRegistry code module, avaiable on the System Registry page, or use the RegistryWorx.dll available on the RegistryWorx page. If you use the modRegistry module, you will also need to include the modGetSystemErrorMessageText, available here. The modRegistry module and the modGetSystemErrorMessageText modules are included in the downloadable example workbook.

Sub TimeBombWithRegistry()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' TimeBombWithRegistry
' This procedure stores the expiration date in the system
' registry. Change C_REG_KEY to a registry key name that
' is used by your application.
'
' This procedure requires either the modRegistry module from
'   www.cpearson.com/Excel/Registry.htm or
'   www.cpearson.com/Excel/Registry.aspx
' or the RegistryWorx DLL from
'   www.cpearson.com/Excel/RegistryWorx.aspx.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Const C_REG_KEY = "Software\Pearson\Test\Settings"
Dim KeyExists As Boolean
Dim ValueExists As Boolean
Dim ExpirationDate As Long
Dim B As Boolean
KeyExists = RegistryKeyExists(HKEY_CURRENT_USER, C_REG_KEY, False)
If KeyExists = True Then
    '''''''''''''''''''''''''''''''''
    ' Key exists. Get the Value from
    ' the key.
    '''''''''''''''''''''''''''''''''
    ValueExists = RegistryValueExists(HKEY_CURRENT_USER, C_REG_KEY, "Expiration")
    If ValueExists = True Then
        '''''''''''''''''''''''''''''''''''''''''
        ' Value exists. It will be the
        ' expiration date.
        '''''''''''''''''''''''''''''''''''''''''
        ExpirationDate = RegistryGetValue(HKEY_CURRENT_USER, C_REG_KEY, "Expiration")
    Else
        '''''''''''''''''''''''''''''''''''''''''
        ' Value doesn't exist. Set the expiration
        ' date and update the Registry.
        '''''''''''''''''''''''''''''''''''''''''
        ExpirationDate = DateSerial(Year(Now), Month(Now), _
            Day(Now) + C_NUM_DAYS_UNTIL_EXPIRATION)
        B = RegistryCreateValue(HKEY_CURRENT_USER, C_REG_KEY, "Expiration", CLng(ExpirationDate))
        If B = False Then
            ' error creating registry value
        End If
    End If
Else
    ''''''''''''''''''''''''''''''''''''''''
    ' Key doesn't exist. Set the expiration
    ' date and create the Key and Value.
    ''''''''''''''''''''''''''''''''''''''''
    ExpirationDate = DateSerial(Year(Now), Month(Now), _
        Day(Now) + C_NUM_DAYS_UNTIL_EXPIRATION)
    B = RegistryCreateKey(HKEY_CURRENT_USER, C_REG_KEY)
    If B = True Then
        B = RegistryCreateValue(HKEY_CURRENT_USER, C_REG_KEY, "Expiration", ExpirationDate)
        If B = False Then
            ' error creating registry value
        End If
    Else
        ' error creating registry key
    End If
End If
'''''''''''''''''''''''''''''''''''''''''''
' If Now is past the expiration date,
' close the workbook.
'''''''''''''''''''''''''''''''''''''''''''
If CLng(Now) > CLng(ExpirationDate) Then
    ThisWorkbook.Close savechanges:=False
End If

End Sub

Making The Code Run At Open

Regardless of which procedure you use to limit usage of the workbook, you should call that procedure for the Workbook_Open event procedure in the ThisWorkbook code module:

Private Sub Workbook_Open()
    TimeBombWithRegistry
End Sub

Note that this will not work if the user has disabled VBA code or has set the Application.EnableEvents property to False.

This page last updated: 11-Oct-2007