Tag Archives: function

vba end

VBA End – Exit or Reset VBA macro

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 4.00 out of 5)

Often we think about beginnings not endings. It is only when we get somewhere it is when we realize we hadn’t thought through our destination. Same goes for writing VBA macros. Fortunately the VBA End statement is here to save the day and help us put that full-stop in the right place. Today I want to share a cool VBA tip for properly ending your VBA macros.

VBA End vs VBA Exit

The VBA End statement ends the execution of a certain VBA scope.

VBA End statement

The VBA End Statement is used to mark the end of a specific VBA scope:

You can End a Function, a conditional If statement, mark the End of an VBA Enum or VBA Type.

VBA Exit statement

The VBA Exit Statement is used to exit a particular scope earlier than defined by the VBA End Statement.

See example below to understand the difference between VBA End and VBA Exit:

Exiting VBA Functions or Procedures

Sometimes however we want to end the execution of a function or procedure early, simply by leaving the current execution scope.

Exit Sub

Ending Sub procedures is very useful. Instead of using the End statement we need to use the VBA Exit statement.

Exit Function

Similarly for Functions we need to use the Exit statement instead of End.

The result of the VBA Function above is 1 not 2.

Exit VBA Loop

You can also use Exit to exit a VBA Loop statement:

The result of the VBA Function above is 1 not 2.

End VBA macro / Reset VBA macro

Now here is the trick I want to sell you today. The Exit statement above will allow you to leave the scope of your current VBA Function or VBA Sub. It will not however end the execution of your entire macro. A simple example below:

The result:

The result of running Exit Sub
The result of running End Sub

So you see Exit Sub exiting only the the current scope of the running VBA Sub procedure and not the entire macro!

End – as simple as that

How to exit the execution of the entire macro (the Main Sub)? A simple End will do…

The result:

Using the VBA End statement to exit macro execution
Using the VBA End statement to exit macro execution

The End statement ends the execution of the ENTIRE MACRO.

Use End carefully!

As Andy Pope has rightfully corrected me, the End statement needs to be used with care. As there are consequences…

The VBA Reset button
The VBA Reset button
The VBA End statement works a lot like the VBA Reset button. This is what happens:

  • Object events will not be invoked e.g. the Unload, QueryUnload, or Terminate events
  • Any running Visual Basic code will be stopped – that means Timers and other running code
  • Object references held by other programs are invalidated – if any application is holding a reference to a VBA object it will be invalidated and inaccessible
  • Closes any open dialogs / forms (e.g. UserForm or MsgBox)

Using End is a lot like pulling a hand brake in a car. It stops the car, but often so abruptly it might cause it to crash.

VBA Function vs VBA Sub

Excel VBA Function vs VBA Sub – Excel Functions and Procedures

1 Star2 Stars3 Stars4 Stars5 Stars (8 votes, average: 3.00 out of 5)

A VBA Function can accept parameters and return results. Functions, however, can’t be executed directly. On the other hand a VBA Sub procedure can be executed directly and can also accept parameters. Procedures, however, do not return values.

We often use Subs and Functions often not thinking about their true potential and how much we can squeeze out of a regular VBA Function. Let’s start with a reminder how both differ from each other and then dive into the details.

VBA Sub, VBA Function Syntax

Below structure resembles a VBA Sub and VBA Function:

Below are simple examples of Subs and Functions:

VBA Sub procedure syntax

The code block of a VBA Sub procedure is marked by the Sub and End Sub statements.
VBA Sub procedure

VBA Function procedure syntax

The code block of a VBA Function is marked by the Function and End Function statements.
VBA Function

VBA Function vs VBA Sub

We often tend to mix up procedures, Subs and Functions in VBA. So let’s get it right this time. There are 2 main differences between VBA Procedures (Subs) and VBA Functions:

  • VBA Functions return values, VBA Subs don’t
  • You can execute a VBA Sub, you can’t execute VBA Functions – they can only be executed by VBA Subs

Executing Functions and Subs

Although I provided examples above there are multiple ways to execute a VBA Function and a VBA Sub:

