# Function

#### Rafael Alvares Tubero

##### New Member
Hello everybody!

I would like to know why my function is not working. Can anybody help me, please?
Code:
``````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

Last edited by a moderator:

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Perhaps

Rich (BB code):
``````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``````

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

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.
Code:
``````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``````

Last edited by a moderator:
How are you calling the function? Have you tried inserting a breakpoint and stepping through it?

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:

 4 10 3 =interpolin(E3;B3:B6;C3:C6) 6 50 4 8 70 5 10 100 6 7 8 9 10 11

<TBODY>
</TBODY><COLGROUP><COL span=4><COL></COLGROUP>

You are passing Ranges not Variant arrays.

You are passing Ranges not Variant arrays.

So, how should I declare those ranges??

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

Thank you so much!

Replies
5
Views
232
Replies
3
Views
44
Replies
0
Views
158
Replies
4
Views
107
Replies
7
Views
113

1,203,174
Messages
6,053,925
Members
444,694
Latest member
JacquiDaly

### 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.

### Which adblocker are you using?

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

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