VBA - Sub with parameters


Board Regular
Jan 14, 2004
could you tell what is the difference in calling sub with parameters?
Sub ABC(Par1,...)
So what is the difference between:
1) Call ABC (X, ...)
2) Call ABC X
3) Call ABC Par1:=X
Thank you guys

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.


Board Regular
Nov 18, 2003
Hey bruno.trudo

This information is taken straight from the Visual Basic Help:

A Sub procedure differs from a Function procedure in that a Sub procedure cannot be called by using its name within an expression. A call to a Sub is a stand-alone statement. Also, a Sub does not return a value in its name as does a function. However, like a Function, a Sub can modify the values of any variables passed to it.

There are two ways to call a Sub procedure:

' Both of these statements call a Sub named MyProc.
Call MyProc (FirstArgument, SecondArgument)
MyProc FirstArgument, SecondArgument
Note that when you use the Call syntax, arguments must be enclosed in parentheses. If you omit the Call keyword, you must also omit the parentheses around the arguments

Hope that helps



Board Regular
Nov 18, 2003
Hi again

I found this in the VB help that could be of use

Creating Simpler Statements with Named Arguments

For many built-in functions, statements, and methods, Visual Basic provides the option of using named arguments as a shortcut for typing argument values. With named arguments, you can provide any or all of the arguments, in any order, by assigning a value to the named argument. You do this by typing the argument name plus a colon followed by an equal sign and the value ( MyArgument:= "SomeValue") and placing that assignment in any sequence delimited by commas. Notice that the arguments in the following example are in the reverse order of the expected arguments:

Function ListText(strName As String, Optional strAddress As String)
List1.AddItem strName
List2.AddItem strAddress
End Sub

Private Sub Command1_Click()
ListText strAddress:=”12345”, strName:="Your Name"
End Sub

This is especially useful if your procedures have several optional arguments that you do not always need to specify.

Determining Support for Named Arguments
To determine which functions, statements, and methods support named arguments, use the AutoQuickInfo feature in the Code window, check the Object Browser, or see the Language Reference. Consider the following when working with named arguments:

Named arguments are not supported by methods on objects in the Visual Basic (VB) object library. They are supported by all language keywords in the Visual Basic for applications (VBA) object library.

In syntax, named arguments are shown as bold and italic. All other arguments are shown in italic only.
Important You cannot use named arguments to avoid entering required arguments. You can omit only the optional arguments. For Visual Basic (VB) and Visual Basic for applications (VBA) object libraries, the Object Browser encloses optional arguments with square brackets [ ].


Watch MrExcel Video

Forum statistics

Latest member