Inconsistant Macro Behoviour

[Chrissy]

Can any one help with this please?

Chrissy.



Chrissy wrote

 

[Keith Willshaw]

Can any one help with this please?

Chrissy.



Chrissy wrote

This suggests your installation may well have a bundle of
different dll's floating around

Win98 is rather unstable for professional use IMHO

On which machine?

Yours , theirs or both ?

The computer

You WILL be since you are using different OS but this shouldnt
matter.


It sounds to me like a corrupt workbook, I'd suggest dumping
out all the code modules and importing them into clean workbook

Alternately try VBA Code Cleaner which essentially
does the same thing

http://www.bmsltd.co.uk/MVP/Default.htm

Keith

 

[keepitcool]

Assuming it's macros that cause excel to crash..

In the xl2k environment:
open the sheet with shift pressed to disable AutoRun macro's

Open the code module by entering the VBA password, as VB cannot
break/debug when the VBA password hasn't been entered.
(Or remove until the problem is solved.)

Check the References for any references which may be missing.
Then compile the code.


You dont give a clue as to what the code that crashes
the PC is doing...

If it compiles ok, the first step is taken.
But no guarantees yet.

Now start running the code and try to analyse WHICH
procedure is crashing excel.

Then post back... with that code snippet


Do you open workbooks or are you just working with
a single book.
Do you work with applications other then excel?
Do you use API's? (Declare function/sub)?

suc6..


keepITcool

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

 

[Chrissy]

Keith Willshaw wrote

This suggests your installation may well have a bundle of
different dll's floating around

I have proved this - I found one DLL in my setup that was for Excel 10.
Since writing this I have uninstalled Excel for Office 2002 from my machine
and reinstalled Excel for Office 2000. Then I noticed that I still had the
Excel 10 DLL.

Win98 is rather unstable for professional use IMHO

Yeah - but that is not my call. Maybe it is now.

On which machine?

Yours , theirs or both ?

Theirs and I forgot to add the important bit - after rebooting (or using the
Task Manager to kill the Excel process that is floating around and still has
the workbook open) if I

open the workbook
remove protection from viewing from it
save it
close Excel
and reopen the workbook
open the VBA editor
run the code

I do not get any problems.

It is SO hard to find a problem that is not there after the editor is opened.

You WILL be since you are using different OS but this shouldnt
matter.

I meant DLLs which are for different versions of Excel - not the changes
needed for different OSs.

It sounds to me like a corrupt workbook, I'd suggest dumping
out all the code modules and importing them into clean workbook

OMG - I never even considered this - bugger. I have always taken
all code and forms and put them in a new workbook in the past before
giving the person the "system".

Alternately try VBA Code Cleaner which essentially
does the same thing

http://www.bmsltd.co.uk/MVP/Default.htm

Thanks - will try that and see what happens but think I will put it in a
new workbook anyway - even though there are 100s of named ranged
in the 10+ worksheets.

Thank you so much for your help.

Chrissy.

 

[Jake Marx]

Hi Chrissy,

It is SO hard to find a problem that is not there after the editor is
opened.

You may want to add some Stop statements throughout your code and remove
them one by one until it crashes - that way, you may be able to determine
where the crash is taking place. Sometimes, when code crashes as the user
runs it but not from the VBE, it is caused by timing issues. Some
well-placed DoEvents statements where the crash is taking place (or an
Application.Wait) may fix the problem for you.

 

[Chrissy]

keepitcool wrote

Assuming it's macros that cause excel to crash..

In the xl2k environment:
open the sheet with shift pressed to disable AutoRun macro's

Open the code module by entering the VBA password, as VB cannot
break/debug when the VBA password hasn't been entered.
(Or remove until the problem is solved.)

Check the References for any references which may be missing.
Then compile the code.

Have done that ages ago - was the first thing I did but it did not work.

You dont give a clue as to what the code that crashes
the PC is doing...

Yeah - I realize that I missed that out - after doing the step you mention above,
it crashes if I have not opened the VBA editor. If I just open it and closing it or
leave it open then it does not crash. Makes it hard to find the problem.

