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