Subroutine in VBA

Calling a subroutine


Sub HelloWorld()
    MsgBox "Hello World"
End Sub

Sub CallRoutine()
    Call HelloWorld
End Sub
                                                           




Subroutine Parameters


Sub HelloWorld(name As String)
    MsgBox "Hello " & name
End Sub

Sub CallRoutine()
    Call HelloWorld("John")
End Sub
                                                           


ByRef Subroutine Parameters

You can pass parameters to a subroutine by reference or by value. By default, Excel VBA passes parameters by reference. You can also specify this in the subroutine as shown below.


Sub HelloWorld(ByRef row As Range)
    MsgBox "Hello " & row
End Sub

Sub CallRoutine()
    Call HelloWorld(Range("A1"))
End Sub
                                                           


ByVal Subroutine Parameters


Sub HelloWorld(ByVal row As Range)
    MsgBox "Hello " & row
End Sub

Sub CallRoutine()
    Call HelloWorld(Range("A1"))
End Sub