vba - isepartners

ise consultancy - Visual basic for Applications

Microsoft Office consultancy for small business and charity organisations

What is a Visual Basic function?

The best way to answer this question is through a worked example of a simple function to be used in excel.

Suppose that we have the spreadsheet below. It contains a list of names formated as initials and then surname.

For this example the task will be to get the surname in a cell of its own.

Now if you are familiar with excel functions you'll know that with a combination of the 'Find' and 'Mid' functions you can produce the answer, however lets assume we want do do this by a user designed function.

First of all open the Visual Basic editor by selecting Tools/Macro/Visual Basic Editor. Then Insert/Module

The following window opens.

n the main window type in the code as shown below.

Function SplitSurname(Text As String, Char As String)

As soon as this is typed note the entry in the drop down box above the window.

Now carry on with the rest of the code until your window looks as below.

What does all this mean?

Follow through the code in the window by going through the steps below, note that the items in green are comments that Visual Basic will ignore but can explain to the reader what is happening.

1.Define the function name as Splitsurname and the variables that it will use, ie the cell to look at (Text) and the character to split by (Char).Ensure that both variables are defined as Text types.

2.Define two additional variables only to be used in the calculation. Length is the length of the Text we want to look and CharPos is the position of the character we want to look for.

Dim Length As Integer
Dim CharPos As Integer

3.Get the Length of the Text by using the Len Function

Length = Len(Text)

4.Get the character position by using the InStr function
1 means look from character one, Text means look in the cell that we call Text at runtime,Char means look for the character we ask for at runtime

CharPos = InStr(1, Text, Char)

5.Use the Mid function to find the Surname
Text means look in the cell that we call Text at runtime, CharPos+1 means look for the position of the character we ask for at runtime plus 1, Length-CharPos means return a string the length of the 'Text' less the character position.

SplitSurname = Mid(Text, CharPos + 1, Length - CharPos)
End Function

To use the function first save it, go to excels function list and you will find it in the user defined group. Select it and enter the variables as if it were a standard function.
In our example spreadsheet the entry in 'Text' will be A1 and in 'Char' it will be " ".
You can then copy the function down the sheet as normal.