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

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

"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

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?

"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

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.

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".


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"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