Passing sheet (using codename) to separate sub as optional paramet

[ker_01]

I have a large, convoluted workbook. With some of my subs that bring in raw
data from other files, I want to unhide the sheet where that data is pasted
just so I can review it (without unhiding all sheets). There are also some
subs where I want to unhide every worksheet in the workbook.

So, I wrote a sub that I thought would handle both, but I'm getting errors
when I try to call it, and it also fails when I place the cursor inside the
sub and try to run it directly.

Sub ShowAllSheets(Optional IndSht As Worksheet)
Dim sht As Worksheet
'name<>codename, but irrelevant -just determines if a sheet was passed to
the sub
If (IndSht.Name) <> "" Then
IndSht.Visible = xlSheetVisible
Else
For Each sht In Excel.ActiveWorkbook.Worksheets
sht.Visible = xlSheetVisible
Next
End If
End Sub

When I try to run the code above, it pops up a dialogue box with a list of
macros to run, rather than just running this one as I would have expected.

Also, when I try to call the sub (below) I get a run time error 438, object
doesn't support this property or method

Sub ALoad_FinRTs()
ShowAllSheets (Sheet15)
'do stuff
End Sub

I think I have more than one problem here, but I'm not sure what the problem
is, so I can't experiment with solutions...

Thanks!
Keith

 

[Bob Phillips]

You cannot run a sub with an argument, even an optional one, you need a sub
that calls it and run that.

But your code is flawed anyway, if you don't pass a sheet then IndSht object
is nothing, so you cannot test name. Better to test for Nothing

If Not IndSht Is Nothing Then
IndSht.Visible = xlSheetVisible

 

[JLGWhiz]

to test it, you need to call it with another macro. Also modified one line:

Sub dk()
Call ShowAllSheets(Sheets("Sheet2")) 'This can be a problem area for
other users.
End Sub 'They can
get type mismatch errors here.

Sub ShowAllSheets(Optional IndSht As Worksheet)
Dim sht As Worksheet
'name<>codename, but irrelevant -just determines if a sheet was passed tothe
sub
If Not IndSht Is Nothing Then '<<<modified
IndSht.Visible = xlSheetVisible
Else
For Each sht In Excel.ActiveWorkbook.Worksheets
sht.Visible = xlSheetVisible
Next
End If
End Sub

 

[Dave Peterson]

Drop the ()'s in your procedure:

Sub ALoad_FinRTs()
ShowAllSheets Sheet15
'do stuff
End Sub

Or add the Call keyword:

Sub ALoad_FinRTs()
Call ShowAllSheets(Sheet15)
'do stuff
End Sub

But I'd listen to Bob, too.

 

[ker_01]

Thank you Bob and JLG; I have incorporated the "not nothing" line. I still am
unable to test it due to my second issue with passing the procedure the
codename of the sheet; I'm still getting a RTE 438. Any additional thoughts
would be greatly appreciated.

I prefer to always use codename because my users may change the sheetname,
and I don't want to risk having that break the code.

Sub testShowSheets()
ShowAllSheets (Sheet14) '<--RTE 438
End Sub

Sub ShowAllSheets(Optional IndSht As Worksheet)
Dim sht As Worksheet
If Not IndSht Is Nothing Then
IndSht.Visible = xlSheetVisible
Else
For Each sht In Excel.ActiveWorkbook.Worksheets
sht.Visible = xlSheetVisible
Next
End If
End Sub

Just for comparison, I did try the sheetname as well, but got the same RTE 438

Sub testShowSheets()
ShowAllSheets (Sheets("Div 14")) '<--RTE 438
End Sub

Thank you,
Keith

 

[ker_01]

Woohoo - success!

After careful rereading of the replies, I saw the use of the 'Call' keyword,
which isn't something I've used before (I can't recall ever passing
parameters to a sub before, I've always just used global variables).

It is now working as expected/hoped.


Thanks again,
Keith

 

[ker_01]

Interesting, "Call" is only needed if I put the parameters in parenthesis? It
works, but I don't comprehend the relationship (or reasons when to use) Call
vs the parens.

But I'd listen to Bob, too.

I always do ...and to you and the other experts in this forum too- I've
never had a formal class in VBA, but it is amazing how much I can do just
based on what I've learned here (and hours of hitting my head on my keyboard
while trying random syntax ;-)

Best,
Keith

 

[Dave Peterson]

If you don't use call, you may not need the ()'s. Excel will essentially try
evaluate the "sheet15" in this line:

ShowAllSheets(Sheet15)

But there really isn't any way to evaluate a sheet.

If you use Call, you have to use ()'s. Excel won't evaluate it, because it
knows you're passing something to the Call statement.

You use the ()'s kind of like when you're calling a function/returning a value:

msgbox "hi"

or

dim resp as long
resp = msgbox(prompt:="hi",buttons:=vbyesno)
msgbox resp