0

I am wanting to create a demo for a VBA module I have created. The module has many different Private functions that are called by 2 Public functions (the public functions are used in Excel). I want to distribute the code as an Excel Add-in (.xlam) file with password protection. Is there a way to determine whether the Add-in is being used for the FIRST time? I.e. In an Excel Workbook the user clicks on Developer -> Excel Add-ins -> Ticks "my Add-in" then an input box will ask for the password, but then only again once a specified date has passed (i.e. the first trial has ended)?

I only have a working solution for the Workbook itself, but I am struggling to create a similar method within the actual module code so that it is not Workbook specific. Here is the Sub for the ThisWoorkbook Object in VBA:

 Private Sub Workbook_Open()

 Dim i, trials, passCnt As Integer
 trials = 3 'Number of trials
 passCnt = 4 'Number of times to enter password

 ReDim AllPassWords(1 To trials) As String
 AllPassWords(1) = "123"
 AllPassWords(2) = "456"
 AllPassWords(3) = "789"

 ReDim ExpDate(1 To trials) As Date 'We pre-define the expiry dates and passwords
 ExpDate(1) = CStr(DateSerial(2023, 1, 30))
 ExpDate(2) = CStr(DateSerial(2023, 2, 30))
 ExpDate(3) = CStr(DateSerial(2023, 3, 30))

 Dim PassWord As String

 If CDate(Now) < ExpDate(1) Then 'If the 1st trial has not expired we do the following
    

    For i = 1 To passCnt ' chances to enter password
        'Enter password before we can use the worksheet
        PassWord = InputBox("Trial 1 has started. Please input password.")
        If PassWord = AllPassWords(1) Then
        
            Exit For
            
            ElseIf i < passCnt Then
                MsgBox "Incorrect password. " & passCnt - i & " attempts remaining."
            
            ElseIf i = passCnt Then
                MsgBox "Password limit reached. Closing workbook"
                ThisWorkbook.Close
            
        End If
    Next i

    MsgBox ("You have " & ExpDate(1) - CDate(Now) & " days left")

    ElseIf CDate(Now) < ExpDate(2) And CDate(Now) > ExpDate(1) Then 
    

It is easy to check the Current Date within my Private or Public functions that form the Add-in, but I don't want a message box to pop up every time the user opens a Workbook, ONLY when the user activates the Add-in. Is this possible?

  • `i` and `trials` are declared as variants rather than integers. `Dim i as integer, trials as integer, passCnt as integer`. Although `Long` would be better. – Darren Bartrup-Cook Jan 17 '23 at 11:56
  • You should save the first time `Date` in Registry and check if the key already exists. Also using it to make calculations related to expiration period. And, it will also be good to use relative dates as Expiration ones. So you should add 30 days to the first date, also save them in Registry and check each time, firstly reading the appropriate keys. But password protection for VBA add-in does not assure any security, I am afraid... And having acces to your code, everything can be by-passed. Even witting expiration date after 20 years... – FaneDuru Jan 17 '23 at 13:30
  • @FaneDuru. Thank you. I will have a look at keeping the Date in the Registry! We are also protecting the module code itself with the built-in Password protection, the users will not be able to open up our code. Thanks again! – Joshua Kilian Jan 17 '23 at 13:39
  • 1
    As I said, any protection can be easily cracked, but this is a different issue. About registry, you can use the next lines to test the idea: `SaveSetting "TestApp", "Settings", "TestKey", CStr(Date)`. Then read it in a different testing sub: `Dim myDate As String` `myDate = GetSetting("TestApp", "Settings", "TestKey", "No value")` followed by `If myDate <> "No value" Then Debug.Print CDate(myDate)`. If you firstly run this second sub, it will not return any `Date`... – FaneDuru Jan 17 '23 at 13:52
  • @FaneDuru. Thank you. I also found another function that actually retrieves the date of a specific directory path: "FileDateTime". I will now have a look if there is some way I can retrieve the Add-in Path from within the Add-in code and then get the date of installation this way. – Joshua Kilian Jan 18 '23 at 07:15
  • @FaneDuru. So I think I have found something that works. I use the function "Application.VBE.ActiveVBEProject.Filename" which gives me the path to the Add-in. I then store this in a string and call the "FileDateTime" function with the path as the input. – Joshua Kilian Jan 18 '23 at 07:31
  • `FileDateTime` retrieves the creation/last modified file `Date`. If your add-in keeps data in its sheets, followed by `Save`, that time will be returned. Then, If I will download your add-in today, but I start working with it next month, it is not fair/even legal to consider me that I used it from the moment of the file download. But if it looks convenient to you, I do not have any problem... – FaneDuru Jan 18 '23 at 08:11
  • Then `Application.VBE.ActiveVBEProject.Filename` returns the same as `ThisWorkbook.fullname`... – FaneDuru Jan 18 '23 at 08:20
  • @FaneDuru. Ok I understand. The add-in does not store any data in the Workbook so the Add-in file date should remain the same as the date it was activated. – Joshua Kilian Jan 18 '23 at 09:52
  • What about the other scenario: I Buy the add-in today and start using it next month? Is it fair to consider the starting date the one when I downloaded it? – FaneDuru Jan 18 '23 at 10:17
  • @FaneDuru. So this is for trial purposes only. The users will be told that once they recieve the Add-in, the trial expires in x-days. I appreciate all your feedback, these are good points to keep in mind! – Joshua Kilian Jan 18 '23 at 12:28
  • OK. Do it as it looks convenient for you. – FaneDuru Jan 18 '23 at 12:34

0 Answers0