Value Error

[strokebow]

I get a #value error for my UDF which I have agonised over for hours/days. The code is:

Function deltam(X As Variant, Y As Variant) As Double
'..of a single peak.
    Dim YTenth As Double, XTenth1 As Double, XTenth2 As Double, I As Long, Temp As Double
    If X.Count <> Y.Count Then
        deltam = "## Unequal Range Sizes##"
        Exit Function
    End If
    YTenth = WorksheetFunction.Max(Y) / 10    'peak thenth of height
    I = 0
    Do
        I = I + 1                             'seeking the pair of points
        Temp = Y(I)
        If Y(I) >= YTenth Then                  'on the increasing branch
            XTenth1 = X(I) + ((YTenth - Y(I)) * (X(I + 1) - X(I))) / (Y(I + 1) - Y(I))
            Exit Do
        End If
    Loop
 
    Do
        I = I + 1                             'seeking the pair of points
        Temp = Y(I)
        If Y(I) <= YTenth Then                  'on the decreasing branch
            XTenth2 = X(I) + ((YTenth - Y(I + 1)) * (X(I + 1) - X(I))) / (Y(I) - Y(I + 1))
            Exit Do
        End If
    Loop
    deltam = XTenth2 - XTenth1
End Function

The X data is:

98
98.02
98.04
98.06
98.08
98.1
98.12
98.14
98.16
98.18
98.2
98.22
98.24
98.26
98.28
98.3
98.32
98.34
98.36
98.38
98.4
98.42
98.44
98.46
98.48
98.5
98.52
98.54
98.56
98.58
98.6
98.62
98.64
98.66
98.68
98.7
98.72
98.74
98.76
98.78
98.8
98.82
98.84
98.86
98.88
98.9
98.92
98.94
98.96
98.98
99
99.02
99.04
99.06
99.08
99.1
99.12
99.14
99.16
99.18
99.2
99.22
99.24
99.26
99.28
99.3
99.32
99.34
99.36
99.38
99.4
99.42
99.44
99.46
99.48
99.5
99.52
99.54
99.56
99.58
99.6
99.62
99.64
99.66
99.68
99.7
99.72
99.74
99.76
99.78
99.8
99.82
99.84
99.86
99.88
99.9
99.92
99.94
99.96
99.98
100
100.02
100.04
100.06
100.08
100.1
100.12
100.14
100.16
100.18
100.2
100.22
100.24
100.26
100.28
100.3

... and the Y data selected is:
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
2
4
5
11
22
30
40
49
58
74
89
89
90
72
64
38
18
3
3
0
0
1
0
0
1

If anyone can help me understand why I keep getting this error I will be very grateful!!!


Thanks

[Whizbang]

One thing I see is that you declare your function as a Double, and yet this line returns a string:

deltam = "## Unequal Range Sizes##"

I don't have a lot of experience with UDF's, but this seems potentially wrong.

Additionally, between your two loops, you do not reset I, so this may lead to troubles.

Also, this line:

Temp = Y(I)

Doesn't appear to do anything, since Temp is not used anywhere.


I re-wrote your UDF to suit my own style:

Function deltam(X As Variant, Y As Variant) As Variant
'..of a single peak.
    Dim YTenth As Double, XTenth1 As Double, XTenth2 As Double, I As Long, Temp As Double
    If X.Count <> Y.Count Then
        deltam = "## Unequal Range Sizes##"
    Else
        YTenth = WorksheetFunction.Max(Y) / 10    'peak thenth of height
        I = 1
        Do Until Y(I) >= YTenth
            'seeking the pair of points
            I = I + 1       'on the increasing branch
        Loop
        
        XTenth1 = X(I) + ((YTenth - Y(I)) * (X(I + 1) - X(I))) / (Y(I + 1) - Y(I))
        
        I = 1
        
        Do Until Y(I) <= YTenth
            'seeking the pair of points
            I = I + 1           'on the decreasing branch
        Loop
        
        XTenth2 = X(I) + ((YTenth - Y(I + 1)) * (X(I + 1) - X(I))) / (Y(I) - Y(I + 1))
        
        deltam = XTenth2 - XTenth1
        
    End If
End Function

[strokebow]

Thank you tlafferty. Appreciate your help.

I'll look in to that now. I wonder why it wouldn't complete the last part. Maybe because their is an issue with XTenth2?

[tlafferty]

