CODEWITHSUNDEEP
arraysvbaexcel
j0nr
j0nr

Reputation: 301

VBA Phantom Empty Array Value

I am looping through some data (only 8 values) in an Excel range from VBA, but I seem to be getting a 'phantom' empty array value in the middle.

Here is how I build the array. The range that it loops through results in 2 values being found, i.e. 2 values = 'rad'.

' arRoPtsXY is a 2D array
counter2 = UBound(arRoPtsXY, 2)
Dim arSecPtCt()
loopCtr = 0

For i = 1 To counter2
  rad = Sqr((secCX - arRoPtsXY(1, i)) ^ 2 + (secCY - arRoPtsXY(2, i)) ^ 2)
  If rad = secR Then
     ReDim Preserve arSecPtCt(i)
     arSecPtCt(loopCtr) = i
     loopCtr = loopCtr + 1
  End If
Next i

But when I perform this:

For a = LBound(arSecPtCt) To UBound(arSecPtCt)
  Debug.Print arSecPtCt(a)
Next a

I get this output:

1 
3 


'end

Note, above, there are 2 blank spaces being output (after 3, I added 'end to show the spaces here), like the array 'space' exists, but it is empty.

If I do a debug.print UBound(arSecPtCt) I get '3', meaning 0,1,2,3 occupied array 'spaces'.

I just want my array to be:

1
3

As it is only the 1st and 3rd value that = 'rad'.

If I debug.print i during the if loop, it only outputs the 1 and 3, no spaces, so the loop seems to work, but I am just getting these extra blanks.

Hope someone can put me out my misery.

Many thanks in advance.

Jon

Upvotes: 0

Views: 133

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60324

Try changing:

ReDim Preserve arSecPtCt(i)

to 

ReDim Preserve arSecPtCt(loopCtr)

Upvotes: 2

Related Questions