VBA: Subscript out of range

Asked By 0 points N/A Posted on -
qa-featured

Greetings to everyone.

As I am an amateur to VBA and its process, I would like to apologize at first if there is anything wrong. My problem arises when I created a range of countries and locations according to my data. 

More precisely I can explain it with an example, like if I choose England I want to have only English speaking countries from my range. 

Now I am totally confused on how to proceed and do the task. 

Again, while trying to fill up command box for each statement, kind of following message I got "Subscript out of range". 

Please help me on how to find a solution of this.

Thank you.

SHARE
Answered By 0 points N/A #92844

VBA: Subscript out of range

qa-featured

Elements of arrays and members of collection can only be accessed within their defined ranges. This error has the following causes and solutions:

1. You referenced a nonexistent array element

Solution: The subscript may be larger or smaller than the range of possible subscripts, or the array may not have dimensions assigned at this point in the application. Check the declaration of the array to verify its upper and lower bounds. Use the UBound and LBound functions to condition array accesses if you are working with arrays that are re-dimensioned. If the index is specified as a variable, check the spelling of the variable name.

2. You declared an array but did not specify the number of elements. For example, the following code causes this error:

Solution:

Dim My Array () As integer

MyArray(8)=234  ' Causes Error 9.

Visual basic does not implicitly dimension unspecified array ranges 0 10. Instead, you must use Dim or ReDim to specify explicitly the number of element array.

3. You referenced a nonexistent collection member

Solution: Try using the For Each…Next construct instead of specifying index element.

4. You used a shorthand form of subscript that implicity specified an invalid element.

Solution: For example, when you use the ! Operator with a collection, the ! Implicitly specifies a key. For example, object!keyname.value is equivalent to the object.item(keyname).value. In this case, an error is generated if keyname represents an invalid key in the collection. To fix the error, use a valid key name or index of the collection.

Thanks.

Regards

salman.

Login/Register to Answer

Related Questions