Subscript out of range access как исправить

Sometimes the users face the error of subscript out of range in access. This error occurs when you trying to reference an index for a collection that is invalid. Most likely, the index in Windows doesn’t actually include .xls. The index for the windows should be as same as the name of the displayed workbook in the title bar of Excel. Well, there can be many other reasons for subscript out of range access import error.

Reasons Behind Subscript Out of Range Error

The possible reasons for this error are given below: 

• In the Excel spreadsheet presence of an excessive number of columns.
• There can be some corruption issues that occurred in Excel files.
• Access is unable to translate the formatted or calculated Excel fields.
• While using the disabled Macros to access Excel.

These are the reasons responsible for the error. Now, let’s forward to the methods to resolve this issue.

Methods to Fix Error of Subscript Out of Range in Access

Subscript Out of Range Error is not a big issue that requires so much professional knowledge to fix. Generally, it occurs because of silly mistakes. Review all the given below checklist for fixing the error by making a few adjustment settings.

Method#1: Don’t Put Anything Over the Limit

Firstly, check out how many numbers of columns are present in your Excel spreadsheet? It’s necessary because MS Access Table can not consist of more than 255 number of files.

The best option to resolve this error or Subscript Out of Range Access import by reducing the unwanted column numbers. Only keep those columns that contain important data or the primary key.

The second step is that you can use is splitting the Access database

Method#2: Remove the Calculated Columns

If your Excel workbook consists of any calculated column and you are transferring it Access directly. Then it may create Subscript Out of Range Error. As it’s found that the Access database fails in transferring the formatted or calculated Excel fields.

So, for this, you have to copy the calculated column. After then only paste the calculated column’s value into a new column. At last, delete the calculated column.

Method#3: Make Your Access or Excel Error Free

Just make a quick move over complete your Excel spreadsheet, so that no error will found in your Excel workbook. It can help you to resolve the subscript out of range in access error.

It’s necessary to check because if you are importing an error in an Excel spreadsheet into Access, then it will harm your Excel database. As a result, you will get an error of subscript out of range in the access. So it’s better to go for a regular check.

The same rule of making the Access database error fee is also applicable if you are facing the error while splitting the Access database.

Method#4: Enable Your Disabled Macros

The steps are below for enabling and disabling Macros in all MS Office files.

When you open a file that shows the error of disabled macro then at the top bar, you will a yellow message bar along with the enable content button.

Here are the steps to enable it and fix the subscript out of range access import

• Firstly, tap on the file.
• Now, go to the security warnings section and click on the enable content.
• Select advanced options.
• A dialog box of MS Office security options will pop-up. From this, choose the option of enable content for this session, for each of the Macros.
• At last, click on OK.

Method#5: Professional Method

There is an alternative solution to fix the subscript out of range in access error. This contains a third-party tool that you can try. There is professional third-party software i.e, Access Database Recovery Tool. It can easily repair the subscript out of range import issue easily. It comes with some advanced features which are given below:

Features

• It can easily repair corrupt of MDB and ACCDB files.
• Also resolves the head corruption and the data misalignment issues.
• Have the tendency to restore, OLE, MEMO, & BLOB data of MS Access.
• Supports all Windows OS edition.

Wrap Up

If you have gone through the above post sincerely, then you have got a perfect idea to fix the error. Now, you can work according to these methods and can fix the subscript out of range in access error. As we have discussed the different methods. If we talk about the best, then you can opt for the Access Database Recovery Tool. This can easily fix this error without any consequences. The other methods contain many limitations and also consumes time. So choose the best for a better experience.

Related Post

Ms Access Subscript Out of Range error often occurred when you try to import Ms Excel database to Ms Access or trying to split the database. The possible cause of subscript out of range error:
1. Too many column in Excel spreadsheet
2. Corruption in Excel file.
3. Access cannot translate the formatted or modified Excel fields.
4. Disabled Macros from Excel to Access.
5. Using tool wizards in creating query or other objects.
6. Error code in VBA code or Macros programming.
(Sample of the Subscript error when importing database)

The solutions for solving the Subscript Out of Range error may be vary depends on the error source. Here are some way to resolve it based on the problem:

1. For Too many Excel Column, the best way to fix it is by reduce the column amount. Just save the crucial, important, or the primary key fields. Or, you can split the database into several databases.

2. For corrupted file, try the repair tools in Ms. Excel. Make sure to back up the spreadsheet first.

3. In the modified Excel fields, you need to remove the format or modified value and convert them into the simplest format. If the field is equation field (summarized, or other calculation) or has formula (like If else), try to not import them and recreate the said fields in the Access instead with Access syntax.

