In This Lesson, you’ll learn
Create Sub, Event and Function procedures
Ways to execute these procedures
How to execute Function procedures
1) Comparison of Subs, Events and Functions procedures
Procedures are constructed with VBA code in the Visual Basic Editor. The three most common types of procedures are Sub, Function and Event
* A Sub procedure is consist of VBA statements that performs an action (or actions) with Excel. Program code are executed when they are called (by other part of the program)
* An Event procedure is predefines by VBA in the sense that you cannot change the name of the procedure, nor the object within Excel to which the procedure belongs, nor the conditions under which the procedure is triggered. Program code are executed when they are triggered by an event (like the Worksheet_Activate event)
* A Function procedure is a group of VBA statements that performs a calculation and returns a single value. (You cannot create a Function using the macro recorder)
Most of the macros you write in VBA are Sub procedures. Depends on the VBA code that you’ve written, if you execute the Sub procedure and something happens.
As for Event procedures , all you need to do with these procedures is to add the code to be executed when the event is triggered. (Like the Worksheet_Activate event)
And to define Function as a procedure, just think of the many Excel function that we use everyday like MAX, SUM, AVERAGE. I believe you’re already familiar with the concept of a function. These function takes one or more arguments and returns a single value. The same goes for Function procedures that you can develop with VBA.
2) Private, Public, and Procedure Scope
Procedures in Excel VBA can have either private or public scope. The Private and Public keywords used with procedure definitions have a similar function to that used with variable declarations(discussed in Part C). The use of the Private and Public keywords are optional, but VBA includes them in predefined event procedures.
When you declare a procedure…
Public - makes the procedure visible to all other procedures in all modules in the project.
Private - that the procedure is visible only to other procedures within the same module
And, if the Private or Public is not specified, then the procedure is public by default.
a) Sub procedures
To create a procedure, activate the VBE window and insert a module. Double click on Module1. This will bring up the code window. Add the code shown in Figure 5.1
Procedures are constructed with VBA code in the Visual Basic Editor. The three most common types of procedures are Sub, Function and Event
* A Sub procedure is consist of VBA statements that performs an action (or actions) with Excel. Program code are executed when they are called (by other part of the program)
* An Event procedure is predefines by VBA in the sense that you cannot change the name of the procedure, nor the object within Excel to which the procedure belongs, nor the conditions under which the procedure is triggered. Program code are executed when they are triggered by an event (like the Worksheet_Activate event)
* A Function procedure is a group of VBA statements that performs a calculation and returns a single value. (You cannot create a Function using the macro recorder)
Most of the macros you write in VBA are Sub procedures. Depends on the VBA code that you’ve written, if you execute the Sub procedure and something happens.
As for Event procedures , all you need to do with these procedures is to add the code to be executed when the event is triggered. (Like the Worksheet_Activate event)
And to define Function as a procedure, just think of the many Excel function that we use everyday like MAX, SUM, AVERAGE. I believe you’re already familiar with the concept of a function. These function takes one or more arguments and returns a single value. The same goes for Function procedures that you can develop with VBA.
2) Private, Public, and Procedure Scope
Procedures in Excel VBA can have either private or public scope. The Private and Public keywords used with procedure definitions have a similar function to that used with variable declarations(discussed in Part C). The use of the Private and Public keywords are optional, but VBA includes them in predefined event procedures.
When you declare a procedure…
Public - makes the procedure visible to all other procedures in all modules in the project.
Private - that the procedure is visible only to other procedures within the same module
And, if the Private or Public is not specified, then the procedure is public by default.
a) Sub procedures
To create a procedure, activate the VBE window and insert a module. Double click on Module1. This will bring up the code window. Add the code shown in Figure 5.1
Fig 5.1 - Click for larger imageYou start a Sub procedure with the keyword Sub and ends with an End Sub statement. Figure 5.1 show this: The code is in the Code Window
Sub Hello()
MsgBox “Hello World!”
End Sub
This example shows a procedure named Hello. A set of parentheses follows the procedure’s name. Most of the time, these parentheses are empty. However, you may pass arguments to Sub procedures from other procedures. If your Sub uses arguments, list them between the parentheses.
Like the macro you recorded in Part B using the Excel macro recorder, the result is always a Sub procedure.
As you see later in this Part, Excel provides quite a few ways to execute a VBA Sub procedure.
b) Event procedures
When something happens in Excel, its call an Event. Let’s look at a few examples…
· A workbook is opened or closed.
· A workbook is activated.
· A worksheet is activated or deactivated.
· A workbook is saved.
· A worksheet is calculated.
· When a button is clicked
· When a particular key or key combination is pressed.
· A particular time of day occurs.
· An error occurs.
When you create an event procedure, VBA will automatically define it for you. Activate the VBE window and double click on Sheet1 to bring up the Code Window. Select Worksheet and then the Calculate event. When you do this, like the example shown in Figure 5.2, the Private Sub Worksheet_Calculate() statement and the End Sub is set by VBA. Add the code
Private Sub Worksheet_Calculate()
MsgBox "Welcome!"
End Sub
Sub Hello()
MsgBox “Hello World!”
End Sub
This example shows a procedure named Hello. A set of parentheses follows the procedure’s name. Most of the time, these parentheses are empty. However, you may pass arguments to Sub procedures from other procedures. If your Sub uses arguments, list them between the parentheses.
Like the macro you recorded in Part B using the Excel macro recorder, the result is always a Sub procedure.
As you see later in this Part, Excel provides quite a few ways to execute a VBA Sub procedure.
b) Event procedures
When something happens in Excel, its call an Event. Let’s look at a few examples…
· A workbook is opened or closed.
· A workbook is activated.
· A worksheet is activated or deactivated.
· A workbook is saved.
· A worksheet is calculated.
· When a button is clicked
· When a particular key or key combination is pressed.
· A particular time of day occurs.
· An error occurs.
When you create an event procedure, VBA will automatically define it for you. Activate the VBE window and double click on Sheet1 to bring up the Code Window. Select Worksheet and then the Calculate event. When you do this, like the example shown in Figure 5.2, the Private Sub Worksheet_Calculate() statement and the End Sub is set by VBA. Add the code
Private Sub Worksheet_Calculate()
MsgBox "Welcome!"
End Sub
Fig 5.2 - Click for larger image
Fig 5.3 - Click for larger image
This code when executed will display the message box “Welcome”. It will executed whenever there is calculation in Sheet1 i.e. the Calculate event is triggered.
I’ll show you another example with a Workbook_Open event.
i) Open a new workbook.
ii) Activate the VBE by pressing Alt-F11
iii) Double-click the ThisWorkbook item. (Figure 5.2)
The VBE displays an empty Code window for the ThisWorkbook object.
iv) In the Code window on the right, select Workbook from the Object (left) dropdown list.
The VBE will automatically enters the beginning and ending statements for a Workbook_Open procedure
v) Enter the following code:
Private Sub Workbook_Open()
Dim Msg As String
If Today(Now) = 2 Then
Msg = “Its Monday. Welcome back “
Msg = Msg & “to the real world!”
MsgBox Msg
End If
End Sub
Workbook_Open is executed automatically whenever the workbook is opened. It uses VBA’s Today function to determine the day of the week. If it’s Monday (i.e. day 2), a message box greets the user to back to work. If it’s not Monday, nothing happens.
c) Function procedures
For a Function procedure, you start with the keyword Function and ends with an
End Function statement. Here’s a simple example:
Function SquareRoot(num)
SquareRoot = num ^ (1 / 2)
End Function
This function, named SquareRoot, takes one argument (i.e num), which is enclosed in parentheses. Functions can have any number of arguments or none at all. When the function is executed, it returns a single value — the square root of the argument passed to the function.
VBA allows you to define what type of value (such as data type) is returned by a Function procedure. Part D contains more information on specifying data types.
Function can be used in only two ways. You can execute it from another procedure (a Sub or another Function procedure) or use it in a worksheet formula.
Let’s do another example. This function is to calculate the area of a Rectangle. The formula equal : Rectangle = Length x Width. Here’s how
Public Function Rect(ByVal length As Double, width As Double) As Double
Rect = length * width
End Function
A procedure that utilizes the Rect() function can be written as follows:
Private Sub CalcRect()
Dim length As Double
Dim width As Double
Dim result As Double
length = Val(InputBox(“Enter the length.”, “Length”))
width = Val(InputBox(“Enter the width.”, “Width”))
result = Rect(length, width)
MsgBox (“The area is” & result)
End Sub
The code is really very simple. First it ask for the user input with two input boxes i.e. the length and the width. After that the two values is assigned to the function Rect(). Here the calculation is made and the result is returned to the calling procedure. A msgbox will displays the area of the rectangle.
3) Naming Subs and Functions
There are rules to follow when you name procedures, i.e
* VBA does not diferentiate between uppercase and lowercase letters.
* You can’t use any of the following characters in a name: #, $, %, &, @, ^, *, or !.
* Letters, numbers, and some punctuation characters are allowed, but the first character must be a letter.
* Each name should be less than 255 characters. (Of course, you would never make a procedure name this long.)
* No spaces or periods in the name.
Its common sense that a procedure’s name should describe the routine’s purpose. For example, GetUserName, InputTaxData, PerformSort_and etc.
Some programmers prefer using sentence like names that provide a complete description of the procedure. Some examples include WriteReportToTextFile and Get_Print_Options_and_Print_Report. The use of such lengthy names has pros and cons. On the one hand, such names are descriptive, unambiguous and not too long. You can have your own naming style, just make the names descriptive and to avoid meaningless names such as GetIt, DoThis, RunAgain, and Macro1.
4) Executing Events procedures
Events procedures are automatically executed when the event is triggered. You have already seen a few examples of event procedures; such as the Click() event procedure of a Command Button control, and the Calculate() event procedure of a worksheet.
You cannot change the name of the event procedures as they are already predefines by Excel VBA. Here, all you need to do is search the specific event and insert code to be executed.
When the specified event happened or triggered like Worksheet_Activate, the code that you add on this procedure will be executed. (I will discuss more on this in Part H)
5) Executing Sub procedures
Next, I am going to explain to you on the methods or ways to execute these procedures. Else a procedure is worthless unless you know how to run it.
Let’s start with the Sub Procedures. To execute a Sub Procedure (not an exhaustive list)
* Use the Run-Run Sub/UserForm command on the VBE window. This menu command has two alternatives: The F5 key, and the Run Sub/UserForm button on the Standard toolbar in the VBE. (show screenshot)
* In the Excel’s Macro dialog box, choose Tools-Macro-Macros or press the Alt-F8 shortcut key. When the Macro dialog box appears, select the Sub procedure you want and click Run.
* An easy one will be by pressing Ctrl+key shortcut assigned to the Sub procedure. You must remember the shortcut key that you have assigned.
* Clicking a button or a shape on a worksheet that you have assigned a Sub procedure to it.
* From another Sub procedure that you write.
* Automatically, when you open or close a workbook.
* And, when an event occurs like worksheet activate, userform initialize etc
I will show you some of these techniques in the following sections. We will execute the procedures done above.
a) Executing the Sub procedure directly
The quickest way to execute this procedure is by doing so directly from the VBA module in which you defined it. Follow these steps:
i) Activate the VBE and select the VBA module that contains the procedure.
ii) Move the cursor anywhere in the procedure’s code.
iii) Press F5 (or choose Run-Run Sub/UserForm).
iv) Click on the Run-Run Sub/UserForm
I’ll show you another example with a Workbook_Open event.
i) Open a new workbook.
ii) Activate the VBE by pressing Alt-F11
iii) Double-click the ThisWorkbook item. (Figure 5.2)
The VBE displays an empty Code window for the ThisWorkbook object.
iv) In the Code window on the right, select Workbook from the Object (left) dropdown list.
The VBE will automatically enters the beginning and ending statements for a Workbook_Open procedure
v) Enter the following code:
Private Sub Workbook_Open()
Dim Msg As String
If Today(Now) = 2 Then
Msg = “Its Monday. Welcome back “
Msg = Msg & “to the real world!”
MsgBox Msg
End If
End Sub
Workbook_Open is executed automatically whenever the workbook is opened. It uses VBA’s Today function to determine the day of the week. If it’s Monday (i.e. day 2), a message box greets the user to back to work. If it’s not Monday, nothing happens.
c) Function procedures
For a Function procedure, you start with the keyword Function and ends with an
End Function statement. Here’s a simple example:
Function SquareRoot(num)
SquareRoot = num ^ (1 / 2)
End Function
This function, named SquareRoot, takes one argument (i.e num), which is enclosed in parentheses. Functions can have any number of arguments or none at all. When the function is executed, it returns a single value — the square root of the argument passed to the function.
VBA allows you to define what type of value (such as data type) is returned by a Function procedure. Part D contains more information on specifying data types.
Function can be used in only two ways. You can execute it from another procedure (a Sub or another Function procedure) or use it in a worksheet formula.
Let’s do another example. This function is to calculate the area of a Rectangle. The formula equal : Rectangle = Length x Width. Here’s how
Public Function Rect(ByVal length As Double, width As Double) As Double
Rect = length * width
End Function
A procedure that utilizes the Rect() function can be written as follows:
Private Sub CalcRect()
Dim length As Double
Dim width As Double
Dim result As Double
length = Val(InputBox(“Enter the length.”, “Length”))
width = Val(InputBox(“Enter the width.”, “Width”))
result = Rect(length, width)
MsgBox (“The area is” & result)
End Sub
The code is really very simple. First it ask for the user input with two input boxes i.e. the length and the width. After that the two values is assigned to the function Rect(). Here the calculation is made and the result is returned to the calling procedure. A msgbox will displays the area of the rectangle.
3) Naming Subs and Functions
There are rules to follow when you name procedures, i.e
* VBA does not diferentiate between uppercase and lowercase letters.
* You can’t use any of the following characters in a name: #, $, %, &, @, ^, *, or !.
* Letters, numbers, and some punctuation characters are allowed, but the first character must be a letter.
* Each name should be less than 255 characters. (Of course, you would never make a procedure name this long.)
* No spaces or periods in the name.
Its common sense that a procedure’s name should describe the routine’s purpose. For example, GetUserName, InputTaxData, PerformSort_and etc.
Some programmers prefer using sentence like names that provide a complete description of the procedure. Some examples include WriteReportToTextFile and Get_Print_Options_and_Print_Report. The use of such lengthy names has pros and cons. On the one hand, such names are descriptive, unambiguous and not too long. You can have your own naming style, just make the names descriptive and to avoid meaningless names such as GetIt, DoThis, RunAgain, and Macro1.
4) Executing Events procedures
Events procedures are automatically executed when the event is triggered. You have already seen a few examples of event procedures; such as the Click() event procedure of a Command Button control, and the Calculate() event procedure of a worksheet.
You cannot change the name of the event procedures as they are already predefines by Excel VBA. Here, all you need to do is search the specific event and insert code to be executed.
When the specified event happened or triggered like Worksheet_Activate, the code that you add on this procedure will be executed. (I will discuss more on this in Part H)
5) Executing Sub procedures
Next, I am going to explain to you on the methods or ways to execute these procedures. Else a procedure is worthless unless you know how to run it.
Let’s start with the Sub Procedures. To execute a Sub Procedure (not an exhaustive list)
* Use the Run-Run Sub/UserForm command on the VBE window. This menu command has two alternatives: The F5 key, and the Run Sub/UserForm button on the Standard toolbar in the VBE. (show screenshot)
* In the Excel’s Macro dialog box, choose Tools-Macro-Macros or press the Alt-F8 shortcut key. When the Macro dialog box appears, select the Sub procedure you want and click Run.
* An easy one will be by pressing Ctrl+key shortcut assigned to the Sub procedure. You must remember the shortcut key that you have assigned.
* Clicking a button or a shape on a worksheet that you have assigned a Sub procedure to it.
* From another Sub procedure that you write.
* Automatically, when you open or close a workbook.
* And, when an event occurs like worksheet activate, userform initialize etc
I will show you some of these techniques in the following sections. We will execute the procedures done above.
a) Executing the Sub procedure directly
The quickest way to execute this procedure is by doing so directly from the VBA module in which you defined it. Follow these steps:
i) Activate the VBE and select the VBA module that contains the procedure.
ii) Move the cursor anywhere in the procedure’s code.
iii) Press F5 (or choose Run-Run Sub/UserForm).
iv) Click on the Run-Run Sub/UserForm
b) Executing the procedure from the Macro dialog box
Most of the time, you execute Sub procedures from Excel, not from the VBE.
The steps below describe how to execute a macro by using Excel’s Macro dialog box.
i. Activate Excel.
Alt+F11 is the express route (of course you can skip this step if Excel is already active).
ii. Choose Tools-Macro-Macros (or press Alt+F8).
Excel displays the dialog box shown below (Fig 5.4)
iii. Select the macro.
iv. Click Run (or double-click the macro’s name in the list box).
Most of the time, you execute Sub procedures from Excel, not from the VBE.
The steps below describe how to execute a macro by using Excel’s Macro dialog box.
i. Activate Excel.
Alt+F11 is the express route (of course you can skip this step if Excel is already active).
ii. Choose Tools-Macro-Macros (or press Alt+F8).
Excel displays the dialog box shown below (Fig 5.4)
iii. Select the macro.
iv. Click Run (or double-click the macro’s name in the list box).
Fig 5.4 - Click for larger image
c) Executing a macro by using a shortcut key
Another way to execute a macro is to press its shortcut key. But before you can use this method, you have to set things up. Specifically, you must assign a shortcut key to the macro.
You have the opportunity to assign a shortcut key in the Record Macro dialog box when you begin recording a macro. If you create the procedure without using the macro recorder, you can assign a shortcut key (or change an existing shortcut key) by using the following procedure:
i) Choose Tools-Macro-Macros.
ii) Select the Sub procedure name from the list box.
In this example, the procedure is named Hello.
iii) Click the Options button.
Excel displays the dialog box shown in Figure 5.5.
iv) Click the Shortcut Key option and enter a letter in the box labeled Ctrl.
The letter you enter corresponds to the key combination you want touse for executing the macro. Here you enter the letter c, you can then execute the macro by pressing Ctrl+h.
v) Click OK or Cancel to close the Macro Options dialog box.
Another way to execute a macro is to press its shortcut key. But before you can use this method, you have to set things up. Specifically, you must assign a shortcut key to the macro.
You have the opportunity to assign a shortcut key in the Record Macro dialog box when you begin recording a macro. If you create the procedure without using the macro recorder, you can assign a shortcut key (or change an existing shortcut key) by using the following procedure:
i) Choose Tools-Macro-Macros.
ii) Select the Sub procedure name from the list box.
In this example, the procedure is named Hello.
iii) Click the Options button.
Excel displays the dialog box shown in Figure 5.5.
iv) Click the Shortcut Key option and enter a letter in the box labeled Ctrl.
The letter you enter corresponds to the key combination you want touse for executing the macro. Here you enter the letter c, you can then execute the macro by pressing Ctrl+h.
v) Click OK or Cancel to close the Macro Options dialog box.
Fig 5.5 - Click for larger image
Fig 5.6 - Click for larger image
After assigning a shortcut key, you can press that key combination to execute the macro. Please note that the shortcut keys you assign to macros override Excel’s built-in shortcut keys. For example, if you assign Ctrl+C to a macro, you can’t use this shortcut key to copy data in your workbook like you usually do.
d) Executing the procedure from a button or shape
Another way for executing the macro is by assigning the macro to a button (or any other shape) on a worksheet. To assign the macro to a button, follow these steps:
i) Activate a worksheet.
ii) Add a button from the Forms group.
iii) Click the Button tool in the Forms group.
iv) Drag in the worksheet to create the button.
After you add the button to your worksheet, Excel will displays the Assign Macro dialog box automatically.
v) Select the macro you want to assign to the button.
vi) Click OK.
Clicking the button will execute the macro.
You can also assign a macro to any other shape or object. For example, assume you’d like to execute a macro when the user clicks a Rectangle object then draw a Rectangle object, right click on it and choose Assign Macro from its shortcut menu and click OK.
After performing these steps, clicking the rectangle will execute the macro.
e) Executing the procedure from another procedure
You can also execute a procedure from another procedure. Follow these steps if you want to give this a try:
i) Activate the VBA module that holds the Hello routine.
ii) Enter this new procedure.
Sub CallSub()
Call Hello
End Sub
iii) Execute the CallSub macro.
The examples given above is not an exhaustive list. There are other ways to execute a procedure, I hope I can explain to you in future.
6) Executing Function procedures
You can only execute Functions in two ways i.e.
* By calling the function from another Sub procedure or Function procedure
*
* By using the function in a worksheet formula
Try this simple function. Enter it into a VBA module:
Function SquareRoot(num)
SquareRoot = num ^ (1/2)
End Function
Altough this function is very simple, that is it only calculates the square root of the number passed to it as its argument. But it is a good starting point for us to understand functions especially the important concept on how functions return the value that pass to it.
Here, a formula is calculated (number to the power of ½) and then the result is assigned to the variable SquareRoot. To tell the function what value to return, you assign that value to the name of the function. We use SquareRoot as the function name as well.
a) Calling the function from a Sub procedure
Looks how the function is call from another procedure by entering the following simple procedure in the same VBA module that contains the SquareRoot function:
Sub GetSub()
Ans = SquareRoot(16)
MsgBox Ans
End Sub
When this Sub is run, Excel shows a message box that contains the value of the Ans variable, which is 4.
What happened is this,
* We pass an argument of 16 to the SquareRoot function to be calculated
* Here the function returns a value (4) and assigned to the Ans variable.
* After that, the MsgBox function displays the Ans variable as 4
You can also simplify the procedure GetSub by omitting the Ans variable code
Sub GetSub()
MsgBox SquareRoot(16)
End Sub
b) Calling a function from a worksheet formula
Its very simple to call this VBA Function procedure from a worksheet formula.
Goto a worksheet in the same workbook that holds the SquareRoot function. Then enter the following formula into any cell:
=SquareRoot(25)
You will get 5 in the cell i.e. the square root of 25
you can also use a cell reference as the argument for the SquareRoot function. For example, if cell C1 contains a value, you can enter
=SquareRoot(C1). In this case, the function returns the number obtained by calculating the square root of the value in C1.
This function can be used as many times as you want in the worksheet. When you select the Insert-Insert Function menu from Excel and choose the User Defined category, your custom functions also appear in the Insert Function dialog box together with Excel’s built-in functions. As shown in Figure 5-7, the Insert Function dialog box lists your very own function. Bravo!
d) Executing the procedure from a button or shape
Another way for executing the macro is by assigning the macro to a button (or any other shape) on a worksheet. To assign the macro to a button, follow these steps:
i) Activate a worksheet.
ii) Add a button from the Forms group.
iii) Click the Button tool in the Forms group.
iv) Drag in the worksheet to create the button.
After you add the button to your worksheet, Excel will displays the Assign Macro dialog box automatically.
v) Select the macro you want to assign to the button.
vi) Click OK.
Clicking the button will execute the macro.
You can also assign a macro to any other shape or object. For example, assume you’d like to execute a macro when the user clicks a Rectangle object then draw a Rectangle object, right click on it and choose Assign Macro from its shortcut menu and click OK.
After performing these steps, clicking the rectangle will execute the macro.
e) Executing the procedure from another procedure
You can also execute a procedure from another procedure. Follow these steps if you want to give this a try:
i) Activate the VBA module that holds the Hello routine.
ii) Enter this new procedure.
Sub CallSub()
Call Hello
End Sub
iii) Execute the CallSub macro.
The examples given above is not an exhaustive list. There are other ways to execute a procedure, I hope I can explain to you in future.
6) Executing Function procedures
You can only execute Functions in two ways i.e.
* By calling the function from another Sub procedure or Function procedure
*
* By using the function in a worksheet formula
Try this simple function. Enter it into a VBA module:
Function SquareRoot(num)
SquareRoot = num ^ (1/2)
End Function
Altough this function is very simple, that is it only calculates the square root of the number passed to it as its argument. But it is a good starting point for us to understand functions especially the important concept on how functions return the value that pass to it.
Here, a formula is calculated (number to the power of ½) and then the result is assigned to the variable SquareRoot. To tell the function what value to return, you assign that value to the name of the function. We use SquareRoot as the function name as well.
a) Calling the function from a Sub procedure
Looks how the function is call from another procedure by entering the following simple procedure in the same VBA module that contains the SquareRoot function:
Sub GetSub()
Ans = SquareRoot(16)
MsgBox Ans
End Sub
When this Sub is run, Excel shows a message box that contains the value of the Ans variable, which is 4.
What happened is this,
* We pass an argument of 16 to the SquareRoot function to be calculated
* Here the function returns a value (4) and assigned to the Ans variable.
* After that, the MsgBox function displays the Ans variable as 4
You can also simplify the procedure GetSub by omitting the Ans variable code
Sub GetSub()
MsgBox SquareRoot(16)
End Sub
b) Calling a function from a worksheet formula
Its very simple to call this VBA Function procedure from a worksheet formula.
Goto a worksheet in the same workbook that holds the SquareRoot function. Then enter the following formula into any cell:
=SquareRoot(25)
You will get 5 in the cell i.e. the square root of 25
you can also use a cell reference as the argument for the SquareRoot function. For example, if cell C1 contains a value, you can enter
=SquareRoot(C1). In this case, the function returns the number obtained by calculating the square root of the value in C1.
This function can be used as many times as you want in the worksheet. When you select the Insert-Insert Function menu from Excel and choose the User Defined category, your custom functions also appear in the Insert Function dialog box together with Excel’s built-in functions. As shown in Figure 5-7, the Insert Function dialog box lists your very own function. Bravo!
Fig 5.6 - Click for larger image7) Summary
In this Part, you learn some of the tools required to help you build a strong programming foundation specifically, event, sub, and function procedures. You learned how to use and build these procedures while considering the procedure’s scope, available parameters, and return values (function procedures).
In this Part, you learn some of the tools required to help you build a strong programming foundation specifically, event, sub, and function procedures. You learned how to use and build these procedures while considering the procedure’s scope, available parameters, and return values (function procedures).
No comments:
Post a Comment