Use of PUBLIC

I need some help in understanding the use of the PUBLIC statement.

VBA Help for PUBLIC states that "Variables declared using the Public
statement are available to all procedures in all modules in all
applications...". I have taken that to mean that if I am running VBA in one
spreadsheet, and it opens a second spreadsheet, the PUBLIC variables in the
first code are available to the code in the second application. But that
doesn't seem to be the case. Is this an incorrect interpretation, or am I
not doing something right?

If this is not the way PUBLIC works, is there some other way for a file
(FILE B) opened by another file (FILE A) to use variables defined in the
original file (FILE A)?

If nothing else, is there a way for FILE A to write a value to a particular
memory location, and then have FILE B read from that memory location?
--
Bill @ UAMS

I think the PUBLIC statement makes variables available only within modules of
the workbook containing that statement...don't take that as gospel, but
that's what I've always assumed...

Use the SaveSetting and GetSetting statements to save a variable in the
registry for use later or (I assume this should work) another workbook thats
open at the same time.

I use it all the time for saving values of variables for when the Add-In's I
design are next loaded...

Chris

"BillCPA" wrote:

> I need some help in understanding the use of the PUBLIC statement.
>
> VBA Help for PUBLIC states that "Variables declared using the Public
> statement are available to all procedures in all modules in all
> applications...". I have taken that to mean that if I am running VBA in one
> spreadsheet, and it opens a second spreadsheet, the PUBLIC variables in the
> first code are available to the code in the second application. But that
> doesn't seem to be the case. Is this an incorrect interpretation, or am I
> not doing something right?
>
> If this is not the way PUBLIC works, is there some other way for a file
> (FILE B) opened by another file (FILE A) to use variables defined in the
> original file (FILE A)?
>
> If nothing else, is there a way for FILE A to write a value to a particular
> memory location, and then have FILE B read from that memory location?
> --
> Bill @ UAMS

I created two workbooks book1.xls and book2.xls.

I opened the VBE and selected the project for Book1.xls.
I hit F4 to see the project explorer.
I changed the name of the project from VBAProject to book1proj (to give it a
nice unique name).

Then I put this code into a general module in book1proj:

Option Explicit
Public MyValueFromBook1 As Long
Sub testme()
MyValueFromBook1 = 999
End Sub

And I ran the testme to put something into that variable.

Then I clicked on the project for Book2.xls.
I clicked on Tools|references
And checked book1proj.

Then I could use this code in Book2.xls's project:
Option Explicit
Sub testme()
MsgBox book1proj.MyValueFromBook1
End Sub

Since Book2.xls has a reference to book1.xls's project, each time book2.xls
opens, so will book1.xls.

============
If you want to avoid the references, you can create functions in the "sending"
workbook's project.

Inside book1.xls:
Option Explicit
Public MyValueFromBook1 As Long
Sub testme()
MyValueFromBook1 = 999
End Sub
Function GetMyValue() As Variant
GetMyValue = MyValueFromBook1
End Function

And inside book2.xls:
Option Explicit
Sub testme()
Dim wkbk As Workbook
Set wkbk = Workbooks("book1.xls")
MsgBox Application.Run("'" & wkbk.Name & "'!Getmyvalue")
End Sub

The "sending" workbook (Book1.xls) has to be open to use this to work.


BillCPA wrote:
>
> I need some help in understanding the use of the PUBLIC statement.
>
> VBA Help for PUBLIC states that "Variables declared using the Public
> statement are available to all procedures in all modules in all
> applications...". I have taken that to mean that if I am running VBA in one
> spreadsheet, and it opens a second spreadsheet, the PUBLIC variables in the
> first code are available to the code in the second application. But that
> doesn't seem to be the case. Is this an incorrect interpretation, or am I
> not doing something right?
>
> If this is not the way PUBLIC works, is there some other way for a file
> (FILE B) opened by another file (FILE A) to use variables defined in the
> original file (FILE A)?
>
> If nothing else, is there a way for FILE A to write a value to a particular
> memory location, and then have FILE B read from that memory location?
> --
> Bill @ UAMS

--

Dave Peterson

This looks like a good solution. I was considering creating a separate
'control' file for one spreadsheet to write to and then the other one read
from. But I assume this will be much faster.

If I could pick your brain just a little bit further - I've heard of the
'registry', but never done anything with it. (Perhaps you might post one of
your SaveSetting lines fo code - I've always done better with real-life
examples.)

In the SaveSetting Help, it mentions the 'section' parameter where the
setting is to be saved - is there someplace I can find a list of these
sections, and does it matter which section the data is saved in? And are
there any restrictions on what can be used for the 'key' parameter?

I've been looking for a solution to this for a long time - if this works as
it looks like it will, i will be extremely grateful.

