Some Function

This is chapter 43

He suggests

There are over 450 predefined functions.
But using functions, you could simplify nested calls.

A simple function

He gives us the following example:

Function NumSign(num)
    If (IsNumeric(num)) Then
        If (num = 0) Then
            NumSign = "Zero"
        ElseIf (num < 0) Then
            NumSign = "Negative"
        Else
            NumSign = "Positive"
        End If
    Else
       NumSign = "Not A Number"
    End If

End Function

He points out that functions are for computation

They are not for changing the formatting of a cell.

He provides a nice function to find the average of the top n items in a range

Function TopAvg(data, num)
    Sum = 0

    For i = 1 To num
        Sum = Sum + WorksheetFunction.Large(data, i)
    Next i
    TopAvg = Sum / num
End Function

A nested formlua preventer

Function ExtractElement(txt, n, separator)
    ExtractElement = Split(Application.Trim(txt), separator)(n - 1)
End Function

Function ElementCount(txt, sep)
    ElementCount = UBound(Split(Application.Trim(txt), sep)) + 1
End Function

Split returns an array of elements
- Split from a given text.
- With a given delimiter
- You can limit the number of splits if you wish.
The (n-1) just indexes into the array.
UBound
- Returns the largest subscript for an array.
In VBA array indexes start at 0. (Reference

One more

Function AlphaCat(data As Range)
    rowSize = data.Columns.Count

    Dim words() As Variant
    ReDim words(0 To rowSize - 1)

    i = 0
    For Each cell In data
        words(i) = cell.Value
        i = i + 1
    Next cell

    word = ""
    For i = 0 To rowSize - 1
       word = word & words(i)
    Next i

    AlphaCat = word
End Function