Hi.

How can I access a range of cells or a particular cell from within a
VBA macro? Can I pass a worksheet range in as a parameter to the
macro? If I can, is there a way I can access a cell offset from an
item in the range, i.e., do something like the OFFSET worksheet
function from within a VBA macro?

Thanks!

Ken

Hi Ken
many questions :-)
0. You may consider buying a good VBA book :-)
1. Access a range - Some examples:
sub foo ()
dim rng as range
dim cell as range
set rng = selection 'set rng to the current range selection of the
active sheet

set rng = Range("A1:A100")
set rng = range(cells(1,1),cells(5,5))
for each cell in rng
msgbox cell.value
next
end sub

2. Pass a worksheet range - call this function in your worksheet with
=FUNC_FOO(A1:A10)
Public function func_foo(rng as range)
dim cell as range
dim ret_value
for each cell in rng
ret_value = ret_value + cell.value 'a simple sum function
end if
func_foo = ret_value
end function

3. Offset function - Range.offset(row_index,col_index)

dim rng as range
set rng = range("B1")
rng.offset(1,1).value = "test" 'one cell up, one cell to the right





--
Regards
Frank Kabel
Frankfurt, Germany

Ken wrote:
Hi.

How can I access a range of cells or a particular cell from within a
VBA macro? Can I pass a worksheet range in as a parameter to the
macro? If I can, is there a way I can access a cell offset from an
item in the range, i.e., do something like the OFFSET worksheet
function from within a VBA macro?

Thanks!

Ken

Thanks! That's exactly what I was looking for.

Are there any VBA books that you'd recommend? My only hesitation with
that is they tend to be very expensive, quickly antiquated, and my
need for using VBA is infrequent. Still, if there is a good reference
you'd recommend, perhaps I can find a used copy out there.

Thanks again!

- Ken

"Frank Kabel" wrote in message ...
Hi Ken
many questions :-)
0. You may consider buying a good VBA book :-)
1. Access a range - Some examples:
sub foo ()
dim rng as range
dim cell as range
set rng = selection 'set rng to the current range selection of the
active sheet

set rng = Range("A1:A100")
set rng = range(cells(1,1),cells(5,5))
for each cell in rng
msgbox cell.value
next
end sub

2. Pass a worksheet range - call this function in your worksheet with
=FUNC_FOO(A1:A10)
Public function func_foo(rng as range)
dim cell as range
dim ret_value
for each cell in rng
ret_value = ret_value + cell.value 'a simple sum function
end if
func_foo = ret_value
end function

3. Offset function - Range.offset(row_index,col_index)

dim rng as range
set rng = range("B1")
rng.offset(1,1).value = "test" 'one cell up, one cell to the right





--
Regards
Frank Kabel
Frankfurt, Germany

Ken wrote:
Hi.

How can I access a range of cells or a particular cell from within a
VBA macro? Can I pass a worksheet range in as a parameter to the
macro? If I can, is there a way I can access a cell offset from an
item in the range, i.e., do something like the OFFSET worksheet
function from within a VBA macro?

Thanks!

Ken