If it compiles ok, the first step is taken.
But no guarantees yet.

Now start running the code and try to analyse WHICH
procedure is crashing excel.

That is hard to do as I have to change the code to write a trace of where
it is going and save this to a file with each procedure and I have to do this
on a machine which is in a store. This is because, as I said above and forgot
to add to the original post that it does not happen if the VBA editor has been
opened. I have extra code in there already (which is commented out) that
can trace procedures - I just need to add the bit to save this to a file and not
display it on screen.

I think I need to get one of those machines to play, oops - do further testing - on
because it is not going wrong on my machine.

Then post back... with that code snippet

It either gets a page fault or access violation - I think I got it wrong on the original
post as it was late - I was tired and very frustrated.

I believe that it has something to do with trying to change something in a worksheet
which is protected but this is only a hunch at this time.

Do you open workbooks or are you just working with
a single book.

I open one workbook.

Only one or two pieces of code activates a workbook to do something on
it. Obviously opening it activates it. After this workbook is opened I activate
the other workbook - the one with the code in it - and go to a specific sheet
which is linked to another workbook - I change the links to be to the newly opened
workbook. If I do not activate the worksheet which contains the links then
Excel cannot change the link as the worksheets are protected and the
UserInterfaceOnly option does not seem to apply to changing links on a non active
worksheet with links in any of its formulas.

.... and that point is why I believe that it has something to do with the protection
I have on the worksheets.

All other code operates no matter what workbook and worksheet is active and I
refer to specific workbooks and worksheet or named ranges. Most of the worksheets
are hidden.

Do you work with applications other then excel?
No.


Do you use API's? (Declare function/sub)?

Not totally sure what you mean - but I did have some code which was an API
call and I have deleted this.

I have reinstalled Excel and uninstalled it to try to get only the Excel 2000 DLLs
there - last night when I went to bed I was not able to get the error on my machine
but have just got this one - I did a final reboot before going to bed but thought that
I had not done a reinstall immediately before that. The error was...


The instruction at "0x00000000" referenced memory at 0x00000000". The memory
could not be "read".

If I restart Excel and try again but open the VBA editor it does not get this error
hence my inability to even identify the code at fault.

Chrissy.

 

[Chrissy]

Jake Marx wrote

Hi Chrissy,


You may want to add some Stop statements throughout your code and remove
them one by one until it crashes - that way, you may be able to determine
where the crash is taking place. Sometimes, when code crashes as the user
runs it but not from the VBE, it is caused by timing issues. Some
well-placed DoEvents statements where the crash is taking place (or an
Application.Wait) may fix the problem for you.

I know this and just opening the VBE is all that is needed to fix things.
Does having the editor opened slow things down? I would have thought
that it made no difference to the code execution speed.

I like the idea of the stop events as this means I can have the code EXACTLY
the same in all other respects so timing should not be a issue - if I add code
which does things then that could alter the timing of the bit that is causing
problems.

I just had a thought. The code does things based on values in the workbook.
If recalculation has not finished some of these values will not be consistent
and I may be using a value which should have changed but has not changed.
For example - in one part of the code I decide which record to give the user
to edit. I get the row number for this record from a cell. If that cell is not
yet updated then it may point to a record which is not there. Anything that
affects timing will alter this. My machine is faster than their machines and
having the VBE opened may just slow it enough to "solve" the problem.

I really need one of their machines to play on.

Chrissy.

 

[Chrissy]

I think I have just worked out what is wrong.

I installed the system on 1/2 the computers (someone else
did the other 1/2) and I was not happy with the speed. Two
machines were so slow that it was unusable. One had a hardware
fault as well so it was upgraded yesterday (with second hand
bits but it is decent now). The other is just VERY slow.

Something which takes 10 seconds on my machine took 2+
mins on these machines. It took about 30 to 40 seconds on
most machines.

I decided to make some changes to speed things up. I had not
fully tested it on any of these machines but had done some testing
on one (the very slow one actually) and nothing was going wrong
other than the speed. After these changes were made is when it
started to crash.

