View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.programming
Dave Peterson Dave Peterson is offline
external usenet poster
 
Posts: 35,218
Default 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