I've developed a lot of VBA macros, menus, etc for an Excel based club
membership database. I'd now like to hand this over to another member
soon to take over as the club membership person. I would like to
provide support,
probably involving adding new features etc.

The question is, what's the best way to do this? I know I can just
send him the
VBA modules, user forms, etc. and show him how to remove the old one
and
import a new one, but that seems a bit involved. I've read up on
Add-ins, but can't
quite see how this would help.

Ideas?

TIA

Ed

Why not use CDs, that is pretty cheap and will not overload the mail server
if you send them USPS.

"Ed" wrote:

I've developed a lot of VBA macros, menus, etc for an Excel based club
membership database. I'd now like to hand this over to another member
soon to take over as the club membership person. I would like to
provide support,
probably involving adding new features etc.

The question is, what's the best way to do this? I know I can just
send him the
VBA modules, user forms, etc. and show him how to remove the old one
and
import a new one, but that seems a bit involved. I've read up on
Add-ins, but can't
quite see how this would help.

Ideas?

TIA

Ed

"JLGWhiz" wrote in message
...
Why not use CDs, that is pretty cheap and will not overload the mail
server
if you send them USPS.


Thanks for the reply.

The problem is the data. the VBA code seems to be unavoidably part and
parcel with the
data, i.e., all the worksheets in the workbook. So, lets say I send
the new guy a CD with
the current membership state, e.g., list of members, addresses, dues
payment status etc.,
and of course the current set of VBA macros. He works with it for a
month or two and in
that time updates some of the addresses, some members pay their dues,
etc. He then calls
and says, "Hey, Ed. Why don't you fix ....." That is, he doesn't like
the way some of
my macros work. If I fix the macros and send him a new CD he has new
macros but
old data. If he copies my new xls file into the membership directory
on his machine he wipes
out his last two months of work.

How do I get around this?

thanks again.

Ed

I would think that he could install the new macros without destroying the
database.
Well, I would hope he could. Of course if the code change required a
database structure change, that could be a problem. Also, printing commands
could be affected by transferring to a different mainframe, and there is the
software compatibility to worry about, versions, etc. A lot of detail. No
matter how you do it there will still be loose ends, so just pick one and go
with it. Work out the bugs as you go.

"Ed" wrote:


"JLGWhiz" wrote in message
...
Why not use CDs, that is pretty cheap and will not overload the mail
server
if you send them USPS.


Thanks for the reply.

The problem is the data. the VBA code seems to be unavoidably part and
parcel with the
data, i.e., all the worksheets in the workbook. So, lets say I send
the new guy a CD with
the current membership state, e.g., list of members, addresses, dues
payment status etc.,
and of course the current set of VBA macros. He works with it for a
month or two and in
that time updates some of the addresses, some members pay their dues,
etc. He then calls
and says, "Hey, Ed. Why don't you fix ....." That is, he doesn't like
the way some of
my macros work. If I fix the macros and send him a new CD he has new
macros but
old data. If he copies my new xls file into the membership directory
on his machine he wipes
out his last two months of work.

How do I get around this?

thanks again.

Ed

I would think that he could install the new macros without destroying the
database.
Well, I would hope he could.

I would not expect a user to be able to do this (it might be possible on
rare occasions with rare users, but I would not count on it). The goal is to
make it as easy as possible for the user to update the code, and as hard as
possible to do it incorrectly. This points to an add-in for code and a
workbook for data at a minimum. Even better would be an add-in for code, a
database or dedicated workbook just for data, and another workbook for
analysis and display (the front end). Add to that an installation routine
that puts the updated files in the appropriate directories and writes the
registry keys for installing the add-in, so the user can't screw that up.

Was it Barnum who said you'll never go broke underestimating the
intelligence of your fellow man?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"JLGWhiz" wrote in message
...
I would think that he could install the new macros without destroying the
database.
Well, I would hope he could. Of course if the code change required a
database structure change, that could be a problem. Also, printing
commands
could be affected by transferring to a different mainframe, and there is
the
software compatibility to worry about, versions, etc. A lot of detail.
No
matter how you do it there will still be loose ends, so just pick one and
go
with it. Work out the bugs as you go.

"Ed" wrote:


"JLGWhiz" wrote in message
...
Why not use CDs, that is pretty cheap and will not overload the mail
server
if you send them USPS.


Thanks for the reply.

The problem is the data. the VBA code seems to be unavoidably part and
parcel with the
data, i.e., all the worksheets in the workbook. So, lets say I send
the new guy a CD with
the current membership state, e.g., list of members, addresses, dues
payment status etc.,
and of course the current set of VBA macros. He works with it for a
month or two and in
that time updates some of the addresses, some members pay their dues,
etc. He then calls
and says, "Hey, Ed. Why don't you fix ....." That is, he doesn't like
the way some of
my macros work. If I fix the macros and send him a new CD he has new
macros but
old data. If he copies my new xls file into the membership directory
on his machine he wipes
out his last two months of work.

How do I get around this?

thanks again.

Ed

