Christopher Greaves

416-621-9348 cgreaves@chrisgreaves.com
Home Services Products

The Macro PasswordOfTheDay

Let’s jump right in and LOOK at the basic macro:

Public Sub TypePasswordOfTheDay()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''' Function:   TypePasswordOfTheDay
'''
''' Comments:   Generates text at the current selection point.
'''
''' Arguments:  None
'''
''' Returns:    None
'''
''' Date        Developer       Action
''' --------------------------------------------------------------------------
''' 2008/11/22  Chris Greaves   Created
'''
Dim rng As Range
Set rng = Selection.Range
Selection.TypeText (strPasswordOfTheDay) ' Write a generated string at/over the current selection.
rng.End = Selection.Range.End
rng.Select
End Sub

The core of the macro is the line that begins “Selection.TypeText”.

This line types the result of calling a function “strPasswordOfTheDay”. The rest of the macro is taken up with comments, establishing where we are before we start, and selecting the typed text to facilitate the use of Ctrl-C to copy the generated password to the clipboard.

The function strPasswordOfTheDay is our main interest:

Public Function strPasswordOfTheDay(Optional lng As Long) As String
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''' Function:   strPasswordOfTheDay
'''
''' Comments:   Returns a string developed from a date and a machine. Reasonably obscure.
'''
''' Arguments:  OPTIONAL LONG   Offset to/from the current date.
'''
''' Returns:    STRING          Pseudo-random sequence of Alphabetic and Decimal characters.
'''
''' Date        Developer       Action
''' --------------------------------------------------------------------------
''' 2008/11/22  Chris Greaves   Created
'''
Dim strIn As String
strIn = strUnique(Format(Now() + lng, "Long Date"))
Dim strMachineKey
strMachineKey = strAppInfo
strPasswordOfTheDay = (strAlphaDigitsOnly(EncryptText(strIn, strMachineKey, Now() + lng)))
'Sub TESTstrPasswordOfTheDay()
'    MsgBox UCase(strPasswordOfTheDay)
'    MsgBox UCase(strPasswordOfTheDay(-1))
'    MsgBox UCase(strPasswordOfTheDay(-2))
'End Sub
End Function

The core of the function is the line that starts “strPasswordOfTheDay =”. This line asks for the alphabetic and decimal digit characters ONLY from an encrypted representation of today’s date, offset by an optional number of days.

The Date Factor

Our call to the function made no use of the optional parameter, so its value will be assumed to be ZERO in this case.

We start by creating a formatted version of a date. Which date? Today’s date. We format it in the “Long date” format, which means we generate something like this: “Tuesday, March 17, 2009”.

We then eliminate any duplicate characters, which would yield something like this: “Tuesday, Mrch17209”

This reduced string will be the basis of our password. Remember that with that “Optional Long” we can use a date other than today’s date, for example -1 would yield yesterday’s date, +1 tomorrow’s date, while -14 would yield the password string that was valid two weeks ago.

The Machine Factor

The line “strMachineKey = strAppInfo” obtains a string of data that is probably unique to your computer. The function “strAppInfo” can be adapted by you to raise or lower the degree of data.

We use the checksum values of the characters of the machine information only, not the information content. The string of characters that describes your machine is used solely as a key to scramble the formatted date string.

Encrypting The Result

Toronto and Mississauga, Sunday, December 05, 2010 9:07 PM