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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
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.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,348
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.
 

SliderJeff

Board Regular
Joined
Oct 5, 2016
Messages
63
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
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

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:

Watch MrExcel Video

Forum statistics

Threads
1,114,528
Messages
5,548,570
Members
410,851
Latest member
glowe2020
Top