nonexisting array-element null value ?

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:

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

Hi

Why don't you check the upper boundary of testarray? Something like
[vba]
if ubound(testarray,1) < 5 then msgbox "Yeah"
[/vba]


Tony

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.

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

Code:

---

Dim array_ofPairs() As Integer    ' global

---

.....

Code:

---

    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) 
.......

---

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.

Code:

---

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