get 10% Discount on personalized products

show coupan code

VBA Error Types

There are three types of VBA error that you may encounter when executing an Excel macro. These are:

·         Compile Errors;

·         Runtime Errors;

·         Logical Errors ('bugs').

These three VBA error types are discussed in turn, below.

Compile Errors

Auto Syntax Check Option

The Auto Syntax Check option causes a message box to pop up whenever a syntax error is typed into the visual basic editor.

If this option is switched off, the visual basic editor still highlights syntax errors in red.

The Auto Syntax Check option is accessed by selecting Tools→Options from the menu at the top of the visual basic editor.

Compile Errors are recognized by the VBA compiler as being illegal and therefore, are highlighted as errors before your macro even starts to run.

If you type in a syntax error, the VBA editor will immediately highlight this, either by popping up a message box or by highlighting the error in red, depending on the setting of the Auto Syntax Check option (see right).

Alternatively, a compile error may be detected when the code is compiled, just before being executed.

A compile error is generally easy to fix, as the VBA compiler pops up a message box, which provides information on the nature of the error.

For example, if you get the message "Compile error: Variable not defined"when you attempt to run your code, this indicates that you are referencing a variable that has not been declared in the current scope. (This error is only generated when you are using Option Explicit).

Runtime Errors

Runtime errors occur during the execution of your code, and cause the code to stop running. This type of VBA error is also usually relatively easy to fix, as you will be given details of the nature of the error, and shown the location where the code has stopped running.

For example, if your code attempts to divide by zero, you will be presented with a message box, which states "Run-time error '11': Division by zero".

Depending on the structure of your VBA project, you may be given the option to debug the code, (see below). In this case, clicking on the Debug button on the debug message box, causes the line of code that generated the VBA error to be highlighted in your vba editor.

Due to the message box content and the highlighted line of code in the above example, it is very easy to spot the error in this code.

If your code is more complex, you can gain further information on the reason for the VBA error by looking at the values of the variables in use. This can be done in the VBA editor by simply hovering your mouse cursor over the variable name, or by opening the local variables window (by selecting View→Locals Window).

The different runtime error codes are explained on the Microsoft Support Website. However, some of the more common VBA error messages are shown in the table below:

5	-	Invalid procedure call
7	-	Out of memory
9	-	Subscript out of range (this error arises if you attempt to access elements of an array outside of the defined array size - e.g. if you define an array indexed from 1 to 10, then attempt to access entry no. 11)
11	-	Division by zero
13	-	Type mismatch (this error arises when you attempt to assign the wrong type of value to a variable - e.g. define i as an integer, then attempt to assign the string "text" to i)
53	-	File not found (occurs when attempting to open a file)

Trapping Runtime Errors

Some runtime errors may not be caused by faulty code. For example, if you need to open a file, that contains essential data for your macro, you can't avoid the generation of a VBA error, if the file does not exist. In these types of cases, it is far more professional to 'trap' the error, and write VBA code to handle it, so that your macro exits gracefully, rather than having your macro crashing.

In order to assist with runtime error trapping, VBA provides us with the On Error and the Resume statements. These statements capture a runtime error and divert the macro into a specified section of VBA code, where the error is handled. After the error handling code has run, the programmer can request that the VBA code resumes from the point of the error, or alternatively, the macro can be terminated cleanly. This is shown in the example code below.

' Sub procedure to set the supplied values, Val1 and Val2 to the values ' in cells A1 and B1 of the Workbook "Data.xls" in the C:\ directory Sub Set_Values(Val1 As Double, Val2 As Double) Dim DataWorkbook As Workbook On Error GoTo ErrorHandling ' Open the Data Workbook Set DataWorkbook = Workbooks.Open("C:\Documents and Settings\Data") ' Set the variables Val1 and Val2 from the data in DataWorkbook Val1 = Sheets("Sheet1").Cells(1, 1) Val2 = Sheets("Sheet1").Cells(1, 2) DataWorkbook.Close Exit Sub ErrorHandling: ' If the file is not found, ask the user to place it into ' the correct directory and then resume MsgBox "Data Workbook not found;" & _        "Please add the workbook to C:\Documents and Settings and click OK" Resume End Sub

In the above example, the code attempts to open the Excel File 'Data' and if it fails to find the specified file, prompts the user to place the data file into the correct folder. Once the user does this and clicks OK, the code is resumed and a further attempt is made to open the file. If desired, instead of re-trying the file, the Sub procedure could be terminated at this point, by using the Exit Sub command.

Logical Errors