--
Bill @ UAMS


"Chris Gorham" wrote:

> I think the PUBLIC statement makes variables available only within modules of
> the workbook containing that statement...don't take that as gospel, but
> that's what I've always assumed...
>
> Use the SaveSetting and GetSetting statements to save a variable in the
> registry for use later or (I assume this should work) another workbook thats
> open at the same time.
>
> I use it all the time for saving values of variables for when the Add-In's I
> design are next loaded...
>
> Chris
>
> "BillCPA" wrote:
>
> > I need some help in understanding the use of the PUBLIC statement.
> >
> > VBA Help for PUBLIC states that "Variables declared using the Public
> > statement are available to all procedures in all modules in all
> > applications...". I have taken that to mean that if I am running VBA in one
> > spreadsheet, and it opens a second spreadsheet, the PUBLIC variables in the
> > first code are available to the code in the second application. But that
> > doesn't seem to be the case. Is this an incorrect interpretation, or am I
> > not doing something right?
> >
> > If this is not the way PUBLIC works, is there some other way for a file
> > (FILE B) opened by another file (FILE A) to use variables defined in the
> > original file (FILE A)?
> >
> > If nothing else, is there a way for FILE A to write a value to a particular
> > memory location, and then have FILE B read from that memory location?
> > --
> > Bill @ UAMS

BillCPA wrote:
> I need some help in understanding the use of the PUBLIC statement.
>
> VBA Help for PUBLIC states that "Variables declared using the Public
> statement are available to all procedures in all modules in all
> applications...". I have taken that to mean that if I am running VBA in one
> spreadsheet, and it opens a second spreadsheet, the PUBLIC variables in the
> first code are available to the code in the second application. But that
> doesn't seem to be the case. Is this an incorrect interpretation, or am I
> not doing something right?

public functions -> yes
public variables -> no



>
> If this is not the way PUBLIC works, is there some other way for a file
> (FILE B) opened by another file (FILE A) to use variables defined in the
> original file (FILE A)?

if these arefixed file A and fixed File B, go to File A and add File B
in Tool/References


>
> If nothing else, is there a way for FILE A to write a value to a particular
> memory location, and then have FILE B read from that memory location?

use windows registry.

Here's some code of mine:

