How to pass address(es) as parameters
 

I have a subroutine (below) that works by itself. I'd like to modify it to
receive address passed as parameters. How do I do that?

Private Sub FormatEntry()
Dim myRange As Range

Set myRange = Worksheets(5).Range("B30:G31")
With myRange
.Interior.ColorIndex = 20
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
End With

End Sub

--
Thanks!

Pat

How to pass address(es) as parameters
 

Sub Main
Dim myRng As Range
Set myRng = Worksheets(5).Range("B30:G31")
FormatEntry myRng
End Sub



Sub FormatEntry(myRange as Range)
With myRange
.Interior.ColorIndex = 20
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
End With
End Sub

--
Regards,
Tom Ogilvy


"Pat" wrote in message
...
I have a subroutine (below) that works by itself. I'd like to modify it to
receive address passed as parameters. How do I do that?

Private Sub FormatEntry()
Dim myRange As Range

Set myRange = Worksheets(5).Range("B30:G31")
With myRange
.Interior.ColorIndex = 20
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
End With

End Sub

--
Thanks!

Pat

How to pass address(es) as parameters
 

Maybe...

Private Sub FormatEntry(myRange as Range)

With myRange
.Interior.ColorIndex = 20
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
End With

End Sub

You're passing a real range--not the address.

sub testme02()
dim testrng as range

call FormatEntry(worksheets(5).range("B30:g31"))

'or
set testrng = worksheets(5).range("B30:g31")

call formatentry(testrng)

end sub

Pat wrote:

I have a subroutine (below) that works by itself. I'd like to modify it to
receive address passed as parameters. How do I do that?

Private Sub FormatEntry()
Dim myRange As Range

Set myRange = Worksheets(5).Range("B30:G31")
With myRange
.Interior.ColorIndex = 20
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
End With

End Sub

--
Thanks!

Pat

--

Dave Peterson

How to pass address(es) as parameters
 

Thanks, mucho! I don't think I could have found the answer going through all
the books and Help I looked through. I appreciate your help.
--
Pat


"Tom Ogilvy" wrote:

Sub Main
Dim myRng As Range
Set myRng = Worksheets(5).Range("B30:G31")
FormatEntry myRng
End Sub



Sub FormatEntry(myRange as Range)
With myRange
.Interior.ColorIndex = 20
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
End With
End Sub

--
Regards,
Tom Ogilvy


"Pat" wrote in message
...
I have a subroutine (below) that works by itself. I'd like to modify it to
receive address passed as parameters. How do I do that?

Private Sub FormatEntry()
Dim myRange As Range

Set myRange = Worksheets(5).Range("B30:G31")
With myRange
.Interior.ColorIndex = 20
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
End With

End Sub

--
Thanks!

Pat

How to pass address(es) as parameters
 

Thanks for your help! I appreciate it.
--
Pat


"Dave Peterson" wrote:

Maybe...

Private Sub FormatEntry(myRange as Range)

With myRange
.Interior.ColorIndex = 20
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
End With

End Sub

You're passing a real range--not the address.

sub testme02()
dim testrng as range

call FormatEntry(worksheets(5).range("B30:g31"))

'or
set testrng = worksheets(5).range("B30:g31")

call formatentry(testrng)

end sub

Pat wrote:

I have a subroutine (below) that works by itself. I'd like to modify it to
receive address passed as parameters. How do I do that?

Private Sub FormatEntry()
Dim myRange As Range

Set myRange = Worksheets(5).Range("B30:G31")
With myRange
.Interior.ColorIndex = 20
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
End With

End Sub

--
Thanks!

Pat

--

Dave Peterson

How to pass address(es) as parameters
 

I have a follow-up question in this same general area. Instead of passing a
Range as a single parameter, I'd like to pass the two Addresses that make up
the Range as two separate parameters. I can't get a clean compile for the
second of the following subroutines. Can I do what I'm asking for?

Private Sub CallFormatClaim2()
Dim top, bot As Range
Worksheets(5).Activate
Set top = Range("B5")
Set bot = Range("G6")
Call FormatClaim2(top, bot)
End Sub

