Problems with arrays in function

I'm a fairly new user of VBA an I'm trying to write a simple function to do CAPM (finance). This is the first time I'm using arrays in VBA and I'm having a few problems although I've gotten the function to work. This is the working code:

Please Login or Register  to view this content.

But I don't like using datatype "Variant" and since all my values are numbers with a few decimals I can use "Double" with stock and index. But when I change this and get the following code it won't work!

Please Login or Register  to view this content.

I think the problem might be that theres not only numbers in my array. The input I give as index and stock is ONLY numbers but it seems for some reason that the cell above my inputs also comes into "index" and "stock" and that's the word "Open". To clarify some I give B2:B250 as index and it seems to also use B1. If I say "For i=0 to n" then the first value is "Open" and I thought that 0 was the first value of the array and 1 the second?

But I thought that I would check if this really is the problem so I insert instead a debug print to see but then it won't run and I get the feeling I'm fairly stuck.

Please Login or Register  to view this content.

Hopefully I made some sense in explaining the problem, I would appreciate some help!

Here's the excel file I'm doing it in.
CAPM.xlsm

Hi Shek

It's normal, Index and Stock can't be Double only Variant because you send "range" to your Function

Please Login or Register  to view this content.

Double required a value, not a range

Originally Posted by BrianM45

Hi Shek

It's normal, Index and Stock can't be Double only Variant because you send "range" to your Function

Please Login or Register  to view this content.

Double required a value, not a range

Originally Posted by Norie

What is the problem?

No output? Code errors? Incorrect output?

I don't think changing from Variant to Double would change anything about the array.

By the way, a range that has been created from a range is 1-indexed not 0-indexed.

For example, this

Please Login or Register  to view this content.

will result in arr having the dimensions 1 to 10, 1 to 1.

PS Using LBound and Ubound can help when working with arrays.

Ok, so I should use Variant when taking a range from excel and using it as an array but if I create the array inside the function it's ok to use double or something else? And weird that it would go outside the range I gave it as input to find i=0 even if it's incorrect of me to use i=0 with a range.

Still confused about the debug.print though. Why won't it print my almost 250 values of index in the immediate window? The error i get is just "VALUE!" in the cell where I'm implementing my function.

Thanks for the help so far but still not entirely happy!

Originally Posted by Norie

#VALUE! being returned from a UDF usually means there's an error in the function's code.

Try putting a breakpoint on the first line of the code with F9, then enter a formula using the function in a cell.

VBA should then allow you to step through the code to see what's happening.

Unfortunately if there is an error you won't get an error message, the code will just stop.

Actually I've just tried that and I don't even get to debug which kind of indicates the problem is the function header where you've used Double instead of Variant.

When I change the first 2 arguments back to variant the code works fine, it also works fine when I use Range and adjust the code a little.

Please Login or Register  to view this content.

PS You can't print out entire arrays with Debug.Print.

Yes I tried debugging it with the breakpoint also. But I had the same problem because of using Double.
Ok, that PS was helpful. Those little things that I want to know so I won't have to tear the hair out of my skull . Didn't google enough for that I guess!