VBA - function doesn't calculate unless clicked on
 

Hi all

I have written a function in vba (I am pretty new to vba). It takes two arrays and finds the gradient. You also have two inputs to remove points from the beginning or end. It works exactly how I want it to except when I changed from the dependant cell (cut min and max) being on the same sheet to being on a new sheet and then changed the contents of these cells the function returns a #value! error.

when i click on the function within the worksheet (in the formula bar) and press enter the function suddenly works fine again.

Is there an autocalculate property which needs to be enabled or something?

Thanks

Danny

code below:

Public Function myGradient(lngMinRow As Long, InputColumn As String, lngCutRows As Long)


'declare variables
Dim xArray, yArray, lngMaxRow As Long

'define variables
lngMaxRow = 79 - lngCutRows

ReDim xArray(1, lngMaxRow - lngMinRow)
xArray = Range(InputColumn & lngMinRow, InputColumn & lngMaxRow)

ReDim yArray(1, lngMaxRow - lngMinRow)
yArray = Range("A" & 89, "A" & 89 + lngMaxRow - lngMinRow)


'gradient calculation
myGradient = Application.WorksheetFunction.Slope(xArray, yArray)


End Function

Hi this might help put you on the right track