Home » Excel-VBA-Tutorial » VBA-Functions-And-Subroutines. Excel VBA Tutorial Part 4 - VBA Function & Sub Procedures. Find the Excel help that you need in this large collection of Excel tips, clear step by step instructions, free workbooks and easy to follow videos. Hello Friends, This is a small VBA code to help you in dealing with Protecting and UnProtecting the WorkSheet using Excel Macro. Assuming the fact here that you know. AFAIK you cannot use VBA Excel to create custom tab in the Excel ribbon. You can however hide/make visible a ribbon component using VBA. Additionally, the link that.

VBA Function and Sub Procedures. Ubuntu Adobe Flash Install Terminal Service. Home » Excel- VBA- Tutorial » VBA- Functions- And- Subroutines. User- Defined VBA Function & Sub Procedures.

In Excel Visual Basic, a set of commands to perform a specific task is placed into a procedure, which can be a Function procedure or a Sub procedure (also known as functions and subroutines). The main difference between a VBA Function procedure and a Sub procedure is that a Function procedure returns a result, whereas a Sub procedure does not. Therefore, if you wish to perform a task that returns a result (e. For example, you could have a VBA Sub procedure that adds an Integer to every cell in the current selected range on a worksheet. You could supply the value of the integer to the Sub via an argument, as follows: Sub Add.

To. Cells(i As Integer).. End Sub. Note that it is not essential for VBA Functions or Subs to have arguments. Some procedures may not require them. Optional Arguments. You can also define VBA procedures to have Optional arguments. These are arguments that the user can supply if they want, but if they are omitted, the procedure will assign a default value to them.

To return to the example above, if we wanted to make the supplied integer argument optional, this would be declared as follows: Sub Add. To. Cells(Optional i As Integer = 0)In this case, the supplied integer, i, has a default value of 0.

You can use multiple Optional arguments in a VBA procedure, as long the Optional arguments are all positioned at the end of the argument list. Passing Arguments By Value and By Reference. When arguments are passed to VBA procedures, they can be passed in two ways: By. Val - The argument is passed by Value. This means that just the value (i. This means that the address of the argument is passed to the procedure. Any changes that are made to the argument inside the procedure will be remembered when the procedure is exited.

You can specify whether an argument is passed to a VBA procedure by value or by reference by using the By. Val or the By. Ref keyword when defining the procedure. This is shown below: Sub Add. To. Cells(By. Val i As Integer).. End Sub. In this case, the integer i is passed by Value. Any changes that are made to i will be lost when you exit the Sub procedure. Sub Add. To. Cells(By.

Ref i As Integer).. End Sub. In this case, the integer i is passed by Reference. Download Game Symbian S60v3 3D Printer here. When you exit the Sub, any changes that have been made to i will be remembered by the variable that was passed into the Sub procedure.

It should be noted that, by default, VBA arguments are passed by Reference. Therefore, if you do not use the By.

Val or the By. Ref keyword, the arguments will be passed by Reference. Before discussing further properties of VBA Function and Sub procedures, it is useful to look at the two types of procedure individually. The following two sections provide a brief discussion of VBA Function and VBA Sub procedures, along with simple examples. VBA Function Procedures. The VBA editor recognises a Function procedure, because the commands are positioned between the following start and end commands: As previously mentioned, VBA function procedures (unlike sub procedures) return a value.

The return values have the following rules: The data type of the returned value must be declared in the Function header. The value to be returned must be assigned to a variable having the same name as the Function. This variable does not need to be declared, as it already exists as a part of the function. This is illustrated in the following example.

VBA Function Procedure Example: Perform a Mathematical Operation on 3 Numbers. The following code shows an example of a simple VBA Function procedure that receives three arguments, each of which are 'Doubles' (double precision floating point numbers). The Function returns a further 'Double', which is the sum of the first two arguments, minus the third argument: Function Sum. Minus(d. Num. 1 As Double, d. Num. 2 As Double, d. Num. 3 As Double) As Double. Sum. Minus = d. Num.

Num. 2 - d. Num. 3End Function. The above very simple VBA Function procedure illustrates the way in which data arguments are supplied to a procedure. It is also seen that the Function procedure return type is defined as being a 'Double' (i. The following example shows a call to the simple Sum. Minus function that was defined above: Sub main()Dim total as Doubletotal = Sum.

Minus(5, 4, 3)End Sub. Calling a VBA Function Procedure From A Worksheet. You can call VBA Function procedures from an Excel Worksheet, in the same way as you can call any of the built- in Excel functions. Therefore, you could call the Sum.

Minus Function procedure by typing the following into any cell of your worksheet: =Sum. Minus(1. 0, 5, 2)VBA Sub Procedures. The VBA editor recognises a Sub procedure, because the commands are positioned between the following start and end commands: VBA Sub Procedure Example 1: Center and Apply Font Size to a Selected Range of Cells. The following code shows an example of a simple VBA Sub procedure that applies formatting to the current selected cell range. The cells are formatted to be aligned centrally (both horizontally and vertically) and to have a user- supplied font size: Sub Format. If i. Font. Size is not supplied to the Sub, then the default font size of 1.

However, if i. Font. Size is supplied to the Sub, then the current range is set to have the user- supplied font size.

VBA Sub Procedure Example 2: Center and Apply Bold Font to a Selected Range of Cells. The following code is similar to example 1, but instead of supplying a font size to the selected range, the cells are set to have a bold font. This example has been included to show a Sub procedure that does not receive any arguments: Sub Format. This is shown in the example below: Sub main()Call Format. E. g. Sub main()Call Format.

However, provided they have no arguments (and are Public - see below), Excel VBA Sub procedures are still available to the user of a spreadsheet. Therefore, of the above simple Sub procedures, the Format. To do this: Open up the 'Macro' dialog box by either: Clicking the Macros option on the 'Developer' tab of the Excel ribbonor. Using the keyboard shortcut  Alt + F8    (i.

If you do select an existing Excel key combination, this will be overwritten by your macro, and you, or other users, may end up accidentally executing your macro code. Scope of VBA Procedures. In Part 2 of this tutorial, we discussed the scope of variables and constants and the role of the Public and Private keywords. These keywords have the same meaning when applied to VBA procedures: Public Sub Add. To. Cells(i As Integer).. End Sub. If a procedure declaration is preceded by the keyword Public, this makes the procedure accessible to all other modules in the VBA Project.

Private Sub Add. To. Cells(i As Integer).. End Sub. If a procedure declaration is preceded by the keyword Private, this makes the procedure only available to the current module. It cannot be accessed from any other modules, or from the Excel workbook. If no keyword is inserted at the start of a VBA Function or Sub declaration, then the default setting is for the procedure to be Public (i.

This differs from variable declarations, which are Private by default. Early Exit From VBA Function & Sub Procedures. If you want to exit a VBA Function or Sub procedure before it has run to the end, you can do this using the Exit Function or the Exit Sub command. This is illustrated below, in a simple Function procedure that expects to receive a positive value to work with. If the value received is not positive, the function cannot continue, so it highlights the error to the user and exits the procedure immediately: Function VAT.