Private Sub FormatClaim2(top As Range, bot As Range)
Dim myrange As Range
Set myrange = (top:bot) ' << THIS IS NOT WORKING
With myrange
.Interior.ColorIndex = 20
.BorderAround Weight:=xlThin
End With
End Sub
--
Pat


"Tom Ogilvy" wrote:

Sub Main
Dim myRng As Range
Set myRng = Worksheets(5).Range("B30:G31")
FormatEntry myRng
End Sub



Sub FormatEntry(myRange as Range)
With myRange
.Interior.ColorIndex = 20
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
End With
End Sub

--
Regards,
Tom Ogilvy


"Pat" wrote in message
...
I have a subroutine (below) that works by itself. I'd like to modify it to
receive address passed as parameters. How do I do that?

Private Sub FormatEntry()
Dim myRange As Range

Set myRange = Worksheets(5).Range("B30:G31")
With myRange
.Interior.ColorIndex = 20
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
End With

End Sub

--
Thanks!

Pat

How to pass address(es) as parameters
 

If you want to pass addresses:

Option Explicit
Private Sub CallFormatClaim2()
Dim top as Range
dim bot As Range
Worksheets(5).Activate
Set top = Range("B5")
Set bot = Range("G6")
Call FormatClaim2(top.Address, bot.Address)
'same as
'call formatclaim2("B5","G6")
End Sub
Private Sub FormatClaim2(topAddr As String, botAddr As String)
Dim myrange As Range
Set myrange = ActiveSheet.Range(topAddr, botAddr)
With myrange
.Interior.ColorIndex = 20
.BorderAround Weight:=xlThin
End With
End Sub

If you wanted to pass the range objects:

Option Explicit
Private Sub CallFormatClaim2()
Dim top As Range
dim bot As Range
Worksheets(5).Activate
Set top = Range("B5")
Set bot = Range("G6")
Call FormatClaim2(top, bot)
End Sub
Private Sub FormatClaim2(top As Range, bot As Range)
Dim myrange As Range
Set myrange = top.Parent.Range(top, bot)
With myrange
.Interior.ColorIndex = 20
.BorderAround Weight:=xlThin
End With
End Sub

And you'll want to be a little careful.

This line:
Dim top, bot As Range
declares bot as a range object, but top as a variant.

You could use:
dim top as range, bot as range

or (my preference)
dim top as range
dim bot as range

But you do have to pass the type of parameter that the called routine is looking
for--in this case a range.




Pat wrote:

I have a follow-up question in this same general area. Instead of passing a
Range as a single parameter, I'd like to pass the two Addresses that make up
the Range as two separate parameters. I can't get a clean compile for the
second of the following subroutines. Can I do what I'm asking for?

Private Sub CallFormatClaim2()
Dim top, bot As Range
Worksheets(5).Activate
Set top = Range("B5")
Set bot = Range("G6")
Call FormatClaim2(top, bot)
End Sub

Private Sub FormatClaim2(top As Range, bot As Range)
Dim myrange As Range
Set myrange = (top:bot) ' << THIS IS NOT WORKING
With myrange
.Interior.ColorIndex = 20
.BorderAround Weight:=xlThin
End With
End Sub
--
Pat

"Tom Ogilvy" wrote:

Sub Main
Dim myRng As Range
Set myRng = Worksheets(5).Range("B30:G31")
FormatEntry myRng
End Sub



Sub FormatEntry(myRange as Range)
With myRange
.Interior.ColorIndex = 20
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
End With
End Sub

--
Regards,
Tom Ogilvy


"Pat" wrote in message
...
I have a subroutine (below) that works by itself. I'd like to modify it to
receive address passed as parameters. How do I do that?

Private Sub FormatEntry()
Dim myRange As Range

Set myRange = Worksheets(5).Range("B30:G31")
With myRange
.Interior.ColorIndex = 20
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
End With

End Sub

--
Thanks!

Pat




--

Dave Peterson

How to pass address(es) as parameters
 

Thanks, Dave. I tried your first suggestion and it worked like a charm. I'm
sure the others will work too. You've taught me a lot. Thanks!
--
Pat


"Dave Peterson" wrote:

If you want to pass addresses:

