Excel 2007 creashes unless vba code can been seen

[DeBeuz]

Hi,

I have an interresting problem that puzzles me.
In the past I have created an xlsm workbook on a laptop running XP and Office 2007.
The code runs there without a problem.

My firm wants me to migrate to a new laptop running Windows 7.
Since I also will be needing Access for my work I have Office 2007 installed there with the professional edition instead of the limited version I was running on the old laptop (e.g. Enterprise lacking Access).

Now I have a strange problem with one of my xlsm workbooks (others are running fine for the moment).
When I open my BOP_2013_convertor.xlsm workbook on my new laptop, Excel crashes.
The message shown is: "Microsoft Office Excel has stopped working". And it just offers a button to close the application (recovery did not work so far).
Sometimes also the message "Automation error" is shown, no details however.

The workbook contains a Auto_open macro calling other macro's.
All VBA code is digitally signed and protected from viewing with a password.

This is what I have tried so far:
Rename Auto_open to xAuto_open -> workbook opens
Manually start xAuto_open -> Excel crashes
I replaced the signature with another signature -> same results
I unchecked the checkbox, making vba visible for everybody -> Code runs normally
I checked the box again -> Excel crashes
Step-by-step making the vba code visible revealed that the code will crash untill the vba code is visible in the VBA editor screen.
To be clear: just making the modules visible in the vba editor giving the password needed is not sufficient.
The code needs to be visible on the screen, or the protection of the project should be lowered.

Although I'm not allowed to share the code with you, I can tell you that the code is simple and straight forward.
It opens another workbook and reads and memorizes values and their new row numbers using dictionaries.
The actual converting is not yet started before Excel crashes.

I have no clue what is going on.
Any ideas what I might try to get this solved?

 

[Michael M]

If you can't share the code then I'd suggest
1. Disable the Auto-open macro and run it manually with the other macro calls commented out.
2. The run each macro called by the Auto-open macro, again manually

hopefully that will narrow down the source of the problem to one area, but without the code it's going to be difficult to help further

 

[RoryA]

A few questions:
1. Is the new copy of Office 2007 fully patched up to date?
2. Does the code run if you start Excel in Safe Mode? (do you have any COM add-ins loaded?)
3. Which code needs to be visible on screen - that actual running code or any code in that workbook?

 

[DeBeuz]

Hi Michael and Rory,

Thanks for your quick replies.
I may not share all code, but maybe these pieces clarify things.
The xAuto_open calles 3 subs:

'============================================================================
Sub xAuto_open()                                 ' TOOL
'============================================================================
    'This macro runs when the workboo is opening.
    'It checks the access and sets up the menu.
    
    Dim Username As String
    Dim Found_user As Boolean
    Dim i As Integer

    'Add menu
    Run "Add_menu"
    Call Load_mappings
    Call Update_filenames
    
    Updrow = 0
    
End Sub

The first call is always successfull.
It did not matter if I made the second or the third line comment, either possibility lead to a crash.
I did most attempts with the second line active and third commented out.
I have put in 2 extra debug print statements in that piece of code and guess what: it runs without problems??
Here is the new code:

'-------------------------------------------------------------------------------
Sub Load_mappings()
'-------------------------------------------------------------------------------
    'Load mappings from file.
    
    Dim Crow As Long
    Dim Key As String
    Dim Mname As String
    Dim Mwb As Workbook
    Dim Val As String
    Dim Wsht As Worksheet

    Debug.Print "In Load_mappings"

    'Define the global variables to hold correct type
    Set Mapping_celems = CreateObject("Scripting.Dictionary")
    Set Mapping_plus = CreateObject("Scripting.Dictionary")
    Set Mapping_min = CreateObject("Scripting.Dictionary")
    
    'Set Mwb = Workbooks.Open(ThisWorkbook.Path & "\Mappings_2013.xlsb", UpdateLinks:=0, ReadOnly:=1)
    Mname = "\\s4nlhesyajjg002\80023-finance$\01. Actuals & Monthly Forecast\99. Standard Mappings\Mappings_2013.xlsb"
    Set Mwb = Workbooks.Open(Mname, UpdateLinks:=0, ReadOnly:=1)
    
    Notify BTN_D, "Loading Celem rows"
    Set Wsht = Mwb.Sheets("Celem")
    Crow = 2
    While Wsht.Cells(Crow, 1).Value <> ""
        Key = CStr(Wsht.Cells(Crow, 1).Value)
        Val = Wsht.Cells(Crow, 8).Value
        Mapping_celems(Key) = Val
        Crow = Crow + 1
    Wend

    Notify BTN_D, "Loading Plus/Min rows"
    Set Wsht = Mwb.Sheets("OldNewRows")
    Crow = 2
    While Wsht.Cells(Crow, 1).Value <> ""
        Key = Wsht.Cells(Crow, 1).Value
        Val = Wsht.Cells(Crow, 2).Value
        Mapping_plus(Key) = Val
        Val = Wsht.Cells(Crow, 3).Value
        Mapping_min(Key) = Val
        Crow = Crow + 1
    Wend

    Set Wsht = Nothing
    Mwb.Saved = True
    Mwb.Close
    Set Mwb = Nothing

    Debug.Print "Exit Load_mappings"

End Sub

As far as I know, all patches available are installed and I did not myself include any extra COM modules.
I do not know how to start Excel in Safe mode.
Making the code visible, it was enough just to double click on the only module included (Financial_convertor), leaving the cursor at the very first line (Option Explicit).

Why the code works when just adding 2 debug statements (and 2 blank lines), I do not understand.
Since the code is now working, the pressure is off.
I'm still interrested if this is a known problem and how to get rid of it.

Thanks,

Paul

 

[RoryA]

Is Excel already open when you open this workbook?

 

[Michael M]

As well as Rorys comment...
It appears then that the Auto-Open won't be the issue, but is this line

Run "Add_menu"

another macro ??
If so, should it also be Call, not Run.
If I understand correctly, using RUN instead of CALL for the macro can reset your variable declarations.
I've never experimented with the RUN command, but I would have thought consistency would be the best way to approach the code

 

[DeBeuz]

Hi Michael and Rory,

Thanks again for your assistance.
@Rory: no Excel is started by double clicking the spreadsheet.
@Michael: I replaced the run with call as suggested; this code is very old, but worked so far.
Also I can not explain why it does not mess up the code when the debug statements are added.

I tried the corrected code with the call instead of the run.
It works, but so does the original code that previously kept crashing ??
No clue what changed, I was working on my old laptop.

It also migth have to do with certificate handling and/or encryption (company pollicy).
Some others can activate my signed code, while others can not.

I suggest we leave this toppic for now since I'm able to work on my new laptop now and have no more information to contribute,

Thanks again. It is always a good feeling to know you are never alone when having a problem.

Regards,

Paul

 

[RoryA]

If you open the workbook from within Excel, does the crash still occur? If not, I suggest calling your code using OnTime rather than directly on open. (I generally recommend that anyway)