VBA Macro Excel Tutorial: Function Procedure

The difference between Sub procedure and Function procedure is Function procedure usually return a single value or an array. Function procedures can be used in two ways:

• As part of an expression in VBA Macro Excel procedure.
• In formulas that you create in a worksheet.

The following is a custom function named DiscountPrice. This function returns the discount price as currency.


Function DiscountPrice(Price as Currency) as Currency
   DiscountPrice = Price * 0.95
End Function


Here's an example how to use the function in a formula:

=DiscountPrice(200)

And this is an example how to use the function in a VBA Macro Excel procedure:

Sub DiscountIt()
   inputPrice = InputBox("Enter price: ")
   MsgBox "New price: " & DiscountPrice(inputPrice)
End Sub