Logical Errors, otherwise known as 'bugs', occur during the execution of the VBA code, and allow the code to continue to run to completion. However, the 'bug' may cause the macro to perform unexpected actions or return an incorrect result. These errors are the most difficult to detect and fix, as there is no way that the VBA compiler can identify and 'point to' the error, in the way that it does for compile and runtime errors.

For example, you may accidentally code your macro to add together the wrong variables in a procedure. The result would be incorrect, but the macro would (usually) continue to run to completion.

The Excel VBA editor provides a number of debugging tools to assist you in finding logical errors in your VBA code, so that they can be fixed. These tools are currently beyond the scope of this page. However, an overview of the Excel VBA debugging tools is provided on the Microsoft Help & Support Website.

Visual Basic Arrays

Excel Visual Basic arrays are structures which are used to store a set of related variables of the same type. Each of the entries in the array can be accessed by an index number.

For example, if you had 20 members of a team and you wanted to store all the names for use in your VBA code. You could declare 20 variables to hold the team member names, as follows:

Dim Team_Member1 As String Dim Team_Member2 As String . . . Dim Team_Member20 As String

Alternatively, you could use the simpler and more organised method of storing the Team members in an array of 20 String variables:

Dim Team_Members(1 To 20) As String

Once you have declared the array as above, each entry of the array can be populated as follows:

Team_Members(1) = "John Smith" . . .

A further advantage of storing data in an array, rather than in individual variables, arises if you want to perform the same action on every member of the list. If the team member names were stored in 20 individual variables, you would need 20 lines of code to carry out a specific action on each name. However, if you have stored the names in an array, you can use a simple loop to carry out the action for each entry in the array.

This is shown in the example code below, which prints out each name in the Team_Members array to Column A of the current Excel Worksheet:

For i = 1 To 20 Cells(i, 1).Value = Team_Members(i) Next i

It is clear from the above that handling an array of 20 names is much less cumbersome and more organised than handling 20 individual variables, but imagine if you had 1,000 names to store! And imagine you wanted to store Surnames separately from Forenames! It is clear that it would soon become almost impossible to handle this amount of data without the use of Arrays in your VBA code.

Multi-Dimensional Excel Visual Basic Arrays

The Visual Basic Arrays discussed above are one-dimensional, in that they refer to one list of Names. However, arrays can have multiple dimensions. An array having two dimensions acts as a grid of values.

For example, imagine that you want to store daily sales figures for the month of January, for 5 different teams. You would need a 2-dimensional array, consisting of 5 sets of figures over 31 days. You would then declare the array as follows:

Dim Jan_Sales_Figures(1 To 31, 1 To 5) As Currency

In order to access the entries in the array 'Jan_Sales_Figures', you need to use two indices, refering to the day of the month and the team number. For example, the sales figures for Team2 on January 15th would be referenced as:

Jan_Sales_Figures(15, 2)

You can declare arrays with 3 or more dimensions in the same way. I.e. by adding further dimensions into the declaration and using a further index to reference the array entries.

Declaring Excel Visual Basic Arrays

The above sections have already given some examples of Visual Basic Array declarations, but it is worth discussing this further. As seen above, a one-dimensional array can be declared as follows:

Dim Team_Members(1 To 20) As String

This declaration tells the VBA compiler that the array 'Team_Members' has 20 variables, which are referenced by indices 1 to 20. However, we could also decide to index the array variables from 0 to 19, in which case the declaration would be:

Dim Team_Members(0 To 19) As String

In fact, the default form of array indexing is to start at 0, so if you omit the start index from the declaration, and simply declare the array as:

Dim Team_Members(19) As String

Then the VBA compiler will understand this to be an array of 20 variables, which are indexed from 0 to 19.

The same rules are applied to declarations of multi-dimensional Visual Basic arrays. As shown in the previous example, a two-dimensional array is declared by separating the dimension indices by a comma:

Dim Jan_Sales_Figures(1 To 31, 1 To 5) As Currency

However, if we omit the start indices from both dimensions, as follows:

Dim Jan_Sales_Figures(31, 5) As Currency

this is understood to be a two-dimensional array in which the first dimension has 32 entries, indexed from 0 to 31 and the second dimension has 6 entries, indexed from 0 to 5.

Dynamic Arrays

In the above examples, the arrays all have fixed dimensions. However, in many cases, you may not know how big an array is going to be before run time. One way to solve this is to declare a huge array, in an attempt to cover the maximum possible size needed, but this would use up an unnecessarily large amount of memory and could slow down your program. A better option would be to use a Dynamic array, which is an array that can be sized and re-sized as many times as you like, during the execution of a macro.

A dynamic array is declared with empty parentheses, as follows:

Dim Team_Members() As String

It is then necessary to declare the dimension of the array during the execution of the code, using the ReDimstatement:

