Labels in User-Defined Function Not Working Right (First Problem)

F

fred

---------------
First Problem---
---------------

(Excel 2002 sp3)

A BIG SPREADSHEET
I've created a spreadsheet with 5000 lines and 50 columns.
The file size is 26 megs. Like most of my spreadsheets,
this one has all the formulas copied all the way down to the bottom.

EXCEL CRASHES ON 'CLOSE'
Excel gives me 'faulting module Excel.exe' when I close.
"Send report ........ etc"
This doesn't seem to happen until all my development is done and
I copy down all the formulas. It seems I can load the spreadsheet,
make changes, and save it, but when I shut Excel down I get the error.
This seems to be pretty predictable whenever I do the 'big copy down',
and doesn't happen when it's just 50 columns by 2 rows.

SOLUTIONS TRIED
In searching for a solution, I found suggestions such as:
Delete the toolbar-config XLB files,
uninstall Acrobat 5.0,
install SP3,
try it on other machines (it still happens),
empty the 'Windows temp' folder and reboot
turn off all spyware and virus software
reinstall Excel.....
nothing has worked.
If anyone else knows of solution I'd love to hear it,
but I've spent way too much time on it, and
would be happy with just a workaround. (next post)

Fred
 
F

fred

--------------
Workaround? ---
--------------

ATTEMPING WORK-AROUND INSTEAD OF A FIX
Although it seems I can edit, save, print, etc.
it still makes me nervous. I don't want to be
basing my financial results on a spreadsheet that 'errors'
every time I close it. So I thought if I shrunk it down
it might stop the error upon closing. I set up
a user defined function for each of the columns so
instead of copying 150-char formulas down 10,000 rows,
it's only taking up the userDefined function name of 10 characters.
The problem is that the the userDefined funtions don't work
with labels -- I keep getting the whole column instead of just the cell
the formula used to refer to. I can't go back to R1C1,
we need to understand the varables, so we NEED labels.

Google search indicates some sort of 'intersection problem'
but that was 4 years ago.

Has anyone dealt with this recently? Does it still not work?

thanks,
Exhausted
 
D

Dave Peterson

Just guesses...

If you copy the cells to a new worksheet in a new workbook, does it help?

If it works, maybe your workbook is getting corrupted. Recreating the worksheet
may help.

If that didn't help, maybe opening (a copy) in OpenOffice and saving it there
would clean up any problems.

(http://www.openoffice.org, a 60-65 meg download or a CD)

If neither of those helped, how about converting to values right before you
save. And adding the copy formulas to the workbook_open event?
 
F

fred

Thanks Dave,
Just guesses...

If you copy the cells to a new worksheet in a new workbook, does it help?
Click to expand...
yep, still happens. I've done that about 5 times now. I copied the top
cells to a new blank workbook and all is fine until I do the big copy down
of all the formulas. Then I start getting the 'close error.' In fact I
started deleting rows from the bottom and found that the error went away
when I got down to 2325 rows!

It's starting to look like there is some size limit I'm exceeding. Maybe
related to RAM size or Virtual Memory size. Does anyone know what Excel
does when it closes? If the file is already saved, what's all the activity
about? .... It doesn't just close immediately. I noticed that when I
crashes it doesn't save the recently-opened files in the list.

I'm assuming the error upon closing my workbook isn't affecting the
calculations in the workbook .............. I hope!

Also, I a long time ago I noticed 'auto-recover' caused a lock up, so I
turned that off. I'm not sure if that's related or not.
 
D

Dave Peterson

I got nothing!

Maybe one more question....

If you start excel in safe mode, does it help?

close excel
windows start button|run
excel /safe

(I'm not sure what my answer would be if you said yes or no, though.)
 
F

fred

most interesting, 'safe mode' for Excel....
what is it?

anyway, I still got the error when I closed :(

btw, I put an answer inline with your question
did you notice that I said the problem went away
when I had deleted enough lines off the bottom of the sheet?

It sure seem like a memory limit.
I expected safe mode might work, but it didn't.
 
T

Thuggin'

That's because in a regular formula, sitting in a cell, it has a
reference point, but in a module, Excel is too dumb to use the
reference point of the cell that's calling the module. IIRC, it was
"Application.Cell", or "Application.Selected" (or something) you had
to use in the module. I saw it somewhere but never tried it. good
luck, Clybor
 
D

Dave Peterson

I saw your message that it worked when you reduced the number of
rows--but your data didn't sound very big to me to begin with.

By chance, do you have lots of conditional formatting or objects (even
comments) in your worksheet? I may not be remembering this correctly,
but I think I had some problems when I had lots of these.

Safe mode stops excel from doing lots of things when it opens. It
disables addins, macros, and essentially opens excel in plain vanilla
mode.
 
F

fred

50 columns x 10,000 rows, 50 meg file.

no, no conditional formatting in this particular workbook

I started deleting rows from the bottom
after I had deleted enough so I had only 2325 rows left,
the error went away.

Is there any resource that would know
what Excel does when it closes?
I mean, the file is saved, what is it doing
that's getting an error? Could it be some sort
of admin thing, like closing virtual memory?
Why doesn't it just close the window and be done with it?
 
D

Dave Peterson

Excellent questions all, but I don't have an answer for any of them.

When you loaded it in Safe mode, I figured that all the things that excel does
special would be disabled. But it still blew up!

(yeah, no help at all. Sorry.)
 
F

fred

Well, I'm still using it, even though
I can't shut it down without a crash.
I can still save, edit, undo, etc. ..........
I guess I'll just have to live with the crashing.

I thank you for giving it a shot anyway.

Fred
 
Top