Separation of VBA and Spreadsheet
 

Wes,

If it will only be the same workbook being worked upon, why do you need to
separate them. Why not keep them together?

--

HTH

Bob Phillips

"Wes Jester" wrote in message
...
I am fairly new to using VBA and have a question regarding maintenance of
the VBA code

1. I need to separate the execution of the VBA from the spreadsheet. The
application will probably get updated often, and the user will not be
using
a new template, simply opening the saved workbook. Is there a method I
can
use in workbook_activate, or some other event, to "load" the current set
of
modules or at least point to a location where a "master" may be kept?


If you wish, you can e-mail me direct at

Separation of VBA and Spreadsheet
 

The user may have several workbooks for various projects. They all have
exactly the same template and input requirements. However, the business
process will change over the next few months as we implement new features.
The template will not. The user does not want to have to copy and paste from
a previous workbook to a new template simply necaue some code changed..

There are several hunderd users involved. They want to use EXCEL as if it
is a "smartclient".

Wes

"Bob Phillips" wrote in message
...
Wes,

If it will only be the same workbook being worked upon, why do you need
to
separate them. Why not keep them together?

--

HTH

Bob Phillips

"Wes Jester" wrote in message
...
I am fairly new to using VBA and have a question regarding maintenance
of
the VBA code

1. I need to separate the execution of the VBA from the spreadsheet.
The
application will probably get updated often, and the user will not be
using
a new template, simply opening the saved workbook. Is there a method I
can
use in workbook_activate, or some other event, to "load" the current set
of
modules or at least point to a location where a "master" may be kept?


If you wish, you can e-mail me direct at

Separation of VBA and Spreadsheet
 

Wes,

If you want to import it on workbook open, first create a workbook with all
of your code in a code module(s), then export that module(s) in the VB IDE.
Finally, add this code to the Workbook_Open event procedure in the
ThisWorkbook code module in your working workbook(s(

ThisWorkbook.VBProject.VBComponents.Import _
FileName:="C:\My Documents\myMod.bas"

Where Filename is the name of the exported bas file.

--

HTH

Bob Phillips

"Wes Jester" wrote in message
...
The user may have several workbooks for various projects. They all have
exactly the same template and input requirements. However, the business
process will change over the next few months as we implement new features.
The template will not. The user does not want to have to copy and paste
from
a previous workbook to a new template simply necaue some code changed..

There are several hunderd users involved. They want to use EXCEL as if it
is a "smartclient".

Wes

"Bob Phillips" wrote in message
...
Wes,

If it will only be the same workbook being worked upon, why do you need
to
separate them. Why not keep them together?

--

HTH

Bob Phillips

"Wes Jester" wrote in message
...
I am fairly new to using VBA and have a question regarding maintenance
of
the VBA code

1. I need to separate the execution of the VBA from the spreadsheet.
The
application will probably get updated often, and the user will not be
using
a new template, simply opening the saved workbook. Is there a method
I
can
use in workbook_activate, or some other event, to "load" the current
set
of
modules or at least point to a location where a "master" may be kept?


If you wish, you can e-mail me direct at

Separation of VBA and Spreadsheet
 

I disagree with this approach as all those templates will be filled with
useless codesheets... and each time it is opened it has to check and
import the latest version of the code.

I suggest:

No code in the template at all.

Keep all code in an addin, and store that in the central Library folder
on the server.

Then the only thinkg you need to worry about is that the users have the
addin activated.




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Bob Phillips" wrote:

Wes,

If you want to import it on workbook open, first create a workbook
with all of your code in a code module(s), then export that module(s)
in the VB IDE. Finally, add this code to the Workbook_Open event
procedure in the ThisWorkbook code module in your working workbook(s(

ThisWorkbook.VBProject.VBComponents.Import _
FileName:="C:\My Documents\myMod.bas"

Where Filename is the name of the exported bas file.

--

HTH

Bob Phillips

"Wes Jester" wrote in message
...
The user may have several workbooks for various projects. They all
have exactly the same template and input requirements. However, the
business process will change over the next few months as we implement
new features. The template will not. The user does not want to have
to copy and paste
from
a previous workbook to a new template simply necaue some code
changed..

There are several hunderd users involved. They want to use EXCEL as
if it is a "smartclient".

Wes

"Bob Phillips" wrote in message
...
Wes,

If it will only be the same workbook being worked upon, why do you
need
to
separate them. Why not keep them together?

--

HTH

Bob Phillips

"Wes Jester" wrote in message
...
I am fairly new to using VBA and have a question regarding
maintenance
of
the VBA code

1. I need to separate the execution of the VBA from the
spreadsheet.
The
application will probably get updated often, and the user will
not be
using
a new template, simply opening the saved workbook. Is there a
method
I
can
use in workbook_activate, or some other event, to "load" the
current
set
of
modules or at least point to a location where a "master" may be
kept?


If you wish, you can e-mail me direct at

Separation of VBA and Spreadsheet
 

I think that would be how I would do it, but our OP has his choice<g.

The advantage of his method is that he can maintain the code separately, and
just post any updated modules to a server, and all of his users will
automatically get it with no effort on their part. An adding will need
re-distributing and re-installing.

You pays your money (even when it's free here), and you takes your choice.

--

HTH

Bob Phillips

"keepitcool" wrote in message
...
I disagree with this approach as all those templates will be filled with
useless codesheets... and each time it is opened it has to check and
import the latest version of the code.

I suggest:

No code in the template at all.

Keep all code in an addin, and store that in the central Library folder
on the server.

Then the only thinkg you need to worry about is that the users have the
addin activated.




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Bob Phillips" wrote:

Wes,

If you want to import it on workbook open, first create a workbook
with all of your code in a code module(s), then export that module(s)
in the VB IDE. Finally, add this code to the Workbook_Open event
procedure in the ThisWorkbook code module in your working workbook(s(

ThisWorkbook.VBProject.VBComponents.Import _
FileName:="C:\My Documents\myMod.bas"

Where Filename is the name of the exported bas file.

--

HTH

Bob Phillips

"Wes Jester" wrote in message
...
The user may have several workbooks for various projects. They all
have exactly the same template and input requirements. However, the
business process will change over the next few months as we implement
new features. The template will not. The user does not want to have
to copy and paste
from
a previous workbook to a new template simply necaue some code
changed..

There are several hunderd users involved. They want to use EXCEL as
if it is a "smartclient".

Wes

"Bob Phillips" wrote in message
...
Wes,

If it will only be the same workbook being worked upon, why do you
need
to
separate them. Why not keep them together?

--

HTH

Bob Phillips

"Wes Jester" wrote in message
...
I am fairly new to using VBA and have a question regarding
maintenance
of
the VBA code

1. I need to separate the execution of the VBA from the
spreadsheet.
The
application will probably get updated often, and the user will
not be
using
a new template, simply opening the saved workbook. Is there a
method
I
can
use in workbook_activate, or some other event, to "load" the
current
set
of
modules or at least point to a location where a "master" may be
kept?


If you wish, you can e-mail me direct at