Issue Averaging Array bc of Div/0 errors & 0's

[khabi21]

I am getting an error at the worksheetfunction.average line when I run this macro because of div/0 errors in the data. It runs fine when there are no div/0 errors in the code, so everything else is fine. I just need a way to be able to ignore the div/0 in the data, and preferably to filter out the zeros as well.

In this macro, I create a range to collect data from rngCollect, and then put every 4th value into an array. I then do some computations from that array, and that is where it hangs up when the data has errors in it. There is no way to get rid of the errors in the data. There are over 500 workbooks I am grabbing from, so I can't go through and manually filter.

wbData is defined in an earlier subroutine in this macro. Like I said there are no other issues with the code, it runs perfect when the data is full. Just need a way to filter the div/0 and zeros from the array so I can do the average, stdev, min, max calculations.

Public wbData As Workbook

Sub InputData()

Dim rngCollect As Range
Dim arrCOR As Variant, arrCT As Variant
Dim wsData As Worksheet, wsSamp As Worksheet
Dim i As Long, j As Long
Dim LastRow As Integer


Set wsData = wbData.Worksheets("Database")
Set wsSamp = wbData.Worksheets("Sample")


'Set range of data to collect from:
Set rngCollect = wsSamp.Range("C1", "C192")


ReDim arrCOR(1 To 48)
ReDim arrCT(1 To 48)


'Grab COR data which is every 4th row:
j = 1
For i = 3 To rngCollect.Count Step 4
    arrCOR(j) = rngCollect(i)
    j = j + 1
Next i


'Grab CT data which is every 4th row:
j = 1
For i = 4 To rngCollect.Count Step 4
    arrCT(j) = rngCollect(i)
    j = j + 1
Next i


'Input all the data into the database:
LastRow = wsData.UsedRange.Rows.Count + 1


wsData.Cells(LastRow, 1).Value = wsSamp.Range("M1").Value
wsData.Cells(LastRow, 2).Value = wsSamp.Range("M2").Value
wsData.Cells(LastRow, 3).Value = wsSamp.Range("M3").Value
wsData.Cells(LastRow, 4).Value = wsSamp.Range("M4").Value
wsData.Cells(LastRow, 5).Value = wsSamp.Range("G1").Value
wsData.Cells(LastRow, 6).Value = wsSamp.Range("G2").Value
wsData.Cells(LastRow, 7).Value = wsSamp.Range("G3").Value
wsData.Cells(LastRow, 8).Value = wsSamp.Range("G4").Value
wsData.Cells(LastRow, 9).Value = wsSamp.Range("G5").Value
wsData.Cells(LastRow, 10).Value = WorksheetFunction.Average(arrCT)
wsData.Cells(LastRow, 11).Value = WorksheetFunction.StDev(arrCT)
wsData.Cells(LastRow, 12).Value = WorksheetFunction.Max(arrCT)
wsData.Cells(LastRow, 13).Value = WorksheetFunction.Min(arrCT)
wsData.Cells(LastRow, 14).Value = wsData.Cells(LastRow, 12).Value - wsData.Cells(LastRow, 13).Value
wsData.Cells(LastRow, 15).Value = WorksheetFunction.Average(arrCOR)
wsData.Cells(LastRow, 16).Value = WorksheetFunction.StDev(arrCOR)
wsData.Cells(LastRow, 17).Value = WorksheetFunction.Max(arrCOR)
wsData.Cells(LastRow, 18).Value = WorksheetFunction.Min(arrCOR)
wsData.Cells(LastRow, 19).Value = wsData.Cells(LastRow, 17).Value - wsData.Cells(LastRow, 18).Value
wsData.Cells(LastRow, 20).Value = wsSamp.Range("J1").Value
wsData.Cells(LastRow, 21).Value = wsSamp.Range("J2").Value
wsData.Cells(LastRow, 22).Value = wsSamp.Range("J3").Value
wsData.Cells(LastRow, 23).Value = wsSamp.Range("J4").Value
wsData.Cells(LastRow, 24).Value = wsSamp.Range("J5").Value


End Sub

 

[offthelip]

You just need to modify your code to check for 0 and whether it is a number as follows:

j = 1


For i = 3 To rngcollect.Count Step 4
    If IsNumeric(rngcollect(i)) Then
    If rngcollect(i) <> 0 Then
    arrCOR(j) = rngcollect(i)
    j = j + 1
    End If
    End If
Next i

Then use exactly the same code for ArrCT array as well.


Note you could speed up your code by decaring rngcollect as a variant array by changing this line:

Set rngCollect = wsSamp.Range("C1", "C192")

to

 rngCollect = wsSamp.Range("C1:C192")

and changing the reference to it to a two dimensional array e.g.

For i = 3 To ubound(rngcollect) Step 4
If IsNumeric(rngcollect(i,1)) Then
    If rngcollect(i,1) <> 0 Then
    arrCOR(j) = rngcollect(i,1)
    j = j + 1
    End If
    End If

Don't forget to change the declaration as well.

 

[khabi21]

That worked beautifully, thank you.

Can I ask why does changing the rngCollect from a range object to a variant array quickens the code?
Seems like a learning opportunity for me.

 

[offthelip]

One of the main reasons that Vba is slow is the time taken to access the worksheet from VBa is a relatively long time.
To speed up vba the easiest way is to minimise the number of accesses to the worksheet. What is interesting is that the time taken to access a single cell on the worksheet in vba is almost identical as the time taken to access a large range if it is done in one action.
So instead of writing a loop which loops down a range copying one row at a time which will take along time if you have got 50000 rows it is much quicker to load the 50000 lines into a variant array ( one worksheet access), then copy the lines to a variant array and then write the array back to the worksheet, ( one worksheet access for each search that you are doing),
I have a simple rule for fast VBA: NEVER ACCESS THE WORKSHEET IN A LOOP.
Just to show you how much faster variant arrays are try running these two subroutines which both do the same calculations except the "FAST" routine does it 500 times. The slow routine does it once. On my machine "slow" takes 3 secodns while "Fast" take 1 second. i.e it is 1500 time faster. Worth doing !!

Sub slow()
tt = Timer()
'initialise
 For j = 1 To 10
  Cells(j, 1) = 0
 Next j
For i = 1 To 1000
 For j = 1 To 10
  Cells(j, 1) = Cells(j, 1) + 1
 Next j
Next i
MsgBox (Timer() - tt)


End Sub


Sub fast()
tt = Timer()
Dim outarr(1 To 10, 1 To 1)
For k = 1 To 500
'initialise
 For j = 1 To 10
  outarr(j, 1) = 0
 Next j
Range(Cells(1, 1), Cells(10, 1)) = outarr


inarr = Range(Cells(1, 1), Cells(10, 1))
For i = 1 To 1000
 For j = 1 To 10
  inarr(j, 1) = inarr(j, 1) + 1
 Next j
Next i


Next k
Range(Cells(1, 1), Cells(10, 1)) = inarr
MsgBox (Timer() - tt)


End Sub

 

[khabi21]

Gotcha, makes sense. Why do you want to use a 2 dimensional array, when the 2nd dimension is not used?

 

[offthelip]

if you are reading from or writing to the worksheet directly then a variant array must be a two dimensoinal array, this is because the worksheet is two dimensional e.g. rows and columns. be careful when referencing an array read directly from the worksheets because thie indexing is array(row,column). so:

inarr=range("A1:B10")

has dimensions
Inarr(1 to 10 , 1 to 2)

inarr=range("A1:A10")

has dimensions
inarr (1 to 10 ,1)
so it is still a two dimension array butthe second dimension only has one value (1)