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

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

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!

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