So, it goes faster - my machine recalculates the workbook in a few
seconds - their machines take many more seconds. I believe that
I need to wait on a recalc event to finish before what ever instruction
is causing the crash - now to identify that event.

I am now getting the problem too and that was after adding back in the
code which sped things up and after trying to make sure I am using ONLY
Excel 2000 - not Excel 2002.

I thought that I had enough recalcs in the code - everywhere I need them
but now I think maybe I need one more.

Thanks for you help guys.

Chrissy.

 

[Quaoar]

I have developed an Excel spreadsheet which I want to run
on 15 different computers.

I developed it in Excel 2002 and Excel 2000 - depending one
which machine I was at. It is to run on Excel 2000. I cannot
make it fail in any way on my computer but it does on all other
computers I have tried it on now.

My setup is:

Win2k Professional
Office XP Professional installed then
Excel from Office 2k professional installed separately then,
because Office XP stopped working
Office XP Professional reinstalled

Their setup

Win98
Office2k Professional

I have a couple of forms and 10 worksheets. I have protected the
project from viewing and the worksheets are all password protected.

There is a button on the first spreadsheet which opens up the main
form. This causes a page fault in Excel and Excel then closes. The
computer needs rebooting at this stage so the workbook can be
reopened as it will get a sharing problem if just opened without a
reboot. The computer is not networked.

If I remove the protection from the project it works on one machine
but not on one other. If I run it on my machine even after a reboot
and without entering any passwords it runs fine. I run it under
Excel2k and not Excel XP.

Am I correct in thinking that I must be using different DLLs to the
other machines? What can I do to get this working?

Any help or suggestions are more than welcome.

TIA.

Chrissy.

Chrissy, your clock needs to be set to the correct time/time zone.

Q

 

[Quaoar]

keepitcool wrote

Have done that ages ago - was the first thing I did but it did not
work.



Yeah - I realize that I missed that out - after doing the step you
mention above,
it crashes if I have not opened the VBA editor. If I just open it
and closing it or leave it open then it does not crash. Makes it
hard to find the problem.


That is hard to do as I have to change the code to write a trace of
where
it is going and save this to a file with each procedure and I have to
do this
on a machine which is in a store. This is because, as I said above
and forgot
to add to the original post that it does not happen if the VBA editor
has been
opened. I have extra code in there already (which is commented out)
that
can trace procedures - I just need to add the bit to save this to a
file and not display it on screen.

I think I need to get one of those machines to play, oops - do
further testing - on because it is not going wrong on my machine.



It either gets a page fault or access violation - I think I got it
wrong on the original post as it was late - I was tired and very
frustrated.

I believe that it has something to do with trying to change something
in a worksheet which is protected but this is only a hunch at this
time.



I open one workbook.

Only one or two pieces of code activates a workbook to do something on
it. Obviously opening it activates it. After this workbook is
opened I activate
the other workbook - the one with the code in it - and go to a
specific sheet
which is linked to another workbook - I change the links to be to the
newly opened workbook. If I do not activate the worksheet which
contains the links then
Excel cannot change the link as the worksheets are protected and the
UserInterfaceOnly option does not seem to apply to changing links on
a non active worksheet with links in any of its formulas.

... and that point is why I believe that it has something to do with
the protection
I have on the worksheets.

All other code operates no matter what workbook and worksheet is
active and I
refer to specific workbooks and worksheet or named ranges. Most of
the worksheets
are hidden.



Not totally sure what you mean - but I did have some code which was
an API
call and I have deleted this.

I have reinstalled Excel and uninstalled it to try to get only the
Excel 2000 DLLs there - last night when I went to bed I was not able
to get the error on my machine
but have just got this one - I did a final reboot before going to bed
but thought that I had not done a reinstall immediately before that.
The error was...


The instruction at "0x00000000" referenced memory at 0x00000000".
The memory
could not be "read".

If I restart Excel and try again but open the VBA editor it does not
get this error hence my inability to even identify the code at fault.