ReDim Team_Members(1 To 20)

If, during the execution of the code, you need to extend the size of an array, you can use ReDim again:

If Team_Size > 20 Then ReDim Team_Members(1 To Team_Size) End If

It should be noted that resizing a dynamic array in this way will result in the loss of all the values that had previously been stored in the array. If you want to avoid this loss, and keep the values that have previously been assigned to the array, you need to use the "Preserve" keyword, as shown below:

If Team_Size > 20 Then ReDim Preserve Team_Members(1 To Team_Size) End If

The disadvantage of using the "Preserve" keyword when resizing Visual Basic Arrays is that you can only change the upper bound of an array, not the lower bound. Also, if you have a multi-dimensional array, the use of the "Preserve" keyword limits you to changing only the last dimension of the array.

Click Here to  Main Menu 

VBA Variables & Constants

In VBA, as in any programming language Variables and Constants are names that represent values. As suggested by the names, the values of Variables can change, while Constants generally have fixed values.

For example, you might use the constant "Pi" to store the value 3.14159265... The value of "Pi" will not change throughout the course of your program, but it is useful to store this value in a Constant for ease of use.

Also, we might use a variable named "sVAT_Rate", to store the VAT Rate to be paid on purchased goods. The value of sVAT_Rate may vary, depending on the type of goods.

Data Types

All variables and constants have a data type. The following table shows the VBA data types, along with a description of each type and the range of possible values.

Data Type	Size in Memory	Description	Range of Values
Byte	1 byte	Represents an unsigned (non-negative) number - often used for binary data	0 to 255
Boolean	2 bytes	A simple True or False value	True or False
Integer	2 bytes	Integer (no decimals)	-32,768 to +32,767
Long	4 bytes	Long Integer (no decimals)	-2,147,483,648 to +2,147,483,647
Single	4 bytes	Single Precision Floating Point Number	-3.4e38 to +3.4e38
Double	8 bytes	Double Precision Floating Point Number	-1.8e308 to +1.8e308
Currency	8 bytes	A Floating Point Number with a fixed number of decimal places	-922,337,203,685,477.5808 to +922,337,203,685,477.5807
Date	8 bytes	Date & Time - The Date type is represented internally by a floating point number. The integer part of the number represents the date, and the decimal portion represents the time.	1st January 100 to 31st December 9999
Object	4 bytes	A reference to an object	Any Object Reference
String	varies	Holds a series of characters. The String type can be defined to have a fixed or a variable length, although it is most commonly defined to have a variable length	Fixed - Up to 65,500 characters Variable - Up to approx. 2 billion characters
Variant	varies	Can hold Dates, Floating Point Numbers or Strings of Characters, and should therefore be used when you are not sure what type of data to expect.	Number - same as Double Type String - same as String Type

From the above table, it is clear that you can save on memory by using specific data types (e.g. Integers rather than Longs, or Singles rather than Doubles). However, if you are planning to use the 'smaller' data types, you must be sure that your code will not encounter larger values than can be handled by the data type.

Declaring Variables & Constants

Before using a variable or constant, you can declare it. This is done by adding a simple line of code to your macro, as follows.

To declare a variable:

Dim Variable_Name As Data_Type

Note that in the above line of code, Variable_Name should be replaced by your actual variable name and Data_Typeshould be replaced by one of the above listed data types. For example:

Dim sVAT_Rate As Single
Dim i As Integer

Constants are declared in a similar way, except a constant should always be assigned a value when it is declared. Examples of the declaration of constants in VBA are:

Const iMaxCount = 5000
Const iMaxScore = 100

Reasons to Declare Variables

Excel does not force you to declare variables. By default, all variables in Excel will have the Variant type, and can be assigned a number or text.

Therefore, at any point during your program, you can use any variable name (even if it has not been declared), and Excel will assign the Variant type to it. However, it is not good programming practice not to declare variables for the following reasons:

1.       Memory & Calculation Speed: If you do not declare a variable to have a data type, it will, by default, have the Variant type. This takes up more memory than many of the other data types.

While a few extra bytes per variable might not seem to be a lot of memory, it is not uncommon for programmers to have thousands of variables in their programs (especially when you start to use arrays). Therefore, the additional memory used to store Variants instead of Integers or Singles, can add up significantly.

Variant data types also take more time to process than some of the other data types, so if you have thousands of unnecessary Variant data types, this can slow down your calculations.

2.       Prevention of 'Typo' Bugs: If you always declare your variables, then you can use the VBA Option Explicit (see below) to force you to declare variables.

