{"id":140,"date":"2012-01-13T16:52:45","date_gmt":"2012-01-13T16:52:45","guid":{"rendered":"http:\/\/www.itcrumbs.com\/?p=140"},"modified":"2019-02-07T04:40:23","modified_gmt":"2019-02-07T04:40:23","slug":"vba-tip-limit-the-number-of-times-a-file-can-be-opened","status":"publish","type":"post","link":"http:\/\/www.itcrumbs.com\/?p=140","title":{"rendered":"VBA tip: Limit the number of times a file can be opened"},"content":{"rendered":"

http:\/\/blogs.office.com\/b\/microsoft-excel\/archive\/2012\/01\/04\/vba-tip-limit-the-number-of-times-a-file-can-be-used.aspx<\/a><\/p>\n

Using a demo of a file\u2014not allowing it to be used more than x times<\/h4>\n

Suppose you want to send out a demo version of a file for a user to examine, but you don\u2019t 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\u2019m going to show the use of some simple VBA statements called SaveSetting and GetSetting. <\/p>\n

Here\u2019s a screenshot of the whole procedure. It\u2019s 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.<\/p>\n

\"VBA
The syntax for GetSetting is: <\/p>\n

\"Syntax
…and for SaveSetting is:<\/p>\n

\"Syntax
Each of the parameters is an arbitrary name you supply to access information stored in or read from the registry.<\/p>\n

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(\u201cDemo\u201d,\u201dDemo\u201d,\u201dDemo\u201d,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.<\/p>\n

So n is not 5 (yet), and it runs into the SaveSetting. The statement SaveSetting \u201cDemo\u201d, \u201cDemo\u201d, \u201cDemo\u201d,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.<\/p>\n

To reset this to zero on your own machine, you can either run SaveSetting \u201cDemo\u201d, \u201cDemo\u201d, \u201cDemo\u201d, 0, or you can run another variation of the VBA, called DeleteSetting. This syntax is:<\/p>\n

\"Syntax<\/p>\n

As you can see, the Section and Key are optional. So executing DeleteSetting \u201cDemo\u201d clears the registry of the AppName as well as Section and Key.<\/p>\n

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\u2019m 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\u2019t occur! So I intersperse my code with random lines of something like SaveSetting \u201cX\u201d, \u201cX\u201d, \u201cX\u201d, 1 and SaveSetting \u201cX\u201d, \u201cX\u201d, \u201cX\u201d, 2 and SaveSetting \u201cX\u201d, \u201cX\u201d, \u201cX\u201d, 3, etc. Once the program crashes, I start up Excel and run this line in the immediate window: ?GetSetting (\u201cX\u201d, \u201cX\u201d, \u201cX\u201d) 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.<\/p>\n

Lastly, the value stored is not limited to numbers as I\u2019ve shown in this example\u2014it can be any string you want, using it like a storage area for any purpose.<\/p>\n

— Bob Umlas, Excel MVP<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"

http:\/\/blogs.office.com\/b\/microsoft-excel\/archive\/2012\/01\/04\/vba-tip-limit-the-number-of-times-a-file-can-be-used.aspx Using a demo of a file\u2014not 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\u2019t want it used more than a certain number of times, perhaps without your being paid for it. There are […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-140","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"http:\/\/www.itcrumbs.com\/index.php?rest_route=\/wp\/v2\/posts\/140","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.itcrumbs.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.itcrumbs.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.itcrumbs.com\/index.php?rest_route=\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"http:\/\/www.itcrumbs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=140"}],"version-history":[{"count":1,"href":"http:\/\/www.itcrumbs.com\/index.php?rest_route=\/wp\/v2\/posts\/140\/revisions"}],"predecessor-version":[{"id":768,"href":"http:\/\/www.itcrumbs.com\/index.php?rest_route=\/wp\/v2\/posts\/140\/revisions\/768"}],"wp:attachment":[{"href":"http:\/\/www.itcrumbs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=140"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.itcrumbs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=140"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.itcrumbs.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=140"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}