Passing arguments ByVal and ByRef

The common knowledge is that only VBA Functions can return values. That indeed is mostly true. However, instead of a value (the result of the Function), we can assign values to variables that can be passed to the procedure by reference. What does that mean?

Variables can either by passed to a procedure (Function, Sub etc.) by their value or their reference.
Passing by value translates to making a copy of the variable. Thus any changes to the copy will not be reflected in the original variable.
Passing by reference is passing the address of the variable to the procedure. This means that any changes to a argument will be reflected in the original variable.

ByVal vs ByRef
Passing a variable by its Value or its Reference

By default all arguments are passed to procedures ByVal, so that no changes can be made to the original variables

ByVal and ByRef examples

Let’s look at some examples now:

When passing by value the variable someLong is not modified. However, when we pass it by reference its value is changed within the Sub procedure.

Objects and arrays are always passed by reference, because objects are in fact references (e.g. a Collection). Beware in such cases not to modify referenced objects by mistake!

Passing arrays to Subs and Functions

You can also easily pass arrays to Subs or Functions, even redefining their length. See example below:

Optional parameters

VBA Functions and Subs permit optional parameters, ones that need not be provided when executing the Function or Sub. It is a good practice to specify the default value for such parameters. See example below:

You can verify if a parameter has not been passed to an Sub or Function by using the IsMissing function. The IsMissing function works however only for Variant type parameters. See the same SayHi procedure as above, this time with the IsMissing function.

Dynamic parameter list

Say you want to create a VBA function like the Excel SUM or AVERAGE formulas – that can be provided with a dynamic list of parameters. VBA extends a neat solution for such a scenario called the ParamArray.

The ParamArray transforms a list of Variant variables passed as parameters into a neat Variant array.

VBA ParamArray

The VBA ParamArray for a dynamic list of VBA arguments

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 4.50 out of 5)

Today’s VBA Tip of the Day concerns dynamic parameter declaration via the VBA ParamArray. When declaring Functions/Procedures in some cases you may need to specify Optional arguments. Let’s say we want to write a procedure that works similarly as other procedures you know natively from Excel like SUM:

Excel SUM
Excel SUM takes any number of arguments separated by the locale array delimiter (; or ,)

Now how would you create such a Function in VBA? Well certainly NOT like THIS!!!:
SUM Excel example
Optional arguments are nice to use, only when you know there is going to be only a few. But in the above example we may as well get 20 different Excel Ranges!

Interestingly, this totally non-sense approach is used by Microsoft Excel WorksheetFunctions. See for yourself:
WorksheetFunctions.Sum declaration
WorksheetFunctions.Sum declaration
If only the guy writing this knew about ParamArrays…

Use the VBA ParamArray

In the above example, when dealing with a large amount of optional parameters use the VBA ParamArray statement in your Function/Procedure declaration:

Public Function MySUM(ParamArray args())
    For Each arg In args
        MySUM = MySUM + arg
    Next arg
End Function

Notice that I replaced Optional arg1, Optional arg2, Optional arg3, Optional arg4, Optional arg5 with ParamArray args(). Cool huh? Well that’s basically it! Nothing hard there!

Remember! The ParamArray statement works only with Variant variables. So don’t try the following

ParamArray args() as String

Also the ParamArray statement can’t be used with the following variable statements:

  • ByVal
  • ByRef
  • Optional

That’s it for today’s VBA Tip of the Day!

Next steps

Be sure to read my other posts on:
VBA Array Tutorial
VBA Collection Tutorial
VBA Dictionary and other data structures

distinct list

Dynamic Distinct Column in Excel using Array Formulas

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 4.00 out of 5)

I see that often many users have issues with managing lists of values and translating them to unique/distinct lists where values do not repeat themselves. There are many way to tackle this problem and you would be surprised that there even is a formula to handle this task. Let’s dive into this subject.

What we have (unsorted list) Vs. What we want (distinct list of sorted values)

