Creating macro with expiration date

bukimi

Board Regular
Joined
Apr 12, 2017
Messages
105
Office Version
  1. 2019
Platform
  1. Windows
Hello!

Today I need some creativity.
I need to protect my VBA code from unauthorized sharing, but it must be a discreet protection (that is, all password protection is not an option).

Currently I'm thinking about setting some expiration date hidden in the code (most possibly, people who will receive any unauthorized copy will not know much about VBA).
Right now I have something like this:
Code:
[COLOR=#333333]If Date > DateValue("01-12-2017") Then[/COLOR]
[COLOR=#333333]End[/COLOR]
[COLOR=#333333]End If[/COLOR]
It will just quietly exit macro without executing its code. A beginner will just think that my code doesn't work.

Still, I would like to have something less obvious. With code like that, it's sufficient to find date (like year) with CTRL-F and change it.
Can you give me some advice on some more hidden ways to code in a date after which the code will not work anymore?

Thank you in advance!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
What are your reasons for wanting to do this?
 
Upvote 0
This piece of code sort of hides the date. You need to take the required date serial number and convert the digits to letters. 1=A, 2=B etc. Today for example is 42915 so would be "dbiae".

Code:
Function Alterstring(myString As String)

Dim a As String, b As String, i As Integer

Const oldChars = "abcdefghij"
Const newChars = "1234567890"

For i = 1 To Len(oldChars)
    a = Mid(oldChars, i, 1)
    b = Mid(newChars, i, 1)
    myString = Replace(myString, a, b)
Next

Alterstring = myString

End Function

Sub Converter()

Dim myStr As String

myStr = "dbiae"

myDate = CDate(Alterstring(myStr))

MsgBox myDate

End Sub
 
Upvote 0
@steve the fish
I believe that requests like this should trigger alarm bells.
The OP may well have genuine reasons for requiring this (rather than password protection).
However, this sort of thing is sometimes done by disgruntled employees before they leave their employment.
 
Upvote 0
What are your reasons for wanting to do this?
There is a possibility that some people will use my personal code without my authorization. The problem with password protect is that then someone (not knowing much about VBA) will go on and try to get another "unprotected" version or break the security.
Another reason I don't want it to be password-protected is that I don't want people who use my code everyday to know and input anything when it's not absolutely necessary.
I update my code on regular basis: therefore people who work with my code everyday will have no problems with expiration date.

This piece of code sort of hides the date. You need to take the required date serial number and convert the digits to letters. 1=A, 2=B etc. Today for example is 42915 so would be "dbiae".
This one is clever. I'll try this one for sure.

@steve the fish
I believe that requests like this should trigger alarm bells.
The OP may well have genuine reasons for requiring this (rather than password protection).
However, this sort of thing is sometimes done by disgruntled employees before they leave their employment.

I'll be grateful if you don't make assumptions like this without knowing me.
The problem is that people using my code and making any data based on it without my supervision may create false data not knowing about that. It's as simple as that.
I want to "enforce" using current, updated versions of my code. Password protection is not solving that, is it?
"Discreet" protection is for making "smart" (lazy) people unable to continue using outdated code.
 
Last edited:
Upvote 0
The same could be said about requests to crack passwords, but this board does not entertain them.
I don't see how SETTING any security is anywhere similar to request of BREAKING a security measure.
If I ask you to install alarm in my car you would be as suspicious as if I asked you to tell me how to disable car alarms?

I don't see the point of discussing it any further. If my thread is a violation of some regulations of this forum, please do inform me about that.
 
Upvote 0
try:

Code:
Private Sub Workbook_Open()
Dim udate As Date, xdate As Date
Dim curWs As Workbook

Set curWs = ThisWorkbook
udate = Date
xdate = "06/30/2017"


    If udate >= xdate Then
        With curWs
            .Close False
        End With
    End If


End Sub

insert this code in the ThisWorkbook, not Module and not Sheet###, note that once the date is equal to or greater than the expiry date then the workbook will not open.
But...... password protect your macro, since theres a workaround when opening a macro enabled file, by opening the file while holding the SHIFT key.
 
Last edited:
Upvote 0
Yes but if you dont know a password then you didnt create it. Presumably cracking a password is against the wishes of the person who created it. This however is placing a date check by the person who is creating the code. I see that as more legitimate to be honest. I agree that an inexperienced user would have no idea how to get around it but then again they would have no idea by just using the date as is.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top