Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separating code from data
I need to know it there is any realistic way, the operative word here is
realistic, to have vast tracts of VB code exist and be usable independent of a set of worksheets. The application is only 4 or so sheets but the volume of code supporting the application is quite large. Unfortunately in the MS world, elegance is expensive. The problem being that the entire .xls file is unacceptably large and not only saves at glacial speeds, it seems to have grown to the point where Excel's reach exceeds its grasp and is somewhat delicate. Saving the file while doing development is unpredictable and often entertaining. Using a personal.xls file will keep the code separate and get it to load but actually using it is painful. The 'Application.Run' method is unacceptable. Not only is is exceedingly clumsy, you cannot use named parameters. Is there any way to tell Excel where to look to resolve procedure references? Or am I simply SOL? -- Terry "I said I never had much use for one, I never said I didn't know how to use one." M. Quigley |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separating code from data
I'm sure you have already thought of this, but I've taken to putting my bulk
code into modules and class modules and calling the subs in them from the workbook (or application) events (by creating an app class). I'm assuming that technique doesn't help you here...? Randall Arnold "Terry von Gease" wrote in message ... I need to know it there is any realistic way, the operative word here is realistic, to have vast tracts of VB code exist and be usable independent of a set of worksheets. The application is only 4 or so sheets but the volume of code supporting the application is quite large. Unfortunately in the MS world, elegance is expensive. The problem being that the entire .xls file is unacceptably large and not only saves at glacial speeds, it seems to have grown to the point where Excel's reach exceeds its grasp and is somewhat delicate. Saving the file while doing development is unpredictable and often entertaining. Using a personal.xls file will keep the code separate and get it to load but actually using it is painful. The 'Application.Run' method is unacceptable. Not only is is exceedingly clumsy, you cannot use named parameters. Is there any way to tell Excel where to look to resolve procedure references? Or am I simply SOL? -- Terry "I said I never had much use for one, I never said I didn't know how to use one." M. Quigley |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separating code from data
"Randall Arnold" wrote in message
... I'm sure you have already thought of this, but I've taken to putting my bulk code into modules and class modules and calling the subs in them from the workbook (or application) events (by creating an app class). I'm assuming that technique doesn't help you here...? Randall Arnold That's sort of what I had in mind. Understand that I'm a crusty old unreconstructed Unix hand so could you elaborate with a minimum of jargon? For example, I have never have had a satisfactory explanation of just what in hell a class module is and why would I want one. Moreover what might an 'app class' be? What I figured on doing is put all of the real live code for the sheet events into a vanilla module and then have the actual event code like change, double_click, etc simply call the real procedure using the 'Application.Run ' syntax. I assume that once I invoke the code in the module I can call any other code in that workbook in something resembling a normal manner. I figure that this ought to work for the 30-odd forms as well. What I hope for is virtually ALL of the VB code in, say, 'personal.xls' [Can there be more than one? Does Excel try to load everything in XLStart, it seems to, or does the name matter?] Then all of the events for all of the sheets in the actual .xls file would merely have: 'Application.Run "personal.xls! parm1, parm2,..parmn I also assume that any buttons on the actual .xls file could also be set up to invoke code in the personal or whatever file. I further assume that there's probably a lot better way to do this, if so enlighten me. Thanks for the consideration.... "Terry von Gease" wrote in message ... I need to know it there is any realistic way, the operative word here is realistic, to have vast tracts of VB code exist and be usable independent of a set of worksheets. The application is only 4 or so sheets but the volume of code supporting the application is quite large. Unfortunately in the MS world, elegance is expensive. The problem being that the entire .xls file is unacceptably large and not only saves at glacial speeds, it seems to have grown to the point where Excel's reach exceeds its grasp and is somewhat delicate. Saving the file while doing development is unpredictable and often entertaining. Using a personal.xls file will keep the code separate and get it to load but actually using it is painful. The 'Application.Run' method is unacceptable. Not only is is exceedingly clumsy, you cannot use named parameters. Is there any way to tell Excel where to look to resolve procedure references? Or am I simply SOL? -- Terry "I said I never had much use for one, I never said I didn't know how to use one." M. Quigley |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separating code from data
Do you know about Tools/References in the VB Editor? You can set a reference to Personal.xls or
the workbook that contains your code. Maybe that will solve your problems. On Fri, 25 Jul 2003 14:35:34 -0700, "Terry von Gease" wrote: I need to know it there is any realistic way, the operative word here is realistic, to have vast tracts of VB code exist and be usable independent of a set of worksheets. The application is only 4 or so sheets but the volume of code supporting the application is quite large. Unfortunately in the MS world, elegance is expensive. The problem being that the entire .xls file is unacceptably large and not only saves at glacial speeds, it seems to have grown to the point where Excel's reach exceeds its grasp and is somewhat delicate. Saving the file while doing development is unpredictable and often entertaining. Using a personal.xls file will keep the code separate and get it to load but actually using it is painful. The 'Application.Run' method is unacceptable. Not only is is exceedingly clumsy, you cannot use named parameters. Is there any way to tell Excel where to look to resolve procedure references? Or am I simply SOL? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separating code from data
"Randall Arnold" wrote in message
... There's a MUCH better way, using the Application class (app class) and code modules in your Personal.xls file. I don't have access to the info now; I'll try to remember to post it here Monday. Or, try searching google for keywords like [Excel +"Application Class"] (without brackets). I just ran it, and turned up one of my links: http://www.cpearson.com/excel/events.htm . That page has 90% of what you need to know! Also try www.mvps.org as a launching point for Office application help. It that's 90% of what I need to know, there's no hope. I stand in awe wondering just what it might say had it been written by a sentient being using English. Is there, somewhere, somehow, an explanation suitable for someone other than those who don't need an explanation? My impression is that this stuff has a level of contrivance that would make MacGyver swoon. Simple, we want simple. You don't need Application.Run at all, I don't think. The other solution is much more elegant! If the truth be known I really don't want to end up using personal.xls. Too much potential for conflict. But if that's the only way, I guess I'll have to suck it up and do it. I made a start thusly: following instructions gleaned from office.microsoft.com... 1. created a personal.xls file in the proper place 2. exported about a bazillion modules and forms from the actual workbook and imported them into personal.xls 3. changed a couple of calls in the actual workbook to call into the personal.xls code. 4. Trivial test seem to work, I go to bed happy Now, I arise and attempt to continue with diddling the code... I cannot seem to edit anything in personal.xls. Yes, I unhid the sheet. No matter what I name it or where I move it, every time I make a change to the code in this file and attempt to save it I get a cheery little dialog telling me 'File not saved'. No help, no explanation, no nothing. When I originally started this endeavor I was thinking about doing it in perl using Tk. It would have been done by now. It would have been fast. It would have been simple. It would have worked. All the time, Every time. Under water. Buried in mud. In a hurricane. But no, I had to try to use Excel. Maybe it's not too late to back up and do it properly...probably not. I think I'm stuck with Excel, where you can almost, but not quite, do what you want some of the time. -- Terry "I said I never had much use for one, I never said I didn't know how to use one." M. Quigley "Terry von Gease" wrote in message ... "Randall Arnold" wrote in message ... I'm sure you have already thought of this, but I've taken to putting my bulk code into modules and class modules and calling the subs in them from the workbook (or application) events (by creating an app class). I'm assuming that technique doesn't help you here...? Randall Arnold That's sort of what I had in mind. Understand that I'm a crusty old unreconstructed Unix hand so could you elaborate with a minimum of jargon? For example, I have never have had a satisfactory explanation of just what in hell a class module is and why would I want one. Moreover what might an 'app class' be? What I figured on doing is put all of the real live code for the sheet events into a vanilla module and then have the actual event code like change, double_click, etc simply call the real procedure using the 'Application.Run ' syntax. I assume that once I invoke the code in the module I can call any other code in that workbook in something resembling a normal manner. I figure that this ought to work for the 30-odd forms as well. What I hope for is virtually ALL of the VB code in, say, 'personal.xls' [Can there be more than one? Does Excel try to load everything in XLStart, it seems to, or does the name matter?] Then all of the events for all of the sheets in the actual .xls file would merely have: 'Application.Run "personal.xls! parm1, parm2,..parmn I also assume that any buttons on the actual .xls file could also be set up to invoke code in the personal or whatever file. I further assume that there's probably a lot better way to do this, if so enlighten me. Thanks for the consideration.... "Terry von Gease" wrote in message ... I need to know it there is any realistic way, the operative word here is realistic, to have vast tracts of VB code exist and be usable independent of a set of worksheets. The application is only 4 or so sheets but the volume of code supporting the application is quite large. Unfortunately in the MS world, elegance is expensive. The problem being that the entire .xls file is unacceptably large and not only saves at glacial speeds, it seems to have grown to the point where Excel's reach exceeds its grasp and is somewhat delicate. Saving the file while doing development is unpredictable and often entertaining. Using a personal.xls file will keep the code separate and get it to load but actually using it is painful. The 'Application.Run' method is unacceptable. Not only is is exceedingly clumsy, you cannot use named parameters. Is there any way to tell Excel where to look to resolve procedure references? Or am I simply SOL? -- Terry "I said I never had much use for one, I never said I didn't know how to use one." M. Quigley |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separating code from data
"Myrna Larson" wrote in message
... Do you know about Tools/References in the VB Editor? You can set a reference to Personal.xls or the workbook that contains your code. Maybe that will solve your problems. That would be too simple. I tried it and Excel gave me the finger and told me that I couldn't use that file. Or any other .xls file of any name living in any directory. I expected no less. Thanks for trying. On Fri, 25 Jul 2003 14:35:34 -0700, "Terry von Gease" wrote: I need to know it there is any realistic way, the operative word here is realistic, to have vast tracts of VB code exist and be usable independent of a set of worksheets. The application is only 4 or so sheets but the volume of code supporting the application is quite large. Unfortunately in the MS world, elegance is expensive. The problem being that the entire .xls file is unacceptably large and not only saves at glacial speeds, it seems to have grown to the point where Excel's reach exceeds its grasp and is somewhat delicate. Saving the file while doing development is unpredictable and often entertaining. Using a personal.xls file will keep the code separate and get it to load but actually using it is painful. The 'Application.Run' method is unacceptable. Not only is is exceedingly clumsy, you cannot use named parameters. Is there any way to tell Excel where to look to resolve procedure references? Or am I simply SOL? -- Terry "I said I never had much use for one, I never said I didn't know how to use one." M. Quigley |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separating code from data
"Terry von Gease" wrote in message
I'll do it but I can only wonder just why would I want to do this. Well, I thought that you wanted to separate code from data, putting the code in one workbook, such as personal.xls, and be able to run that code from any other workbook. Placing "vast tracts" of common code in personal.xls is one way to do this. Since you say that using Application.Run is unacceptable in your circumstances, you need another mechanism to allow VBA to find the common code to execute. Setting a reference to the workbook containing the code is the method you use to do this. However, referenced workbooks and libraries must have unique names. Since a workbook's project name defaults to "VBA Project" you need to rename the project in order to set a reference to it. You can then call the code in the referenced project as if it existed in the same project -- no need for Application.Run. Your original post asked: Is there any way to tell Excel where to look to resolve procedure references? Setting a reference is exactly how you do it. That's why you would want to do this. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Terry von Gease" wrote in message ... "Chip Pearson" wrote in message ... Terry, I tried it and Excel gave me the finger and told me that I couldn't use that file. Specifically which finger did it give you, and specifically what did the error message say? You description is less than clear. The finger with "Can't add a reference to the specified file." tattooed on it. The 'help' button generates a disply that says, in so many words, 'Can't add a reference to the specified file'. Very helpful. You will want to change the Project Name of your personal.xls file before referencing it from other files. With the personal.xls file open in the VBA Editor, go to the Tools menu, choose "VBA Project Properties" and change the name from "VBA Project" to something unique, such as "Personal". I'll do it but I can only wonder just why would I want to do this. -- Terry "I said I never had much use for one, I never said I didn't know how to use one." M. Quigley "Terry von Gease" wrote in message ... "Myrna Larson" wrote in message ... Do you know about Tools/References in the VB Editor? You can set a reference to Personal.xls or the workbook that contains your code. Maybe that will solve your problems. That would be too simple. I tried it and Excel gave me the finger and told me that I couldn't use that file. Or any other .xls file of any name living in any directory. I expected no less. Thanks for trying. On Fri, 25 Jul 2003 14:35:34 -0700, "Terry von Gease" wrote: I need to know it there is any realistic way, the operative word here is realistic, to have vast tracts of VB code exist and be usable independent of a set of worksheets. The application is only 4 or so sheets but the volume of code supporting the application is quite large. Unfortunately in the MS world, elegance is expensive. The problem being that the entire .xls file is unacceptably large and not only saves at glacial speeds, it seems to have grown to the point where Excel's reach exceeds its grasp and is somewhat delicate. Saving the file while doing development is unpredictable and often entertaining. Using a personal.xls file will keep the code separate and get it to load but actually using it is painful. The 'Application.Run' method is unacceptable. Not only is is exceedingly clumsy, you cannot use named parameters. Is there any way to tell Excel where to look to resolve procedure references? Or am I simply SOL? -- Terry "I said I never had much use for one, I never said I didn't know how to use one." M. Quigley |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separating code from data
"Chip Pearson" wrote in message ... "Terry von Gease" wrote in message I'll do it but I can only wonder just why would I want to do this. Well, I thought that you wanted to separate code from data, putting the code in one workbook, such as personal.xls, and be able to run that code from any other workbook. Placing "vast tracts" of common code in personal.xls is one way to do this. Since you say that using Application.Run is unacceptable in your circumstances, you need another mechanism to allow VBA to find the common code to execute. Setting a reference to the workbook containing the code is the method you use to do this. However, referenced workbooks and libraries must have unique names. Since a workbook's project name defaults to "VBA Project" you need to rename the project in order to set a reference to it. I did all that. Unique file name, unique project name. I still can't get Tools- References... to accept it. Is there some incantation or another that I'm missing here or am I simply accursed? You can then call the code in the referenced project as if it existed in the same project -- no need for Application.Run. Your original post asked: Is there any way to tell Excel where to look to resolve procedure references? Setting a reference is exactly how you do it. That's why you would want to do this. I fervently want to do this. The problem is that it's not letting me. And just what's with this ""Document not saved" crap that keeps popping up, or not, based on some bizarre principle unknown to me? ....deletia... -- Terry "I said I never had much use for one, I never said I didn't know how to use one." M. Quigley |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separating code from data
Terry,
I did all that. Unique file name, unique project name. I still can't get Tools- References... to accept it. Specifically what do you mean that you can't get VBA to accept the reference? What happens, or fails to happen? What error message do you get? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Terry von Gease" wrote in message ... "Chip Pearson" wrote in message ... "Terry von Gease" wrote in message I'll do it but I can only wonder just why would I want to do this. Well, I thought that you wanted to separate code from data, putting the code in one workbook, such as personal.xls, and be able to run that code from any other workbook. Placing "vast tracts" of common code in personal.xls is one way to do this. Since you say that using Application.Run is unacceptable in your circumstances, you need another mechanism to allow VBA to find the common code to execute. Setting a reference to the workbook containing the code is the method you use to do this. However, referenced workbooks and libraries must have unique names. Since a workbook's project name defaults to "VBA Project" you need to rename the project in order to set a reference to it. I did all that. Unique file name, unique project name. I still can't get Tools- References... to accept it. Is there some incantation or another that I'm missing here or am I simply accursed? You can then call the code in the referenced project as if it existed in the same project -- no need for Application.Run. Your original post asked: Is there any way to tell Excel where to look to resolve procedure references? Setting a reference is exactly how you do it. That's why you would want to do this. I fervently want to do this. The problem is that it's not letting me. And just what's with this ""Document not saved" crap that keeps popping up, or not, based on some bizarre principle unknown to me? ...deletia... -- Terry "I said I never had much use for one, I never said I didn't know how to use one." M. Quigley |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separating code from data
"Chip Pearson" wrote in message
... Terry, I did all that. Unique file name, unique project name. I still can't get Tools- References... to accept it. Specifically what do you mean that you can't get VBA to accept the reference? What happens, or fails to happen? What error message do you get? The same one I've been getting since I entered this madhouse. "Can't add a reference to the specified file." But...Through some insane sequence of having the code.xls file either loaded, hidden or not hidden, or not loaded, the names of ever thing with a name changed to something unique, or maybe not, I finally got the accursed thing to be referenced as you describe. I couldn't repeat the steps if my life were threatened. Chalk it up to good JuJu. Very cool. Now, save me much agony... Why do I, from time to time, get the "Document not saved" message when I try to save something. Especially the code.xls file. What's the proper way, if there is a proper way, to continue to diddle the code.xls file? While it's loaded via the reference in the actual.xls file or loaded separately all by itself? The latter makes for a royal pain in the ass for testing the stuff. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Terry von Gease" wrote in message ... "Chip Pearson" wrote in message ... "Terry von Gease" wrote in message I'll do it but I can only wonder just why would I want to do this. Well, I thought that you wanted to separate code from data, putting the code in one workbook, such as personal.xls, and be able to run that code from any other workbook. Placing "vast tracts" of common code in personal.xls is one way to do this. Since you say that using Application.Run is unacceptable in your circumstances, you need another mechanism to allow VBA to find the common code to execute. Setting a reference to the workbook containing the code is the method you use to do this. However, referenced workbooks and libraries must have unique names. Since a workbook's project name defaults to "VBA Project" you need to rename the project in order to set a reference to it. I did all that. Unique file name, unique project name. I still can't get Tools- References... to accept it. Is there some incantation or another that I'm missing here or am I simply accursed? You can then call the code in the referenced project as if it existed in the same project -- no need for Application.Run. Your original post asked: Is there any way to tell Excel where to look to resolve procedure references? Setting a reference is exactly how you do it. That's why you would want to do this. I fervently want to do this. The problem is that it's not letting me. And just what's with this ""Document not saved" crap that keeps popping up, or not, based on some bizarre principle unknown to me? ...deletia... -- Terry "I said I never had much use for one, I never said I didn't know how to use one." M. Quigley |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separating code from data
Rob
I know the original poster did not appreciate your efforts (quite unreasonably) but I have printed off your post as I think they may be helpful to me: I have always avoided using References on the basis of some advice of one of the well respected Excel posters but your method seems straightforward. Regards Tim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separating data into new sheet | Excel Discussion (Misc queries) | |||
Separating data | Excel Worksheet Functions | |||
separating out data in columns | Excel Discussion (Misc queries) | |||
coma not separating data | Excel Discussion (Misc queries) | |||
separating data | Excel Discussion (Misc queries) |