REAL-TIME

VBA Projects

Full Access with Source Code
  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

Effortlessly
Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Share Post

We write the procedures to perform certain tasks and some times we may required to write another procedure with small variations. In this situation we can take advantage of the Passing Arguments.

In this Section:

What are By Value (ByVal) and By Reference (ByRef) Passing Arguments?

We can pass the arguments in two different ways:
1. By Value (ByVal): We will pass the actual value to the arguments
2. By Reference (ByRef): We will pass the reference (address, pointers in other language) to the arguments

ByRef is default passing argument type in VBA. This means, if you are not specifying any type of the argument it will consider it as ByRef type. However, it is always a good practice to specify the ByRef even if it is not mandatory.

Writing a procedure or a function with Arguments:

Syntax for Passing Arguments:
'Procedure:
Sub ProcedureName(Arguments)
   'Statements…
End Sub

'Function:
Function FunctionName(Arguments) As DataType
   'Statements…
Function Sub

Following example shows how to write a Function to add two integers:
'Function to add two integers
Function fnSum(ByVal intVal1 As Integer, ByVal intVal2 As Integer) As Long
    fnSum = intVal1 + intVal2
End Function

Following example shows how to write a Procedure to multiply two integers:
'Procedure to multiply two numbers
Sub sbMultiplyValues(ByVal intVal1 As Integer, ByVal intVal2 As Integer)
    MsgBox intVal1 * intVal2
End Sub

How to call a function or a procedure?

  • We can call a function from either a procedure or function or Worksheet.
  • And we can call a procedure from either a procedure or function.
Calling a Function from a Procedure:
'Procedure to call function to add two values
Sub sbAddValues()
    MsgBox fnSum(200, 300) ‘Here 200 and 300 are the parameters passing to the function (fnSum)
End Sub

Calling a Procedure from another Procedure:
'Procedure to call procedure to multiply two values
Sub sbCallMultiplyValues()
   Call sbMultiplyValues(200, 300)
End Sub

Calling a Function from another Function:
'Function to add three integers
Function fnSumA(ByVal intVal1 As Integer, ByVal intVal2 As Integer, ByVal intVal3 As Integer) As Long
    fnSumA = fnSum(200, 300) + intVal3
End Function

Calling a Function from Worksheet:

You call the user defined functions as similar to the built-in excel function. The following picture shows how to call a user defined function to add to add two integers:
Passing Arguments in Excel VBA

Passing Arguments in Excel VBA – Example Macro File to Download

You can download the example file

[ANALYSIS TABS – Passing Arguments.xlsm] and have a look into this.

Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Save Up to 85% LIMITED TIME OFFER
Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: Excel VBATags: Last Updated: June 17, 2022

2 Comments

  1. Sub December 29, 2016 at 4:37 PM

    I am trying to call the below Sub but getting a syntax error.
    Sub AddNewWorkbook1(ByVal nPas As String)

    ‘Adding New Workbook
    Workbooks.Add
    nPassword.Copy
    Range(a1).Select
    ActiveSheet.Paste
    ‘Saving the Workbook
    ActiveWorkbook.SaveAs “C:WorkbookName.xls”

    End Sub

    ‘calling sub
    AddNewWorkbook1 (f)

  2. Shane July 3, 2017 at 3:55 PM

    “A1”

Leave A Comment