You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
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.
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
Anneliese, this is a great tip. Thank you for taking the time to share it!
Hi Martin. You are so welcome. I'll be sure to let Bob know you found it useful. Thanks for stopping by!
Thanks for the tip! BTW, will the settings be saved in Window's registry?
Hi Raymond. Yep, these settings will be saved in the registry. You can delete them by using the DeleteSetting command that Bob mentions above.
This is a nice tip but I do have some (positive) criticism. Because Excel uses several "containers" (to put it simple) within the same project I'm somewhat missing an explanation where this code should go. In my opinion you should have mentioned the "ThisWorkbook" section where this code is supposed to be placed into.
Another important factor is that if you do use this approach it becomes important to protect your macro so that people can't easily change it. That is done in the VBA editor using the 'Extra -> Properties' option.
And finally; since Office 2010 separates between worksheets with and without macro's and is also very restrictive with allowing macro's in sheets to be executed (only from specific locations on the computer) people should also keep in mind that this macro snippet isn't always running when the sheet opens. If a user downloads the sheet and then opens it from within the download folder chances are high that nothing will happen.
The reason why I feel that these aspects should have been included is because I can well imagine that not everyone who reads these blogs are fully experiences with vba programming yet IMO the article seems to assume that a little.
Apart from that I think this is a very good tip for people to work on.
Great tip, and thanks for the input from ShelLuser too.
Great tip Bob!
There are, of course, some caveats:
1) If macros are disabled, the code will not run
2) Even if macros are enabled, if EnableEvents has been set to False, the code will not run
3) A user who knows where to find the counter in the Registry can override the current counter value, or just delete that Registry entry
To mitigate #1 and #2, I will sometimes use an approach outlined here: www.vbaexpress.com/.../getarticle.php It "forces" users to have enabled macros and events by showing them only a "splash" worksheet if macros and events have not been enabled.
Patrick, this is really useful information. Thank you! (BTW, I've no idea why I'm showing up as Anonymous, despite being signed in...working on it. :-))
Hi ShelLUser: Thanks a million for the feedback. This post came directly from Bob's new book, which is definitely geared toward a more advanced Excel user. That said, I agree that we should have added more context in our intro for the less experienced coder. You've done that for us here, and I thank you!
1) There is a typo in the post. The paragraph beginning with "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." should read "...the value 1 in the registry."
2) That font you use in the VB Editor is just plain wrong.