Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba code problem
I have written the code below to switch on and off a
coloured input range. The code worked fine in its raw form but as I try to make it more concise I have encountered problems, such as VBA suggesting there should be an '=' after my call to Reveal_Cells Have I misunderstood the nature of subroutines?? Steve Sub alterSheet() Hide_Cells (Range("C22:L28")) End Sub Sub alterSheet2() Reveal_Cells (3,Range("C21:L28")) 'wants = here?? End Sub Private Sub Hide_Cells(Rng As Range) Dim c As Range For Each c In Rng.Cells c.Interior.ColorIndex = 2 c.Font.ColorIndex = 2 c.Value = 0 Next c End Sub Private Sub Reveal_Cells(Default_Value As Variant, Rng As Range) Dim c As Range For Each c In Rng.Cells c.Interior.ColorIndex = 19 c.Font.ColorIndex = 5 c.Value = Default_Value Next c End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba code problem
Remove the parentheses that are around the argument list in the call to the subs.
Sub alterSheet() Hide_Cells Range("C22:L28") End Sub Sub alterSheet2() Reveal_Cells 3, Range("C21:L28") End Sub This is a potentially confusing topic, but you only use parentheses around an argument list when the procedure returns a value that is being captured (or you use Call). -- John Green - Excel MVP Sydney Australia "Steve" wrote in message ... I have written the code below to switch on and off a coloured input range. The code worked fine in its raw form but as I try to make it more concise I have encountered problems, such as VBA suggesting there should be an '=' after my call to Reveal_Cells Have I misunderstood the nature of subroutines?? Steve Sub alterSheet() Hide_Cells (Range("C22:L28")) End Sub Sub alterSheet2() Reveal_Cells (3,Range("C21:L28")) 'wants = here?? End Sub Private Sub Hide_Cells(Rng As Range) Dim c As Range For Each c In Rng.Cells c.Interior.ColorIndex = 2 c.Font.ColorIndex = 2 c.Value = 0 Next c End Sub Private Sub Reveal_Cells(Default_Value As Variant, Rng As Range) Dim c As Range For Each c In Rng.Cells c.Interior.ColorIndex = 19 c.Font.ColorIndex = 5 c.Value = Default_Value Next c End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba code problem
Hi Steve, Use parameteres without the parenthesis as under Sub alterSheet() Hide_Cells Range("C22:L28") End Sub Sub alterSheet2() Reveal_Cells 3,Range("C21:L28") End Sub Regards, Shah Shailesh http://members.lycos.co.uk/shahweb/ *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba code problem
"John Green" skrev i melding
... Remove the parentheses that are around the argument list in the call to the subs. (or you use Call). My vote goes to "Call", I find that easier to read and maintain (but I know I'm in the minority): Sub alterSheet() Call Hide_Cells (Range("C22:L28")) End Sub Sub alterSheet2() Call Reveal_Cells (3,Range("C21:L28")) End Sub Best wishes Harald Followup to newsgroup only please |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with code | Excel Discussion (Misc queries) | |||
VB Code Problem | Excel Discussion (Misc queries) | |||
XLS to CSV Code Problem | Excel Worksheet Functions | |||
Code problem | Excel Programming | |||
Code Problem ! | Excel Programming |