This will prevent you from introducing bugs into your code by accidentally typing a variable name incorrectly. For example, you might be using a variable called "sVAT_Rate" and, when assigning a value to this variable, you might accidentally type "VATRate = 0.175". From this point onwards, you are expecting your variable "sVAT_Rate" to have the value 0.175, but of course it doesn't. However, if you had been using the VBA option Explicit to force you to declare all variables before using them, this error would be highlighted by the VBA compiler, as the variable "VATRate" would not have been declared.

3.       Highlighting Unexpected Data Values: If you declare a variable to have a specific data type, and you attempt to assign the wrong type of data to it, this will generate an error in your program which, if not handled within your code, can cause your programme to crash.

While this may initially seem to be a good reason not to declare variables, it is actually useful to know as soon as possible if your variable receives an unexpected data type. Otherwise, if the program continues to run, you could end up with incorrect or unexpected results at a later time, when it is likely to be much more difficult to detect the causes of the errors.

Also, it is possible that the macro may complete with incorrect results, and you may not notice the error at all - and continue to work with incorrect data!

It is therefore preferable to detect the unexpected data type at an early stage and add code to handle this appropriately.

Due to the reasons listed above, it is recommended that you always declare all variables when programming in VBA, and use the Option Explicit (see below) to ensure that all variables in the code are declared.

Option Explicit

The option 'Explicit' forces you to declare all variables that you use in your VBA code, by highlighting any undeclared variables as errors during compilation (before the code will run). To use this option, simply type the line

Option Explicit

at the very top of your VBA file.

If you want to always include the option Explicit at the top of every new VBA module that you open up, this can be done automatically via the 'Require Variable Declaration' option of your VBA editor.

To activate this:

·         In the Visual Basic Editor, select Tools→Options...;

·         Ensure the Editor tab is selected;

·         Check the box next to the option Require Variable Declaration and click OK.

Once the 'Require Variable Declaration' option is selected, the Option Explicit will automatically be inserted at the top of all new modules in the VBA Editor.

Scope of Variables and Constants

Each time you declare a variable or a constant, this only has a limited Scope (i.e. a limited part of the program over which the variable exists). This depends on the position of your declaration.

For example, imagine you are using the variable "sVAT_Rate" within the function, "Total_Cost". The following table discusses the scope of "sVAT_Rate" when it is declared in 2 different places within the module:

Option Explicit Dim sVAT_Rate As Single Function Total_Cost() As Double . . . End Function

If you declare "sVAT_Rate", at the top of your module file, then the scope of this variable is the whole of the module (i.e. "sVAT_Rate" will be recognised throughout all procedures within the module). Therefore, if you assign a value to "sVAT_Rate" in the Total_Cost function and then step into another function in the current module, the assigned value of "sVAT_Rate" will be remembered. However, if you step into a function that resides in a different module and attempt to use the variable "sVAT_Rate", the variable will not be recognised.

Option Explicit Function Total_Cost() As Double Dim sVAT_Rate As Single    .    .    . End Function

If you declare "sVAT_Rate", at the start of the Total_Cost function, the scope of this variable will be the whole of this function, (i.e. "sVAT_Rate" will be recognised throughout the Total_Cost function, but not outside of this function). Therefore, if you attempt to use "sVAT_Rate" in any other procedure, the VBA compiler will raise an error, as the variable has not been declared outside of the Total_Cost function (and the Option Explicit is in use).

In the above example, the module level variable has been declared using the 'Dim' keyword. However, it is possible that you may want to declare variables that can be shared with other modules. This can be specified by using the keyword Public in the declaration, instead of 'Dim'.

Note that, for a module-level variable, the 'Dim' keyword could also be replaced with the keyword Private to indicate that the scope of the variable is limited to the current module.

Constants can also use the 'Public' and 'Private' keywords, but in this case, the 'Public' or 'Private' keyword is used in addition to the 'Const' keyword (not instead of).

The following examples show the Public and Private keywords applied to variables and constants:

Option Explicit Public sVAT_Rate As Single Public Const iMax_Count = 5000 . . .	This example shows the 'Public' keyword used to declare the variable, "sVAT_Rate", and the constant, "iMax_Count". The scope of these two declarations is the whole of the current project. Therefore "sVAT_Rate" and "iMax_Count" can be accessed from any procedure in any module in the project.
Option Explicit Private sVAT_Rate As Single Private Const iMax_Count = 5000 . . .	This example shows the 'Private' keyword used to declare the variable, "sVAT_Rate", and the constant, "iMax_Count". The scope of these two declarations is the current module. Therefore "sVAT_Rate" and "iMax_Count" can be accessed from any procedure in the current module, but can not be accessed from procedures that reside in different modules.

Click Here to Main Menu