Let’s assume a simple table of data. Where column A is full of repeating Names and column B is a list of corresponding values. We will be working on this data set through out this post. This data set is a typical example of the issue we often have with repeating rows of indistinct data. What we will want to do is to somehow summarize this table with a list of distinct Names and aggregated Values.

What we have

Let’s assume we have a simple table of Names and Values. What we will want to do is to somehow summarize this table with a list of distinct Names and aggregated Values.

Name (A column) Value (column B)
Tom 60
Matthew 98
James 19
John 16
Matthew 45
John 26
John 70
James 60

What we want

The result of this operation should look somewhat like this:

Name Value
James 79
John 112
Matthew 143
Tom 60

Now let’s familiarize with 2 approaches to this issue.

Method 1: Distinct column using a PivotTable

The most obvious solution is of course to create a PivotTable from this data set. All we need to do is add the Names as ROWS and Values as VALUES to the Pivot to get a simple summary with the exact data we need. See below:

Distinct list: Pivot Table to aggregate Values
Pivot Table to aggregate Values

Method 2: Distinct column using Array Formulas

Now putting aside the obvious, in some cases Pivots are not the answer. Especially if we don’t want to use VBA Macros and want to create a dynamic table which will simply update itself with the latest Values and Names.

Provide list of distinct Names

The first issue which we stumble upon is to somehow produce a list of distinct Names. At first this may seem impossible to be done by a Excel function. Fortunately again Array Formulas can come in handy in this task. This site features the elegant solution to this problem which I will try to explain in much detail.

To provide a list of distinct Nameswe must use the following formula:


This is the final outcome when we hit CTRL+SHIFT+ENTER to make the formula an Array Formula and drag it down:

Distinct list: Distinct column of Names
Distinct column of Names

Now let’s ponder for a second on how the formula works as it might not be so straightforward as it seems:

'Gets an item from A$1:A$9 with an index provided by the MATCH function
'Find value "0" in the column of value provided by the COUNTIF function
'Returns an array. Count items from array A$1:A$9 if they are provided
'on the E$1:E1 list
'Notice that this list has only 1 static item. The first item is the 
'anchor, however, the second item will move to include all rows above
'the current one

The exciting thing is how the MATCH function is used above. Usually the MATCH function is provided with a range of cells. This time, however, we are providing it with an array being the result of the COUNTIF function. What the MATCH function does is search for the first item resulting from the COUNTIF that has 0 counts i.e. it has not yet been provided in the list.

Now the formula will start producing #N/A errors when dragged beyond the number of distinct elements in the Names columns. You can correct this by wrapping it with a IFERROR function:


Sort the list of distinct Names

Now that we know how to provide a list of distinct names we still need to make sure that the list is sorted alphabetically else it will be provided in the same order as the items on the initial list. Here we need again to resort to Array Formulas to help us with this tasks.

Let’s start with explaining how to get the index of each name (concept from Chandoo.org):

Get index of elements in our unsorted distinct list
Get index of elements in our unsorted distinct list

Notice the code in column E:


What we are doing is counting, for each distinct name, the number of items alphabetically of lower lexicographical value than our the current Name (D2).

Let’s now consider an Array Formula based on this:


If we hit F9 we will see that an Array Formula would evaluate to:


This is the correct sequence of our list of distinct items.

If we combine this with an INDEX-MATCH combo we can iterate through this sequence:

Sorted list of distinct Names
Sorted list of distinct Names

'Gets an item from D$2:D$5 with an index provided by the MATCH function
'Find value of the current ROW-2 (this is simply to sequence through 
'the arrary) in the column of value provided by the COUNTIF function
'Evaluates to {3;2;0;1} - this is the correct sequence of our distinct
'array of Names


Now to summarize what we have.

  • First, we provided the distinct list of Names in column D
  • Secondly, we provided a separate column which sorts the distinct Names in column D
  • Lastly, if we add a simple SUMIF function as shown below we can sum all values for each distinct Name in the Names column
Final data table
Final data table

Next Steps

Check out other similar posts:
EXCEL: Dynamic row numbers
EXCEL: 10 Top Excel features
EXCEL: Split columns on any pattern