To put a stop statement in the code, you just go to the end of an existing
line and hit the [Enter] key to insert a new line and simply type
Stop
and hit the [Enter] key again. When the program gets to that point, it will
enter into debug mode with the Stop highlighted. Then you can press the [F8]
key to go to the next step in the code and repeat pressing [F8] for as long
as you care to follow it to see what is happening. But I believe I know
what's happening: you have the VB Project protected with a password, so the
line that reads
Set refs = ThisWorkbook.VBProject.References
is unable to get into the VBProject because of the password keeping it out.
So in the next line 'refs' is Nothing, so the global iCloseExel is set to 1
at that point and something else somewhere is checking that value and when it
sees that it is 1, it is closing Excel.
It seems that the logical thing to do here is not protect the VBProject with
a password unless there is some real serious need to keep people from seeing
the code. The question is why the program needs to be able to see the VBA
code anyhow, unless it is trying to determine somewhere else whether or not a
particular reference to a library is available or not. If that is the case,
then it can probably be rewritten to test for that in some other fashion.
There are too many "why" and "what" questions to make a real recommendation.
If you want to just ignore this whole situation, which may result in the
workbook not working for someone else, you could simply remove the
iCloseExcel = 1
statement from the code there. You'd still get an alert that things may not
be quite right, but iCloseExel would remain 0 when the routine ended and
probably not trigger closing the application later.
As for the file shrinkage, I doubt that you've lost any data. More than
likely the used at some time in the past, but no longer used areas on
worksheets have gotten marked as unused now, which is a good thing.
Alex St-Pierre said:
Thanks !
I don't know how to put a stop.
As you said, I have check the Private Sub Workbook_Open()
Set refs = ThisWorkbook.VBProject.References
If refs Is Nothing Then
MsgBox "The program cannot access your VBA code."
iCloseExcel = 1
Exit Sub
End If
What I found is that iCloseExcel = 1 is a Global Variable that is defined
inside a module. (Global i as integer). I don't know why but...
1) if I remove this line, the program open correctly.
2) if I remove the module that contains Global iCloseExcel As Integer, the
program work correctly
3) if I remove the password of the vba code, the program work correctly (can
initiliaze Global iCloseExcel as integer before saying iCloseExcel = 1 ???
4) if I use Excel 2002, the program work better than with version 2003
5) I used this line for a while and never had problem with password. The
only thing I can say it that the module are bigger and this is why it create
a problem with the global variable. If I removed a lot of module and let the
module with Global iCloseExcel as integer, all work good !!!
Before having problem with that, the file size was 2.1mo, after having the
problem, I opened the program with Excel 2002 and save it. The program takes
only 0.9mo. Does I loose informations or what?
Thanks!
Alex
--
Alex St-Pierre
JLatham said:
The logical suspect would be the macro that is running when you open the
workbook. Try either simply removing that macro code from it all and see how
things go. If it opens without that macro, then examine that macro closely
to see what it is doing - or put a STOP statement right at the beginning of
the code and open the workbook and then use [F8] to single step thru it to
see where it bombs.
If you still have troubles, post the code and people will try to help you
with it.
Alex St-Pierre said:
Hi,
I have an Excel file that seems too big but only 2mb. The file cannot be
opened after saving it. I thought it was a problem with the module code but
seems to be an error with vba code size?
1) If I remove the vba code password, all word good!
2) If I remove a selected module, all work good.
3) If I remove a 3 others modules at the same time, all work good.
These 3 solutions are completly independant. Is it possible that I have
reach some excel limitation ?
Thanks!
Alex
Note: The program contains 19 modules, 2 forms and a macro that is opened at
the opening of the file. All work very good when the file is open. I really
don't know how to debug it.
