Difficulties with For... Next Loop

pficky

New Member
Joined
Jun 2, 2016
Messages
10
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.
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Are xvalues and yvalues ranges that are passed to the function?
 

pficky

New Member
Joined
Jun 2, 2016
Messages
10
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Jun 2, 2016
Messages
10

ADVERTISEMENT

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

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.

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

New Member
Joined
Jun 2, 2016
Messages
10
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,878
Messages
5,598,623
Members
414,248
Latest member
Tirali

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top