You can actually pick up all the values in a single area range and plop them
into an array in one line:

dim myArr as variant
dim LastRow as long
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
myarr = .range("a1:X" & lastrow).value
end with

I used column A to find the last row and went from A1:X(Lastrow).

You'll end up with a two dimensional array (rows by columns)--even if you pickup
a single column.

A1:A10 would be placed into a array 10 rows by 1 column.

But you could loop through each cell in the range.

dim iRow as long
dim iCol as long
dim LastRow as long
dim myrng as range
dim myCell as range
dim myRow as range
dim myArr() as variant

with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
set myrng = .range("a1:X" & lastrow)
end with

redim myArr(myrng.rows.count, myrng.columns.count)
for irow = 1 to myrng.rows.count
for icol = 1 to myrng.columns.count
myarr(irow,icol) = myrng(irow,icol).value
next icol
next irow





Tom Kreutz wrote:

Dear Dave,

I'm attempting to load the values of a range (whose length is not
pre-set in the VB procedure) into a Double array within the procedure,
and then manipulate it. I was struggling to guess the syntax that would
allow me to step through the range, cell by cell, so that I could stuff
the values into the VB array. Your example was VERY helpful! (But I
must admit that the logic behind "for each mycell in rng.cells" is
straining my peewee noggin!)

Many thanks for your help!

Tom

Dave Peterson wrote:
I'm not sure what you're doing, but you could loop through each cell in the
range that was passed, too:

Option Explicit
Function mySum(rng as range) as double
dim myCell as range
dim myTotal as double

mytotal = 0

for each mycell in rng.cells
if isnumeric(mycell.value) then
mytotal = mytotal + mycell.value
end if
next mycell

mySum = myTotal

end function

(with not much error checking)



Tom Kreutz wrote:
Dear Folks,

In Visual Basic, how does one determine the size of an array (range?)
being passed in from Excel? For example, if I wanted to write my own
version of SUM(), how would I know the size/length of the range that the
user has chosen?

Many thanks,

Tom Kreutz


--

Dave Peterson