Passing a range of data to a function - Excel - visual basic
 

I am trying to pass a range of values to a Function.
Every time I try to perform a mathematical function (add, subtract, etc.) on
one of these values from the range, the function returns #value error.

Basically, I'm trying to read a range of cells containing data into an array
and then, manipulate the array mathematically. How do I accomplish this task?

Thanks!

Passing a range of data to a function - Excel - visual basic
 

Dim MyValues As Variant

MyValues = Range("A1:C10").Value

....will return a two-dimension array of values you can manipulate i.e.
MyValues(Rows, Cols)

Note that if your range is only one row or one column you will still get a
two-dimension array


"dochoa" wrote:

I am trying to pass a range of values to a Function.
Every time I try to perform a mathematical function (add, subtract, etc.) on
one of these values from the range, the function returns #value error.

Basically, I'm trying to read a range of cells containing data into an array
and then, manipulate the array mathematically. How do I accomplish this task?

Thanks!

Passing a range of data to a function - Excel - visual basic
 

In the function, use .Value:

Function splice_it(r As Range) As Long
' just like sum
Dim rr As Range
splice_it = 0
For Each rr In r
splice_it = splice_it + rr.Value
Next
End Function


in the worksheet use it like
=splice_it(A1:D4)
--
Gary's Student


"dochoa" wrote:

I am trying to pass a range of values to a Function.
Every time I try to perform a mathematical function (add, subtract, etc.) on
one of these values from the range, the function returns #value error.

Basically, I'm trying to read a range of cells containing data into an array
and then, manipulate the array mathematically. How do I accomplish this task?

Thanks!