If Val(Application.Version) < 9 Then
check = MsgBox(prompt:="Master Tool only works with Excel 2000 or
later. It will therefore load but not function.", title:="WARNING!!")
SaveSetting "master_tool", "options", "check1", "no"
Else
SaveSetting "master_tool", "options", "check1", "yes"
End If

If GetSetting("master_tool", "options", "check", "no") = "no" And
GetSetting("master_tool", "options", "check1", "no") = "yes" Then
Call set_up_EULA
If GetSetting("master_tool", "options", "check", "no") = "no" Then
check = MsgBox(prompt:="The EULA has NOT been accepted. " &
ThisWorkbook.Name & " will continue to load but will not function.",
title:="WARNING!!")
End If
etc...etc....etc

Once you run the "SaveSetting" code if you search in your registry by going
"Start", "Run" and type "regedit" - the registry editor appears - an entry
called "master_tool" will have appeared. Under this will be "options" and
then "check1" and it associated value (i.e. no or yes in the above example).

GetSetting retrieves the value of "check1" but also allows for a default
value (the fourth entry in the statement" if there is the value has yet to be
set (i.e. SaveSetting hasn't been run yet).

Check out my web site www.mastertool.co.uk

Rgds...Chris

"BillCPA" wrote:

> This looks like a good solution. I was considering creating a separate
> 'control' file for one spreadsheet to write to and then the other one read
> from. But I assume this will be much faster.
>
> If I could pick your brain just a little bit further - I've heard of the
> 'registry', but never done anything with it. (Perhaps you might post one of
> your SaveSetting lines fo code - I've always done better with real-life
> examples.)
>
> In the SaveSetting Help, it mentions the 'section' parameter where the
> setting is to be saved - is there someplace I can find a list of these
> sections, and does it matter which section the data is saved in? And are
> there any restrictions on what can be used for the 'key' parameter?
>
> I've been looking for a solution to this for a long time - if this works as
> it looks like it will, i will be extremely grateful.
>
> --
> Bill @ UAMS
>
>
> "Chris Gorham" wrote:
>
> > I think the PUBLIC statement makes variables available only within modules of
> > the workbook containing that statement...don't take that as gospel, but
> > that's what I've always assumed...
> >
> > Use the SaveSetting and GetSetting statements to save a variable in the
> > registry for use later or (I assume this should work) another workbook thats
> > open at the same time.
> >
> > I use it all the time for saving values of variables for when the Add-In's I
> > design are next loaded...
> >
> > Chris
> >
> > "BillCPA" wrote:
> >
> > > I need some help in understanding the use of the PUBLIC statement.
> > >
> > > VBA Help for PUBLIC states that "Variables declared using the Public
> > > statement are available to all procedures in all modules in all
> > > applications...". I have taken that to mean that if I am running VBA in one
> > > spreadsheet, and it opens a second spreadsheet, the PUBLIC variables in the
> > > first code are available to the code in the second application. But that
> > > doesn't seem to be the case. Is this an incorrect interpretation, or am I
> > > not doing something right?
> > >
> > > If this is not the way PUBLIC works, is there some other way for a file
> > > (FILE B) opened by another file (FILE A) to use variables defined in the
> > > original file (FILE A)?
> > >
> > > If nothing else, is there a way for FILE A to write a value to a particular
> > > memory location, and then have FILE B read from that memory location?
> > > --
> > > Bill @ UAMS

Fantastic stuff - I think this is just what I've been needing. Thanks so much!

--
Bill @ UAMS


"Chris Gorham" wrote:

> Here's some code of mine:
>
> If Val(Application.Version) < 9 Then
> check = MsgBox(prompt:="Master Tool only works with Excel 2000 or
> later. It will therefore load but not function.", title:="WARNING!!")
> SaveSetting "master_tool", "options", "check1", "no"
> Else
> SaveSetting "master_tool", "options", "check1", "yes"
> End If
>
> If GetSetting("master_tool", "options", "check", "no") = "no" And
> GetSetting("master_tool", "options", "check1", "no") = "yes" Then
> Call set_up_EULA
> If GetSetting("master_tool", "options", "check", "no") = "no" Then
> check = MsgBox(prompt:="The EULA has NOT been accepted. " &
> ThisWorkbook.Name & " will continue to load but will not function.",
> title:="WARNING!!")
> End If
> etc...etc....etc
>
> Once you run the "SaveSetting" code if you search in your registry by going
> "Start", "Run" and type "regedit" - the registry editor appears - an entry
> called "master_tool" will have appeared. Under this will be "options" and
> then "check1" and it associated value (i.e. no or yes in the above example).
>
> GetSetting retrieves the value of "check1" but also allows for a default
> value (the fourth entry in the statement" if there is the value has yet to be
> set (i.e. SaveSetting hasn't been run yet).
>
> Check out my web site www.mastertool.co.uk
>
> Rgds...Chris
>
> "BillCPA" wrote:
>
> > This looks like a good solution. I was considering creating a separate
> > 'control' file for one spreadsheet to write to and then the other one read
> > from. But I assume this will be much faster.
> >
> > If I could pick your brain just a little bit further - I've heard of the
> > 'registry', but never done anything with it. (Perhaps you might post one of
> > your SaveSetting lines fo code - I've always done better with real-life
> > examples.)
> >
> > In the SaveSetting Help, it mentions the 'section' parameter where the
> > setting is to be saved - is there someplace I can find a list of these
> > sections, and does it matter which section the data is saved in? And are
> > there any restrictions on what can be used for the 'key' parameter?
> >
> > I've been looking for a solution to this for a long time - if this works as
> > it looks like it will, i will be extremely grateful.
> >
> > --
> > Bill @ UAMS
> >
> >
> > "Chris Gorham" wrote:
> >
> > > I think the PUBLIC statement makes variables available only within modules of
> > > the workbook containing that statement...don't take that as gospel, but
> > > that's what I've always assumed...
> > >
> > > Use the SaveSetting and GetSetting statements to save a variable in the
> > > registry for use later or (I assume this should work) another workbook thats
> > > open at the same time.
> > >
> > > I use it all the time for saving values of variables for when the Add-In's I
> > > design are next loaded...
> > >
> > > Chris
> > >
> > > "BillCPA" wrote:
> > >
> > > > I need some help in understanding the use of the PUBLIC statement.
> > > >
> > > > VBA Help for PUBLIC states that "Variables declared using the Public
> > > > statement are available to all procedures in all modules in all
> > > > applications...". I have taken that to mean that if I am running VBA in one
> > > > spreadsheet, and it opens a second spreadsheet, the PUBLIC variables in the
> > > > first code are available to the code in the second application. But that
> > > > doesn't seem to be the case. Is this an incorrect interpretation, or am I
> > > > not doing something right?
> > > >
> > > > If this is not the way PUBLIC works, is there some other way for a file
> > > > (FILE B) opened by another file (FILE A) to use variables defined in the
> > > > original file (FILE A)?
> > > >
> > > > If nothing else, is there a way for FILE A to write a value to a particular
> > > > memory location, and then have FILE B read from that memory location?
> > > > --
> > > > Bill @ UAMS