Randall Arnold said:
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 said:
Randall Arnold said:
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....
I need to know it there is any realistic way, the operative word
here
is Saving
the
--
Terry
"I said I never had much use for one,
I never said I didn't know how to use one."
M. Quigley