PDA

View Full Version : nonexisting array-element null value ?

vb_albion
05-22-2006, 04:46 PM
Hello girls and others,

Now I have a problem with an (nonexisting) array-element null value (how to get it?).

If i have for instance a 2dimensional array testArray(2, 2) , i need something like:

Sub TestSub()
If testArray(5, 3) = Null Then
MsgBox "Success"
End if
End Sub

but i get error message : "Run-time error '9': Subscript out of range"

Actually, here is an entire test code:

Dim testArray() As Integer 'global

Sub Fill()
Dim k As Integer
Dim w As Integer
k = 100
w = 200
For t = 0 To 3
ReDim Preserve testArray(2, t)
testArray(0, t) = k
testArray(1, t) = w
MsgBox testArray(0, t) 'works
MsgBox testArray(1, t) 'works

k = k + 1
w = w + 1
Next t

Call TestSub
End Sub

Sub TestSub()
If testArray(5, 3) = Null Then
MsgBox "Yeah"
End If
End Sub
"Run-time error '9': Subscript out of range" :wot

Thanks

acw
05-22-2006, 05:00 PM
Hi

Why don't you check the upper boundary of testarray? Something like

if ubound(testarray,1) < 5 then msgbox "Yeah"

Tony

vb_albion
05-23-2006, 03:41 PM
Thanks Tony

Actually, i needed to know whether my scanning loop has reached the end of an array.

I solved this by placing appropriate counter in the array-filling loop, but I also tried that UBound() function, and don't quite understand what it does.

I have the following array:
arrayX(0,0) = 150
arrayX(1,0) = 3
arrayX(0,1) = 200
arrayX(1,1) = 2
arrayX(0,2) = 150
arrayX(1,2) = 1
arrayX(0,3) = 180
arrayX(1,3) = 1

UBound(arrayX, 2) = 7 and i don't know why.

acw
05-23-2006, 03:48 PM
Hi

How did you declare the arrayX array? What are the dimensions you have used? Something like arrayX(?,7)

The ubound function will find the upper boundary of the array. In this case, it is finding the upper boundary of the second element.

Tony

vb_albion
05-23-2006, 04:25 PM
Dim array_ofPairs() As Integer ' global
.....

k = 0 ' price
w = 0 ' number of consecutive appearances of certain price
z = 0 ' "price" column increment (1 down)
r = 0 ' counter

m = ActiveSheet.Range("Y2").Value ' total number of numerical data _ in "price" column

For t = 1 To m
k = ActiveSheet.Cells(7 + z, 5).Value
w = w + 1
z = z + 1
If k <> ActiveSheet.Cells(7 + z, 5).Value Then ' only if the next value is different
ReDim Preserve array_ofPairs(2, t)
array_ofPairs(0, r) = k
array_ofPairs(1, r) = w

w = 0
r = r + 1
End If
Next t

s = r - 1 'this is the counter which i solved the problem with, so i know how many pairs "price/numberofconsecutiveappearances" i have

MsgBox "array_ofPairs(0,0) = " & array_ofPairs(0, 0)
MsgBox "array_ofPairs(1,0) = " & array_ofPairs(1, 0)
MsgBox "array_ofPairs(0,1) = " & array_ofPairs(0, 1)
MsgBox "array_ofPairs(1,1) = " & array_ofPairs(1, 1)
MsgBox "array_ofPairs(0,2) = " & array_ofPairs(0, 2)
MsgBox "array_ofPairs(1,2) = " & array_ofPairs(1, 2)
MsgBox "array_ofPairs(0,3) = " & array_ofPairs(0, 3)
MsgBox "array_ofPairs(1,3) = " & array_ofPairs(1, 3)

MsgBox "UBound(array_ofPairs, 2) = " & UBound(array_ofPairs, 2)
.......

vb_albion
05-23-2006, 06:51 PM
Ok, I've found the mistake.

I should have put a new counting variable, say "i", in the For loop, to determine second dimension dinamicaly.
Using "t" for that purpose was wrong.

If k <> ActiveSheet.Cells(7 + z, 5).Value Then
ReDim Preserve array_ofPairs(2, i)
i=i+1
array_ofPairs(0, r) = k
array_ofPairs(1, r) = w

Thanks