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?
- Writing a procedure or a function with Arguments
- How to call a function or a procedure?
- Example macro file to explain passing arguments
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
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)
“A1”