Difficulties with For... Next Loop

[pficky]

I am trying to write UDF in VBA that will give me a the y-coordinates to confidence bands for a linear regression of data. I am new to programming with VBA but I have scoured the internet in order to try and figure out what I'm doing wrong, but I cannot figure it out. I basically can't get the loop to save each iteration as a value in an array that I can then output as the function answer. I can get it to output an array (Just spit back the range of x-values I put in), but if I try to save each iteration to a place in a new array it just gives me the #VALUE! error in each cell in my worksheet I've chosen for output. I am using Excel 2010

my code is here:

Function CINTERVAL(xvalues As Variant, yvalues As Variant, t)
'Pre-define variables
Dim n As Integer
Dim syx As Variant
Dim average As Variant
Dim ssx As Variant
Dim i As Integer
Dim x As Variant
'Perform Calculations
syx = WorksheetFunction.StEyx(yvalues, xvalues)
average = WorksheetFunction.average(xvalues)
ssx = WorksheetFunction.DevSq(xvalues)
n = WorksheetFunction.Count(xvalues)
x = xvalues.Value
'Store Values in an Array
For i = 1 To UBound(x)
CI.Value(i) = t * syx * (1 / n + (WorksheetFunction.Index(x, i) - average) ^ 2 / ssx ^ 2) ^ (1 / 2)
Next i
'Output Array
CINTERVAL = CI.Value
End Function

If anyone knows what I'm doing wrong i'd be soooo appreciative.

 

[JoeMo]

Are xvalues and yvalues ranges that are passed to the function?

 

[pficky]

Yes. They come from my worksheet. They are are properly sourced so the #VALUE! isn't coming from the wrong values being selected. I made sure by outputting my x values, then my y values, and then all of my calculations variables, the issues come from when I try to use the loop to write an array.

 

[JoeMo]

Yes. They come from my worksheet. They are are properly sourced so the #VALUE! isn't coming from the wrong values being selected. I made sure by outputting my x values, then my y values, and then all of my calculations variables, the issues come from when I try to use the loop to write an array.

Try changing this:
CI.Value(i) = .....
to this:
CI(i) = ....

and this:
CINTERVAL = CI.Value
to this:
CINTERVAL = CI

Value is a keyword in VBA and might be causing the problem.

 

[pficky]

I have done this already before but I tried again to double check. Without defining it before hand VBA thinks that I'm calling another function called CI that is dependent on a variable i, however its not a function. So, I then define it as a variant because I don't want to bind it as a specific type of data, but then I still end up with all the cells in my target array with a #VALUE! and no idea why. updated code looks like this

Function CINTERVAL(xvalues As Variant, yvalues As Variant, t)
'Pre-define variables
    Dim n As Integer
    Dim syx As Variant
    Dim average As Variant
    Dim ssx As Variant
    Dim x As Variant
    Dim CI As Variant
'Perform Calculations
    syx = WorksheetFunction.StEyx(yvalues, xvalues)
    average = WorksheetFunction.average(xvalues)
    ssx = WorksheetFunction.DevSq(xvalues)
    n = WorksheetFunction.Count(xvalues)
    x = xvalues.Value
'Store Values in an Array
    For i = 1 To UBound(x)
        CI(i) = t * syx * (1 / n + (WorksheetFunction.Index(x, i) - average) ^ 2 / ssx ^ 2) ^ (1 / 2)
    Next i
'Output Array
    CINTERVAL = CI
End Function

 

[JoeMo]

I have no idea if your data ranges and output range are vertical or horizontal or a mix so it's hard to diagnose. Here's a simple example where xvals and yvals are vertical ranges and the output array (Ar) is also vertical. Note that the array CI has to be redim'd and that it has to be transposed to force it to a vertical range.

Function Ar(xvals As Range, yvals As Range, z As Variant)
Dim CI() As Variant
x = xvals.Value
ReDim CI(1 To xvals.Count)
For i = 1 To UBound(x, 1)
    CI(i) = x(i, 1) * z
Next i
Ar = Application.Transpose(CI)

End Function

 

[pficky]

Ah that did the trick! I am calling vertical arrays and outputting a vertical array. I believe you hit the nail on the head and that my issue was that I had not ReDimmed the CI to match the length of my input array AND that I had not transposed it. It didn't even occur to me that it would be storing as a horizontal array. Thank you so much!

 

[JoeMo]

Ah that did the trick! I am calling vertical arrays and outputting a vertical array. I believe you hit the nail on the head and that my issue was that I had not ReDimmed the CI to match the length of my input array AND that I had not transposed it. It didn't even occur to me that it would be storing as a horizontal array. Thank you so much!

You are welcome - thanks for the reply.