Chrissy.

I am suspicious that this might be a problem with early vs. late binding
of object variables, or possibly unqualified references. There is a KB
(AIRC) on a Excel2k problem with early vs. late binding - early binding
causing errors in some instances. Essentially, early binding is use of
a specific object type like Dim myBook as Workbook rather than late
binding as Dim myBook as Object. Unqualified references are like using
Sheets(1) instead of fully qualified references like
Application.Workbooks("myBook").Worksheets(1). At the Beginning of Time
for VBA, I wrote code that would run just fine in the editor and fail in
Excel from unqualified references. It turned out that *I* was confused
and then writing confused code.

The idea of using Stop to help debug is good. I use msgbox("a,b") where
a is a procedure name or number and b is a line number. This lets me
track a persistent problem during execution quickly.

One thing to do is make sure your Win98 is clean. Removing temp files,
temp internet files is important, and defragmenting is important also.
IMO, Win98 is subject to memory leaks from all kinds of sources so
rebooting on persistent errors is important. Also, make sure that
virtual memory is under Windows control.

Win98 is quite resource-bound, so resources must be kept under control
to make VBA work well since the use of objects,forms, etc, will consume
what little resource stack Win98 has, and error dialogs are often no
help in diagnosing low resources. The Office Startup application
(osa.exe) is notorious for eating resources and should be removed from
startup in Win98 - it serves no useful purpose anyway. No wallpaper,
screensaver, active desktop, no more than 30 files (not folders) in the
root of C (C:\), no desktop icons, no temp files, minimum startup
applications, no more than 40-50 fonts, no visual effects in menus, will
help keep VBA and Office under control by freeing resource stack. You
can run the resource monitor to see what is happening with resources
when your code runs; any color other than green can make VBA squirrely.
For VBA, always release local objects when exiting a procedure, and
public objects when no longer needed. You know there is big resource
trouble brewing if forms only partially appear.

Sorry for the long response, but I have a special dark place in my heart
for VBA in Win98.

Q

PS. Keep in mind (someone correct me if wrong) that Excel2k, XP
workbooks are not downward compatible with Excel97 unless specifically
saved to Excel97 workbook format (universal format Excel2002, 2000, 97 &
95 format) . Excel97 workbooks are upward compatible. Excel97 is
somewhat better for developing "universal" VBA as a consequence.

 

[Jose Rojas]

Would you please add the part of the code that it make the system to crash,?

 

[Chrissy]

I have developed an Excel spreadsheet which I want to run
on 15 different computers.

I developed it in Excel 2002 and Excel 2000 - depending one
which machine I was at. It is to run on Excel 2000. I cannot
make it fail in any way on my computer but it does on all other
computers I have tried it on now.

My setup is:

Win2k Professional
Office XP Professional installed then
Excel from Office 2k professional installed separately then,
because Office XP stopped working
Office XP Professional reinstalled

Their setup

Win98
Office2k Professional

I have a couple of forms and 10 worksheets. I have protected the
project from viewing and the worksheets are all password protected.

There is a button on the first spreadsheet which opens up the main form.
This causes a page fault in Excel and Excel then closes. The computer
needs rebooting at this stage so the workbook can be reopened as it will
get a sharing problem if just opened without a reboot. The computer is
not networked.

If I remove the protection from the project it works on one machine
but not on one other. If I run it on my machine even after a reboot
and without entering any passwords it runs fine. I run it under Excel2k
and not Excel XP.

Am I correct in thinking that I must be using different DLLs to the other
machines? What can I do to get this working?

Any help or suggestions are more than welcome.

TIA.

Chrissy.

 

[Chrissy]

Quaoar wrote

I am suspicious that this might be a problem with early vs. late binding
of object variables, or possibly unqualified references. There is a KB
(AIRC) on a Excel2k problem with early vs. late binding - early binding
causing errors in some instances. Essentially, early binding is use of
a specific object type like Dim myBook as Workbook rather than late
binding as Dim myBook as Object. Unqualified references are like using
Sheets(1) instead of fully qualified references like
Application.Workbooks("myBook").Worksheets(1). At the Beginning of Time
for VBA, I wrote code that would run just fine in the editor and fail in
Excel from unqualified references. It turned out that *I* was confused
and then writing confused code.

