Passing Constant Arguments to custom Subroutine & Functions

[cLiffordiL]

All my sheets are being named, and since the names have a chance of
changing regularly, I've thought of storing them in constant variables
inside my Module1. I have many of my macro functions that I need to address
these sheets and their contents. Problem is VBA doesn't seems to let me pass
constant variables through as arguments ("Compile error: ByRef argument type
mismatch"). I've tried declaring ByVal before my arguments (all with blank
values) but the constant values doesn't seems to get passed through in this
case.

Inside Module1:

Const sht001 As String = "MainMenu"
Const sht002 As String = "DynMenu1"
Const sht003 As String = "DynMenu2"
Const sht004 As String = "DynMenu3"
....
Sub SwitchActiveSheet(Original As String, Destination As String)
Sheets(Destination).Visible = True
Sheets(Original).Visible = False
End Sub

Inside Sheet-Level Module of Visual Basic:

' Inside Sheet1
Sub Switch()
Call SwitchActiveSheet(sht001 , sht002)
End Sub

Any help is appreciated. Thanks!
__________
cLiffordiL

 

[NickHK]

Clifford,
It's not a problem with your routine, but one of "Scope".
This is very good reason to make sure you use "Option Explicit" on all
modules.

Also, a worksheet has 2 names; the name/text that appears on the tab in
Excel and the .CodeName that can only be read/changed through VBA. So if you
passed the WS.CodeNames as your parameters, you would not have to worry
about what a user may have changed them to and ditch those constants anyway.

NickHK

 

[cLiffordiL]

Hi Nick,
I understand what you're saying, but my constant and the general routine
SwitchActiveSheet is in the module, which places their scope at a public
level. Unless when Switch calls SwitchActiveSheet with the constants, VBA
"downgrades" the constants (since they're passed ByRef) into the local
scope. Is that what happened?
Thanks!
________
cLiffordiL

 

[NickHK]

Cliffird,
So which error do you get with:
Option Explicit '<<<< Add this
' Inside Sheet1
Sub Switch()
Call SwitchActiveSheet(sht001 , sht002)
End Sub

And/Or with:
Public Const sht001 As String = "MainMenu" '<<<< Note "Public"
Public Const sht002 As String = "DynMenu1"

But as I said, using the CodeName of the worksheet means these constants
serve no purpose.

NickHK

 

[cLiffordiL]

Cliffird,
So which error do you get with:
Option Explicit '<<<< Add this
' Inside Sheet1
Sub Switch()
Call SwitchActiveSheet(sht001 , sht002)
End Sub

And/Or with:
Public Const sht001 As String = "MainMenu" '<<<< Note "Public"
Public Const sht002 As String = "DynMenu1"

No errors @ all, Nick, no errors at all.

But as I said, using the CodeName of the worksheet means these constants
serve no purpose.

I knew, & know , but my situation would not allow me to: I'm not in sole
control of the file.
_____________
cLiffordiL

 

[NickHK]

Clifford,
Well, you said you a have an error ("Compile error: ByRef argument type
mismatch") , I told you the solution.
If you don't want to fix it, I suggest you carry on regardless.
Good luck.

NickHK

 

[Andy Pope]

For the help file.

Constants are private by default.
In standard modules, the default visibility of module-level constants
can be changed using the Public keyword.

So use NickHK's suggestion.

Cheers
Andy