User Defined Functions are really useful. With a little bit of VBA you can really streamline your workflow and by putting them into an Add-in they are permanently available every time you open Excel.
In this post, I’ll give a very simple example to give a taste of what can be achieved. Imagine you are working on an Excel-based version of Scrabble – the crossword game (https://scrabble.hasbro.com/en-us). Let’s say, in your version of the game, the player types his word, one letter per cell, like this example:
Let’s not worry about vertical words for the purpose of this basic version! What we would like now is a way to calculate the value of the word in Scrabble. S is worth 1 point, C is worth 3 points, R is worth 1 point, A is worth 1 point, B is worth 3 points, L Is worth 1 point and E is also worth 1 point. So the total value of the word SCRABBLE is 14 points (also no special squares for now!). Wouldn’t it be nice if there were an Excel formula that would do that calculation? Something like this:
With a User Defined Function you can achieve this. I won’t show any ways this can be done using standard Excel formulas – suffice to say, for each letter in the word, you would probably need a lot of nested IF formulas or, if you had the foresight to make a lookup table of letter values, a lot of nested VLOOKUP formulas. All the letter values would then have to be summed for the whole word (which could vary in length). It doesn’t take much thinking about to realise that the resultant formula would be very complex and error-prone.
Let’s simplify this with a User Defined Function. The best place to start is with a table of letter values – we’ll definitely need that.
I’ve also given the sheet a new name – “Letter Values”. Next, in the Visual Basic editor, let’s add a new function called WORDVALUE, that looks like this:
Function WORDVALUE(Word As Range) Dim Score As Integer Score = 0 For I = 1 To Word.Columns.Count Letter = LCase(Word.Cells(1, I)) LetterValue = Application.WorksheetFunction.VLookup(Letter, Sheets("Letter Values").Cells.Range("A:B"), 2, False) Score = Score + LetterValue Next I WORDVALUE = Score End Function
As you can see, it is a small block code. It is easy to see how the score is calculated for the Word that is passed to the function as an Excel Range of cells. Please note though – the final version will be more complex than this as some error handling code should be added (what if the Range is not a single row? What if one of the cells is not a letter? And so on. And, of course, we haven’t catered for the special Scrabble tile – BLANK).
Now, back in the Worksheet, type the word SCRABBLE, in cells C3 through H3 and the formula =WORDVALUE(C3:H3) in another cell and that cell will show the expected result – 14!
Now, for the final touch – to make the function permanently available in Excel by saving the function into an Add-in. First of all, update the VLOOKUP in the VBA code by adding the workbook name:
And then use “Save as…” to save the workbook as an Excel Add-in, called “scrabble.xlam” (as preempted in the VBA code above). Now, as long as you have that Add-in activated in your installation of Excel, the WORDVALUE function will always be available.