Results 1 to 9 of 9
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Implement expiration policy (Excel97/2000)

    I need to implement a non-trivial time bomb in my Excel workbook that will prevent the users from using the tool beyond a given time period (3 months after initial opening). I need something that can not be easily tricked by resetting the system clock. Are there tools out there that will let me do this with Excel? I would prefer not to use external COM objects as then I need to deal with installation scripts and that's not practical in this case. Any ideas would be greatly appreciated as I need to come up with a list of solutions by end of day Friday.
    Deb <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>

  2. #2
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Implement expiration policy (Excel97/2000)

    I'm answering my own post for the moment.... I have easily implemented a method by which I use static varibles to keep track of the initial date, current date and expiration date which does not get fooled if user changes their clock back. Is there a better way to do this? I'm sure it's a problem that's been solved before. I need a solution that works in xl97 and xl2000.

  3. #3
    Lounger
    Join Date
    Dec 2002
    Posts
    25
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Implement expiration policy (Excel97/2000)

    A simple method is to have a hidden worksheet with two cells for date and time. One holds the expiration date and time, either set by you before you distribute the workbook, or by the workbook the first time it's opened. The other holds the last-opened date and time.

    Each time the workbook is opened, compare the system date and time to the expiration cell. If less, then check the system date against the last-opened cell. If it is less, gripe at the user about screwing with the system clock, and close the workbook. If greater, update the last-opened cell to the system date and time, and continue with your application.

    Not foolproof, since the user can break into the workbook and determine what's going on, but it will help the honest stay honest.

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Implement expiration policy (Excel97/2000)

    Thanks for the suggestion. It's effectively exactly what I've done but I did it with static variables, same idea though (and same result). I also added code to check if the current date is less than the "last open" date to detect clock changes. The only thing is that there's no way to recognize if the user made a copy of the workbook and then after the first one expired, just continued using this second (or more) copies. I think for our purposes this is as good as it's going to get. (I can't determine if they're using the original workbook I sent or a copy which they made.)
    Thank you, Deb <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Implement expiration policy (Excel97/2000)

    I don't know how you could implement this using static variables since static variables do not maintain their values after the workbook is closed. You might want to check that implementation more closely.

    If you want to prevent using a copy of the workbook after the expiration date, then you need to keep the expiration date someplace else like the Registry or a .ini file that you hide away someplace. Once you have detected the expiration, you should also set a separate variable that says it has expired. If this variable is set, you don't even check the expiration date. This would keep someone from resetting the system date after the expiration had been detected. I don't know how you can defeat someone resetting the system date before every execution.
    Legare Coleman

  6. #6
    Lounger
    Join Date
    Dec 2002
    Posts
    25
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Implement expiration policy (Excel97/2000)

    If you set the expiration date before you distribute a copy (on the assumption you prepare each copy for distribution), you avoid any worry about users copying. That may not be a luxury you have, in which case you end up dependent on the honesty of the users.

  7. #7
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Implement expiration policy (Excel97/2000)

    YIKES! You're absolutely right and I KNEW that (I'm not a VBA novice), what a dunce. I need to blame standing too close to the BBQ on the 4th I guess, the brain cells are squishy. It's trivial for me to change my statics to fixed cells as lookups and I'll do that.
    Thanks for the brain kick.

    Deb <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

  8. #8
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Implement expiration policy (Excel97/2000)

    Quick thought.

    If someone knew it was time limited and had a bit of an <img src=/S/devil.gif border=0 alt=devil width=15 height=15> streak in them, could they be smart enough to set their PC time forward say a couple of years before installing, in the hope of thwarting you?
    You could always add a maximum number of times it could be run as well, just to make sure.

    Not that I would ever think of doing something like that.... <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Implement expiration policy (Excel97/2000)

    Well, the code should check to see if there is more than three months difference in the dates in either direction.

    The maximum number of executions would be the most difficult to defeat though.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •