Help with function that crashes Excel

[Ken Loomis]

When I send my workbook to someone to run, it bombs and crashes Excel. No
warnings, No Message. No nothing.

I used this Function from Chip Peasron to determine if a file exits:

Function SheetExists(sname As String, _
Optional ByVal wb As Workbook) As Boolean

MarkSub "SheetExists"

' Chip Pearson
On Error Resume Next
If wb Is Nothing Then Set wb = ThisWorkbook
SheetExists = CBool(Len(wb.Sheets(sname).Name))
End Function
============

I call that function with this if statement:

If Not SheetExists("Oldfiles", ActiveWorkbook) Then

=============


I do not really understand the "File Exists" function, but it works fine on
my system.

Using a GetInfo routine I got from somewhere, I have been able to capture
the following information about the system where this crashes, if it helps:

Application Name 10.0
Operating system Windows (32-bit) 4.90
Application Path C:\PROGRAM FILES\OFFICEXP\OFFICE10
Library Path C:\PROGRAM FILES\OFFICEXP\OFFICE10\LIBRARY


On my system, where the program works, that GetInfo routine captures this:

Application Name 11.0
Operating system Windows (32-bit) NT 5.01
Application Path C:\Program Files\Microsoft Office\OFFICE11
Library Path C:\Program Files\Microsoft Office\OFFICE11\LIBRARY


Sorry for the bad formatting.



I hope I haven't provided too much information, but if you should need more
please let me know.

Please help me figure this out.

TIA,
Ken Loomis

 

[Tom Ogilvy]

I would check the file on your machine and remove any references that are
non-standard (create a new workbook on your machine and with it active in
the VBE, go to tools=>References - that should show you what is standard).
Do the same with your troublesome workbook as the activeworkbook (selected
in the Project explorer).

Remove any differences.
Then save the file

This is just a guess, however.

 

[Ken Loomis]

Thanks, Tom.

When I select this troublesome workbook and go to "References." I only see
the following checked:

Visual Basic for Applications
Microsoft Excel 11.0 Object Library
OLE Application
Microsoft Forms 2.0 Object Library
Microsoft 11.0 Object Library

When I create a new workbook, I see the following:

Visual Basic for Applications
Microsoft Excel 11.0 Object Library
OLE Application
Microsoft 11.0 Object Library

Since the only one missing is "Microsoft Forms 2.0 Object Library" I assume
that's because the project I am building has a form, but the new blank
worksheet doesn't.

Can you give me any other suggestions to try?

Ken

 

[STEVE BELL]

A very long time ago - this was a major issue with my workbooks.

The kinds of things that were causing the problems
1. Public variables also Dim'd in separate macros
2. Module name and macro names being the same
3. Using names for variables that are also used by Excel (when in doubt I
use F1 to see if name
exists)
Sub Save()
Dim sheets as .....
and others that I don't remember.

Compiling the workbook helps find some of these.
You just need to search for any others...

You might also try the code cleaner at this site
http://www.appspro.com/Utilities/CodeCleaner.htm

 

[Tom Ogilvy]

On the troublesome machine, clean out the temp directory and any directories
and files below the temp directory, particularly any file with an extension
of .exd

You can try what Steve suggested, particularly the code cleaner. If all
that fails, you might try rebuilding the workbook on the problem machine.

--
Regards,
Tom Ogilvy

I would check the file on your machine and remove any references that are
non-standard (create a new workbook on your machine and with it active in
the VBE, go to tools=>References - that should show you what is standard).
Do the same with your troublesome workbook as the activeworkbook (selected
in the Project explorer).

Remove any differences.
Then save the file

This is just a guess, however.

--
Regards,
Tom Ogilvy

When I send my workbook to someone to run, it bombs and crashes Excel. No
warnings, No Message. No nothing.

I used this Function from Chip Peasron to determine if a file exits:

Function SheetExists(sname As String, _
Optional ByVal wb As Workbook) As Boolean

MarkSub "SheetExists"

' Chip Pearson
On Error Resume Next
If wb Is Nothing Then Set wb = ThisWorkbook
SheetExists = CBool(Len(wb.Sheets(sname).Name))
End Function
============

I call that function with this if statement:

If Not SheetExists("Oldfiles", ActiveWorkbook) Then

=============


I do not really understand the "File Exists" function, but it works fine on
my system.

Using a GetInfo routine I got from somewhere, I have been able to capture
the following information about the system where this crashes, if it helps:

Application Name 10.0
Operating system Windows (32-bit) 4.90
Application Path C:\PROGRAM FILES\OFFICEXP\OFFICE10
Library Path C:\PROGRAM FILES\OFFICEXP\OFFICE10\LIBRARY

 

[STEVE BELL]

Thanks Tom,

Now I remember -
any directory that can be emptied should be emptied or cleaned up by
removing select items
especially
recycle bin
internet temp files
deleted items folders
mail in-boxes
too many messages in my ng folders
I was amazed at how the stuffing of these folders affected Excel...

In your workbook
too many -
font types
custom number formats
cell formats

--
steveB

Remove "AYN" from email to respond

On the troublesome machine, clean out the temp directory and any
directories
and files below the temp directory, particularly any file with an
extension
of .exd

You can try what Steve suggested, particularly the code cleaner. If all
that fails, you might try rebuilding the workbook on the problem machine.

--
Regards,
Tom Ogilvy

I would check the file on your machine and remove any references that are
non-standard (create a new workbook on your machine and with it active in
the VBE, go to tools=>References - that should show you what is
standard).
Do the same with your troublesome workbook as the activeworkbook
(selected
in the Project explorer).

Remove any differences.
Then save the file

This is just a guess, however.

 

[Ken Loomis]

Thanks, Steve & Tom.

Your suggestions had me dig a little deeper. I did what all you indicated
with no effect, so decided to add a sort of trace routine that tracks &
records the progress of my VBA. The problem is that I am in a different city
from most of the users and don't have physical access to the troublesome
computers.

What I found was that it was a different routine that bombs Excel.

I have posted another question with more specifics.

Thanks for your suggestions.

Ken

Thanks Tom,

Now I remember -
any directory that can be emptied should be emptied or cleaned up by
removing select items
especially
recycle bin
internet temp files
deleted items folders
mail in-boxes
too many messages in my ng folders
I was amazed at how the stuffing of these folders affected Excel...

In your workbook
too many -
font types
custom number formats
cell formats