VBA - Entering Arguments

You use Objects when you are working with codes. Objects have Methods which you can use to perform some tasks for you in Excel. These methods require you to pass arguments to them to work with. We have been seeing many methods in our previous posts like Application.InputBox. Many built in VBA functions like MsgBox & InputBox also uses arguments to do some task.

In this post, let’s see few details about arguments and how to pass them to Methods or Functions

What is an Argument?

No, argument here is not used in the sense of quarrel. Arguments are values that VBA need to perform certain task for you. For example: if you want to show MsgBox, you need to supply a prompt that should be displayed to user, may be a title and what are the buttons you want the MsgBox contain. Here, the prompt, the title and the buttons will be arguments for MsgBox function.

When you enter a function or method that requires arguments and press space, tab or open a parenthesis, VBA will show you list of arguments required for that function / method as a tool tip. See the picture below.

Notes:

• Optional arguments will have square brackets surrounds them. You can ignore them if you don’t want.
• Mandatory arguments will not have any brackets, you must provide these in order to work with the function.
• Argument you are entering currently will be bolded in the tip.
• Optional arguments have default settings which will be used by VBA if you do not enter values for those arguments.

You can type argument values (by placing them in quotes if it is text) or pass on a variable as its value. For example: both the codes below show a message box with a message Hello World. Second code snippet uses a variable to show the message.

Sub Test()
MsgBox (“Hello World”)
End Sub

Sub Test()
MyMsg = “Hello World”
MsgBox (MyMsg)
End Sub

Skip an Argument

Method 1 – Using Comma

When there are multiple arguments to enter like in above picture for MsgBox, when you are done entering the first argument, you can move to the next argument by placing a comma. See the below picture, I entered first argument “Hello” and typed a comma, VBA moved the argument focus to second argument by bolding it.

You may skip any optional arguments if you wish to use VBA default settings for them. For example: for MsgBox, if you skip 2^nd argument, VBA displays OK button only. If you want to skip an argument, you can place a comma to represent the end of the argument without actually entering anything in it as shown below.

As soon as I entered a comma, VBA focus moved to next argument. Note that you should only skip optional arguments. VBA will complain if you try to omit mandatory arguments.

Entering commas are mandatory only if you want to skip one or more arguments in between and enter a later argument. In the above example: you need to enter a comma to skip Buttons argument only if I have to enter Title or HelpFile or Context. If you have no other arguments to provide after entering mandatory arguments, you can simply close the parenthesis.

For example: if you only have a prompt to display to user with OK button and have no other criteria, code as in below is sufficient.

Sub Test()
MsgBox ("Hello")
End Sub

If you have a prompt and a title to be displayed and nothing else, use below code.

Sub Test()
MyMsg = MsgBox("Hello", , "Welcome")
End Sub

Notice that I am assigning the MsgBox to a variable (because I am using more than one argument) and skipping second argument with a comma; third argument is provided and function is closed with a parenthesis. Though there are 2 more arguments for MsgBox, I haven’t skipped them with commas.

This commas method is basic and good enough to work with code. But if you want to keep your code clean without much of those skipping commas and to look clean, there is another method you can use to skip arguments.

Named Arguments

Methods / Functions support what is called as a named argument. Notice the MsgBox arguments below. First argument is shown as Prompt, next is Buttons, third is Title and so on.

You can use these names while you pass values to them. Named arguments can be called by using the name of that argument followed by a colon followed by an equal sign (:=). An example code using named arguments is given below.

Sub Test()
MyMsg = MsgBox(prompt:="Hello", Title:="Welcome")
End Sub

Note that there is no comma in-between to skip arguments. Above code works just like what we saw earlier using commas to skip an argument.

Sub Test()
MyMsg = MsgBox("Hello", , "Welcome")
End Sub

Though both ways of entering arguments work in similar fashion, using named arguments make your code more understandable to users.  Take your pick on which method you want to follow. Happy Learning!!