Option Explicit
Private Sub CallFormatClaim2()
Dim top as Range
dim bot As Range
Worksheets(5).Activate
Set top = Range("B5")
Set bot = Range("G6")
Call FormatClaim2(top.Address, bot.Address)
'same as
'call formatclaim2("B5","G6")
End Sub
Private Sub FormatClaim2(topAddr As String, botAddr As String)
Dim myrange As Range
Set myrange = ActiveSheet.Range(topAddr, botAddr)
With myrange
.Interior.ColorIndex = 20
.BorderAround Weight:=xlThin
End With
End Sub

If you wanted to pass the range objects:

Option Explicit
Private Sub CallFormatClaim2()
Dim top As Range
dim bot As Range
Worksheets(5).Activate
Set top = Range("B5")
Set bot = Range("G6")
Call FormatClaim2(top, bot)
End Sub
Private Sub FormatClaim2(top As Range, bot As Range)
Dim myrange As Range
Set myrange = top.Parent.Range(top, bot)
With myrange
.Interior.ColorIndex = 20
.BorderAround Weight:=xlThin
End With
End Sub

And you'll want to be a little careful.

This line:
Dim top, bot As Range
declares bot as a range object, but top as a variant.

You could use:
dim top as range, bot as range

or (my preference)
dim top as range
dim bot as range

But you do have to pass the type of parameter that the called routine is looking
for--in this case a range.




Pat wrote:

I have a follow-up question in this same general area. Instead of passing a
Range as a single parameter, I'd like to pass the two Addresses that make up
the Range as two separate parameters. I can't get a clean compile for the
second of the following subroutines. Can I do what I'm asking for?

Private Sub CallFormatClaim2()
Dim top, bot As Range
Worksheets(5).Activate
Set top = Range("B5")
Set bot = Range("G6")
Call FormatClaim2(top, bot)
End Sub

Private Sub FormatClaim2(top As Range, bot As Range)
Dim myrange As Range
Set myrange = (top:bot) ' << THIS IS NOT WORKING
With myrange
.Interior.ColorIndex = 20
.BorderAround Weight:=xlThin
End With
End Sub
--
Pat

"Tom Ogilvy" wrote:

Sub Main
Dim myRng As Range
Set myRng = Worksheets(5).Range("B30:G31")
FormatEntry myRng
End Sub



Sub FormatEntry(myRange as Range)
With myRange
.Interior.ColorIndex = 20
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
End With
End Sub

--
Regards,
Tom Ogilvy


"Pat" wrote in message
...
I have a subroutine (below) that works by itself. I'd like to modify it to
receive address passed as parameters. How do I do that?

Private Sub FormatEntry()
Dim myRange As Range

Set myRange = Worksheets(5).Range("B30:G31")
With myRange
.Interior.ColorIndex = 20
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
End With

End Sub

--
Thanks!

Pat




--

Dave Peterson

How to pass address(es) as parameters
 

One more question (maybe): once I'm in the called subroutine (say, using the
first method you suggested), how can I accomplish adjusting the addresses in
the called subroutine?

For example, after I've successfully passed parameters for addresses "B5"
and "G6", now referenced as topAddr and botAddr in the called subroutine, how
can I move around within that range in the called subroutine-- e.g. change
topAddr (or some other variable) to "G6"?
--
Pat


"Dave Peterson" wrote:

If you want to pass addresses:

Option Explicit
Private Sub CallFormatClaim2()
Dim top as Range
dim bot As Range
Worksheets(5).Activate
Set top = Range("B5")
Set bot = Range("G6")
Call FormatClaim2(top.Address, bot.Address)
'same as
'call formatclaim2("B5","G6")
End Sub
Private Sub FormatClaim2(topAddr As String, botAddr As String)
Dim myrange As Range
Set myrange = ActiveSheet.Range(topAddr, botAddr)
With myrange
.Interior.ColorIndex = 20
.BorderAround Weight:=xlThin
End With
End Sub

If you wanted to pass the range objects:

Option Explicit
Private Sub CallFormatClaim2()
Dim top As Range
dim bot As Range
Worksheets(5).Activate
Set top = Range("B5")
Set bot = Range("G6")
Call FormatClaim2(top, bot)
End Sub
Private Sub FormatClaim2(top As Range, bot As Range)
Dim myrange As Range
Set myrange = top.Parent.Range(top, bot)
With myrange
.Interior.ColorIndex = 20
.BorderAround Weight:=xlThin
End With
End Sub

