Conflict in Code Crashing Excel when Sheets Deleted

[JungleJme]

I have a macro that cycles through sheets in a workbook and deletes them. However, excel crashes on the Delete command and i don't know how to fix it.

I have an Index page (excluded from the delete process) at the start of the workbook.

This index page is basically a table of contents. It lists all the sheets in the workbook, the sheet name, and the value of a specific cell in the sheet. This is done using UDFs. However, the UDFs use the Sheets object of the workbook so when my other macro goes through the delete process i think these UDFs are crashing excel.

I've tried adding

Please Login or Register  to view this content.

to the start of my "delete" macro but it doesn't help. Is there anything i can do or am i going to have to rethink my solution?

Many thanks

J

The Culprits FYI:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Function TESTTITLE(IndexNumber As Integer, NamedRange As String)

Please Login or Register  to view this content.

EDIT: An example formula from my index page

Please Login or Register  to view this content.

[romperstomper]

What exactly do you mean by "crashing Excel"? If you mean the program literally crashes, rather than a simple code error, that shouldn't happen simply from that - the functions would just return errors when they recalculate.

[JungleJme]

What exactly do you mean by "crashing Excel"? If you mean the program literally crashes, rather than a simple code error, that shouldn't happen simply from that - the functions would just return errors when they recalculate.

yup. Full on crash. Asks to send an error report, asks if you want to re-open at the last autosave. Reboots the instance of Excel.

Anyway for other people's info, i fixed it (don't know how though really!)

I've taken the delete code out of the Private_Sub of the command button within the sheet and instead put it into a module and got the command button to call it.

Also, when this started happening i bookended my code with application calculation manual and then automatic. BUT, my final line was a full calculation (my thinking was to force Excel to recalculate everything)

By removing the command to recalculate everything and also putting the code in its own module the crash no longer occurs. My final line of code on the delete macro simply sets the application calculation back to automatic.

I have noticed though, that the values in the cells containing the UDFs calculate when the delete macro executes WITHOUT the command to calculatefull. So perhaps this is where i was generating a conflict...

Anyway, these steps appear to have sorted it for one reason or another!

[romperstomper]

Where was the button?

[JungleJme]

The button was in the same sheet that the UDFs were calculating on, which made me think there may be an issue there...