Microsoft Office Tutorials and References
In Depth Information
Function Examples
Even if you don’t need custom functions in a worksheet, creating Function procedures can
make your VBA coding much simpler. For example, if your VBA procedure calculates sales
commissions, you can use the exact same function and call it from a VBA procedure. Here’s a tiny
procedure that asks the user for a sales amount and then uses the Commission function to
calculate the commission due:
Sub CalcComm()
Dim Sales as Long
Sales = InputBox(“Enter Sales:”)
MsgBox “The commission is “ & Commission(Sales)
End Sub
The CalcComm procedure starts by displaying an input box that asks for the sales amount. Then
it displays a message box with the calculated sales commission for that amount.
This Sub procedure works, but it’s rather crude. Following is an enhanced version that displays
formatted values and keeps looping until the user clicks No (see Figure 10-5).
Figure 10-5: Using a function to display the result of a calculation.
Sub CalcComm()
Dim Sales As Long
Dim Msg As String, Ans As String
‘ Prompt for sales amount
Sales = Val(InputBox(“Enter Sales:”, _
“Sales Commission Calculator”))
‘ Build the Message
Msg = “Sales Amount:” & vbTab & Format(Sales, “$#,##0.00”)
Msg = Msg & vbCrLf & “Commission:” & vbTab
Msg = Msg & Format(Commission(Sales), “$#,##0.00”)
Msg = Msg & vbCrLf & vbCrLf & “Another?”
‘ Display the result and prompt for another
Ans = MsgBox(Msg, vbYesNo, “Sales Commission Calculator”)
If Ans = vbYes Then CalcComm
End Sub
Search JabSto ::

Custom Search