declaring public variable value

Hi all,

I have a public variable that is currently declared as string at the top
module 1

How and where should I set/define the variable value so that it is the same
and available to any macro/procedure in the project/wookbook?

currently trying

set Pwd = "owntext" ' Pwd is the public variable

The value for Pwd is the password for locking/unlocking sheets so I do not
want to have to set it more than once if possible..

On a kind of related note is there a way to stop people viewing the macros?

I can protect pages, I can prevent the original file being over written, I
can even prevent anybody other than me from saving the file at all but this
is almost pointless if the user can view & edit macros

Hi Damon,

Try:

In a standard module try:


Public PWORD As String


Sub SetIt()
PWORD = "MyPassword"
End Sub

Sub ReadIt()
MsgBox PWORD
End Sub

---
Regards,
Norman



"Damon" <[email protected]> wrote in message
news:[email protected]...
> Hi all,
>
> I have a public variable that is currently declared as string at the top
> module 1
>
> How and where should I set/define the variable value so that it is the
> same
> and available to any macro/procedure in the project/wookbook?
>
> currently trying
>
> set Pwd = "owntext" ' Pwd is the public variable
>
> The value for Pwd is the password for locking/unlocking sheets so I do not
> want to have to set it more than once if possible..
>
> On a kind of related note is there a way to stop people viewing the
> macros?
>
> I can protect pages, I can prevent the original file being over written, I
> can even prevent anybody other than me from saving the file at all but
> this
> is almost pointless if the user can view & edit macros

Hi Damon,

Forgot to respond to the second question:

> On a kind of related note is there a way to stop people viewing the
> macros?

You can declare the sub as Private.

You can put Option Private Module at the top of the module.

You could add a dummy parameter to the sub.

---
Regards,
Norman



"Damon" <[email protected]> wrote in message
news:[email protected]...
> Hi all,
>
> I have a public variable that is currently declared as string at the top
> module 1
>
> How and where should I set/define the variable value so that it is the
> same
> and available to any macro/procedure in the project/wookbook?
>
> currently trying
>
> set Pwd = "owntext" ' Pwd is the public variable
>
> The value for Pwd is the password for locking/unlocking sheets so I do not
> want to have to set it more than once if possible..
>
> On a kind of related note is there a way to stop people viewing the
> macros?
>
> I can protect pages, I can prevent the original file being over written, I
> can even prevent anybody other than me from saving the file at all but
> this
> is almost pointless if the user can view & edit macros

Firstly, you can seta public variable anywhere. As public, it is accessible
to any macro.

Second, you do not Set a string variable, just assign it

Pwd = "owntext"

You can protect a project in the VBE, Tools>Properties>Protection, but it
will only deter a casual onlooker, it is easily circumvented.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Damon" <[email protected]> wrote in message
news:[email protected]...
> Hi all,
>
> I have a public variable that is currently declared as string at the top
> module 1
>
> How and where should I set/define the variable value so that it is the
same
> and available to any macro/procedure in the project/wookbook?
>
> currently trying
>
> set Pwd = "owntext" ' Pwd is the public variable
>
> The value for Pwd is the password for locking/unlocking sheets so I do not
> want to have to set it more than once if possible..
>
> On a kind of related note is there a way to stop people viewing the
macros?
>
> I can protect pages, I can prevent the original file being over written, I
> can even prevent anybody other than me from saving the file at all but
this
> is almost pointless if the user can view & edit macros

Hi Damon.

Apologies, I misread your second question. My suggestions prevent the macros
in question from appearing in Excel's macro list.

The broader question of hiding code has been addressed by Bob in his more
apposite response.


---
Regards,
Norman



"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
>
> Hi Damon,
>
> Forgot to respond to the second question:
>
>> On a kind of related note is there a way to stop people viewing the
>> macros?
>
> You can declare the sub as Private.
>
> You can put Option Private Module at the top of the module.
>
> You could add a dummy parameter to the sub.
>
> ---
> Regards,
> Norman
>
>
>
> "Damon" <[email protected]> wrote in message
> news:[email protected]...
>> Hi all,
>>
>> I have a public variable that is currently declared as string at the top
>> module 1
>>
>> How and where should I set/define the variable value so that it is the
>> same
>> and available to any macro/procedure in the project/wookbook?
>>
>> currently trying
>>
>> set Pwd = "owntext" ' Pwd is the public variable
>>
>> The value for Pwd is the password for locking/unlocking sheets so I do
>> not
>> want to have to set it more than once if possible..
>>
>> On a kind of related note is there a way to stop people viewing the
>> macros?
>>
>> I can protect pages, I can prevent the original file being over written,
>> I
>> can even prevent anybody other than me from saving the file at all but
>> this
>> is almost pointless if the user can view & edit macros
>
>