And you'll want to be a little careful.

This line:
Dim top, bot As Range
declares bot as a range object, but top as a variant.

You could use:
dim top as range, bot as range

or (my preference)
dim top as range
dim bot as range

But you do have to pass the type of parameter that the called routine is looking
for--in this case a range.




Pat wrote:

I have a follow-up question in this same general area. Instead of passing a
Range as a single parameter, I'd like to pass the two Addresses that make up
the Range as two separate parameters. I can't get a clean compile for the
second of the following subroutines. Can I do what I'm asking for?

Private Sub CallFormatClaim2()
Dim top, bot As Range
Worksheets(5).Activate
Set top = Range("B5")
Set bot = Range("G6")
Call FormatClaim2(top, bot)
End Sub

Private Sub FormatClaim2(top As Range, bot As Range)
Dim myrange As Range
Set myrange = (top:bot) ' << THIS IS NOT WORKING
With myrange
.Interior.ColorIndex = 20
.BorderAround Weight:=xlThin
End With
End Sub
--
Pat

"Tom Ogilvy" wrote:

Sub Main
Dim myRng As Range
Set myRng = Worksheets(5).Range("B30:G31")
FormatEntry myRng
End Sub



Sub FormatEntry(myRange as Range)
With myRange
.Interior.ColorIndex = 20
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
End With
End Sub

--
Regards,
Tom Ogilvy


"Pat" wrote in message
...
I have a subroutine (below) that works by itself. I'd like to modify it to
receive address passed as parameters. How do I do that?

Private Sub FormatEntry()
Dim myRange As Range

Set myRange = Worksheets(5).Range("B30:G31")
With myRange
.Interior.ColorIndex = 20
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
End With

End Sub

--
Thanks!

Pat




--

Dave Peterson

How to pass address(es) as parameters
 

I'm not sure what you mean about changing that parm, but you can loop through
the range like:

Private Sub FormatClaim2(topAddr As String, botAddr As String)
Dim myrange As Range
dim myCell as range

Set myrange = ActiveSheet.Range(topAddr, botAddr)

for each mycell in myrange.cells
with mycell
.Interior.ColorIndex = 20
.BorderAround Weight:=xlThin
End With
next mycell

End Sub


Pat wrote:

One more question (maybe): once I'm in the called subroutine (say, using the
first method you suggested), how can I accomplish adjusting the addresses in
the called subroutine?

For example, after I've successfully passed parameters for addresses "B5"
and "G6", now referenced as topAddr and botAddr in the called subroutine, how
can I move around within that range in the called subroutine-- e.g. change
topAddr (or some other variable) to "G6"?
--
Pat

"Dave Peterson" wrote:

If you want to pass addresses:

Option Explicit
Private Sub CallFormatClaim2()
Dim top as Range
dim bot As Range
Worksheets(5).Activate
Set top = Range("B5")
Set bot = Range("G6")
Call FormatClaim2(top.Address, bot.Address)
'same as
'call formatclaim2("B5","G6")
End Sub
Private Sub FormatClaim2(topAddr As String, botAddr As String)
Dim myrange As Range
Set myrange = ActiveSheet.Range(topAddr, botAddr)
With myrange
.Interior.ColorIndex = 20
.BorderAround Weight:=xlThin
End With
End Sub

If you wanted to pass the range objects:

Option Explicit
Private Sub CallFormatClaim2()
Dim top As Range
dim bot As Range
Worksheets(5).Activate
Set top = Range("B5")
Set bot = Range("G6")
Call FormatClaim2(top, bot)
End Sub
Private Sub FormatClaim2(top As Range, bot As Range)
Dim myrange As Range
Set myrange = top.Parent.Range(top, bot)
With myrange
.Interior.ColorIndex = 20
.BorderAround Weight:=xlThin
End With
End Sub

And you'll want to be a little careful.

This line:
Dim top, bot As Range
declares bot as a range object, but top as a variant.

You could use:
dim top as range, bot as range

