Error Handling for Excel User Defined Function

Aaron Greene
Aaron Greene used Ask the Experts™
on
I have written a function that retrieves a value from a process historian through an Excel add in.  I have tested the function against known parameters and retrieved the expected result.  Now, I need to put an error handling process in place.  

This is my function:

Public Function returnsNorthChannelFlowTTL(Optional dtReportDate As Date = 0) As Double
Dim wb As Workbook
Dim wsData As Worksheet
Dim wsParameters As Worksheet
Dim aahQueryFormulaString As String
Dim aahQueryStartDate As Date
Dim aahQueryEndDate As Date
Dim aahDateCurrent As Date
Dim NChannelFlowPD As Double
Dim valueRng01 As Variant
Dim valueRng02 As Variant
Dim dblValue01 As Double
Dim dblValue02 As Double
Dim diffValue As Double

If dtReportDate = 0 Then
dtReportDate = Date - 1
End If

aahQueryStartDate = dtReportDate
aahQueryEndDate = DateAdd("d", 1, aahQueryStartDate)

aahQueryFormulaString = "wwWideHistory3(" & Chr(34) & "SCADA01" & Chr(34) & ", Tags!$B$17," & Chr(34)
aahQueryFormulaString = aahQueryFormulaString & "Res86400000" & Chr(34) & "," & Chr(34) & aahQueryStartDate & Chr(34)
aahQueryFormulaString = aahQueryFormulaString & "," & Chr(34) & aahQueryEndDate & Chr(34)
aahQueryFormulaString = aahQueryFormulaString & ",254,0,0,0,2,3,0," & Chr(34) & Chr(34) & ",3," & Chr(34) & Chr(34)
aahQueryFormulaString = aahQueryFormulaString & ",-1,0," & Chr(34) & " ORDER BY DateTime ASC" & Chr(34)
aahQueryFormulaString = aahQueryFormulaString & "," & Chr(34) & "NoFilter" & Chr(34) & ",16384)"
aahQueryFormulaString = "=" & aahQueryFormulaString
Debug.Print aahQueryFormulaString
Debug.Print ""
Set wb = ThisWorkbook
Set wsData = wb.Sheets("Data")
Set wsParameters = wb.Sheets("Parameters")

wsData.Range("A1:h15").Clear
wsData.Range("$A$10").Formula = aahQueryFormulaString
wsData.Range("$A$10").Select
Call activefactoryworkbook.mnuRefreshSelection
Application.Wait (Now + TimeValue("00:00:02"))
valueRng01 = wsData.Range("$B$10").Value
valueRng02 = wsData.Range("$B$11").Value
Call activefactoryworkbook.mnuConvert
wsData.Range("A1:h15").Clear
Set wsData = Nothing
Set wsParameters = Nothing
Set wb = Nothing

dblValue01 = CDbl(valueRng01)
dblValue02 = CDbl(valueRng02)
diffValue = Abs(dblValue01 - dblValue02)

returnsNorthChannelFlowTTL = diffValue
End Function

If valueRng01 and/or valueRng02 return either a string or nothing at all, the calculation fails.

valueRng01 = wsData.Range("$B$10").Value
valueRng02 = wsData.Range("$B$11").Value


What I would like to happen is for the function to return a 0 in case of error, but I am not sure how to get that done.  I have been attempting something like:

On Error GoTo ErrorResult

ErrorResult
returnsNorthChannelFlowTTL = 0
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
This will return -1 if there's an error. There's also a a Msgbox describing the error that you can uncomment.
Public Function returnsNorthChannelFlowTTL(Optional dtReportDate As Date = 0) As Double
Dim wb As Workbook
Dim wsData As Worksheet
Dim wsParameters As Worksheet
Dim aahQueryFormulaString As String
Dim aahQueryStartDate As Date
Dim aahQueryEndDate As Date
Dim aahDateCurrent As Date
Dim NChannelFlowPD As Double
Dim valueRng01 As Variant
Dim valueRng02 As Variant
Dim dblValue01 As Double
Dim dblValue02 As Double
Dim diffValue As Double

   On Error GoTo returnsNorthChannelFlowTTL_Error

If dtReportDate = 0 Then
dtReportDate = Date - 1
End If

aahQueryStartDate = dtReportDate
aahQueryEndDate = DateAdd("d", 1, aahQueryStartDate)

