VBA Function

Function is similar to a sub procedure but the main purpose of the function is to accept certain input and return a value. The value is then passed to the main procedure to finish the execution. As in stand alone VB6, you can create functions in MS Excel VB editor. The general format of a function is

Function functionName(Arg as dataType,.........) As dataType

Statements

End Function

In the example below, the user can specify a certain number in a certain range and count the total number of that number.

　

Private Sub CommandButton1_Click()
Dim total As Integer, num As Single

num = InputBox("select the number you wish to count")
myrng = InputBox("select the range")
total = addNum(num, Range(myrng))
MsgBox "The total number of " & num & " found in the range " & myrng & " is " & total
End Sub

Function addNum(x As Single, rng As Range) As Integer

Dim cell As Range, k As Integer
k = 0
For Each cell In rng
If cell.Value = x Then k = k + 1
Next cell
addNum = k

End Function

　

Explanation

　
total is the number of occurrences of the number num.
addNum is the number of occurrences of x which is passed back to total
　
k is the counter which is set to 0 at the initial stage. It will increase by 1 each time the value x is found.
The statement that call the procedure is total=addNum(num, Range(myrng))
　
　

Using the Range Object