reading in a user-specified range (excel)

[AndrewWolford]

Hi everyone,

I've written a small function in excel that reads in a range into a dynamic array but I can't figure out how to make the range "user specified" and can't figure out how to call the function from a cell in excel.

Here's my code so far:

Sub testfunction()

Dim myCell As Range
Dim testarray() As Double
Dim i As Integer
i = 1
For Each myCell In Range("B1:B9&quot
ReDim Preserve testarray(i)
testarray(i) = myCell.Value
i = i + 1
Next myCell

End Sub

I need the range("B1:B9&quot to be a range passed by the user instead of a static range. Also, how can I then call this functin from a cell. I thought I could go " =testfunction(B1:B100)" but that doesnt work. Thank you for any help!

-Andrew

 

[ETID]

I cant' figure out what it is your trying to return with your function,...

but here is an example of a function that will return the average squared of a range...

paste this code into a new module......

Function square_avg(in_range As Range) As Double
square_avg = Application.WorksheetFunction.Average(in_range) ^ 2
End Function

example of use in a sheet:

=square_avg(a1:a25)

 

[FaneDuru]

I did not understant very well what you wont the function to return. I wrote a piece of code, which I hope to be relevant.

Before that some basic rules:
To be a function its name has to be "Function x" not "Sub x";
The parameteras which to be passed to the function have to be between the brackets;
The value to be returned has to be in a variable having the same name with the function;
The function code has to be in a module belongging to the Excel file.

The code:
Function testfunction(Rng As Range)
Dim myCell As Range, testarray() As Double, i As Integer, z As Long, x
i = 1: z = 0
For Each myCell In Rng
ReDim Preserve testarray(i)
testarray(i) = myCell.Value
i = i + 1
Next myCell
For Each x In testarray
z = z + x
Next
testfunction = z
End Function

In the Excel sheet the formula will be "=testfunction(A2:E5)" and it will return the sum of array elements.

I hope this helps,
Fane Duru'

 

[AndrewWolford]

Thanks for your help, after playing around with the code I got it to work. For simplicity I only posted part of what the function does which is probably why it wasn't clear what I was doing. Anyway, thanks for the help!