ebmgh.com

I have a fairly simple user-defined function for Excel that I use to create random passwords. (I got kind of freaked out last year after someone hacked into my Facebook account. Trouble was, I used the same password for lots of sites. If this petty crook had been a little more savvy, he could have done a lot more damage. At first, I immediately replaced a bunch of my passwords with strings like this: p9q4MF3nnX4B. The only problem now is that I can’t remember any of my passwords, and have to keep them in a file, which creates security concerns of its own…)

I noticed that my little password routine would repeatedly create the same password each time I used it anew, after re-starting the computer and re-launching Excel. It turns out that VBA’s RAND function is not as random as you’d think it might be. The way around this is

I saw this code that I found on the techguy forum:

  seed = TimeValue("00:" & Format(Second(Time), "00") & ":" & Format(Minute(Time), "00"))
  Randomize (seed)

But according to the RND entry at the support site, you can just call Randomize on its own:

Before calling Rnd, use the Randomize statement without an argument to initialize the random-number generator with a seed based on the system timer.

I had never used Randomize before, and never knew that I needed to. In Python, a more sophisticated programming language, seeding is taken care of for you: “current system time is also used to initialize the generator when the module is first imported.”

Lots more information at the Wikipedia article on Pseudo-Random Number Generators. From here I learned, “A PRNG can be started from an arbitrary starting state using a seed state. It will always produce the same sequence thereafter when initialized with that state.”

It’s often useful to write your own custom functions for Excel. These are often called User-Defined Functions, or UDFs. It’s actually not that hard if you know a bit of programming. It’s all done in VBA, or Visual Basic for Applications. I’ve probably written hundreds of these in the last 7 or 8 years, but one trick I always forget is how to return a customized error message.

If a line in your function produces a run-time error, you won’t get a popup message like you would for a Subroutine. Instead, code execution ends immediately, and you get the error #VALUE! in the cell on the worksheet.

It is often useful to return a more descriptive error. To do this, you can use On Error to trap the error, and return one of Excel’s built in error types. To do this, use the VBA function CVErr and a constant to set the built-in error type. These are (via the online help under “Cell Error Values”):

Error number

Cell error value

2007

#DIV/0!

2042

#N/A

2029

#NAME?

2000

#NULL!

2036

#NUM!

2023

#REF!

2015

#VALUE!

For your UDF to return custom errors, you must declare the function so that it returns the data type Variant. See also this Microsoft knowldgebase article on Error Trapping with Visual Basic for Applications.

Here’s an example of a UDF that returns the error #N/A. In this case, the function is a slightly more sophisticated version of the built-in function FIND, which returns the starting position of a one text string within another string. I wanted a function that would return the nth position, rather than just the first occurrence.

Function FindNth(find_text As String, within_text As String, n As Long) As Variant

'Finding the nth occurrence of a text string within another text string

'Uses Excel's built-in Worksheet Find function iteratively

'(probably faster than a brute-force approach using a loop)

'If find_text is not found, returns the error #N/A

  Dim pos As Variant

  Dim i As Long

  Dim result As Long

  i = 0

  result = 0

  Do

    On Error GoTo ErrLine

    pos = Application.WorksheetFunction.Find(find_text, within_text)

    result = result + pos

    i = i + 1

    If i = n Then Exit Do

    within_text = Right(within_text, Len(within_text) - pos)

  Loop

  FindNth = result

  Exit Function

ErrLine: FindNth = CVErr(xlErrNA)

End Function

For example:

=FINDNTH(" ", "Mira Vista Avenue", 2)

Returns 11, the position of the second occurrence of a space in “Mira Vista Avenue”.

=FINDNTH("x", "Microsoft", 3)

Returns #N/A, since it is looking for the 3^rd letter x in “Microsoft”, and can’t find it.

In this example, the function should probably return #VALUE! to copy the FIND function’s built-in behavior. But I wanted to differentiate between a VBA run-time error and an error returned by the function, and #N/A seemed to fit the bill.

I found myself wishing that Excel or VBA had a simple way to test whether a string exists in another string. In Python you can type

if substring in string:

  #Then do something…

But Excel lacks this convenience. You can sort of hack the built in InStr function to do it for you. Since I always found this confusing (who can remember what the difference is between “text compare” and “binary compare”?), I created a simple function called Contains. On the spreadsheet, you can just type

=Contains("abcdefg", "a"

) and it returns TRUE. In VBA, it is useful in IF statements. Not as clean as Python, but a helpful convenience.

Function Contains(str As String, substr As String, _

   Optional CaseSensitive As Boolean = False) As Boolean

'Does a simple test to see if str contains substr

'Example: =Contains("abcd","a") returns TRUE

'         =Contains("abcd","x") returns FALSE

'Set CaseSensitive flag to TRUE if you don't want "a" to find "A"

'         =Contains("ABCD","a",True) returns FALSE

'         =Contains("ABCD","a",False) returns TRUE (because the comparison is case sensitive)

Dim opt As Long

If CaseSensitive Then

  opt = vbBinaryCompare

Else

  opt = vbTextCompare

End If

If InStr(1, str, substr, opt) > 0 Then

  Contains = True

Else

  Contains = False

End If

End Function

Excel has a pair of useful built-in functions called SMALL and LARGE. Suppose you want to know the third-largest number in a column. Or you want to find the fourth-smallest.

The function LARGE returns the k^th largest value in a dataset. Entering

=LARGE(A1:A9,3) 

returns the third largest value in the range A1 to A9. The function is smart enough to ignore blank cells and text. However, if you have an error in a cells, the result of the formula will be an error.

Here are custom functions to replace the built-in ones, called MyLarge and MySmall. Their use is identical to the Excel version: just enter

=MYLARGE(A1:A9,3).

If you don’t know how to use these, check out the following: How do I… Create a user-defined function in Microsoft Excel at Tech Republic. Or you can download an example workbook with the functions.

(more…)