Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of PUBLIC
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of PUBLIC
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of PUBLIC
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of PUBLIC
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of PUBLIC
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of PUBLIC
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Public Password | Excel Discussion (Misc queries) | |||
Dim vs. Public | Excel Programming | |||
PUBLIC DECLARE | Excel Programming | |||
public sub | Excel Programming | |||
Public Sub Help | Excel Programming |