Why is #DIV/0 error being missed by error handler?

SliderJeff

Board Regular
Joined
Oct 5, 2016
Messages
63
Hey gang,

So I have added a bunch of error handling code for when the AverageIf worksheet function is called, but it seems to be missing when the #DIV/0 error happens.

Basically what I have is a bunch of non-zero numbers in Column D, followed by a bunch of zero values in Column E. The code I am executing is as follows:

Code:
' Find Average Temperature
    On Error Resume Next
    outputVal = Application.WorksheetFunction.Average(inputWorkbook.Worksheets(fileName).Columns("D:D"))
    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
'    outputWorkbook.Worksheets("Sheet1").Cells(fileNum + wbScript.GetConstant("FILE_OFFSET"), wbScript.GetConstant("TEMPERATURE_COL")).Value = Application.WorksheetFunction.Average(inputWorkbook.Worksheets(fileName).Columns("D:D"))
    outputWorkbook.Worksheets("Sheet1").Cells(fileNum + wbScript.GetConstant("FILE_OFFSET"), wbScript.GetConstant("TEMPERATURE_COL")).NumberFormat = "0"
' Find Average of non-zero Lateral Shocks
    On Error Resume Next
    outputVal = Application.WorksheetFunction.AverageIf(inputWorkbook.Worksheets(fileName).Columns("E:E"), ">0")
    On Error GoTo 0
    If IsEmpty(outputVal) Or IsError(outputVal) Then
        outputVal = 0
    End If
    outputWorkbook.Worksheets("Sheet1").Cells(fileNum + wbScript.GetConstant("FILE_OFFSET"), wbScript.GetConstant("LATERAL_SHOCK_COL")).Value = outputVal

For the first call to AverageIf above, outputVal has a value of 18.976. However, whereas I expect outputVal to become an error of "#DIV/0" on the second call to the AverageIf function, it just keeps it's previous value of 18.976 and carries on with the next line of code, completely skipping the line

Code:
    If IsEmpty(outputVal) Or IsError(outputVal) Then

almost as if it didn't even attempt to evaluate the new AverageIf call for outputVal.

Can someone explain what is going on and why my outputVal variable is not correctly being calculated for the second call to AverageIf?

Thanks,
Jeff
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Without the workbooks, it is hard to test.

You might want to use if x<>"" rather than isempty(). Your can also try a:
Code:
Debug.Print err.number, err.descriptionj after you expect an error.  That put the results of a run into the Immediate window.
 
Upvote 0
Before the AVERAGEIF line, you have the

On Error Resume Next

line. By your comments, you're expecting it to process the AverageIf, find the error, and assign the error to outputVal, then proceed to the next line, where you check for it with IsError. What's really happening is that it tries to process the AverageIf, gets the error, does NOT actually change the value of outputVal, and jumps to the next line. There are some ways to assign an error to a variable, but not like this.

And like Kenneth suggests, instead of checking for IsError(outputVal), you can check for Err.Number > 0. It may just be better to write an error handler.
 
Upvote 0
Thanks, guys.

I did as you both suggested and changed, for instance:

Code:
' Find Average Temperature
    On Error Resume Next
    outputVal = Application.WorksheetFunction.Average(inputWorkbook.Worksheets(fileName).Columns("D:D"))
    On Error GoTo 0
    If IsEmpty(outputVal) Then
        outputVal = 0
    End If

to
Code:
' Find Average Temperature
    On Error Resume Next
    outputVal = Application.WorksheetFunction.Average(inputWorkbook.Worksheets(fileName).Columns("D:D"))
    If Err.Number > 0 Then
        outputVal = 0
    End If

As the Goto 0 line was resetting the Err.Number prior to me checking for it to be > 0.

I'm still not clear why a function that has a return value of an error is not capable of being stored into my outputVal variable, but I guess I'll chalk that up to my ignorance of the VBA programming language.

Many thanks!

Regards,
Jeff
 
Upvote 0
Drop the worksheet function, and just use application.

Change
outputVal = Application.WorksheetFunction.Average(inputWorkbook.Worksheets(fileName).Columns("D:D"))
to
outputVal = Application.Average(inputWorkbook.Worksheets(fileName).Columns("D:D"))


Then test the variable for Error (not isempty)
Code:
If IsError(outputVal) Then outputVal = 0
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top