The issue seems to occur before the last step of code since it never seems to arrive. I've placed some stops in your code at critical junctures so you can watch it as it progresses. You will note that it never gets to the last stop:

Function deltam(X As Variant, Y As Variant) As Double
'..of a single peak.
    Dim YTenth As Double, XTenth1 As Double, XTenth2 As Double, I As Long, Temp As Double
    If X.Count <> Y.Count Then
        deltam = "## Unequal Range Sizes##"
        Exit Function
    End If
    YTenth = WorksheetFunction.Max(Y) / 10 'peak thenth of height
    I = 0
    Stop
    Do
        I = I + 1 'seeking the pair of points
        Temp = Y(I)
        If Y(I) >= YTenth Then 'on the increasing branch
            XTenth1 = X(I) + ((YTenth - Y(I)) * (X(I + 1) - X(I))) / (Y(I + 1) - Y(I))
            Exit Do
        End If
    Loop
Stop
    Do
        I = I + 1 'seeking the pair of points
        Temp = Y(I)
Stop
        If Y(I) <= YTenth Then 'on the decreasing branch
            XTenth2 = X(I) + ((YTenth - Y(I + 1)) * (X(I + 1) - X(I))) / (Y(I) - Y(I + 1))
            Exit Do
        End If
    Loop
Stop
    deltam = XTenth2 - XTenth1
Stop     'it never gets here
End Function

Hope this helps in your debug effort.

[strokebow]

Thanks Whizbang for your suggestions.

About 'I'...
Ideally I should be set to be the correct index for when Y is a maximum.

Because...

The program looks at a generally bell shaped curve and find the points the X values that correspond to 10% of the y maximum.
1. So initially Y max is found. Then we find 10% of Y max.
2. Then we start to move along the x axis and examine the y value. When the Y value exceeds 10% of the max then we interpolate to find the corresponding X value where this occurs.
3. Then (what my code was doing) was continuing on from the current index point and looking for the next y value that is less than 10% of y max. Then it interpolates to find the corresponding x value.
It would be best if after 2 has finished we could move the index to the corresponding point where Y max is and continue to iterate from their to complete step 3.

What do you think? Any ideas?

[Whizbang]

First, it appears that posts were made while I was editing my code. So, please re-read my posted code to ensure you see my edited version.

I am not quite following you. Please give my code a try (with and without the second I = 1), and let me know your response.

[strokebow]

Thanks Whizbag I understand your corrections however 'I' must not be reset back to 1

[strokebow]

I've run in the debug mode and I see the problem.

As it iterates through in the second Do Loop, when I = I + 1 is > 108 it cuts out and goes out of context. I cant understand why??

Since there are 115 entries in each column. Any ideas???

[Marcol]

You have multiple scenarios where a #DIV/0! error occurs and you have nothing in place to handle it

e.g.

        If Y(I) <= YTenth Then                  'on the decreasing branch
            ' Handle #DIV/0! error when (Y(I) - Y(I + 1))=0
            XTenth2 = X(I) + ((YTenth - Y(I + 1)) * (X(I + 1) - X(I))) / (Y(I) - Y(I + 1))
            Exit Do
        End If

[strokebow]

Thanks Marcol. That is another problem with the code although it is not causing the problem here.

Thanks

[Whizbang]

To account for the fact that there may never be an instance where Y(I) <= YTenth, you could do this:

Function deltam(X As Variant, Y As Variant) As Variant
'..of a single peak.
    Dim YTenth As Double
    Dim XTenth1 As Double
    Dim XTenth2 As Double
    Dim Temp As Double
    Dim I As Long
    
    If X.Count <> Y.Count Then
        deltam = "## Unequal Range Sizes##"
    Else
        YTenth = WorksheetFunction.Max(Y) / 10    'peak thenth of height
        I = 1
        Do Until Y(I) >= YTenth                         'on the increasing branch
            I = I + 1
        Loop
        
        XTenth1 = X(I) + ((YTenth - Y(I)) * (X(I + 1) - X(I))) / (Y(I + 1) - Y(I))
        
        Do Until Y(I) <= YTenth Or I > UBound(Y)        'on the decreasing branch
            I = I + 1
        Loop
        
        XTenth2 = X(I) + ((YTenth - Y(I + 1)) * (X(I + 1) - X(I))) / (Y(I) - Y(I + 1))
        
        deltam = XTenth2 - XTenth1
        
    End If
End Function

[Marcol]

