We have learned how to write code for the command button in the Excel 2010 VBA editor. Another way to write code in Excel 2010 VBA is to go to the code window directly by clicking on View Code in the Developer menu bar. In the editor window, click on insert module to start code that is not related to an Active-X control.
A Subroutine in Excel 2010 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. Subroutines 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. Sub procedures help to make programs smaller and easier to manage.
A Subroutine begins with a Sub statement and ends with an End Sub statement. The program structure 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
A subroutine is different from a function that it does not return a value directly. You can include parameters in a subroutine.
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 number of hours and display on the message box.
In Excel 2010 VBA, 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 function.
6.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 format 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.
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 show in Figure 6.1
Figure 6.1: InputBox
6.2.1 Creating User Defined Functions
The syntax to create a function is as follows:
Public Function functionName (Arg As dataType,……….) As dataType
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 2010 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 6.2. This function is to calculate the cube root of a number.
Now enter the function CubeRoot just like you enter the formula of MS Excel, as shown in Figure 6.3. The value of cube root for the number in cell C4 will appear in cell D4.