aahQueryFormulaString = "wwWideHistory3(" & Chr(34) & "SCADA01" & Chr(34) & ", Tags!$B$17," & Chr(34)
aahQueryFormulaString = aahQueryFormulaString & "Res86400000" & Chr(34) & "," & Chr(34) & aahQueryStartDate & Chr(34)
aahQueryFormulaString = aahQueryFormulaString & "," & Chr(34) & aahQueryEndDate & Chr(34)
aahQueryFormulaString = aahQueryFormulaString & ",254,0,0,0,2,3,0," & Chr(34) & Chr(34) & ",3," & Chr(34) & Chr(34)
aahQueryFormulaString = aahQueryFormulaString & ",-1,0," & Chr(34) & " ORDER BY DateTime ASC" & Chr(34)
aahQueryFormulaString = aahQueryFormulaString & "," & Chr(34) & "NoFilter" & Chr(34) & ",16384)"
aahQueryFormulaString = "=" & aahQueryFormulaString
Debug.Print aahQueryFormulaString
Debug.Print ""
Set wb = ThisWorkbook
Set wsData = wb.Sheets("Data")
Set wsParameters = wb.Sheets("Parameters")

wsData.Range("A1:h15").Clear
wsData.Range("$A$10").Formula = aahQueryFormulaString
wsData.Range("$A$10").Select
Call activefactoryworkbook.mnuRefreshSelection
Application.Wait (Now + TimeValue("00:00:02"))
valueRng01 = wsData.Range("$B$10").Value
valueRng02 = wsData.Range("$B$11").Value
Call activefactoryworkbook.mnuConvert
wsData.Range("A1:h15").Clear
Set wsData = Nothing
Set wsParameters = Nothing
Set wb = Nothing

dblValue01 = CDbl(valueRng01)
dblValue02 = CDbl(valueRng02)
diffValue = Abs(dblValue01 - dblValue02)

returnsNorthChannelFlowTTL = diffValue

On Error GoTo 0
Exit Function

returnsNorthChannelFlowTTL_Error:

    'MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure returnsNorthChannelFlowTTL of Module Module1"
    returnsNorthChannelFlowTTL_Error = -1
End Function

Open in new window

byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Try it like shown below. I added four statements, each marked by five asterisks in the Comment field.
Public Function returnsNorthChannelFlowTTL(Optional dtReportDate As Date = 0) As Double
Dim wb As Workbook
Dim wsData As Worksheet
Dim wsParameters As Worksheet
Dim aahQueryFormulaString As String
Dim aahQueryStartDate As Date
Dim aahQueryEndDate As Date
Dim aahDateCurrent As Date
Dim NChannelFlowPD As Double
Dim valueRng01 As Variant
Dim valueRng02 As Variant
Dim dblValue01 As Double
Dim dblValue02 As Double
Dim diffValue As Double

On Error GoTo errhandler    '***** Brad added

If dtReportDate = 0 Then
dtReportDate = Date - 1
End If

aahQueryStartDate = dtReportDate
aahQueryEndDate = DateAdd("d", 1, aahQueryStartDate)

aahQueryFormulaString = "wwWideHistory3(" & Chr(34) & "SCADA01" & Chr(34) & ", Tags!$B$17," & Chr(34)
aahQueryFormulaString = aahQueryFormulaString & "Res86400000" & Chr(34) & "," & Chr(34) & aahQueryStartDate & Chr(34)
aahQueryFormulaString = aahQueryFormulaString & "," & Chr(34) & aahQueryEndDate & Chr(34)
aahQueryFormulaString = aahQueryFormulaString & ",254,0,0,0,2,3,0," & Chr(34) & Chr(34) & ",3," & Chr(34) & Chr(34)
aahQueryFormulaString = aahQueryFormulaString & ",-1,0," & Chr(34) & " ORDER BY DateTime ASC" & Chr(34)
aahQueryFormulaString = aahQueryFormulaString & "," & Chr(34) & "NoFilter" & Chr(34) & ",16384)"
aahQueryFormulaString = "=" & aahQueryFormulaString
Debug.Print aahQueryFormulaString
Debug.Print ""
Set wb = ThisWorkbook
Set wsData = wb.Sheets("Data")
Set wsParameters = wb.Sheets("Parameters")

wsData.Range("A1:h15").Clear
wsData.Range("$A$10").Formula = aahQueryFormulaString
wsData.Range("$A$10").Select
Call activefactoryworkbook.mnuRefreshSelection
Application.Wait (Now + TimeValue("00:00:02"))
valueRng01 = wsData.Range("$B$10").Value
valueRng02 = wsData.Range("$B$11").Value
Call activefactoryworkbook.mnuConvert
wsData.Range("A1:h15").Clear
Set wsData = Nothing
Set wsParameters = Nothing
Set wb = Nothing

dblValue01 = CDbl(valueRng01)
dblValue02 = CDbl(valueRng02)
diffValue = Abs(dblValue01 - dblValue02)

returnsNorthChannelFlowTTL = diffValue
Exit Function                           '***** Brad added

errhandler:                             '***** Brad added
    returnsNorthChannelFlowTTL = 0      '***** Brad added
End Function

Open in new window

Aaron GreeneProgrammer

Author

Commented:
Thank you very much for your help.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2018
              Experts Exchange Top Expert VBA 2018
              Experts Exchange Distinguished Expert in Excel 2018

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial