Coding best practices for calling worksheet functions which may return an error?

[SliderJeff]

Hey all,

So I just stumbled across an issue which I hadn't expected when processing certain datafiles with my VBA using the WorksheetFunction.AverageIf(). Evidently it's possible for me to process an array with this function call which may actually contain ONLY zeros, and hence throw an error since there is no non-zero data to process.

Here's an example:

    outputWorkbook.Worksheets("Sheet1").Cells(fileNum + wbScript.GetConstant("FILE_OFFSET"), wbScript.GetConstant("CURRENT_COL")).Value = Application.WorksheetFunction.AverageIf(inputWorkbook.Worksheets(fileName).Columns("B:B"), ">0")

Since the function was trying to average only values that are ">0", and the values in Column B were all 0, I got an error and the script stopped.

For brevity, I'll abbreviate the left side of the equation above as "output.Value".

If such an issue occurs where there is an error thrown, I'd just like to do the following:

output.Value = 0

And then move on to the next line of code.

Given that I have upwards of 150 calls to worksheet functions like AverageIf, Index, Match, etc... I was wondering if I need to insert an:

On Error Resume Next

line of code before each and every single WorksheetFunction I call, or if there is a more universal/better way to code up error handling and still be able to do things like set the output value to a known value if there is an error?

Thanks for any help you can provide.

Regards,
Jeff

 

[SliderJeff]

For the time being, I'm going to add code along these lines below everywhere I call a function.

    On Error Resume Next
    outputVal = Application.WorksheetFunction.Average(inputWorkbook.Worksheets(fileName).Columns("B:B"))
    On Error GoTo 0
    If IsEmpty(outputVal) Then
        outputVal = 0
    End If
    outputWorkbook.Worksheets("Sheet1").Cells(fileNum + wbScript.GetConstant("FILE_OFFSET"), wbScript.GetConstant("TEMPERATURE_COL")).Value = outputVal

Please let me know if there is a better way to handle this. If not, I hope this helps someone else with a similar question.

Regards,
Jeff