VBA tip: Limit the number of times a file can be opened

Today’s VBA power tip is brought to you by guest blogger Bob Umlas. Bob is a long-time Excel MVP and author of Excel Outside the Box, from which the text below is excerpted. In addition, Bob organized the Excel User Conference, which takes place this year on March 29-30, in Atlantic City, New Jersey.

Using a demo of a file—not allowing it to be used more than x times

Suppose you want to send out a demo version of a file for a user to examine, but you don’t want it used more than a certain number of times, perhaps without your being paid for it. There are a lot of possible approaches for this situation, but here I’m going to show the use of some simple VBA statements called SaveSetting and GetSetting.

Here’s a screenshot of the whole procedure. It’s run each time the workbook is opened. If the VBA code is password protected, the user will not be able to easily prevent the demo program from ending.

 
The syntax for GetSetting is:

 
...and for SaveSetting is:

 
Each of the parameters is an arbitrary name you supply to access information stored in or read from the registry.

The AppName is more like a major category, the Section is a subcategory and the Key is yet another category. It gets clearer with the example. The first time this workbook is opened, the statement
N=GetSetting(“Demo”,”Demo”,”Demo”,0)+1 is executed. The 4th parameter is the default value given if no setting is actually already stored. So the first time, the GetSetting returns 0. Adding 1 to this stores a 1 into variable n.

So n is not 5 (yet), and it runs into the SaveSetting. The statement SaveSetting “Demo”, “Demo”, “Demo”,n now stores the value 5 in the registry. The next time the workbook is opened, the Getsetting returns that 1, and 1 is added to it and stored in n. Still not 5, and now a 2 is stored in the registry, etc. Eventually, the GetSetting returns 5, n is 6, and the program quits after giving a message to the user.

To reset this to zero on your own machine, you can either run SaveSetting “Demo”, “Demo”, “Demo”, 0, or you can run another variation of the VBA, called DeleteSetting. This syntax is:

As you can see, the Section and Key are optional. So executing DeleteSetting “Demo” clears the registry of the AppName as well as Section and Key.

Using GetSetting, SaveSetting, DeleteSetting can enable you to also communicate between sessions of Excel, or one of my favorite ways to use it is in debugging. I have frequently come across some stumpers where the VBA code crashes and I’m unable to pinpoint where that happens. This is with over 40,000 lines of VBA code (yes, a very large and intricate set of macros!). The idea of single stepping through the code is good, but there are times when I do that, then the error doesn’t occur! So I intersperse my code with random lines of something like SaveSetting “X”, “X”, “X”, 1 and SaveSetting “X”, “X”, “X”, 2 and SaveSetting “X”, “X”, “X”, 3, etc. Once the program crashes, I start up Excel and run this line in the immediate window: ?GetSetting (“X”, “X”, “X”) and if it returns 2, for example, then I know the program crashed between the SaveSetting that produced a 2 and the one that produced a 3. It has helped.

Lastly, the value stored is not limited to numbers as I’ve shown in this example—it can be any string you want, using it like a storage area for any purpose.

-- Bob Umlas, Excel MVP