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.g. summing of a group of numbers), you will generally use a Function procedure, but if you just need a set of actions to be carried out (e.g. formatting a set of cells), you might choose to use a Sub procedure.
Example: ' a sub to add two numbers Sub s1() dim result as integer dim val1 as integer dim val2 as integer val1=4 val2=9 result= val1+val2 msgbox result End Sub we can also pass values to the sub itself by calling it from another sub sub s1() call addNumbers(8,3) ' call addNumber sub by passing two values 8 and 3 End Sub sub addNumbers(n1 as integer, n2 as integer) ' 8 and 3 are passed on to n1 and n2 variables dim r as integer r= n1+n2 msgbox r end sub Functions are similiar to sub procedures except that they return a value. It means the value of the result of the function can be passed back to the calling function or to any variable. Always create function in standard modules.
sub s1() dim result as integer 'we can call a function like we call a sub normaly call addNumbers(8,3) ' call addNumber sub by passing two values 8 and 3 'we can also pass the (result of) function to a variable which is not possible using sub result= addNumbers(8,3) so result varibale contains 11 value now. End Sub public function addNumbers(n1 as integer, n2 as integer) as integer 'as integer keyword above means that this function will return integer type value dim r as integer r= n1+n2 addNumbers=r 'here we pare passing the result back to function end sub
When arguments are passed to VBA procedures, they can be passed in two ways:
By default, values are passed by reference to procedures in vba
Dim x As Integer
x = 10
In by ref, the address of calling variable is received, so any changes u make is with respect to the address of calling variable. X has now address of calling variable ie x=10. So x * 3 implies multiply what x stores in memory to 3
function Triple(ByRef x As Integer) As Integer
x = x * 3c
Triple = x
function Triple(ByVal x As Integer) As Integer
x = x * 3
Triple = x
output : 10
In the below example, we need to highlight a row with red color if there is any 0 in row and highlight column with green color is there is any 0 in column.
Private Sub CommandButton1_Click() Dim i As Integer Dim j As Integer Dim LR As Integer Dim LC As Integer LR = Cells(Rows.Count, "A").End(xlUp).Row LC = Cells(1, Columns.Count).End(xlToLeft).Column For i = 1 To 5 For j = 1 To 10 If Cells(i, j).Value = 0 Then 'Call color_row(i, j, LR, LC) Call color_RowCol(i, j, LR, LC) End If Next j Next i End Sub Sub color_RowCol(r As Integer, c As Integer, LastR As Integer, LastC As Integer) ' this is same as Sub color_RowCol(byrefr As Integer, byrefc As Integer, byrefLastR As Integer, byrefLastC As Integer) 'To color Row ' Range("A" & r).Resize(, LastC).Interior.Color = vbRed 'To color Column Cells(1, c).Resize(LastR).Interior.Color = vbGreen End Sub In the example, LastR and LastC variables can access LR and LC (last Row and last column in the calling sub (command1_click1 sub), because they are passed by reference, if we use byVal instead of byRef, LastR and LastC variables can not access the LR and LC variables.