Thursday, 17 September 2015

How to add User Defined Function in an Excel Work Book?

MS Excel is very powerful for processing worksheets and any data that can be organized in a tabular form. However  built-in function are not available  for some sort of problems. For example I can share a practical scenario for which one of my friends asked me a solution.

He has a file which contains multiple data records exported from some accounting software. Each record contains an amount as the last word of the line. Like follows

Electricity Charge 3000
News Papers and Periodicals 2000
Food Expenses 1000
Miscellaneous 2500


His requirement is quite simple. He want to calculate the total of these expenses. He copied this text to a Excel wok sheet. In Excel work sheet one line of text will be considered as the content of one cell. Like this

Now the amounts should be displayed in column B so that it can be processed as numeric values.

In excel function to extract last part of a text  is not available.

 So I have added a new function in macro like below:

 Function LASTWORD(strSting As String) As String
    Dim strLastWord As String
    Dim arrWords() As String
    strSting = Trim(strSting)
    arrWords = Split(strSting, " ")
    strLastWord = arrWords(UBound(arrWords))
LASTWORD = strLastWord
End Function

 Now you call this function from any cell as a normal excel function like below