Create A Function

You can create functions to return a value to the procedure that calls them. Unlike subroutines, you cannot call functions directly from the Macro dialog box. When working with macros, only a subroutine can call a function. Like subroutines, functions consist of blocks of VBA code grouped together to perform a common task or series of tasks.

At first glance the value of a function may appear somewhat limiting. But unlike a subroutine, which does not return a value, a function always returns a value making them ideal for performing calculations. For example, you can create a function that always calculates the sales tax for an item and returns that amount.

There are essentially two different types of functions: private and public. All modules within the workbook can access a public function. However, only other procedures within the same module can access a private function. A

function is marked as private by placing Private before the Function statement as in the example: Private Function SampleFunc(Param) As Integer. Excel considers any functions that do not have the Private keyword to be public. That being said, using the Public keyword is redundant because a function with no keyword is the same as one with the Public keyword.

Other functions and subroutines within the same module typically call private functions. Because functions return a value, they are typically called as part of an expression. For example, you can assign the value returned by a function to a variable: FunctionValue = SampleFunc(Param). This line of code exists in a subroutine that calls the function. When Excel encounters this code, the function executes using the value of the Param parameter and the result of the function is placed in the FunctionValue variable.

CREATE A FUNCTION

CREATE A FUNCTION

3 VBAProject (PERSONA—

Modul im Module Alphabetic | categorized |

Act iveCe11. Fo rroulal Range("B3:H14">.Se Select ion. NuroberFo; Selection. Font. Boli End Sub

.ICI = ,r=SUH(RC[-6] :RC [-1] mat = "#,##0.00"

3 VBAProject (PERSONA—

Modul im Module Alphabetic | categorized |

Act iveCe11. Fo rroulal Range("B3:H14">.Se Select ion. NuroberFo; Selection. Font. Boli End Sub

.ICI = ,r=SUH(RC[-6] :RC [-1] mat = "#,##0.00"

_D In the Project window, click to highlight the project where you want to add a new module.

Click Insert O Module.

HI El

B-4Î VBAProject (OffieeExf

É-ËD Microsoft Excel Obj. B-€S Modules -A- Möddel " ModuleZ Module3 Module4 Modules -: VBAProject (PERSONA

Modules Module

Alphabetic | categorized \

-TI |caIculate_EKpenses

-TI |caIculate_EKpenses

B-4Î VBAProject (OffieeExf

É-ËD Microsoft Excel Obj. B-€S Modules -A- Möddel " ModuleZ Module3 Module4 Modules -: VBAProject (PERSONA

BjChapO I 1

■ The Visual Basic Editor creates a blank Code window.

BjChapO I 1

Type Function.

Type the name of your function.

0 0

Post a comment