All unhandled errors will cause your UDF to fail and return #VALUE!

Seems others couldn't get past the lines I posted, so it would seem to be a good idea to at least start there.

[strokebow]

'I' is at 108. At 'I' = 117 one would expect the IF statement to become true and then XTenth2 to be set.

But upon the next iteration after 108, everything goes out of context.

Please see attached.

[Whizbang]

Here is another version. It includes a check to make sure there will not be a Div/0 error, but it doesn't stop or otherwise handle the issue. It just skips past and moves on to the next Y()

Function deltam(X As Variant, Y As Variant) As Variant
'..of a single peak.
    Dim YTenth As Double
    Dim XTenth1 As Double
    Dim XTenth2 As Double
    Dim Temp As Double
    Dim I As Long
    
    If X.Count <> Y.Count Then
        deltam = "## Unequal Range Sizes##"
    Else
        YTenth = WorksheetFunction.Max(Y) / 10    'peak thenth of height
        For I = 1 To UBound(Y)
            If Y(I) >= YTenth And XTenth1 = 0 And (Y(I + 1) - Y(I)) <> 0 Then 
                'on the increasing branch
                XTenth1 = X(I) + ((YTenth - Y(I)) * (X(I + 1) - X(I))) / (Y(I + 1) - Y(I))
            End If
            If Y(I) <= YTenth And XTenth1 <> 0 And XTenth2 = 0 And (Y(I) - Y(I + 1)) <> 0 Then 
                'on the decreasing branch
                XTenth2 = X(I) + ((YTenth - Y(I + 1)) * (X(I + 1) - X(I))) / (Y(I) - Y(I + 1))
            End If
        Next I
        deltam = XTenth2 - XTenth1
        
    End If
End Function

[Whizbang]

Edited my last post to fix an error.

[strokebow]

Edited my last post to fix an error.

Thanks Whizbang I really appreciate that. I like your style. However, I am reluctant to use one for loop for both operations in case there are any discrepancies in the peak and it has some fluctuations.

I still can't understand why I = I + 1 would set itself back to 1 or go out of context. That doesn't make any sense to me?

[Whizbang]

It is actually erroring out when I=109. As Marcol has said, there is no error handling in your original code. When I = 109, then "(Y(I) - Y(I + 1))" =0, which causes a Div/0 error and your function ceases and then returns the #Value error.

Here is a simple error handler added to your original code:

Function deltam(X As Variant, Y As Variant) As Variant
'..of a single peak.
    Dim YTenth As Double, XTenth1 As Double, XTenth2 As Double, I As Long, Temp As Double
    
    On Error GoTo ErrHandler
    If X.Count <> Y.Count Then
        deltam = "## Unequal Range Sizes##"
        Exit Function
    End If
    YTenth = WorksheetFunction.Max(Y) / 10    'peak thenth of height
    I = 0
    Do
        I = I + 1                             'seeking the pair of points
        Temp = Y(I)
        If Y(I) >= YTenth Then                  'on the increasing branch
            XTenth1 = X(I) + ((YTenth - Y(I)) * (X(I + 1) - X(I))) / (Y(I + 1) - Y(I))
            Exit Do
        End If
    Loop
 
    Do
        I = I + 1                             'seeking the pair of points
        Temp = Y(I)
        If Y(I) <= YTenth Then                  'on the decreasing branch
            XTenth2 = X(I) + ((YTenth - Y(I + 1)) * (X(I + 1) - X(I))) / (Y(I) - Y(I + 1))
            Exit Do
        End If
    Loop
    deltam = XTenth2 - XTenth1
Exit Function

ErrHandler:
    MsgBox ("X.Count = " & X.Count & Chr(10) & _
            "Y.Count = " & Y.Count & Chr(10) & _
            "YTenth = " & YTenth & Chr(10) & _
            "XTenth1 = " & XTenth1 & Chr(10) & _
            "XTenth2 = " & XTenth2 & Chr(10) & _
            "I = " & I & Chr(10) & _
            "Y(I) = " & Y(I) & Chr(10) & _
            "Y(I + 1) = " & Y(I + 1))
    deltam = "#Error"

End Function

[strokebow]

Hi peeps,

Thanks so much for your suggestions.

My apologies I left this for a while. So many things came up at work and home and I couldn't concentrate on this any longer but I have more time now :-)

So I've implemented the code as follows:

