wimgielis.com

Changing passwords

• Mar. 09, 2014

	Example files with this article:
	Changing passwords

Introduction

We all know that passwords in Excel are not very safe, although security is improved from version to version. Here is a short article on a nifty trick whereby you can have changing passwords in Excel. Each time, the password asked from the user is (or can be) different (and only the user knows what kind of password is required). The passwords that I discuss here are your own custom passwords within an Excel file - we are not talking about the password that you can use to block (write) access to a file.

The trick is to use for example the current time as the password: if we are now 7:42 PM, then the password would be 0742. I guess that many of us have a clock in the lower right corner of their screen, so in that case only shifting your eyes to the right would be enough to know the password required.

A code sample would be as follows.

VBA-code

Sub ChangingPassword()
' Wim Gielis
' https://www.wimgielis.com
'''''
' Ask a password to the user in a "random" way, based on the current time
' 03/09/14
'''''
    Dim dtNow As Date
    Dim sPassword_Required As String
    Dim sPassword_Given As String

    dtNow = Now
    sPassword_Required = Format(dtNow, "hhmm")
    sPassword_Given = InputBox("Please enter your password:")

    If sPassword_Given = sPassword_Required Then
        MsgBox "Your password is correct"
    Else
        MsgBox "Your password is incorrect"
    End If

End Sub

You can see in the code that I use the functions Format and Now to return the current time as text (a variable declared as String). This is the required password. The password asked from the user, is stored in a variable as well. Lastly, a very simple check is done to see if both passwords match. Please feel free to modify the code and use it to your own benefit. You can include the current day or month, you could even include the level of seconds (and adapt the password match test to suit).

If you use the level of the minute, there is always the possibility that the macro procedure is started just before a minute stops, and when the user starts typing the password, the new minute started. To allow for that possibility, you might want to allow 5 seconds:

Sub ChangingPassword_AllowTimeInterval()
' Wim Gielis
' https://www.wimgielis.com
'''''
' Ask a password to the user in a "random" way, based on the current time, and allow 5 seconds of time
' 03/09/14
'''''
    Dim dtNow As Date
    Dim sPassword_Required As String
    Dim sPassword_Given As String

    dtNow = Now
    sPassword_Required = Format(dtNow, "hhmm")
    sPassword_Given = InputBox("Please enter your password:")

    If (sPassword_Given = sPassword_Required) Or _
       ((sPassword_Given = Format(DateAdd("n", 1, dtNow), "hhmm")) And (Second(Now) <= 5)) Then
        MsgBox "Your password is correct"
    Else
        MsgBox "Your password is incorrect"
    End If

End Sub

Comparing the first code and the last code, the only change is in the extended IF test: now, it is enough to have the minutes correct, OR you can offset by 1 minute if you are within the first 5 seconds of the next minute. See the 5 at the end of the line. DateAdd is a convenient way to add up 1 minute to an existing date/time variable.

Conclusion

In summary, this article showed you a nice trick to ask a more or less random password. The user only needs to look at the system clock to know the password.

• « Previous Excel article
• Next Excel article »