<<Lesson 4>> [Contents] <<Lesson 6>>
5.1 Sub Procedure
A sub procedure in Excel VBA is a procedure that performs a specific task and to return values, but it does not return a value associated with its name. However, it can return a value through a variable name. Sub procedures are usually used to accept input from the user, display information, print information, manipulate properties or perform some other tasks. It is a program code by itself and it is not an event procedure because it is not associated with a runtime procedure or an Excel VBA control such as a command button. It is called by the main program whenever it is required to perform a certain task. A Sub procedure helps to make programs smaller and easier to manage.
A sub procedure begins with a Sub statement and ends with an End Sub statement. The program structure of a sub procedure is as follows:
Sub ProcedureName (arguments)
Statements
End Sub
Example 5.1
In this example, a sub procedure ResizeFont is created to resize the font in the range if it fulfils a value greater than 40. There are two parameters or arguments associated with the sub procedure, namely x for font size and Rge for range. This sub procedure is called by the event procedure Sub CommandButton1_Click () and passed the values 15 to x (for font size) and Range (“A1:A10”) to Rge (for range) to perform the task of resizing the font to 15 for values>40 in range A1 to A10.
Private Sub CommandButton1_Click()
ResizeFont 15, Range(“A1:A10”)
End Sub
Sub ResizeFont(x As Variant, Rge As Range)
Dim cel As Range
For Each cel In Rge
If cel.Value > 40 Then
cel.Font.Size = x
End If
Next cel
End Sub
Figure 5.1
To make the program more flexible and interactive, we can modify the above program to accept input from the user. The values input by the user through the input boxes will be passed on to the procedure to execute the job, as shown in Example 20.2.
Example 5.2
Private Sub CommandButton1_Click()
Dim rng As String
rng = InputBox(“Input range”)
x = InputBox(“Input Font Size”)
ResizeFont x, Range(rng)
End Sub
Sub ResizeFont(x As Variant, Rge As Range)
Dim cel As Range
For Each cel In Rge
If cel.Value > 40 Then
cel.Font.Size = x
End If
Next cel
End Sub
5.2 Functions
In Excel VBA, a function is similar to a sub procedure but the main purpose of the function is to accept a certain input from the user and return a value which is passed on to the main program to finish the execution. There are two types of functions, the built-in functions (or internal functions) and the functions created by the programmers, or simply called user-defined functions. The first built-in function is the Message Box. The message box acts as a dialog box where users can interact with the computer, it is able to perform certain actions in response to what the user clicks or selects.
The syntax for a message box in Excel VBA is as follows,
message=MsgBox(Prompt, Style Value,Title)
The first argument, Prompt, will display the message in the message box. The Style Value determines what type of command button will appear in the message box. The Title argument will display the title of the message board while message is a variable that holds values that are returned by the MsgBox ( ) function. The values are determined by the type of buttons being clicked by the users. It has to be declared as Integer data type in the procedure or in the general declaration section. Please refer to Lesson 10 of Visual Basic Tutorial for the detail listings of the Style Value as well as the returned value.
In this example, We created three command buttons which show different Options. We put in a few program codes in the last button which involve the use of If…Then…Elseif statements.
Figure 5.2
Double-click the top button and enter the following code:
Private Sub CommandButton1_Click()
MsgBox (“Welcome to VBA Programming”)
End Sub
By clicking the first message box, you will see the message as shown in Figure 5.2
Figure 5.3
Now double-click on the second button and enter the following code:
Private Sub CommandButton2_Click()
Dim message As String
message = MsgBox(“Click Yes to Proceed, No to stop”, vbYesNoCancel, “Login”)
If message = vbYes Then
MsgBox “You may proceed”
ActiveWorkbook.Activate
ElseIf message = vbNo Then
MsgBox “Your application is terminated”
ActiveWorkbook.Close
End If
End Sub
Click on the button and you shall see the following dialog. You will notice the Yes, No, Cancel buttons:
Figure 5.4
Now double-click on the second button and enter the following code:
Private Sub CommandButton3_Click()
Dim message As Integer
message = MsgBox(“Click Yes to Proceed, No to stop”, vbYesNo, “Login”)
If message = 6 Then
Range(“A1”).Value = “You may proceed”
ActiveWorkbook.Activate
ElseIf message = 7 Then
ActiveWorkbook.Close
End If
End Sub
Click on the button and you would notice that the displayed message box comprises only the Yes and No button, as shown in Figure 5.4
Figure 5.4
5.2.1 InputBox function
An InputBox( ) function displays a message box where the user can enter a value or a message in the form of text. The syntax is
myMessage=InputBox(Prompt, Title, default_text, x-position, y-position)
myMessage is a variant data type but typically it is declared as a string, which accepts the message input by the users. The arguments are explained as follows:
- Prompt represents the message displayed normally as a question asked.
- The title represents the title of the Input Box.
- default-text represents the default text that appears in the input field where the user can use it as his or her intended input
- x-position and y-position represents the position or the coordinate of the input box.
Example 5.3
In this example, we insert a label and a command button into the MS Excel spreadsheet. Double click on the command button and enter the Excel VBA code as follows:
Private Sub CommandButton1_Click()
Dim userMsg As String
userMsg = InputBox(“What is your message?”, “Message Entry Form”, “Enter your messge here”, 500, 700)
If userMsg <> “” Then
MsgBox( userMsg)
Else
MsgBox(“No Message”)
End If
End Sub
* The InputBox is shown in Figure 5.4
Figure 5.5 InputBox
5.2.1 Creating User-Defined Functions
The syntax to create a User-Defined function is as follows:
Public Function functionName (Arg As dataType,…) As dataType
or
Private Function functionName (Arg As dataType,…) As dataType
Public indicates that the function is applicable to the whole project while Private indicates that the function is only applicable to a certain module or procedure.
In order to create a user-defined function in Excel VBA, you need to go into the Visual Basic Editor in MS Excel Spreadsheet.In the Visual Basic Editor, click on Insert on the menu bar to insert a module into the project. Enter the following code as shown in Figure 5.2. This function is to calculate the cube root of a number.
Figure 5.6
Now enter the function CubeRoot just like you enter the formula of MS Excel, as shown in Figure 5.3. The value of cube root for the number in cell C4 will appear in cell D4.
Figure 5.7