or (my preference)
dim top as range
dim bot as range

But you do have to pass the type of parameter that the called routine is looking
for--in this case a range.




Pat wrote:

I have a follow-up question in this same general area. Instead of passing a
Range as a single parameter, I'd like to pass the two Addresses that make up
the Range as two separate parameters. I can't get a clean compile for the
second of the following subroutines. Can I do what I'm asking for?

Private Sub CallFormatClaim2()
Dim top, bot As Range
Worksheets(5).Activate
Set top = Range("B5")
Set bot = Range("G6")
Call FormatClaim2(top, bot)
End Sub

Private Sub FormatClaim2(top As Range, bot As Range)
Dim myrange As Range
Set myrange = (top:bot) ' << THIS IS NOT WORKING
With myrange
.Interior.ColorIndex = 20
.BorderAround Weight:=xlThin
End With
End Sub
--
Pat

"Tom Ogilvy" wrote:

Sub Main
Dim myRng As Range
Set myRng = Worksheets(5).Range("B30:G31")
FormatEntry myRng
End Sub



Sub FormatEntry(myRange as Range)
With myRange
.Interior.ColorIndex = 20
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
End With
End Sub

--
Regards,
Tom Ogilvy


"Pat" wrote in message
...
I have a subroutine (below) that works by itself. I'd like to modify it to
receive address passed as parameters. How do I do that?

Private Sub FormatEntry()
Dim myRange As Range

Set myRange = Worksheets(5).Range("B30:G31")
With myRange
.Interior.ColorIndex = 20
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
End With

End Sub

--
Thanks!

Pat




--

Dave Peterson


--

Dave Peterson

How to pass address(es) as parameters
 

Let me rephrase my question. With the help of your earlier suggestion, I now
can pass an address range to a called subroutine and refer to those addresses
as variables (topAddr and botAddr in my example). Next, while still in the
called subroutine, I want to work with another range that is offset from the
first in some predetermined manner.

I know how to do this when I'm using address constants, for example:

' first
Set myrange = ActiveSheet.Range("A1:E10")
With myrange
. . .
End With

' then
Set myrange = ActiveSheet.Range("C1:C10")
With myrange
. . .
End With

How do I do this working with address variables?

' first
Set myrange = ActiveSheet.Range(topAddr,botAddr)
With myrange
. . .
End With

' then
Set myrange = ActiveSheet.Range(????,????)
With myrange
. . .
End With

Thanks again for your excellent responsiveness and assistance!
--
Pat


"Dave Peterson" wrote:

I'm not sure what you mean about changing that parm, but you can loop through
the range like:

Private Sub FormatClaim2(topAddr As String, botAddr As String)
Dim myrange As Range
dim myCell as range

Set myrange = ActiveSheet.Range(topAddr, botAddr)

for each mycell in myrange.cells
with mycell
.Interior.ColorIndex = 20
.BorderAround Weight:=xlThin
End With
next mycell

End Sub


Pat wrote:

One more question (maybe): once I'm in the called subroutine (say, using the
first method you suggested), how can I accomplish adjusting the addresses in
the called subroutine?

For example, after I've successfully passed parameters for addresses "B5"
and "G6", now referenced as topAddr and botAddr in the called subroutine, how
can I move around within that range in the called subroutine-- e.g. change
topAddr (or some other variable) to "G6"?
--
Pat

"Dave Peterson" wrote:

If you want to pass addresses:

Option Explicit
Private Sub CallFormatClaim2()
Dim top as Range
dim bot As Range
Worksheets(5).Activate
Set top = Range("B5")
Set bot = Range("G6")
Call FormatClaim2(top.Address, bot.Address)
'same as
'call formatclaim2("B5","G6")
End Sub
Private Sub FormatClaim2(topAddr As String, botAddr As String)
Dim myrange As Range
Set myrange = ActiveSheet.Range(topAddr, botAddr)
With myrange
.Interior.ColorIndex = 20
.BorderAround Weight:=xlThin
End With
End Sub

If you wanted to pass the range objects:

Option Explicit
Private Sub CallFormatClaim2()
Dim top As Range
dim bot As Range
Worksheets(5).Activate
Set top = Range("B5")
Set bot = Range("G6")
Call FormatClaim2(top, bot)
End Sub
Private Sub FormatClaim2(top As Range, bot As Range)
Dim myrange As Range
Set myrange = top.Parent.Range(top, bot)
With myrange
.Interior.ColorIndex = 20
.BorderAround Weight:=xlThin
End With
End Sub

And you'll want to be a little careful.

This line:
Dim top, bot As Range
declares bot as a range object, but top as a variant.

You could use:
dim top as range, bot as range

or (my preference)
dim top as range
dim bot as range

But you do have to pass the type of parameter that the called routine is looking
for--in this case a range.




Pat wrote:

I have a follow-up question in this same general area. Instead of passing a
Range as a single parameter, I'd like to pass the two Addresses that make up
the Range as two separate parameters. I can't get a clean compile for the
second of the following subroutines. Can I do what I'm asking for?

Private Sub CallFormatClaim2()
Dim top, bot As Range
Worksheets(5).Activate
Set top = Range("B5")
Set bot = Range("G6")
Call FormatClaim2(top, bot)
End Sub

Private Sub FormatClaim2(top As Range, bot As Range)
Dim myrange As Range
Set myrange = (top:bot) ' << THIS IS NOT WORKING
With myrange
.Interior.ColorIndex = 20
.BorderAround Weight:=xlThin
End With
End Sub
--
Pat

"Tom Ogilvy" wrote:

Sub Main
Dim myRng As Range
Set myRng = Worksheets(5).Range("B30:G31")
FormatEntry myRng
End Sub



Sub FormatEntry(myRange as Range)
With myRange
.Interior.ColorIndex = 20
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
End With
End Sub

--
Regards,
Tom Ogilvy


"Pat" wrote in message
...
I have a subroutine (below) that works by itself. I'd like to modify it to
receive address passed as parameters. How do I do that?

Private Sub FormatEntry()
Dim myRange As Range

Set myRange = Worksheets(5).Range("B30:G31")
With myRange
.Interior.ColorIndex = 20
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
End With

End Sub

--
Thanks!

Pat




--

Dave Peterson


--

Dave Peterson

How to pass address(es) as parameters
 

Once you've got the range, you can use that with your offset (and resize???).

' first
Set myrange = ActiveSheet.Range(topAddr,botAddr)
With myrange
. . .
End With

' then
Set myrange = ActiveSheet.Range(topaddr,botaddr).offset(5,17)
'or
Set myrange _
= ActiveSheet.Range(topaddr,botaddr).offset(5,17).re size(12,15)

With myrange
. . .
End With

The offset will keep the same number of rows/columns, but you can use .resize()
to change them to what you want (well, if you want).



Pat wrote:

Let me rephrase my question. With the help of your earlier suggestion, I now
can pass an address range to a called subroutine and refer to those addresses
as variables (topAddr and botAddr in my example). Next, while still in the
called subroutine, I want to work with another range that is offset from the
first in some predetermined manner.

I know how to do this when I'm using address constants, for example:

' first
Set myrange = ActiveSheet.Range("A1:E10")
With myrange
. . .
End With

' then
Set myrange = ActiveSheet.Range("C1:C10")
With myrange
. . .
End With

How do I do this working with address variables?

' first
Set myrange = ActiveSheet.Range(topAddr,botAddr)
With myrange
. . .
End With

' then
Set myrange = ActiveSheet.Range(????,????)
With myrange
. . .
End With

Thanks again for your excellent responsiveness and assistance!
--
Pat

"Dave Peterson" wrote:

I'm not sure what you mean about changing that parm, but you can loop through
the range like:

Private Sub FormatClaim2(topAddr As String, botAddr As String)
Dim myrange As Range
dim myCell as range

Set myrange = ActiveSheet.Range(topAddr, botAddr)

for each mycell in myrange.cells
with mycell
.Interior.ColorIndex = 20
.BorderAround Weight:=xlThin
End With
next mycell

End Sub


Pat wrote:

One more question (maybe): once I'm in the called subroutine (say, using the
first method you suggested), how can I accomplish adjusting the addresses in
the called subroutine?

