index > Visual Basic for Applications (VBA) > VBA registry interface

VBA registry interface

I would like to be able to write to and read from the registry given a specific address in the registry to write to. Ultimately, I want to be able to distribute a ms excel program with the understanding that it is a 2 week trial and I would like to enforce that in some way where it is not easily gotten around. I would also like to put a check so that the customer does not distribute the spreadsheet to other people. If there are other ways of doing this other than interfacing with the registry that would work well, I would be more than open to suggestions.

Potters Clay

To answer your first question, yes, it would be possible, but it's going to be a hassle. You'll need to create a separate "installer" program which installs the worksheet and registers the installation date in the system registry. Every time your worksheet is opened, it can check the registry and, if the current date is more than 14 days after the installation date, deny access to the functions.

As far as making sure the spreadsheet isn't redistributed, there really isn't a good way to do this. The best approach, I believe, would be a remote registration process such as Windows XP activation uses. Think hard about whether you really want to do this -- I think this is considerably more work than you've bargained for.

On another note, I'm not sure Microsoft would approve of your distributing VBA modules (embedded in Excel worksheets) on a commercial basis. I could be wrong about that, but it might not be a bad idea to check it out.

a little trolley is a dangerous thing for example you could trip over it

duck thing

Quite true that it may be more trouble than it is worth. Would the installer need to be done in VB with the excel spreadsheet packaged in with it?

Derek at Potters Clay

Hi,

I'm going to disagree with duckthing about using the registry. It's fairly easy to add keys to registry without needing to write an installer. You can use the windows api HOWEVER you need to be sure you know what your doing. You'd could use the windows api to read the key. You'd have to encrypt the key value so that it's not that easy to change though.

I'm also going to disagree about preventing it from being redistributable (sorry duckthing) but it requires the user to have a network card or modem. Each machine in the world has a unique MAC address, prior to giving the user a copy of your spreadsheet you'd need to ask your user to supply their MAC address, you would need to embed that into your code, i.e. if machines MAC address = "something" then run.

You'll need to use the windows api again... heres a wonderful example of how to do that

http://www.osix.net/modules/article/print.php?id=2

www.dsmyth.net/blog

Derek Smyth

Can I read the mac address instead of asking the user to supply it?

Derek at Potters Clay

yes but you need some condition in place so that the code won't run if the MAC address is not what it's suppose to be. You would need to store it somehow in your code. I suppose it would be possible to have your spreadsheet write VBA code containing the MAC into itself the first time it ran so that the next time it ran it knew what MAC address to expect.

www.dsmyth.net/blog

Derek Smyth

Thank you both for your help on this.

Derek at Potters Clay

Derek Smyth wrote:

I'm going to disagree with duckthing about using the registry. It's fairly easy to add keys to registry without needing to write an installer. You can use the windows api HOWEVER you need to be sure you know what your doing. You'd could use the windows api to read the key. You'd have to encrypt the key value so that it's not that easy to change though.

Yes, you're absolutely right. I wasn't thinking clearly! What the workbook could do, then, is to first check to see if the appropriate key exists. If it doesn't, the workbook can assume this is the first time it's being run, and set up the necessary key. And, of course, he'd need to make sure his code is locked for viewing.

Depending on the user's security policies, the program may not be allowed to write to the registry. But it's a starting point.

(Just to clarify, I wasn't implying that he needs to create some massive InstallShield installation process. )

a little trolley is a dangerous thing for example you could trip over it

duck thing

Hey duckthing,

Your right about having admin rights to write to the registry, the install file would fix that but then an admin guy would need to install it, and I can hear them now... 'install a spreadsheet??.... He'd need to be sure the key he created was in a fairly open section.

It would be better to write the information to a file hidden somewhere on the hard drive and give in an obscure name. It's kind of tricky protecting software, where there is a will there is a way. Protecting spreadsheets is even more tricky.

www.dsmyth.net/blog

Derek Smyth

Derek Smyth wrote:

It's kind of tricky protecting software, where there is a will there is a way. Protecting spreadsheets is even more tricky.

If it were easy, every software company on the planet would already be doing it! I think Microsoft's product activation process is the closest I've seen to a secure, reliable process for verifying software installations -- but it's not a model most "hobbyist" or "freelance" developers can implement.

Cheers!

a little trolley is a dangerous thing for example you could trip over it

duck thing

What about the VBA keywords: GETSETTING and SAVESETTING which saves values in the registry.

example:

GetSetting(application,section, key, strDefault)

SaveSetting application,section,key, value

It saves it in a predefined place for VBA in the registry. Can't recall the exact location as my registry is locked by my admin. But it works irregardless if the registry is locked or not.

Will Strutts

reply 11