Function deltam(X As Variant, Y As Variant) As Variant
'..of a single peak.
    Dim YTenth As Double, XTenth1 As Double, XTenth2 As Double, I As Long
    
    On Error GoTo ErrHandler
    
    If X.Count <> Y.Count Then
        deltam = "## Unequal Range Sizes##"
        Exit Function
    End If
    
    YTenth = WorksheetFunction.Max(Y) / 10    'peak thenth of height
    
    I = 0
    Do
        I = I + 1                             'seeking the pair of points
        If Y(I) >= YTenth Then                  'on the increasing branch
            XTenth1 = X(I) + ((YTenth - Y(I)) * (X(I + 1) - X(I))) / (Y(I + 1) - Y(I))
            Exit Do
        End If
    Loop
 
    Do
        I = I + 1                             'seeking the pair of points
        If Y(I) <= YTenth Then                  'on the decreasing branch
            XTenth2 = X(I) + ((YTenth - Y(I + 1)) * (X(I + 1) - X(I))) / (Y(I) - Y(I + 1))
            Exit Do
        End If
    Loop
    deltam = XTenth2 - XTenth1
Exit Function

ErrHandler:
   ' MsgBox ("X.Count = " & X.Count & Chr(10) & _
    '        "Y.Count = " & Y.Count & Chr(10) & _
     '       "YTenth = " & YTenth & Chr(10) & _
      '      "XTenth1 = " & XTenth1 & Chr(10) & _
       '     "XTenth2 = " & XTenth2 & Chr(10) & _
        '    "I = " & I & Chr(10) & _
         '   "Y(I) = " & Y(I) & Chr(10) & _
          '  "Y(I + 1) = " & Y(I + 1))
    deltam = "#Error"

End Function

Now I am getting an error for certain data. For example:

=deltam(L386:L449,M386:M449)

The data in question is:

L M
9.99 0
9.9905 0
9.991 0
9.9915 0
9.992 0
9.9925 0
9.993 0
9.9935 0
9.994 0
9.9945 1
9.995 1
9.9955 2
9.996 4
9.9965 4
9.997 4
9.9975 6
9.998 6
9.9985 6
9.999 7
9.9995 7
10 7
10.0005 8
10.001 8
10.0015 8
10.002 10
10.0025 10
10.003 10
10.0035 11
10.004 13
10.0045 13
10.005 14
10.0055 15
10.006 15
10.0065 18
10.007 19
10.0075 19
10.008 20
10.0085 23
10.009 24
10.0095 25
10.01 26
10.0105 26
10.011 31
10.0115 32
10.012 34
10.0125 36
10.013 36
10.0135 37
10.014 38
10.0145 38
10.015 39
10.0155 39
10.016 40
10.0165 43
10.017 43
10.0175 41
10.018 45
10.0185 44
10.019 44
10.0195 40
10.02 32
10.0205 4
10.021 0
10.0215 0

What should happen?
This function finds deltam. Please see attached for illustration.

Clearly, the function is complaining of an error somewhere. If anyone can please help me find out where this is, why this is and how I can overcome this to get my desired response I will be very grateful indeed?

Thanks for your time. Please help and ask questions if I have not been clear.

[snb]

What strikes me as inexplicable is that you persist in not posting a sample workbook....

[strokebow]

What strikes me as inexplicable is that you persist in not posting a sample workbook....

My apologies snb.

I've attached a sample workbook.

As a side issues. I keep getting a circular reference message...

[snb]

You can't assign an 'error message' to a 'double'.
Dividing by zero is a problem.
In your function you want to divide by the next value in column B minus the curernt value. Most of the time that result is 1; dividing by 1 seems to me to be rather redundant.
I'd change the function into:

Function deltam(X As Range, Y As Range)
 If X.Count <> Y.Count Then Exit Function
    
 sx = X
 sy = Y
 YTenth = WorksheetFunction.Max(Y) / 10

 For j = 1 To UBound(sy)
  If IsEmpty(XTenth1) Then
   If sy(j, 1) >= YTenth Then XTenth1 = sx(j, 1) + ((YTenth - sy(j, 1)) * (sx(j + 1, 1) - sx(j, 1)))
  ElseIf sy(j, 1) <= YTenth Then
   XTenth2 = sx(j, 1) + ((YTenth - sy(j, 1)) * (sx(j + 1, 1) - sx(j, 1)))
   Exit For
  End If
 Next

 If j <= UBound(sy) Then deltam = XTenth2 - XTenth1
End Function