Other users of my Excel VBA macros

[Ed]

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]

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

 

[Ed]

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

 

[JLGWhiz]

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.

 

[Jon Peltier]

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]

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

 

[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]

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/r...&camp=1789&tag=peltiertechni-20&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

 

[Ed]

Thanks Jon.

I implemented a simple project with the VBA forms & modules in one
file and the data in another.
It works great, so I think I'm on top of the issue now and will be
able to convert my real
project to that organization.

Just to share with others what I have learned, I have posted my simple
project here:

http://www.efsowell.us/ed/AddinTest.xls
http://www.efsowell.us/ed/AddinTest.xla

There are a couple things I can't figure out. I would like the menu to
be automatically added to the Excel
toolbar when the AddinTest.xls file is opened, and removed when it is
closed. Hoping to accomplish this,
in Thisworkbook in the AddinTest.xla file I have the code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call RemoveAddinMenu
End Sub

Private Sub Workbook_Open()
Call RemoveAddinMenu
Call AddAddinMenu
End Sub

As you probably already know, this adds the menu when the XLAfile is
opened, e.g., by checking
the AddinTest box in the Excel Tools|Add-ins dialog, and removes it
when the box is unchecked. However,
if the user doesn't do this himself the menu will be on the tool bar
whenever ANY workbook is opened.

So, how can I get the wanted behavior? I tried putting the above subs
in Thisworkbook of the XLS file,
but it fails because the AddAddinTestMenu and RemoveAddinTestMenu are
not found... obviously because
they are in the Addin! Is there a way to do this programmatically,
e.g., by Open("AddinTest.xla")?

Also, can anyone tell me exactly what ThisWorkbook refers to? For
example, if in my VBA code in the XLA I write

With ThisWorkbook.Worksheets("myWorksheet")
.Cells(iRow, iCol).Value = "Hello"
End With

is the referenced cell in the XLA workbook or in the XLS workbook?

See some other comments intext below.

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

That is really neat!

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/r...&camp=1789&tag=peltiertechni-20&creative=9325

I have a Wrox book Excel 2000 VBA. It has helped me quite a bit, but
often left me still puzzled over details.



Thanks.

Ed

 

[Jon Peltier]

if the user doesn't do this himself the menu will be on the tool bar
whenever ANY workbook is opened.

I don't know what this means. When the add-in is installed, it stays
installed when Excel is closed and reopened. I don't think it's good
practice to have code in the regular workbook to bring up the add-in's
menus. If you only want the add-in's menus to be active while certain
workbooks are active, you need to track application events like

SheetActivate(ByVal Sh As Object)
SheetDeactivate(ByVal Sh As Object)
WorkbookActivate(ByVal Wb As Workbook)
WorkbookDeactivate(ByVal Wb As Workbook)
WorkbookOpen(ByVal Wb As Workbook)
WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)

and when the active workbook is one that the add-in is allowed to work on,
activate the menus. The events are tracked using an application events class
module (use Google or the Wrox book to work it out), and some kind of flag
in the workbook (a name or a custom doc property) is used to indicate a
special workbook.

ThisWorkbook refers to the workbook that contains the code containing
"ThisWorkbook". To refer to the active workbook, use ActiveWorkbook. Or
maybe you want ActiveSheet.

- Jon

 

[Ed]

Thanks, Jon.

Yes, it finally dawned on me that Addins work that way. And, I see
that there's nothing wrong
with the Addin being installed when a workbook that it's not intended
for is open. OTOH, the
menu being there could cause some problems if the user, perhaps out of
curiosity, tried to use
it while he had some other workbook open.

Yesterday I played around with it a bit and got it working the way I
want, albeit with some code in
the data workbook. Instead of loading the addin by checking the box in
Tools|Addin, in ThisWorkbook of AddinTest.xls I put the following:

Private Sub Workbook_Open()
'
' Open AddinTest.xla if not already open
'
Dim wkbAddinTestXLA As Workbook
Dim Wkb As Workbook
Present = False
For Each Wkb In Workbooks
If Wkb.Name = "AddinTest.xla" Then
Present = True
Exit For
End If
Next Wkb
If Not Present Then
Set wkbAddinTestXLA = Workbooks.Open(Filename:="C:\Documents
and Settings\Ed\Application Data\Microsoft\AddIns\AddinTest.xla")
End If

End Sub

This does the trick since ThisWorkbook of AddinTest.xla has the code
to add the menu when opened and
remove it before close.

This approach will work for me in my JOCMembership.xls project because
I have no expectation of the user
needing the addin except when the JOCMembership.xls workbook (which I
will provide) is open. Nonetheless,
I will investigate doing it entierly within the XLS file like you
suggest.

Thanks Again!

Ed

 

[Jon Peltier]

Other reasonably simple solutions:

1. Set a reference to the add-in in the JOCMembership.xls project. (I have
used this with some success, although it can get cumbersome.)

2. At the start of each procedure of the add-in, insert this line:

If ActiveWorkbook.Name <> "JOCMembership.xls" Then Exit Sub

This will work as long as the user doesn't change the name of the workbook.

- Jon