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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >>
> >
> >
>
>

[cLiffordiL]

"NickHK" <[email protected]> wrote in message
news:[email protected]...
> 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

"cLiffordiL" <[email protected]> wrote in message
news:[email protected]...
> "NickHK" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>

[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

cLiffordiL wrote:
> 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" <[email protected]> wrote in message
> news:[email protected]...
>
>>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" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>> 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
>>>
>>>
>>
>>
>
>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info