Subscript out of range?

ASP Free Forums Sponsor:

December 23rd, 2005, 11:00 AM

TheJim01

Contributing User

Join Date: Jul 2005

Posts: 45

Time spent in forums: 12 h 4 m 23 sec
Reputation Power: 5

Subscript out of range?

Hi all,

This is probably a stupid question with a simple answer, but for the life of me, I don't see where it's going wrong. I'm filling an array with values from an excel spreadsheet with the following code:

Code:

  thisRow = 2
  
  While Len(Range("I" & CStr(thisRow)).Value) <> 0
    
    If Range("P" & CStr(thisRow)).Value = (Date - 1) Then
      completedCumulative = completedCumulative + 1
      cumulativeArray(completedCumulative, 1) = Range("H" & CStr(thisRow)).Value
      cumulativeArray(completedCumulative, 2) = Range("I" & CStr(thisRow)).Value
    End If
    
    thisRow = thisRow + 1
  Wend

My problem is inside the If statement.

The completedCumulative variable is of type Integer and initialized to 0, the array is of type Variant, and I've verified that both cells contain data. However, the first time it starts through the loop, it throws a "Runtime Error 9", stating that the subscript (I'm assuming it means of the array) is out of range. I'm at a loss, and all suggestions are welcome.

Thanks and happy holidays!

December 23rd, 2005, 10:10 PM

Doug G

Grumpier Old Moderator

ASP Free God 11th Plane (10000 - 10499 posts)

Join Date: Sep 2003

Posts: 10,143

Time spent in forums: 3 Weeks 4 Days 23 h 19 m 36 sec
Reputation Power: 181

I don't completely grasp your code, but one suggestion is to remember VB arrays are 0-based where certain collection objects are 1-based. The first array node in vb is array(0)

Comments on this post

TheJim01 agrees: 0-base, 1-base, who's counting?

__________________
======
Doug G
======
I didn't attend the funeral, but I sent a nice letter saying I approved of it. --Mark Twain

December 24th, 2005, 05:01 AM

Shadow Wizard

Moderator From Beyond

ASP Free God 46th Plane (27500 - 27999 posts)

Join Date: Sep 2004

Location: Israel

Posts: 27,638

Folding Points: 374417 Folding Title: Super Ultimate Folder - Level 1

Time spent in forums: 3 Months 2 Weeks 3 h 2 m 56 sec
Reputation Power: 1902

yep and that means try changing the code to this:

Code:

 cumulativeArray(completedCumulative, 0) = Range("H" & CStr(thisRow)).Value
 cumulativeArray(completedCumulative, 1) = Range("I" & CStr(thisRow)).Value

Comments on this post

TheJim01 agrees: I understand array indicies, and have actually replaced the numerical references with mnemonic
constants

__________________

Visit The Windows Scripting Guru

December 27th, 2005, 12:09 PM

TheJim01

Contributing User

Join Date: Jul 2005

Posts: 45

Time spent in forums: 12 h 4 m 23 sec
Reputation Power: 5

Quote:

Originally Posted by Doug G

one suggestion is to remember VB arrays are 0-based where certain collection objects are 1-based.

Right on. The script was choking because I started at index 1. I should have known better, though I was thinking earlier that it should have been able to start at any subscript as long as the memory has been allocated. I guess I'm thinking too "C++" for that part. Thanks to both of you!

<edit>

Ok, nevermind. It worked the first time I tried it. Now, it doesn't. I haven't changed any of the other code. Same problem as before, same place as before. The first time it tries to copy fom the spreadsheet into the array, it chokes. I took out the mnemonic constants, thinking they were the culprit(s), and put the numerical values back in, but it's saying that subscript 0,0 is out of range. I have verified that both indicies are set to 0 when it tries to copy, and that there is data in the cell. Array type is still variant. Any other suggestions? Thanks again!

</edit>

December 27th, 2005, 01:14 PM

Shadow Wizard

Moderator From Beyond

Join Date: Sep 2004

Location: Israel

Posts: 27,638

Time spent in forums: 3 Months 2 Weeks 3 h 2 m 56 sec
Reputation Power: 1902

just wondering, how exactly do you define cumulativeArray?
having it as Variant is not enough, you must say in advance how many items
the array can hold. I'm not sure about VB syntax, but here are the two ways
to declare array in vbscript:

Code:

Dim cumulativeArray(10, 10)

or:

Code:

 Dim cumulativeArray()
 ReDim cumulativeArray(10, 10)

where in the second way it's also possible to put dynamic value (variable)
instead of the hard coded numbers.

December 27th, 2005, 02:38 PM

TheJim01

Contributing User

Join Date: Jul 2005

Posts: 45

Time spent in forums: 12 h 4 m 23 sec
Reputation Power: 5

Quote:

Originally Posted by Shadow Wizard

you must say in advance how many itemsthe array can hold.

You hit the nail on the head--I hadn't defined the size of my array. The "array(10,10)" worked and fixed the problem. I had been going off of examples I found online, so obviously the examples weren't very good ones! Thanks again!

December 27th, 2005, 03:18 PM

Shadow Wizard

Moderator From Beyond

Join Date: Sep 2004

Location: Israel

Posts: 27,638

Time spent in forums: 3 Months 2 Weeks 3 h 2 m 56 sec
Reputation Power: 1902

in your case, you have to first count how many rows you have then you can
set array size:

Code:

Dim cumulativeArray()

completedCumulative=0
thisRow=0
While Len(Range("I" & CStr(thisRow)).Value) <> 0
   If Range("P" & CStr(thisRow)).Value = (Date - 1) Then
      completedCumulative = completedCumulative + 1
   End If
   thisRow = thisRow + 1
Wend

ReDim cumulativeArray(completedCumulative-1, 1)

completedCumulative=0
thisRow=0
While Len(Range("I" & CStr(thisRow)).Value) <> 0
   If Range("P" & CStr(thisRow)).Value = (Date - 1) Then
      completedCumulative = completedCumulative + 1
      cumulativeArray(completedCumulative, 0) = Range("H" & CStr(thisRow)).Value
      cumulativeArray(completedCumulative, 1) = Range("I" & CStr(thisRow)).Value
   End If
   thisRow = thisRow + 1
Wend

glad I could be the hammer!

December 29th, 2005, 09:58 AM

TheJim01

Contributing User

Join Date: Jul 2005

Posts: 45

Time spent in forums: 12 h 4 m 23 sec
Reputation Power: 5

Quote:

Originally Posted by Shadow Wizard

in your case, you have to first count how many rows you have then you can
set array size
...
glad I could be the hammer!

Good idea, though I'm looking at hundreds-to-thousands of excel rows, and I don't anticipate going over 20 instances that I would need to count, so I've set it to array(20,2)--20 for the maximum number of instances, and 2 for the values I'm storing per instance. Thanks again!

December 29th, 2005, 08:52 PM

Doug G

Grumpier Old Moderator

Join Date: Sep 2003

Posts: 10,143

Time spent in forums: 3 Weeks 4 Days 23 h 19 m 36 sec
Reputation Power: 181

Instead of an array you might consider using a VB collection object.

For VB6 - http://msdn.microsoft.com/library/e...jCollection.asp

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: