How to pass address(es) as parameters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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 said:
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
Click to expand...
 
Thanks for your help! I appreciate it.
--
Pat


Dave Peterson said:
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
Click to expand...
 
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 said:
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
Click to expand...
 
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.



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
Click to expand...
 
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!
 
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"?
 
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
 
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!
 
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).resize(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).


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!
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Do I need these lines? 5
Cell Border 1
Help with macro 1
Loops through Sheets but doesn't work. 5
Format cell and adjacent cell 3
For net borders 5
Formatting stop when reach blank cell 6
Trying to format a cell with a border 7

Back
Top