For example, after I've successfully passed parameters for addresses "B5"
and "G6", now referenced as topAddr and botAddr in the called subroutine, how
can I move around within that range in the called subroutine-- e.g. change
topAddr (or some other variable) to "G6"?
--
Pat

"Dave Peterson" wrote:

If you want to pass addresses:

Option Explicit
Private Sub CallFormatClaim2()
Dim top as Range
dim bot As Range
Worksheets(5).Activate
Set top = Range("B5")
Set bot = Range("G6")
Call FormatClaim2(top.Address, bot.Address)
'same as
'call formatclaim2("B5","G6")
End Sub
Private Sub FormatClaim2(topAddr As String, botAddr As String)
Dim myrange As Range
Set myrange = ActiveSheet.Range(topAddr, botAddr)
With myrange
.Interior.ColorIndex = 20
.BorderAround Weight:=xlThin
End With
End Sub

If you wanted to pass the range objects:

Option Explicit
Private Sub CallFormatClaim2()
Dim top As Range
dim bot As Range
Worksheets(5).Activate
Set top = Range("B5")
Set bot = Range("G6")
Call FormatClaim2(top, bot)
End Sub
Private Sub FormatClaim2(top As Range, bot As Range)
Dim myrange As Range
Set myrange = top.Parent.Range(top, bot)
With myrange
.Interior.ColorIndex = 20
.BorderAround Weight:=xlThin
End With
End Sub

And you'll want to be a little careful.

This line:
Dim top, bot As Range
declares bot as a range object, but top as a variant.

You could use:
dim top as range, bot as range

or (my preference)
dim top as range
dim bot as range

But you do have to pass the type of parameter that the called routine is looking
for--in this case a range.




Pat wrote:

I have a follow-up question in this same general area. Instead of passing a
Range as a single parameter, I'd like to pass the two Addresses that make up
the Range as two separate parameters. I can't get a clean compile for the
second of the following subroutines. Can I do what I'm asking for?

Private Sub CallFormatClaim2()
Dim top, bot As Range
Worksheets(5).Activate
Set top = Range("B5")
Set bot = Range("G6")
Call FormatClaim2(top, bot)
End Sub

Private Sub FormatClaim2(top As Range, bot As Range)
Dim myrange As Range
Set myrange = (top:bot) ' << THIS IS NOT WORKING
With myrange
.Interior.ColorIndex = 20
.BorderAround Weight:=xlThin
End With
End Sub
--
Pat

"Tom Ogilvy" wrote:

Sub Main
Dim myRng As Range
Set myRng = Worksheets(5).Range("B30:G31")
FormatEntry myRng
End Sub



Sub FormatEntry(myRange as Range)
With myRange
.Interior.ColorIndex = 20
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
End With
End Sub

--
Regards,
Tom Ogilvy


"Pat" wrote in message
...
I have a subroutine (below) that works by itself. I'd like to modify it to
receive address passed as parameters. How do I do that?

Private Sub FormatEntry()
Dim myRange As Range

Set myRange = Worksheets(5).Range("B30:G31")
With myRange
.Interior.ColorIndex = 20
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
End With

End Sub

--
Thanks!

Pat




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

How to pass address(es) as parameters
 

This sounds good. I'll give it a try Thanks!

--
Pat


"Dave Peterson" wrote:

Once you've got the range, you can use that with your offset (and resize???).

' first
Set myrange = ActiveSheet.Range(topAddr,botAddr)
With myrange
. . .
End With

' then
Set myrange = ActiveSheet.Range(topaddr,botaddr).offset(5,17)
'or
Set myrange _
= ActiveSheet.Range(topaddr,botaddr).offset(5,17).re size(12,15)

With myrange
. . .
End With

The offset will keep the same number of rows/columns, but you can use .resize()
to change them to what you want (well, if you want).



Pat wrote:

Let me rephrase my question. With the help of your earlier suggestion, I now
can pass an address range to a called subroutine and refer to those addresses
as variables (topAddr and botAddr in my example). Next, while still in the
called subroutine, I want to work with another range that is offset from the
first in some predetermined manner.

I know how to do this when I'm using address constants, for example:

' first
Set myrange = ActiveSheet.Range("A1:E10")
With myrange
. . .
End With

