clearing an array

[NDBC]

If I have the array rider(5) as variant, how can I reset each of the array
variables to 0 without doing a loop 5 times.

count()=0 and
count().clear

do not work.

thanks

 

[p45cal]

Erase ride

 

[Patrick Molloy]

its a variant, so when clear the values are null

Dim a() As Long ' or Double
ReDim a(1 To 10, 1 To 5)

now all values are 0

 

[NDBC]

null is fine. I just want to make sure the values that were stored last time
are gone.

I tried
count()=null
as well and that didn't work.


Should I use
redim count (1 to 5)

 

[OssieMac]

From your message i am not sure if you want to clear or set the values to zero.

If you want to clear then declare the array without the number of elements
and then redim with the number of elements then when you want to clear again
just redim again.

If you want the values to be zero then declare the array without the number
of elements but as a numeric like integer, long etc and do as above.

Also it is always a good idea to declare/redim arrays using the 1 to 5 or 0
to 4 and that way you do not have to worroy about the Option Base 1 etc.

following demo should put you on the right track.

Sub ArrayDemo()

'Dim rider() 'will clear on redim
Dim rider() As Long 'will set to zero on redim
Dim i

'Note the 1 to 5 so the first element
'is addressed as 1 rather than zero
ReDim rider(1 To 5)

For i = 1 To 5
rider(i) = i
Next i

For i = 1 To 5
MsgBox rider(i)
Next i

ReDim rider(1 To 5)

For i = 1 To 5
MsgBox rider(i)
Next i


End Sub

 

[Patrick Molloy]

yes, redim would work for you

null is fine. I just want to make sure the values that were stored last time
are gone.

I tried
count()=null
as well and that didn't work.


Should I use
redim count (1 to 5)

 

[Peter T]

Use Erase as previously suggested. Note if used on a dynamic array you'll
need to Redim before you can use it again. If you know you will want your
1to5 array again use Redim instead.

In passing, best not use keywords like 'count' as variable names.

Regards,
Peter T

 

[NDBC]

Thanks to all. Whilst what you have told me works I have found another
problem. I am using an array to store the fastest lap time for each rider in
a team. The problem is to find the fastest lap I use the min function and
when I compare it to the reset value of the array (0/null) then 0 is always
the minimum.

I know I could just initialise the array to an enormously high time but of
course one day there could be a race with an enormously long lap and it would
fail. There must be a way around this.

This is my code

Private Sub Worksheet_Change(ByVal Target As Range)

Dim TRow As Integer
Dim TCol As Integer
Dim TeamTotal As Date
Dim Total() As Variant
Dim Fast() As Variant
Dim Slow() As Variant
Dim Count() As Variant
Dim Rider As Long
Dim MaxCol As Integer
Dim laps As Integer

If Target.Column >= 14 And Target.Row >= 5 Then

Sheets("A Grade").Unprotect
Application.ScreenUpdating = False
TRow = Target.Row
TCol = Target.Column

MaxCol = Sheets("A lap").Cells(TRow, Columns.Count).End(xlToLeft).Column
TeamTotal = 0
ReDim Count(1 To 2)
ReDim Total(1 To 2)
ReDim Fast(1 To 2)
ReDim Slow(1 To 2)
For laps = 14 To MaxCol Step 2

If Sheets("a lap").Cells(TRow, laps).Font.ColorIndex = 3 Then
Rider = 1
ElseIf Sheets("a lap").Cells(TRow, laps).Font.ColorIndex = 5 Then
Rider = 2
End If

TeamTotal = TeamTotal + Sheets("a lap").Cells(TRow, laps)
Count(Rider) = Count(Rider) + 1
Total(Rider) = Total(Rider) + Sheets("a lap").Cells(TRow, laps)
Fast(Rider) = WorksheetFunction.Min(Fast(Rider), Sheets("a
lap").Cells(TRow, laps))
Slow(Rider) = WorksheetFunction.Max(Slow(Rider), Sheets("a
lap").Cells(TRow, laps))

Next laps

'ave
For Rider = 1 To 2
If Count(Rider) > 0 Then
Sheets("a lap").Cells(TRow, Rider + 4) = Total(Rider) / Count(Rider)
End If
'Slow
Sheets("a lap").Cells(TRow, Rider + 6) = Slow(Rider)
'fast
Sheets("a lap").Cells(TRow, Rider + 8) = Fast(Rider)
Next Rider

'finish time
Sheets("a lap").Range("k" & TRow) = TeamTotal
'team average time
If Sheets("a grade").Range("j" & TRow) > 0 Then
Sheets("a lap").Range("d" & TRow) = TeamTotal / Sheets("a
grade").Range("j" & TRow)
Else
Sheets("a lap").Range("d" & TRow) = ""
End If
End If

End Sub


I know I could/should use a function to calculate some of this but the race
is on Sunday and I'm sticking to what I know for now until I have more time.
I am certainly not expecting anyone to re-write the code. I would rather have
the challenge myself anyway. I would just like to find out how to overcome
the array issue for now.

Thanks.

 

[OssieMac]

If I am interpretting your problem correctly then declare the array as
Variant then the min function ignores any empty elements except when all are
empty min returns zero. (I think you have established that.)

Therefore test for min returning zero and if so assume all are empty or zero
else run min function to return a number other than zero. Following example
populates just some elements of an array declared as variant and you will see
that min returns the correct minimum value of 3.

Sub MinFctnWithArray()

Dim MyArray() As Variant
Dim i

ReDim MyArray(1 To 10)

For i = 3 To 10 Step 2
MyArray(i) = i
Next i

For i = 1 To 10
MsgBox "Array element " & i & " = " & MyArray(i)
Next i

If WorksheetFunction.Min(MyArray()) = 0 Then
MsgBox "All elements in MyArray are empty or zero."
Else
MsgBox "Min value in array is " & WorksheetFunction.Min(MyArray())
End If

End Sub