4. For Disabled Macros, you will receive a pop up or warning to inform you. You only have to enable it.

5. The query wizard can cause the problem. Try to create the query and add the tables manually.

6. So far, the error caused by Error code in the VBA code is the worse and harder to fix. You need to recheck all of the code lines to find the error, typos, or wrong syntax. Make sure you can answer these following question:
a. Check the declared array. Is the array exist? Do you specify the number of elements?
b. If you using subscript (!), does the syntax is right?

The tutorial above covers the most common Subscript Out of Range error. Please note that there still many possible cause and other solutions to solve and fix the problem.

Related Access Database

• subscript out of range access
• access subscript out of range
• access subscript out of range error
• subscript out of range
• ms access subscript out of range
• access error subscript out of range
• subscript out of range access error
• ms access subscript out of range import error
• subscript out of range error
• subscript out of range error access import

Access Database Tags: #how to fix subscript out of range error in access #microsoft access 2016 subscript out of range error #ms access subscript out of range form wizard #subscript out of range access 2013 import excel file #subscript out of range access split database #subscript out of range error in access when exporting to excel #what does subscript out of range mean in vba

• «
• Facebook
• Twitter
• Pin it
• …
• »

When running a VBA procedure, you might receive this error message
“Run-time error ‘9’, Subscript out of range”. In this article, we will talk about the possible reasons for the occurrence of this error and the ways to fix it. 

According to our recent review, there are four categories that can trigger this error:

1. Referring to a nonexistent element
2. Wrong collection member
3. Unidentified element 
4. Shorthand script

We are going to take a look at each of these categories and show you how to generate this error and share the best way to fix it.

Referring to a nonexistent element

In some cases, you might have referenced an element (a workbook, worksheet, array, etc.) in the code that doesn’t exist. It is also possible that the subscript is either smaller or larger than the range of possible subscripts.

You referred to a nonexistent Worksheet

To generate this error, open the VBA editor and write the following code to select the sheet “customers” in Module1.

Sub Test1()
Sheets("customers").Select
End Sub

If you run the code, you will have the error message.

The reason for this error is the fact that there is no sheet named “customers” in the workbook. Our workbook has three sheets named respectively Sheet1, Sheet2 and Sheet3.

To fix the error, create a worksheet named “customers” or rename any of the three existing sheets and run the macro again.

You referred to a Workbook that is not opened on your computer

If you run the code below, you will have the error code because you will be referring to a workbook that is not opened on your computer.

Sub Macro3( B )
Dim Wbk As Workbook
Set Wbk = Workbooks("Employees.xls")
End Sub

Wrong collection member

The subscript may be larger or smaller than the range of possible subscripts. 

To generate this error, run the following code:

Sub Macro( )
Dim PriceArray(5 To 10 ) As Integer
PriceArray(3) = 20
End Sub

Or

Sub Macro( )
Dim PriceArray(5 To 10 ) As Integer
PriceArray(15) = 20
End Sub

The error will be triggered after running any of the two macros above because the PriceArray has been dimensioned from 5 to 10. But the code is referring to a subscript of index 3 (lower than 5) and 15 for the second macro (larger than 10). Elements of arrays and members of collections can only be accessed within their defined ranges.

That is why it is important to check the declaration of the array to verify it’s upper and lower bounds. It is also advisable to use the UBound and LBound functions to condition array accesses if you are working with arrays that are redimensioned.

Unidentified element

You declared an array but didn’t specify the number of elements

The following code will generate the error:

Sub Macro2( )
Dim PriceArray( ) As Integer
PriceArray(5) = 20
End Sub

The error occurs because when declaring the array named PriceArray, the number of elements was not specified. In fact, Visual Basic does not implicitly dimension unspecified array range as 0 to 10.

To avoid the error, you must explicitly specify the number of elements in the array as in the code below:

Sub Macro3( )
Dim PriceArray(0 To 10 ) As Integer
PriceArray(5) = 20

Shorthand script

You might have used a shorthand form of the subscript and it specifies an invalid element. Therefore, it is recommended that you use a valid key name and index for the collection.

For example [A10] is the shorthand for ActiveSheet.Range(A10). This means that instead of writing ActiveSheet.Range(A10).Select you can just write [A1].Select 

If you don’t have a good knowledge of shorthand scripting, avoid using it because it can generate many errors, not just the “Subscript is out of range” error.

The aim of this article was to explain the possible reasons (the list is not exhaustive) for the occurrence of the “Subscript is out of range” error. Through a step by step tutorial, you have been able to fix the error. We hope it has been useful.