' then
Set myrange = ActiveSheet.Range("C1:C10")
With myrange
. . .
End With

How do I do this working with address variables?

' first
Set myrange = ActiveSheet.Range(topAddr,botAddr)
With myrange
. . .
End With

' then
Set myrange = ActiveSheet.Range(????,????)
With myrange
. . .
End With

Thanks again for your excellent responsiveness and assistance!
--
Pat

"Dave Peterson" wrote:

I'm not sure what you mean about changing that parm, but you can loop through
the range like:

Private Sub FormatClaim2(topAddr As String, botAddr As String)
Dim myrange As Range
dim myCell as range

Set myrange = ActiveSheet.Range(topAddr, botAddr)

for each mycell in myrange.cells
with mycell
.Interior.ColorIndex = 20
.BorderAround Weight:=xlThin
End With
next mycell

End Sub


Pat wrote:

One more question (maybe): once I'm in the called subroutine (say, using the
first method you suggested), how can I accomplish adjusting the addresses in
the called subroutine?

For example, after I've successfully passed parameters for addresses "B5"
and "G6", now referenced as topAddr and botAddr in the called subroutine, how
can I move around within that range in the called subroutine-- e.g. change
topAddr (or some other variable) to "G6"?
--
Pat

"Dave Peterson" wrote:

If you want to pass addresses:

Option Explicit
Private Sub CallFormatClaim2()
Dim top as Range
dim bot As Range
Worksheets(5).Activate
Set top = Range("B5")
Set bot = Range("G6")
Call FormatClaim2(top.Address, bot.Address)
'same as
'call formatclaim2("B5","G6")
End Sub
Private Sub FormatClaim2(topAddr As String, botAddr As String)
Dim myrange As Range
Set myrange = ActiveSheet.Range(topAddr, botAddr)
With myrange
.Interior.ColorIndex = 20
.BorderAround Weight:=xlThin
End With
End Sub

If you wanted to pass the range objects:

Option Explicit
Private Sub CallFormatClaim2()
Dim top As Range
dim bot As Range
Worksheets(5).Activate
Set top = Range("B5")
Set bot = Range("G6")
Call FormatClaim2(top, bot)
End Sub
Private Sub FormatClaim2(top As Range, bot As Range)
Dim myrange As Range
Set myrange = top.Parent.Range(top, bot)
With myrange
.Interior.ColorIndex = 20
.BorderAround Weight:=xlThin
End With
End Sub

And you'll want to be a little careful.

This line:
Dim top, bot As Range
declares bot as a range object, but top as a variant.

You could use:
dim top as range, bot as range

or (my preference)
dim top as range
dim bot as range

But you do have to pass the type of parameter that the called routine is looking
for--in this case a range.




Pat wrote:

I have a follow-up question in this same general area. Instead of passing a
Range as a single parameter, I'd like to pass the two Addresses that make up
the Range as two separate parameters. I can't get a clean compile for the
second of the following subroutines. Can I do what I'm asking for?

Private Sub CallFormatClaim2()
Dim top, bot As Range
Worksheets(5).Activate
Set top = Range("B5")
Set bot = Range("G6")
Call FormatClaim2(top, bot)
End Sub

Private Sub FormatClaim2(top As Range, bot As Range)
Dim myrange As Range
Set myrange = (top:bot) ' << THIS IS NOT WORKING
With myrange
.Interior.ColorIndex = 20
.BorderAround Weight:=xlThin
End With
End Sub
--
Pat

"Tom Ogilvy" wrote:

Sub Main
Dim myRng As Range
Set myRng = Worksheets(5).Range("B30:G31")
FormatEntry myRng
End Sub



Sub FormatEntry(myRange as Range)
With myRange
.Interior.ColorIndex = 20
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
End With
End Sub

--
Regards,
Tom Ogilvy


"Pat" wrote in message
...
I have a subroutine (below) that works by itself. I'd like to modify it to
receive address passed as parameters. How do I do that?

Private Sub FormatEntry()
Dim myRange As Range

Set myRange = Worksheets(5).Range("B30:G31")
With myRange
.Interior.ColorIndex = 20
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
End With

End Sub

--
Thanks!

Pat




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson