The range may be of arbitrary size, but why can't you determine what it is?
Can you base it on the current selection? The activecell's current region? The
usedrange of that worksheet?
Maybe you can give some more info.
Tom Kreutz wrote:
>
> Dear Dave,
>
> Thanks again for your very helpful comments.
>
> The code in your latter note seems to be tied to a range whose
> position on the sheet is known or fixed. I'm interested in a routine
> that, like SUM(), will process an arbitrary range. The first step in
> the procedure is to extract the values in the (one dimensional) input
> range and stuff them into a VB array. To that end, your first email
> seems more relevant.
>
> Thanks again,
>
> Tom
>
> Dave Peterson wrote:
> > 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
|
Similar Threads
