Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
Pat Pat is offline
external usenet poster
 
Posts: 210
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.programming
Pat Pat is offline
external usenet poster
 
Posts: 210
Default 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




  #4   Report Post  
Posted to microsoft.public.excel.programming
Pat Pat is offline
external usenet poster
 
Posts: 210
Default 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




  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default 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


  #6   Report Post  
Posted to microsoft.public.excel.programming
Pat Pat is offline
external usenet poster
 
Posts: 210
Default 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

  #7   Report Post  
Posted to microsoft.public.excel.programming
Pat Pat is offline
external usenet poster
 
Posts: 210
Default 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

  #8   Report Post  
Posted to microsoft.public.excel.programming
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
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default 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
  #10   Report Post  
Posted to microsoft.public.excel.programming
Pat Pat is offline
external usenet poster
 
Posts: 210
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to pass on different parameters to VBA Code? Ahmaq Excel Worksheet Functions 2 April 22nd 07 04:30 AM
How do I pass parameters into Excel from a URL? David Jankowski Excel Programming 0 April 4th 06 09:52 PM
Is there a way to pass parameters to a web query in excel daytonsupply Excel Programming 1 February 23rd 05 04:10 PM
Pass multiple parameters through macros Chris Perry[_2_] Excel Programming 1 May 24th 04 05:13 PM


All times are GMT +1. The time now is 03:32 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"