Function

[Rafael Alvares Tubero]

Hello everybody!

I would like to know why my function is not working. Can anybody help me, please?

Function interpolin(dia As Double, ByVal rangedia, ByVal rangevalor) As Double

If UBound(rangedia) <> UBound(rangevalor) Then
    End Function
    
End If

If dia < rangedia(0) Then
    
    interpolin = (dia - rangedia(0)) * ((rangevalor(1) - rangevalor(0)) / (rangedia(1) - rangedia(0))) + rangevalor(0)
    End Function
    
End If
If dia > rangedia(UBound(rangedia)) Then
    interpolin = (dia - rangedia(UBound(rangedia) - 1)) _
    * ((rangevalor(UBound(rangevalor)) - rangevalor(UBound(rangevalor) - 1)) _
    / (rangedia(UBound(rangedia)) - rangedia(UBound(rangedia) - 1))) + rangevalor(UBound(rangevalor) - 1)
    End Function
End If
For i = 0 To UBound(rangedia)
    If dia >= rangedia(i) And dia <= rangedia(i + 1) Then
    
        interpolin = (dia - rangedia(i)) * ((rangevalor(i + 1) - rangevalor(i)) / (rangedia(i + 1) - rangedia(i))) + rangevalor(i)
        End Function
        
    End If
    
Next
End Function

Thanks

 

[VoG]

Perhaps

Function interpolin(dia As Double, ByVal rangedia, ByVal rangevalor) As Double

If UBound(rangedia) <> UBound(rangevalor) Then
Exit Function

End If

If dia < rangedia(0) Then

interpolin = (dia - rangedia(0)) * ((rangevalor(1) - rangevalor(0)) / (rangedia(1) - rangedia(0))) + rangevalor(0)
Exit Function

End If
If dia > rangedia(UBound(rangedia)) Then
interpolin = (dia - rangedia(UBound(rangedia) - 1)) _
* ((rangevalor(UBound(rangevalor)) - rangevalor(UBound(rangevalor) - 1)) _
/ (rangedia(UBound(rangedia)) - rangedia(UBound(rangedia) - 1))) + rangevalor(UBound(rangevalor) - 1)
Exit Function
End If
For i = 0 To UBound(rangedia)
If dia >= rangedia(i) And dia <= rangedia(i + 1) Then

interpolin = (dia - rangedia(i)) * ((rangevalor(i + 1) - rangevalor(i)) / (rangedia(i + 1) - rangedia(i))) + rangevalor(i)
Exit Function

End If

Next
End Function

 

[Rafael Alvares Tubero]

I have already changed. It doesn't work anyway.

 

[Rafael Alvares Tubero]

I have already changed. It doesn't work anyway.

I really need a hand here. When I try to run the function it is give #VALUE! as output. I have tried to change how I declared the variables and it still doesn't work. I am lost.

Function interpolin(dia As Double, rangedia() As Variant, rangevalor() As Variant) As Double

If UBound(rangedia) <> UBound(rangevalor) Then
    Exit Function
    
End If

If dia < rangedia(0) Then
    
    interpolin = (dia - rangedia(0)) * ((rangevalor(1) - rangevalor(0)) / (rangedia(1) - rangedia(0))) + rangevalor(0)
    Exit Function
    
End If
If dia > rangedia(UBound(rangedia)) Then
    interpolin = (dia - rangedia(UBound(rangedia) - 1)) _
    * ((rangevalor(UBound(rangevalor)) - rangevalor(UBound(rangevalor) - 1)) _
    / (rangedia(UBound(rangedia)) - rangedia(UBound(rangedia) - 1))) + rangevalor(UBound(rangevalor) - 1)
    Exit Function
End If
For i = 0 To UBound(rangedia)
    If dia >= rangedia(i) And dia <= rangedia(i + 1) Then
    
        interpolin = (dia - rangedia(i)) * ((rangevalor(i + 1) - rangevalor(i)) / (rangedia(i + 1) - rangedia(i))) + rangevalor(i)
        Exit Function
        
    End If
    
Next
End Function

 

[Andrew Poulsom]

How are you calling the function? Have you tried inserting a breakpoint and stepping through it?

 

[Rafael Alvares Tubero]

I have tried to put a breakpoint but it is not running through the code. It stops at the first line where I announce the function.

Also I am calling the function like this:

[TABLE="width: 728"]
<TBODY>[TR]
[TD]4</SPAN>[/TD]
[TD]10</SPAN>[/TD]
[TD][/TD]
[TD]3</SPAN>[/TD]
[TD]=interpolin(E3;B3:B6;C3:C6)</SPAN>[/TD]
[/TR]
[TR]
[TD]6</SPAN>[/TD]
[TD]50</SPAN>[/TD]
[TD][/TD]
[TD]4</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8</SPAN>[/TD]
[TD]70</SPAN>[/TD]
[TD][/TD]
[TD]5</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10</SPAN>[/TD]
[TD]100</SPAN>[/TD]
[TD][/TD]
[TD]6</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11</SPAN>[/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL span=4><COL></COLGROUP>[/TABLE]

 

[Andrew Poulsom]

You are passing Ranges not Variant arrays.

 

[Rafael Alvares Tubero]

You are passing Ranges not Variant arrays.

So, how should I declare those ranges??

 

[Andrew Poulsom]

As Range. But then you won't be able to use UBound. Use Rows.Count instead.

 

[Rafael Alvares Tubero]

Thank you so much!