Ummm - bugger.

I use Workbooks(constControlWB).Worksheets(constActualsWS). to refer
to worksheets. I have constants which I set up with the name of the workbook
and the name of the worksheet. Would this be late binding of early binding?

Should I change all my
DIM XXX as Workbook
to
DIM XXX as Object

The idea of using Stop to help debug is good. I use msgbox("a,b") where
a is a procedure name or number and b is a line number. This lets me
track a persistent problem during execution quickly.

Message boxes would not help in this instance cos they slow everything
down and they require user input so affect to much when executing code.

One thing to do is make sure your Win98 is clean. Removing temp files,
temp internet files is important, and defragmenting is important also.
IMO, Win98 is subject to memory leaks from all kinds of sources so
rebooting on persistent errors is important.

These computers are, in most cases, totally new installs of the OS and
apps onto new HDDs - fully patched too and defreged after they were
fully set up.

Also, make sure that virtual memory is under Windows control.

Are you sure? I used to find it more stable if Win98 did not manage the
virtual memory - I would make it 4,096 MBytes as minimum and maximum
size and that seemed to work well. But, these machines have Windows
managing it anyway.

Win98 is quite resource-bound,

Bugger - forgot about that. It is about 4 years since I did any development
work for Win98.

so resources must be kept under control
to make VBA work well since the use of objects,forms, etc, will consume
what little resource stack Win98 has, and error dialogs are often no
help in diagnosing low resources. The Office Startup application
(osa.exe) is notorious for eating resources and should be removed from
startup in Win98 - it serves no useful purpose anyway.

Consider it done.

No wallpaper,

Bugger - I like the one I put on there.

screensaver,

Have to have one - company policy as customers can see the screens and
they want to make it not too easy for them to see profit margin info.

active desktop,

Consider it done.

no more than 30 files (not folders) in the root of C (C:\),

ummmm - I think there are about 5 directories and god alone knows how
many files - will check it out.

no desktop icons,

What about MyComputer etc - the standard ones?

no temp files,

Where are the temp files you are talking about? Just the ones in the Temp
directory?

minimum startup applications,

Firewall - antivirus - that is it.

no more than 40-50 fonts,

What is standard with Win98? I did not install extra fonts but will check this
one out.

no visual effects in menus,

Bugger - will remove that one too.

will help keep VBA and Office under control by freeing resource stack. You
can run the resource monitor

What is it called? ResMon.EXE?

to see what is happening with resources
when your code runs; any color other than green can make VBA squirrely.

What do you mean "any color other than green ..."?

For VBA, always release local objects when exiting a procedure, and
public objects when no longer needed. You know there is big resource
trouble brewing if forms only partially appear.

Sorry for the long response, but I have a special dark place in my heart
for VBA in Win98.

Trust me - it was not long - long is the time I have spent trying to fix this.

PS. Keep in mind (someone correct me if wrong) that Excel2k, XP
workbooks are not downward compatible with Excel97 unless specifically
saved to Excel97 workbook format (universal format Excel2002, 2000, 97 &
95 format) . Excel97 workbooks are upward compatible. Excel97 is
somewhat better for developing "universal" VBA as a consequence.

I think many of the issues were to do with my uninstall of ExcelXP not
removing some DLLs so they were being used by Excel2k and I was not
getting problems when I tested things on my computer. On the other
machines with different DLLs (and different OSs too) they crash. It was
not until I got rid of one ExcelXP references totally that I managed to get
it to crash for me too.

Thank you so much for your response - it has given me things to look at.

Chrissy.

 

[Chrissy]

Quaoar wrote

Chrissy, your clock needs to be set to the correct time/time zone.

It is right now.

Actually I put it on tomorrow as I wanted to pretend it was tomorrow.
This application does things at the start of the day.

