Excel VBA - Excel crashes when macro button re-assigned problem

[PaulC]

I have built a workbook BookA in Excel97 with a number of sheets an
associated macros. BookA is backed up to another one (by copy/past
values from the main 'Current' sheet to the BookA Backup 'Current
sheet. These sheets have worksheet formatting - hence the need fo
paste values. This method has worked fine for over a year.

When I make enhancements to BookA or its associated macros, and wan
to copy these changes to BookA Backup I open BookA and then saved as..
BookA Backup (removing the password), overwriting the earlier versio
of the backup.

When I reopen BookA, the macro buttons on the toolbars have re-assigne
themseves to the backup so I just re-assign them back to BookA.

However, I now find that when I try to re-assign them, BookA crashe
with an application error - access violation (0xc0000005), Address
0x3003e74e (although I think these addresses change).

I can, instead, make a copy of BookA and rename it (and remove th
password) which seems to work, but I am concerned about it crashin
during this reassigning of the buttons. So far it has not crashed i
use, but I am concerned that that BookA will not now be stable.

Any ideas why this might be occurring or suggestions for solving it?

Pau

 

[Bill Renaud]

I would basically put the code and toolbar in a new workbook, separate from
all of your data workbooks. This has the advantage of reducing the size of
all of your data workbooks. You can easily convert this toolbar workbook to
an add-in, if you want it to load when you start Excel, rather than when you
click a toolbar button the first time.

You will have some work to do to replace most of the references to
ActiveWorkbook, ActiveSheet, and ActiveCell, over to equivalent code, but
the end result is better programming practice anyway.

Remember to delete the toolbar from the Excel toolbar area before opening
the new toolbar workbook, or confusion will still remain. Don't just "hide"
the toolbar by clicking the "X" at the upper right corner. You must bring up
the Customize dialog box and use the Delete button on the Toolbars tab.

Constantly changing the workbook that a toolbar is attached to has always
been problematic in Excel for me also, for toolbars attached to data
workbooks that are renamed, moved to other folders, etc.

 

[PaulC]

Bill

Many thanks for your reply.

I can see now that it would be much better practice to move the code to
a separate workbook - I presume this could all be put in the Personal
workbook, which I already have the macros for starting up the 3 excel
workbooks and saving/closing everything.

However, it will be a significant task to change all the references -
there are a lot of them! I have limited time to work on this project.


I have just found that the crashing problem does not occur on my pc at
home with Excel2000. (We still have 97 at work). Could it be that 2000
handle re-assigning buttons etc better?

If so is there any reason not to install 2000 on the pc at work that is
used to update and save the data files? Other users (with Excel97) on
the network at work only have read-only access to this data.

I guess I need to know if 2000 save the data any differently to97, or
are there just enhancements in 2000 which are not available in 97?
Might there be any other issues in changing to 2000 on this pc?


Paul

 

[Bill Renaud]

1. Yes, you could put the code in the Personal.XLS workbook, but it is not
necessary. The Personal.XLS workbook tends to be a target for viruses. If
your virus scanning software ever has to clean the Personal.XLS workbook, it
might accomplish it by deleting all code in that workbook! I usually put the
code in another workbook that is saved in a folder somewhere in "C:\My
Documents" so that it is easy to backup.
2.Changing all the references - Usually, you can use the editor to
interactively find and replace each instance. Create object variables for
the 2 workbooks and worksheets, then use those in your program statements,
using With statements as much as possible.
3. Does your machine at work have all Service Packs installed? If you
upgrade to Excel 2000 at work, then you will have to remember to save each
data workbook in a format that the users can read. If you use the "Microsoft
Excel 97-2000 & 5.0/95 Workbook" format, it will roughly double the size of
each file, however. Upgrading might also make developing VBA macros easier.
Check the "Key information for upgraders and new users" topic in the Getting
Started chapter in Excel (2000) Help.

 

[PaulC]

Bill

Thanks for all your help.

I will look through my code to decide whether to move it all from th
data workbook.

After reading your comments I think I probably wont go down the 200
route.

I will take your suggestions to heart though, as I wish to improve m
VBA programming skills.

Best wishes,

Pau

 

[PaulC]

Bill

The workbook crashed with another application error last night durin
normal data inputting.

I am going to try moving the macros to a separate workbook. However
re-reading your suggestions I realise that I have never (knowingly!
attached any of the toolbars to the workbook. They are obviously jus
stored on the main pc used for data inputting. Apart from making th
toolbars available at any other pc is there any need to attach them
Or, could not attaching them be a possible cause of the problems I'
having?

I am quite concerned about dealing with these application errors
Obviously if you have any other suggestions for finding the cause o
these errors I would be very gratefull.

Best wishes,

Pau

 

[Bill Renaud]

Toolbars that are not attached will work okay, as long as they point to the
correct version (or revision) of workbook that contains the code that you
want to run. If you are still creating data workbooks that contain the macro
code, then you will have an impossible job of trying to determine which code
will be run when you click the button. In other words, you create Book1 and
save it. Later, you add more data and maybe save it as Book2, then decide to
make changes to the code. The toolbar button is now referring to the code in
Book2. The next time you click it to make changes to the data in Book1 it
might actually be running the code in Book2 to make the changes in Book1!
This can get very confusing. This is why I usually always put code in a
separate workbook.

My preference is to attach a toolbar to the workbook that contains the code
that the toolbar buttons will call. This way, the workbook with the macro
code can be moved to another machine and the toolbar will be displayed (a
copy made to the local Excel environment) when the code workbook is opened
on that machine.

The toolbar that you see next to all of the other toolbars in Excel on your
computer is actually a COPY of the toolbar that is attached to the workbook.
I know this is difficult for most users to understand and I wish that
Microsoft would make toolbars an object that shows up in the project
explorer window along with the worksheets, user forms, code modules, charts,
class modules, etc. The COPY of the toolbar that you see displayed next to
your menu bar can be modified by having extra buttons added (that could even
refer to other macros in other workbooks). The origninal toolbar attached to
a workbook is NOT automatically updated when the user adds new buttons to
the COPY of the toolbar that is displayed.