Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
how to pass on different parameters to VBA Code? | Excel Worksheet Functions | |||
How do I pass parameters into Excel from a URL? | Excel Programming | |||
Is there a way to pass parameters to a web query in excel | Excel Programming | |||
Pass multiple parameters through macros | Excel Programming |