Lesson 6 Subroutines and Functions

6.1: Subroutines

A Subroutine in Excel VBA is a procedure that performs some tasks. It is a program code but 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.

To program a subroutine, launch the code window by clicking on View Code in the Developer menu bar. In the editor window, click on insert module to start coding.

The module is a code sheet that is specific to your Excel 2010 VBA. Code created in the module is not directly triggered by events on the spreadsheet, but need to be called by another program. It can be called by running a macro, press F5, running a UserForm, etc. If the code is a function, it can be inserted directly into the cells of the spreadsheet.

An Excel 2010 VBA project usually uses one or more modules to store the necessary subroutines or functions known as procedures. You can make the subroutines or functions private or public. If the code is private, it can only be used by the current workbook whereas a code that is made public can be used by other procedure in another module in the workbook.

A Subroutine begins with a Sub statement and ends with an End Sub statement. The syntax of a sub procedure is as follows:

Sub subProg(arguments)
 Statements
End Sub

Subroutines are called by the main program using the Call keyword.

Sub MainProg( )
 Call  SubProg()
End Sub
 

Example 6.1

In this example, the main program calls the subroutine findHidden and execute it. The end result is a message box that displays the hidden text.

Private Sub CommandButton1_Click()
 Call findHidden
End Sub

Sub findHidden()
 hidden_txt = "@#%43&*"
 MsgBox hidden_txt
End Sub

*You can do away with the Call keyword to achieve the same result

Example 6.2

Private Sub CommandButton1_Click()
 Call salary(10, 300)
End Sub

Sub salary(wage As Single, hours As Single)
 MsgBox wage * hours
End Sub

In this example, the Call command calls the subroutine salary and passes the parameters 10 and 300 to it. It will calculate the salary based on wage per hour and the number of hours and display on the message box.

6.2 Functions

In Excel VBA 2010, a function is similar to a subroutine 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 that we have already learned and familiar with its usage is the Message Box. We are not going to repeat here but we shall take a look at its syntax once more, i.e.

message=MsgBox(Prompt, Style Value,Title)

Now we shall examine the next commonly used function in Excel VBA, the InputBox.

6.2.1 The Input Box

The 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–The message displayed normally as a question asked.
• Title– The title of the Input Box.
• default-text–The default text that appears in the input field where users can use it as his intended input or he may change to the message he wishes to key in.
• x-position and y-position – the position or the coordinate of the input box.

Example 6.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

6.2.1 Creating User Defined Functions

The syntax to create a 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.

To create a user-defined function, start a new workbook and click on Visual Basic at the Developer window to enter the VB Editor. Click on Insert on the menu bar to insert a module into the project. Enter the following code as shown in Figure 6.4.

Example 6.4 Cube Root

This function is to calculate the cube root of a number.

Function CubeRoot(num As Double) As Double
CubeRoot = num ^ (1 / 3)
End Function

Now enter the function CubeRoot just like you enter the formula of MS Excel, as shown in Figure 6.5. The value of cube root for the number in cell C4 will appear in cell D4.

Example 6.5 Pythagoras Theorem

This function calculate the hypotenuse of a right angle triangle.

Function h(a As Single, b As Single) As Double
h = Sqr(a ^ 2 + b ^ 2)
End Function

Example 6.6 Salary Function

We can transform the sub procedure in Example 6.2 into a Salary function. This function will calculate the salary based on the wage per hour and hours worked. We use if...then..else to determine payroll based on hours worked. The code is as follows:

Function salary(wage As Single, hours As Single)
If hours > 10 And hours <= 160 Then
 salary = wage * hours
 ElseIf hours > 160 And hours <= 180 Then
 salary = wage * hours * 1.2
 ElseIf hours > 180 Then
 salary = wage * hours * 1.5
 Else
 salary = 0
 End If
  
End Function