how can i pass a optional value to a sub?

Question

I am trying to use a sub. Maybe I should do it as a function because I will be calling this many different times.

But for now it's a sub, unless that is why it's not working?

I'm passing two sheet names via macro, to show one sheet, then hide the previous one.

However, I get the error "Expected = "

Code is below, any ideas?

Sub setupToFront()

    showsheet ("Status","Setup")

End Sub


Sub showsheet(mySheet As String, Optional ByVal hidesheet As Variant)

Dim ws As Worksheet
Set ws = Worksheets(mySheet)

ws.Visible = xlSheetVisible
ws.Activate

If IsMissing(hidesheet) Then
    'nothing, optional val not used
Else
    Worksheets(CStr(hidesheet)).Visible = xlSheetHidden
End If

End Sub

The parentheses indicates you are trying to return something, there is no variable to the left of the sub call so it is yelling, even though there is no return in ```showsheet```. — Warcupine, Feb 06 '20 at 14:19
Or precede with “call” then you can use the parenthesis. — Andrew Truckle, Feb 06 '20 at 15:32
The parentheses in `showsheet ("Status","Setup")` induce VBA to **evaluate** what's inside them as a supposed expression; furthermore this supposed expression is regarded here as the procedure's *first* argument resulting in a compile error as `("Status","Setup")` can't be evaluated with success. C.f. [here] (https://stackoverflow.com/questions/58996540/how-does-byref-to-byval-by-parentheses-work-when-the-byref-is-a-range-with-a-wo?noredirect=1&lq=1) and [here](https://stackoverflow.com/questions/56692769/vba-usage-of-parentheses-for-a-method?noredirect=1&lq=1). — T.M., Feb 07 '20 at 15:23

score 2 · Accepted Answer · edited Feb 06 '20 at 15:26

2

It's expecting to return a result as if you were using a function hence it expected the =.

Just showsheet "Status", "Setup" will work fine to run a sub.

edited Feb 06 '20 at 15:26

SJR

22,986
6
18
26

answered Feb 06 '20 at 14:22

blake

481
4
14

how can i pass a optional value to a sub?

1 Answers1