The deal with add-ins is they help you to put the code into one workbook
(the add-in) while keeping the data in another workbook. This way, updating
code won't hose the data, and vice versa. If you can, split the existing
workbook into two separate ones, then fix the references in the code to
refer to sheets and workbooks more explicitly, to make sure the code knows
which sheet it should be working on.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Ed" wrote in message
...

"JLGWhiz" wrote in message
...
Why not use CDs, that is pretty cheap and will not overload the mail
server
if you send them USPS.


Thanks for the reply.

The problem is the data. the VBA code seems to be unavoidably part and
parcel with the
data, i.e., all the worksheets in the workbook. So, lets say I send the
new guy a CD with
the current membership state, e.g., list of members, addresses, dues
payment status etc.,
and of course the current set of VBA macros. He works with it for a month
or two and in
that time updates some of the addresses, some members pay their dues, etc.
He then calls
and says, "Hey, Ed. Why don't you fix ....." That is, he doesn't like the
way some of
my macros work. If I fix the macros and send him a new CD he has new
macros but
old data. If he copies my new xls file into the membership directory on
his machine he wipes
out his last two months of work.

How do I get around this?

thanks again.

Ed

Thanks to both of you for your insights and help.

I'm beginning to get the picture, I think. Correct me if I am wrong:

1. I have to have at least two workbooks, one with the data and the
other with the VBA
code implementing the functions, forms & menus.

2. The workbook with the code should be made into an Add-in.

3. The workbook with the data should "enable" the add-in.

Is that picture correct?

If so, what's the best way to get there from here? My thought would be
to

4. Make a copy of the current workbook (JOCMembership.xls), renaming
it to JOCMembershipAddin.xla.

5. In JOCMembershipAddin.xla, delete all the data worksheets and
modify the code to refer explicitly
to JOCMembership.xls.

6. In JOCMembership.xls, delete all the code and enable the
JOCMembershipAddin.

Is that going to work?

Ed


"Jon Peltier" wrote in message
...
The deal with add-ins is they help you to put the code into one
workbook (the add-in) while keeping the data in another workbook.
This way, updating code won't hose the data, and vice versa. If you
can, split the existing workbook into two separate ones, then fix
the references in the code to refer to sheets and workbooks more
explicitly, to make sure the code knows which sheet it should be
working on.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Ed" wrote in message
...

"JLGWhiz" wrote in message
...
Why not use CDs, that is pretty cheap and will not overload the
mail server
if you send them USPS.


Thanks for the reply.

The problem is the data. the VBA code seems to be unavoidably part
and parcel with the
data, i.e., all the worksheets in the workbook. So, lets say I send
the new guy a CD with
the current membership state, e.g., list of members, addresses,
dues payment status etc.,
and of course the current set of VBA macros. He works with it for
a month or two and in
that time updates some of the addresses, some members pay their
dues, etc. He then calls
and says, "Hey, Ed. Why don't you fix ....." That is, he doesn't
like the way some of
my macros work. If I fix the macros and send him a new CD he has
new macros but
old data. If he copies my new xls file into the membership
directory on his machine he wipes
out his last two months of work.

How do I get around this?

thanks again.

Ed

1. I have to have at least two workbooks, one with the data and the other
with the VBA code implementing the functions, forms & menus.

That's best practice for this kind of thing.

2. The workbook with the code should be made into an Add-in.

Saved as an add-in. When you do this, Excel tries to save the add-in to a
special directory, but you can save it wherever you want, and run it from
wherever you want.

3. The workbook with the data should "enable" the add-in.

This workbook doesn't really "enable" the add-in. But it can use functions
(UDFs) from the add-in. The best practice is to have the add-in build a menu
when it opens, and the buttons on the menu run the various procedures in the
add-in. The add-in should be smart enough to run on the user's active
worksheet.

Easy way to do a menu:

http://www.j-walk.com/ss/excel/tips/tip53.htm

John Walkenbach's PowerProgramming might be a worthwhile acquisition to help
with all of this (about $33 last I checked):

http://www.amazon.com/exec/obidos/re...&creative=9325

4. Make a copy of the current workbook (JOCMembership.xls), renaming it to
JOCMembershipAddin.xla.

Just do a Save As to an add-in. JOCMembership.xls will remain open, and you
can browse to JOCMembershipAddin.xla and open it. Double clicking on it is
fine. Alternatively, go to Tools menu Add-Ins, click Browse, find
JOCMembershipAddin.xla, say OK, and check the box in front of it in the
list.

5. In JOCMembershipAddin.xla, delete all the data worksheets and modify
the code to refer explicitly
to JOCMembership.xls.

You can keep worksheets in the add-in, but these should only be sheets that
contain generic information, like that used in MenuMakr (link above) to
build the menus. You can't see the sheets in an add-in, but you can switch
the file from an add-in to a regular workbook. In the VB Editor, go to View,
choose Properties. Select the workbook item in the tree of Excel objects
under JOCMembershipAddin.xla, and toggle the IsAddin property of the
workbook between true (sheets are hidden) and false (sheets are visible and
can be manipulated).

6. In JOCMembership.xls, delete all the code

The add-in is enabled by opening it with a menu or commandbars.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______