VBA – 5. SubProcedures and Functions

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

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

Passing Arguments By Value and By Reference

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
MsgBox Triple(x)
MsgBox x
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
End Function
output: 30

function Triple(ByVal x As Integer) As Integer
x = x * 3
Triple = x
End Function
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.




Leave a Reply