Excel VBA, getting error 13 when calling a func within a func

Viewed 49

I've written a short function that calls another function LinInterpF(), linear interpolation within two arrays, which I found online. The LinInterpF() works great when used in a Sheet cell but when called in VBA pops error 13. The arrays fed into LinInterpF() are all numbers XX.XX. After some testing the issue seems to be that while X1,X2,Y1, and Y2 are calculated correctly, and when planted in to a Sheet cell presented as numbers (each is a single number), when used in VBA X1,X2,Y1 and Y2 aren't numbers and so the calculation of LinInterpF can't be performed. If I plant each one in to a Sheet cell and then read it into a variable the function works, but that seems like a backwards way of doing it.

Help please

Thanks

Main function

Option Explicit

Private Sub CommandButton1_Click()
' SET INITIAL CONDITIONS
Dim time As Double          ' declare a variable called "time" of type Double
time = Range("k5").Value    ' set the "time" variable to the value set by the user
Dim timeStep As Double
timeStep = Range("k4").Value
Dim temp As Double
Dim i As Integer

Range("r4").Value = 0   ' step time
Range("s4").Value = 0   ' moment
Range("t4").Value = 0   ' acceleration
Range("u4").Value = 0   ' speed in rad/sec
Range("v4").Value = 0   ' speed in RPM
Range("w4").Value = 0   ' angle traveled
Range("x4").Value = 0   ' pump efficiency
Range("y4").Value = 0   ' Ch
Range("z4").Value = 0   ' Cq
Range("aa4").Value = 0  ' pump flowrate
Range("ab4").Value = 0  ' turbine flowrate
Range("ac4").Value = Range("g4").Value ' current head

i = 1

While i < time / timeStep + 1

' 1. Next time step
Range("r" & (4 + i)).Value = timeStep * i

' 2. Pump efficiency from table
temp = Range("AA" & (4 + i - 1)).Value * 3600

Range("f15").Value = LinInterpF(temp, Range("A3:A13").Value, Range("C3:C13").Value)


i = i + 1

Wend

End Sub

and secondary function... I added the Range(f16) ~ Range("f20") lines for debug only. The correct values are put into these cells, but the LinIterpF isn't calculated.

Function LinInterpF(x, xvalues, yvalues)

x1 = Application.WorksheetFunction.Index(xvalues, Application.WorksheetFunction.Match(x, xvalues, 1))
x2 = Application.WorksheetFunction.Index(xvalues, Application.WorksheetFunction.Match(x, xvalues, 1) + 1)

y1 = Application.WorksheetFunction.Index(yvalues, Application.WorksheetFunction.Match(x, xvalues, 1))
y2 = Application.WorksheetFunction.Index(yvalues, Application.WorksheetFunction.Match(x, xvalues, 1) + 1)

Range("f20").Value = x
Range("F16").Value = x1
Range("f17").Value = x2
Range("f18").Value = y1
Range("f19").Value = y2

LinInterpF = y1 + (y2 - y1) * (x - x1) / (x2 - x1)

End Function
1 Answers

You are calling the function as:

LinInterpF(temp, Range("A3:A13").Value, Range("C3:C13").Value)

The function line is:

Function LinInterpF(x As Double, xvalues As Double, yvalues As Double) As Double

Range("A3:A13").Value => Variant array
xvalues As Double is looking for a single variable (of type Double)

Related