Using cell ranges & arrays in VBA functions?

Hi,

I'm new to the forum and have searched all over for help with this problem but can't find a satisfactory answer.

I want to create a function that can take a range of cells as an argument, manipulate the data and then return an answer. So for instance, as a practise example I've been trying to create my own 'sum' function mySum that would take the following form in an example:

• Cells A1, A2, A3 would have numerical values
• Another cell, e.g. A4 would take the form =mySum(A1:A3)
• mySum formula sum the values in A1:A3

I'd like to do this by putting the contents of cells A1:A3 in an array and then summing the contents of that array, so I understand the mechanics of using arrays in this way within VBA.

Can anyone help!?

Thanks!

JD

Thanks, that makes sense but what I'm looking to do is put the range into a array and then manipulate the array. My example was just to demonstrate what I mean. Eg I want to use the 3rd value in the array that is constructed from a selected range of sells.

So =myFunction(A1:A8) would take range A1:A8 and put it in an array and then return the value of the 3rd element of that array.

JD

Thanks patel45, but am looking to do this using a function that takes the cell range as an argument. So e.g in the cell that uses the function (and returns the calculated answer) it would take the form =myFunction(A1:A3)

Nope pls see my follow up comments

Thanks Norie, your example makes sense. However, when I replicate it / modify it it stops working and gives me a VALUE error.

For instance, as a test I modified your original code to:

Please Login or Register  to view this content.

which returns the first element in the array as expected.

However, when I use the exact same code / logic for a differently named function (with differently named variables) I get VALUE errors and it appears the array I'm attempting to create is not actually an array.

Can you / anyone pls assist? This is driving me mad as I can't see where Im going wrong!

Thanks

JD

Weird.

Here's an example:

Please Login or Register  to view this content.

So the first function works, but the second function gives a VALUE error.

Exact same range in both instances: =mySum(B4:B6) and =test(B4:B6)

Legend! Thank you so much, knew it had to be something silly like that.

Appreciate your help and patience

sorry, got another issue...

I've started building the function I originally intended to use arrays with, but am getting VALUE errors again.

I wish to define an array 'retArr' which will hold the values from the range I pass to the function. I then wish to square each value in the array and put those squared values in an array called 'sqRet'.

However, when I test the function at the end to check it is squaring the values, I get a VALUE error...

Please Login or Register  to view this content.

Adding the parentheses doesnt seem to make any difference (still get the VALUE error)...

Ah, that'll do it. Thank you!