talltk
asked on
Excel VBA Array Out Of Bounds
Topic: Excel VBA Programming
OS : Windows XP Platform
Hi to all. Rather new to this site so please bear with me.
To make things simple, I have 2 tables in EXCEL, one called "IN" and one called "OUT".
Table "IN" has unique document numbers (meaning only one instance of each may be found in the column) which I store in an array called documentNumberIn(200). Table "OUT" has many instances of each document number from table "IN" which I also store in an array called documentNumberOut(200).
I need to compare the two arrays to check for equal values AND also at the same time (this is the important part) check to see if there are any previous instances of the same document number from the OUT table. If there is at least one instance from the previous record, my program will carry out steps ABC; if there isn't an instance from the previous record, my program will carry out steps DEF. Hope you get the picture so far.
I initialize 2 for loops with variable 'i' to control the IN array and 'j' to control the OUT array.
The following code receives the "subscript <out of range>" error because documentNumberOut(j-1) at the very first instance when j=0, causes the array to go out of bounds.
For i To 200
For j To 200
If ((documentNumberIn(i) = documentNumberOut(j)) And (documentNumberOut(j) = documentNumberOut(j-1)))
'Carry out steps ABC here
ElseIf ((documentNumberIn(i) = documentNumberOut(j)) And (documentNumberOut(j)<>doc
'Carry out steps DEF here
Can someone please help me if you know the answer? Is there any array function for EXCEL VBA that tests if the array is out of bounds? Thanks a million!!!!! God bless you all!
OS : Windows XP Platform
Hi to all. Rather new to this site so please bear with me.
To make things simple, I have 2 tables in EXCEL, one called "IN" and one called "OUT".
Table "IN" has unique document numbers (meaning only one instance of each may be found in the column) which I store in an array called documentNumberIn(200). Table "OUT" has many instances of each document number from table "IN" which I also store in an array called documentNumberOut(200).
I need to compare the two arrays to check for equal values AND also at the same time (this is the important part) check to see if there are any previous instances of the same document number from the OUT table. If there is at least one instance from the previous record, my program will carry out steps ABC; if there isn't an instance from the previous record, my program will carry out steps DEF. Hope you get the picture so far.
I initialize 2 for loops with variable 'i' to control the IN array and 'j' to control the OUT array.
The following code receives the "subscript <out of range>" error because documentNumberOut(j-1) at the very first instance when j=0, causes the array to go out of bounds.
For i To 200
For j To 200
If ((documentNumberIn(i) = documentNumberOut(j)) And (documentNumberOut(j) = documentNumberOut(j-1)))
'Carry out steps ABC here
ElseIf ((documentNumberIn(i) = documentNumberOut(j)) And (documentNumberOut(j)<>doc
'Carry out steps DEF here
Can someone please help me if you know the answer? Is there any array function for EXCEL VBA that tests if the array is out of bounds? Thanks a million!!!!! God bless you all!
Last Comment
ASKER
Sorry I need to clarify something.
From the code that I posted in the initial post, the error will only be evident if j=0. However, I still need to make it run as per usual even though documentNumberOut(j-1) causes an array out of bounds error. What I need is something like:
"If document number from IN table is in OUT table AND there is a document number BEFORE (documentNumberOut(j) = documentNumberOut(j-1)) the current document number from the OUT table, execute ABC."
"If document number from IN table is in OUT table AND there isn't a document number BEFORE
(documentNumberOut(j) <> documentNumberOut(j-1)) the current document number from the OUT table, execute DEF."
With what I have, I keep encountering array out of bounds error whenever i=0 and j=0. How do I get around this and let the program function as intended above?
Thanks anyways Raynard7!
From the code that I posted in the initial post, the error will only be evident if j=0. However, I still need to make it run as per usual even though documentNumberOut(j-1) causes an array out of bounds error. What I need is something like:
"If document number from IN table is in OUT table AND there is a document number BEFORE (documentNumberOut(j) = documentNumberOut(j-1)) the current document number from the OUT table, execute ABC."
"If document number from IN table is in OUT table AND there isn't a document number BEFORE
(documentNumberOut(j) <> documentNumberOut(j-1)) the current document number from the OUT table, execute DEF."
With what I have, I keep encountering array out of bounds error whenever i=0 and j=0. How do I get around this and let the program function as intended above?
Thanks anyways Raynard7!
Add:
On Error Resume Next before the IF statement
(incidently, you're missing your THEN in the IF..THEN statement).
On Error Resume Next before the IF statement
(incidently, you're missing your THEN in the IF..THEN statement).
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
I just realised, you don't need it above the ElseIf statment because it forms part of the whole IF statement above - just take that out.
I'm just going to head to the coffee machine now... :-)
I'm just going to head to the coffee machine now... :-)
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Try this
For i=1 To 200
For j=1 To 200
For i=1 To 200
For j=1 To 200
zorvek >
"Yikes! Don't ever use On Error Resume Next to mask a problem with logic!
Talk about letting bad programming habits slide!"
I have to say, this is the first time I've ever heard this being said. I've learned stuff in class at work and college and they seemed happy to use this. I suppose that's why I've never had a problem using it.
Apologies if its rubbed you up the wrong way, I'll get out of the habit of using this now...
"Yikes! Don't ever use On Error Resume Next to mask a problem with logic!
Talk about letting bad programming habits slide!"
I have to say, this is the first time I've ever heard this being said. I've learned stuff in class at work and college and they seemed happy to use this. I suppose that's why I've never had a problem using it.
Apologies if its rubbed you up the wrong way, I'll get out of the habit of using this now...
ASKER
Regarding:
zorvek:
You need to avoid doing the comaprison when j-1 would result in an out of bounds condition:
For i To 200
For j To 200
If j > LBound(documentNumberOut) Then
If ((documentNumberIn(i) = documentNumberOut(j)) And (documentNumberOut(j) = documentNumberOut(j-1)))
'Carry out steps ABC here
ElseIf ((documentNumberIn(i) = documentNumberOut(j)) And (documentNumberOut(j)<>doc
'Carry out steps DEF here
End If
End If
What if I MUST do comparisons which result in array out of bounds? The situation is that supposing documentNumberIn(0) = 15507 and documentNumberOut(0) = 15507. I need to check if there are previous copies of 15507 in the OUT table as there can be more than 1 instance of the same document number in the OUT table. Everywhere else in the array would not pose a problem, however, I must start my comparison from i=0 and j=0. Hence If i check for
documentNumberOut(j-1) = documentNumberOut(j), this would spawn an error stating Array out of bounds.
So in the above scenario, what do you suggest me to do? I tried what Jeggburt supplied as a solution and I think it works (at least there aren't any compilation errors). Sorry to bother you with my elementary knowledge of VBA.
zorvek:
You need to avoid doing the comaprison when j-1 would result in an out of bounds condition:
For i To 200
For j To 200
If j > LBound(documentNumberOut) Then
If ((documentNumberIn(i) = documentNumberOut(j)) And (documentNumberOut(j) = documentNumberOut(j-1)))
'Carry out steps ABC here
ElseIf ((documentNumberIn(i) = documentNumberOut(j)) And (documentNumberOut(j)<>doc
'Carry out steps DEF here
End If
End If
What if I MUST do comparisons which result in array out of bounds? The situation is that supposing documentNumberIn(0) = 15507 and documentNumberOut(0) = 15507. I need to check if there are previous copies of 15507 in the OUT table as there can be more than 1 instance of the same document number in the OUT table. Everywhere else in the array would not pose a problem, however, I must start my comparison from i=0 and j=0. Hence If i check for
documentNumberOut(j-1) = documentNumberOut(j), this would spawn an error stating Array out of bounds.
So in the above scenario, what do you suggest me to do? I tried what Jeggburt supplied as a solution and I think it works (at least there aren't any compilation errors). Sorry to bother you with my elementary knowledge of VBA.
I actually understand what Kevin is explaining above, although I had to go and cool down cause I felt a little bit embarrassed.
Basically, my solution probably *may* work, but later on you may encounter problems that are masked because I've advised you to use that On Error... spiel. What Kevin is saying, is the code you want a solution to, probably can be resolved without the need to ignoring the error message you're getting.
Since you have a basic knowledge of VBA, it will be easier to get into the habit of resolving errors before they keep occuring that (in my case), avoiding using something I've not been discouraged (until today) from using the past year.
Please note that I will still use that code, just a lot less frequently.
Thanks for the heads up Kevin.
Basically, my solution probably *may* work, but later on you may encounter problems that are masked because I've advised you to use that On Error... spiel. What Kevin is saying, is the code you want a solution to, probably can be resolved without the need to ignoring the error message you're getting.
Since you have a basic knowledge of VBA, it will be easier to get into the habit of resolving errors before they keep occuring that (in my case), avoiding using something I've not been discouraged (until today) from using the past year.
Please note that I will still use that code, just a lot less frequently.
Thanks for the heads up Kevin.
Visual Basic Classic
Visual Basic is Microsoft’s event-driven programming language and integrated development environment (IDE) for its Component Object Model (COM) programming model. It is relatively easy to learn and use because of its graphical development features and BASIC heritage. It has been replaced with VB.NET, and is very similar to VBA (Visual Basic for Applications), the programming language for the Microsoft Office product line.
165K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
if you want to know if an array is out of bouds you can test against
lbound(arrayName)
ubound(arrayName)
if your test value is < the lboundvalue or > ubound value then it will be out of range.