Passing Arguments in Excel VBA

Home/Excel VBA/Passing Arguments in Excel VBA

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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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 – Example Macro File to Download

You can download the example file

[mongopono.ru – Passing Arguments.xlsm] and have a look into this.

LIMITED TIME OFFER
By |May 12th, 2013|Excel VBA|2 Comments

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

2 Comments

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

    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)

    • Shane July 3, 2017 at 3:55 PM - Reply

      “A1”

Leave A Comment


Related pages


sas di tutorialconvert excel csvexcel templates with macrosexcel application statusbarvisual basic macro excelexcel vba continue forvba range selectunprotect workbook excel 2007msgbox buttonshow to unlock excel workbookdatabase dmlleft excel vbaconcatenate excel worksheetsgantt chart weekly templatedeveloper menu in excelworkbook.worksheetsexcell find duplicatesvisual basic macro excelautofit excel columnsdata analyst interview questions for freshersautofilter excel 2010how to learn excel macro programmingaccess vba do while looppivot a table in excelxl to xml converterselecting a sheet in vbavba chart typesvba timevaluetranspose excel vbaapplication.getsaveasfilename vbaadd drop down in excel 2007sumif on excelapplication.displayalertsexcel shortcut insertprotecting excel workbookvba send email to multiple recipientsmacro in excel tutorialvb script interview questionssql mcq questions with answers pdfexcel hyperlink to foldervlookup stepsvba code for checkboxms excel unhide rowsremove excel workbook passwordproject plan template in excelfile handling in vb6subtract two dates excelhow to remove a hyperlink in excelpaste special formulaexcel macro hide rowunlock excel workbook forgot passwordbubble chart in excelhow to use pivot table in excel 2003how to make pivot tables in excel 2007excel loop formulasql faqs with answersvba eventsvba copy to new workbookexcel vba xmlisna vlookup excel 2010excel macro concatenate stringvba range autofilterhide worksheet in excelhow does vlookup work in excel 20133 axis chart excel 2010how to run sql in excelexcel formulas for analystsshow developer tab in excel 2007vba debugmatch formula in excelexcel calculate weeks between two dateshow to delete multiple entries in excelexcel formula sumifsexcel copy row to another sheetvba excel sheetssql ddl dml dcl tcl commands with exampleswhat is vba programmingvba datevalue