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

One way:

Public Function foo(ByRef bar As Excel.Range) As Variant
foo = "Rows: " & bar.Rows.Count & vbNewLine & _
"Columns: " & bar.Columns.Count
End Function


In article ,
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

Dear J.E.,

May your offspring and the all the successive generations that follow
be richly blessed!

(Oh yeah, and thanks a TON!)

Tom


JE McGimpsey wrote:
One way:

Public Function foo(ByRef bar As Excel.Range) As Variant
foo = "Rows: " & bar.Rows.Count & vbNewLine & _
"Columns: " & bar.Columns.Count
End Function


In article ,
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

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

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

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

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

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