I need to have two system times - the one I use for testing and the real
one. It is not only a bloody nuisance to have someone post in the future,
it is very annoying to delete the old versions of a file only to remember that
as you set the date back a week that the latest version has the oldest date.

Chrissy.

 

[Chrissy]

I would REALLY REALLY love to be able to.

But, if I could do that I probably would not need help in fixing
it as I have been programming for 20+ years now.

My problem is that it will not crash when the editor is running
and I have not identified which procedure is the prob yet.
It crashes on startup and 90% of the code is run then. It is not
an auto run macro - the user decided to display the form and
initiates the code running.

Chrissy.


Jose Rojas wrote in message

 

[Howard Kaikow]

1. The only way, unless you are lucky, to have multiple versions of Office
FULLY co-exist is to install each in a separate bootable OS.

2. And, when multiple versions of Office are installed in the same bootable
OS, they must be installed in chronological order starting wit the earliest
version.

3. When building an Office app, the distributed workbook, or template in
Word, usually needs to be built with the earliest version you wish to
support and, more importantly, any references to libraries must be to the
earliest version you wish to support.

You are developing in what appears to be a non-standard environment that
does not follow the above guidelines, so it is difficult to know just what
is getting distributed.

 

[Chrissy]

Yep - you are so right.

I thought that I was using only Excel 2000 stuff but I now know I
am not.

The problem now is that I have now totally uninstalled Excel 2002 from
my computer but there are still DLLs from that version being used.
I think I have enough info to work out the issues with all the stuff people
have said here. At least now I have something to fix - before I could not
even recreate the problem.

Thanks for all the help.

Chrissy.


Howard Kaikow wrote

 

[Chrissy]

Jose Rojas wrote

Would you please add the part of the code that it make the system to crash,?

On my machine (Win2K - Office 2002 - Excel for Office 2K with possible some
Excel for Office 2002) it fails on here



PrintTrace "Mid", "GetStaffTotal"
' Gets to this point
GetBranch
End Sub


Private Sub GetBranch()

PrintTrace "Start", "GetBranch"
' Does not get to this point

Dim A1, A2, A3, A4 As Double
Dim B1, B2, B3, B4 As Double
Dim C1, C2, C3 As Double
Dim d1, d2, d3, d4 As Double
Dim intRow As Integer
Dim WS As Worksheet
Dim RNG As String
Dim DateFind As String
Dim C As Range
Dim dTargetTurnover As Double
Dim dTargetTurnoverday As Double
Dim dGPPercent As Double
Dim dGPPercentDay As Double
Dim dGP As Double
Dim dGPDay As Double
Dim dRunningCosts As Double
Dim dRunningCostsDay As Double
Dim dNP As Double
Dim dNPDay As Double
Dim dNPPercent As Double
Dim dNPPercentDay As Double
Dim intDaysInMonth As Integer
Dim intToday As Integer
Dim intDaysLeft As Integer
Dim dT1 As Double
Dim dT1_5 As Double
Dim dT2 As Double
Dim dT3 As Double

Set WS = ControlWB.Worksheets(txtSheetName(constSheetControl))





On one other machine (Win98 - Office 2K - new install)

' Gets to this point
Workbooks.Open txtSystemDir & txtDataDir & DataEntryWB, False
' Stops on this next line with a "Code Halted" error
Set WB = Workbooks(DataEntryWB)

The other places that it does this are all just after the workbook referred to
here is opened or closed. The workbook referred to does exist and it opened
ok.

The first example happens if the editor has not been opened but is not
protected. The second one happens if the editor is opened and not
protected. Both examples give a page fault if run with the code protected
and unopened.

I have set all objects to nothing within the procedure in which they are declared.
How do I release simple variables?

Is there anything else I need to do?

Chrissy.

 

[Chrissy]

You

can run the resource monitor to see what is happening with resources
when your code runs;

Where is the resource monitor?

any color other than green can make VBA squirrely.
What?


For VBA, always release local objects when exiting a procedure,

How?

Chrissy.