Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sharing VBA procedures; alternative to personal.xls?
I have a workbook with a set of VBA procedures (subs and functions).
I want to use the same procedures in another (new) workbook. I know that I could cut-and-paste into the VBA editor. I also know that I could put the shared procedures into personal.xls. I believe there is a "better" way -- at least, a way that I prefer, and a way that I have used in the past without problems. This has been explained in these newsgroups, but I cannot find the explanation. And what I remember is not working for me. As I recall, all I need to do is open the original workbook that contains the VBA procedures in the same Excel instance in which I open the new workbook (File / New / Blank Workbook). That does not work for me now, regardless of whether I open the original workbook before or after creating the new workbook. What am I doing wrong? Oh, I __can__ access the subs in the original workbook. Just not the functions. Does this method of sharing VBA procedures work only for subs? Is there a special way that I must declare the functions for this method of sharing to work? I have declared the functions simply as "function foobar(arglist) as type". I notice that the module that contains the VBA procedures is in one VBA project named for the original workbook, whereas the new workbook is in a separate VBA project. Is that the problem? Or is that correct, namely that each workbook is a separate VBA project? Bottom line: How can I share VBA procedures -- functions as well as subs -- between workbooks, ideally without using personal.xls? FYI, I am using Office Excel 2003. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sharing VBA procedures; alternative to personal.xls?
Save the workbook as Add-in
See this small example Debra Dalgleish's (Toolbar example from Dave Peterson) http://www.contextures.com/xlToolbar02.html -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm wrote in message ... I have a workbook with a set of VBA procedures (subs and functions). I want to use the same procedures in another (new) workbook. I know that I could cut-and-paste into the VBA editor. I also know that I could put the shared procedures into personal.xls. I believe there is a "better" way -- at least, a way that I prefer, and a way that I have used in the past without problems. This has been explained in these newsgroups, but I cannot find the explanation. And what I remember is not working for me. As I recall, all I need to do is open the original workbook that contains the VBA procedures in the same Excel instance in which I open the new workbook (File / New / Blank Workbook). That does not work for me now, regardless of whether I open the original workbook before or after creating the new workbook. What am I doing wrong? Oh, I __can__ access the subs in the original workbook. Just not the functions. Does this method of sharing VBA procedures work only for subs? Is there a special way that I must declare the functions for this method of sharing to work? I have declared the functions simply as "function foobar(arglist) as type". I notice that the module that contains the VBA procedures is in one VBA project named for the original workbook, whereas the new workbook is in a separate VBA project. Is that the problem? Or is that correct, namely that each workbook is a separate VBA project? Bottom line: How can I share VBA procedures -- functions as well as subs -- between workbooks, ideally without using personal.xls? FYI, I am using Office Excel 2003. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sharing VBA procedures; alternative to personal.xls?
On Dec 4, 9:57 am, "Ron de Bruin" wrote:
Save the workbook as Add-in See this small example Debra Dalgleish's (Toolbar example from Dave Peterson) http://www.contextures.com/xlToolbar02.html Thanks for the pointer. It may prove useful someday. But I would prefer not to go the Add-In route, if I can avoid it. The Option Private Module help page explains: "Option Private is only useful for host applications that support simultaneous loading of multiple projects and permit references between the loaded projects. For example, Microsoft Excel permits loading of multiple projects and Option Private Module can be used to restrict cross-project visibility." Conversely, it would seem that a public moodule is "visible" (accessible?) across projects (workbooks?). Since there is no Option Public Module statement, I ass-u-me that modules are public by default. Note: I did not specify either Public or Private for each VBA procedure in the one workbook. My understanding is that they are public by default. If all that is true, why can't I access functions (per se) across "projects" (i.e. workbooks)? Oddly, I can execute sub(routine)s across projects (workbooks) from Excel, but I cannot call subs in one project from another project. Why not? I cannot help but think that my difficulty is a user error (mine). I hope someone can tell me what I'm doing wrong. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sharing VBA procedures; alternative to personal.xls?
Did you want to use these functions in a cell in a worksheet?
If yes, you could use syntax like: ='my other workbook.xls'!myFunct(a1:a7) If you want to access the functions in your other project (via code), you have a couple of choices. You could add a reference in the "receiving" project that points at the "sending" project. First, it's probably better to give the "sending" project a nice unique name. Open the VBE Hit ctrl-r to see the project explorer select your workbook's project. It should look something like: VBAProject (book1.xls) Hit F4 to see the properties window Type the new name in the (Name) property Save this workbook. Select the "receiving" project Tools|References Look for that nice unique name and select it (check it). Then you can use the functions in that other workbook just like they're built into VBA. Dim Resp as long resp = myFunct(activesheet.range("a1:A7") ====== The second way to have your code call a function in another workbook's project is to use Application.run: application.run "'FullfileName.xls'!macroname", myargument or to return something: resp = application.run("'fullfilename.xls"'!macroname, myargument) wrote: On Dec 4, 9:57 am, "Ron de Bruin" wrote: Save the workbook as Add-in See this small example Debra Dalgleish's (Toolbar example from Dave Peterson) http://www.contextures.com/xlToolbar02.html Thanks for the pointer. It may prove useful someday. But I would prefer not to go the Add-In route, if I can avoid it. The Option Private Module help page explains: "Option Private is only useful for host applications that support simultaneous loading of multiple projects and permit references between the loaded projects. For example, Microsoft Excel permits loading of multiple projects and Option Private Module can be used to restrict cross-project visibility." Conversely, it would seem that a public moodule is "visible" (accessible?) across projects (workbooks?). Since there is no Option Public Module statement, I ass-u-me that modules are public by default. Note: I did not specify either Public or Private for each VBA procedure in the one workbook. My understanding is that they are public by default. If all that is true, why can't I access functions (per se) across "projects" (i.e. workbooks)? Oddly, I can execute sub(routine)s across projects (workbooks) from Excel, but I cannot call subs in one project from another project. Why not? I cannot help but think that my difficulty is a user error (mine). I hope someone can tell me what I'm doing wrong. -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sharing VBA procedures; alternative to personal.xls?
On Dec 4, 4:00 pm, Dave Peterson wrote:
Did you want to use these functions in a cell in a worksheet? If yes, you could use syntax like: ='my other workbook.xls'!myFunct(a1:a7) Thanks to you and Gord for this. I shoulda figured this out when I saw that syntax in the macro menu. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sharing my macros that I have saved in Personal.xls | Excel Discussion (Misc queries) | |||
How to connect Excel to stored procedures in SQL database? | Excel Discussion (Misc queries) | |||
How to connect Excel to stored procedures in SQL database? | Excel Discussion (Misc queries) | |||
Running sql stored procedures from Excel | Excel Discussion (Misc queries) | |||
ANOVA procedures and t-test equal variance | Excel Discussion (Misc queries) |