1

I have a question relating to the differences between the 32 bit and 64 bit versions of Excel and VBA.

I am working in Office 365, 64 bit. (I am using 64 bit because I ran into memory issues and as a test, to see if 64 bit works for our company). I am working on an Excel file which has a lot of VBA code used to gather product properties from SQL, calculate parameters from these properties, and output everything to another SQL database. In addition, the Excel file outputs PDF files.

The Excel file is stored as a template, so that users can open the template and save the new file to their respective workspace. At the moment, the users are using Office 365, 32 bit.

When I open the Excel template, Excel closes itself. It seems to crash before calculating the parameters on the new file. There is no error message; in the lower-right message box I can see that Excel doesn't get to the calculating stage.

However, when the other users open the file in 32-bit Excel, everything is fine.

I am not using pointers (no PtrSafe) and no LongLong variables. I also have no compile errors. I am currently rebuilding the Excel file by saving it as a file without macro's, and then copying the VBA modules - a different site mentioned the VBA code may be corrupt. I am halfway through adding macro's, and the error pops up again; the VBA code I have added last has been in use for about a year so I am tempted to exclude it as a culprit.

Where would you suggest I look to find the problem?

braX
  • 11,506
  • 5
  • 20
  • 33
Bjorn
  • 51
  • 2
  • Re "I am tempted to exclude it as a culprit" - why? If adding it causes Excel crash, then it is possible culprit (or it conflicts somehow with some other, already added module). If you add modules in different oreder and crash occurs after adding some other module, then likely said module is not guilty however. – Arvo May 25 '20 at 10:04

1 Answers1

0

You're absolutely right of course. I have gone back to the last working version and began adding a text logfile, which would log the start and end of each procedure. I got lucky as the file crashed as I was doing this, and the logfile pointed me towards a procedure seemed to be working, until the right conditions were met for it to crash.

The procedure itself accesses global variables which were not initialised. I didn't catch errors that could occur properly. For some reason, this works fine in x86 Excel, doesn't trigger any problems in the compiler or during debugging but crashes in x64 Excel when enough memory is used.

Gekozen_Assen is the name of a global variable. It is a dictionary which contains information. I had tried to use .Exists as a method to test if the required item in the dictionary exists, but this doesn't work if the dictionary isn't initialized at all.

The errorous code:

    GetFromGlobal = ""
    If Global_Dictionary.Exists(Answer_Type) Then
        GetFromGlobal = Global_Dictionary.Item(Answer_Type)
    Else
        GetFromGlobal = "Error!"
    End If

Instead I should've been using "Is Nothing", the fixed code below:

    If Global_Dictionary Is Nothing Then
        GetFromGlobal = "Error: Dictionary not initialized"
    Else
        If Global_Dictionary.Exists(Answer_Type) Then
            GetFromGlobal = Global_Dictionary.Item(Answer_Type)
        Else
            GetFromGlobal = "Error: Dictionary item not found"
        End If
    End If
Bjorn
  • 51
  • 2