1

I got a macro that opens 2 workbooks and make some calculations. It works perfectly on Excel 2007 32 bit.

But in Excel 365 64 bits it crashes right after opening the first workbook, with no messages errors. Excel quits directly with no warning.

After some testing, I think it fails right after asking first workbook. The code is:

Sub PROCESO(ByVal EstasHojas As String)
Dim WBSource As Workbook
Dim WBDestiny As Workbook
Dim WKSource As Worksheet
Dim WKDestiny As Worksheet
Dim WBintermedio As Workbook
Dim WKIntermedia As Worksheet
Dim Ruta As String
Dim MiMatriz As Variant
Dim MatrizCampos As Variant
Dim LR As Long
Dim LC As Long
Dim i As Long
Dim j As Long
Dim MiF As WorksheetFunction: Set MiF = WorksheetFunction
Dim FechaPrevista As Long
Dim FechaReal As Long
Dim PagoEur As Long
Dim Proveedor As Long
Dim MatrizHojas As Variant
Dim NoHayDatos As Byte
Dim STRColor As String
Dim MatrizFinal() As Variant
Dim DictFechas As Object
Dim FechaDict As Variant


RutaCostIncomes = ""
RutaCashflow = ""

Application.Calculation = xlCalculationManual

'primero total hojas
MatrizHojas = Split(EstasHojas, "||")


With Application.FileDialog(msoFileDialogFilePicker)
    .Title = "Seleccione archivo COST AND INCOMES"
    .AllowMultiSelect = False
    If .Show = False Then
        MsgBox "No se ha seleccionado ningún archivo.", vbCritical, "PROCESO ABORTADO"
        GoTo Final
    Else
        Ruta = .SelectedItems(1)
        Set WBSource = Application.Workbooks.Open(Ruta)
        DoEvents
    End If
End With

Stop

'//////////////////////////////////////añadimos primero comprobación de que cada campo sea del tipo que le corresponde.
' se crean variables solo para esta comprobación y no se usarán más
Dim HayDatosMal As Boolean
Dim WKErrores As Worksheet
Dim KK As Long
KK = 3
HayDatosMal = False



For j = 0 To UBound(MatrizHojas) - 1 Step 1
    Set WKSource = Nothing
    Set WKSource = WBSource.Worksheets(CByte(MatrizHojas(j))) 'la posición de la hoja
    LR = WKSource.Range("A" & WKSource.Rows.Count).End(xlUp).Row
    
    Dim ZZ As Long
    For ZZ = 12 To 3 Step -1
        
        Select Case ZZ
            Case 3, 4, 9, 10 'son campos de fechas
                For i = 2 To LR Step 1
                    If IsDate(WKSource.Cells(i, ThisWorkbook.Worksheets("PANEL CONTROL").Range("C" & ZZ).Value)) = False And WKSource.Cells(i, ThisWorkbook.Worksheets("PANEL CONTROL").Range("C" & ZZ).Value) <> "" Then
                        HayDatosMal = True
                        If WKErrores Is Nothing Then Set WKErrores = Application.Workbooks.Add.ActiveSheet
                        With WKErrores
                            .Range("A1").Value = "INFORME DE ERRORES ENCONTRADOS"
                            .Range("A3").Value = "HOJA"
                            .Range("B3").Value = "FILA"
                            .Range("C3").Value = "CAMPO"
                            KK = KK + 1
                            .Range("A" & KK).Value = UCase(WKSource.Name)
                            .Range("B" & KK).Value = i
                            .Range("C" & KK).Value = UCase(ThisWorkbook.Worksheets("PANEL CONTROL").Range("A" & ZZ).Value)
                        End With
                    End If
                Next i
            Case 5, 11 'tienen que ser numéricos
                For i = 2 To LR Step 1
                    If IsNumeric(WKSource.Cells(i, ThisWorkbook.Worksheets("PANEL CONTROL").Range("C" & ZZ).Value)) = False And WKSource.Cells(i, ThisWorkbook.Worksheets("PANEL CONTROL").Range("C" & ZZ).Value) <> "" Then
                        HayDatosMal = True
                        If WKErrores Is Nothing Then Set WKErrores = Application.Workbooks.Add.ActiveSheet
                        With WKErrores
                            .Range("A1").Value = "INFORME DE ERRORES ENCONTRADOS"
                            .Range("A3").Value = "HOJA"
                            .Range("B3").Value = "FILA"
                            .Range("C3").Value = "CAMPO"
                            KK = KK + 1
                            .Range("A" & KK).Value = UCase(WKSource.Name)
                            .Range("B" & KK).Value = i
                            .Range("C" & KK).Value = UCase(ThisWorkbook.Worksheets("PANEL CONTROL").Range("A" & ZZ).Value)
                        End With
                    End If
                Next i
            Case Else 'son textos o están vacíos, no hacemos nada
                DoEvents
        End Select
    Next ZZ
Next j



If HayDatosMal = True Then
    'hay que abortar proceso
    WBSource.Close False
    WKErrores.Activate
    WKErrores.Columns("A:C").EntireColumn.AutoFit
    Set WKErrores = Nothing
    MsgBox "Se cancela el proceso porque se han encontrado errores en los datos de origen. Se ha generado un informe de errores para consultar.", vbCritical, "PROCESO CANCELADO"
    GoTo Final
End If
DoEvents
'////////////////////// fin comprobación

'compruebo que los campos coincida con mis datos del configurador

MatrizCampos = ThisWorkbook.Worksheets("PANEL CONTROL").Range("A2").CurrentRegion.Value

'compruebo todas las hojas
For j = 0 To UBound(MatrizHojas) - 1 Step 1
    Set WKSource = Nothing
    Set WKSource = WBSource.Worksheets(CByte(MatrizHojas(j))) 'la posición de la hoja
    
    With WKSource
        'los campos empiezan en la fila 2 de los datos de la matriz de campos
        'comprobamos que en source estén con el mismo nombre en su posición
        For i = 2 To UBound(MatrizCampos) Step 1
            If MiF.CountIf(.Rows(1), MatrizCampos(i, 1)) = 0 Then
                'el campo no está presente. Abortamos
                MsgBox "El campo " & UCase(MatrizCampos(i, 1)) & " no está en la hoja " & WKSource.Index & " de COST AND INCOMES", vbCritical, "PROCESO ABORTADO"
                WBSource.Close False
                GoTo Final
            Else
                'compruebo que esté en su posición
                LR = MiF.Match(MatrizCampos(i, 1), .Rows(1), 0)
                If LR <> MatrizCampos(i, 3) Then
                    'no está donde marca el PANEL CONTROL
                    MsgBox "El campo " & UCase(MatrizCampos(i, 1)) & " no está en la posición que marca PANEL CONTROL en la hoja " & WKSource.Index & " de COST AND INCOMES", vbCritical, "PROCESO ABORTADO"
                    WBSource.Close False
                    GoTo Final
                End If
            End If
        Next i
    End With
Next j




Set WKSource = Nothing

'también comprobamos los campos de ingresos
MatrizCampos = ThisWorkbook.Worksheets("PANEL CONTROL").Range("A8").CurrentRegion.Value

'compruebo todas las hojas
For j = 0 To UBound(MatrizHojas) - 1 Step 1
    Set WKSource = Nothing
    Set WKSource = WBSource.Worksheets(CByte(MatrizHojas(j))) 'la posición de la hoja
    
    With WKSource
        'los campos empiezan en la fila 2 de los datos de la matriz de campos
        'comprobamos que en source estén con el mismo nombre en su posición
        For i = 2 To UBound(MatrizCampos) Step 1
            If MiF.CountIf(.Rows(1), MatrizCampos(i, 1)) = 0 Then
                'el campo no está presente. Abortamos
                MsgBox "El campo " & UCase(MatrizCampos(i, 1)) & " no está en la hoja " & WKSource.Index & " de COST AND INCOMES", vbCritical, "PROCESO ABORTADO"
                WBSource.Close False
                GoTo Final
            Else
                'compruebo que esté en su posición
                LR = MiF.Match(MatrizCampos(i, 1), .Rows(1), 0)
                If LR <> MatrizCampos(i, 3) Then
                    'no está donde marca el PANEL CONTROL
                    MsgBox "El campo " & UCase(MatrizCampos(i, 1)) & " no está en la posición que marca PANEL CONTROL en la hoja " & WKSource.Index & " de COST AND INCOMES", vbCritical, "PROCESO ABORTADO"
                    WBSource.Close False
                    GoTo Final
                End If
            End If
        Next i
    End With
Next j

Set WKSource = Nothing

MatrizCampos = ThisWorkbook.Worksheets("PANEL CONTROL").Range("A2").CurrentRegion.Value

'the code never reachs this part when it crashes

EstasHojas is just a string that contains text like 1|2|

I've read this but could not find a solution.

VBA force closes Excel 365 but works fine in Excel 2019

64-bit Excel 365 crashes, 32-bit Excel 365 works fine

Also tried adding DoEvents right after opening the workbook with no luck.

No add-ins involved at all.

Now comes the funny part. If I add a Stop command right after opening the first workbook, and then VBa stops there, I press F5 so macro keeps going, everything works perfect!

With Application.FileDialog(msoFileDialogFilePicker)
    .Title = "Seleccione archivo COST AND INCOMES"
    .AllowMultiSelect = False
    If .Show = False Then
        MsgBox "No se ha seleccionado ningún archivo.", vbCritical, "PROCESO ABORTADO"
        GoTo Final
    Else
        Ruta = .SelectedItems(1)
        Set WBSource = Application.Workbooks.Open(Ruta)
        DoEvents
    End If
End With

Stop 'this fixes everything

So if I try to execute all at once, it crashed with no errors. But if I force it to make a break and then continue, it works.

I would like to know why adding the Stop makes the code works perfectly on Eccel 365 but without it it crashed and closes Excel with no errors. Tried DoEvents as I said, but it did not help in this case.

By the way, the workbooks opened are just data in XLSX files, no other macros or events. Just this code. I can post the full code if needed but it's really long.

Thanks in advance.

Foxfire And Burns And Burns
  • 10,665
  • 2
  • 17
  • 38
  • Hi, did Excel crash at the same point if you run through the code with step-by-step (F8)? So without stop after opening the file? – Pearli Oct 26 '22 at 19:51
  • 1
    Would be useful to know what the next few lines are after `Stop` if that's where it's crashing. – Tim Williams Oct 26 '22 at 19:55
  • @Pearli If I execute the code with F8 it works too, no crash. But if I remove the stop word and execute code as usual, it crashes. – Foxfire And Burns And Burns Oct 26 '22 at 21:02
  • @TimWilliams I've edited the question and added next lines but it crash before asking the second workbook (the code ask for 2 workbooks). So whatever is causing this, it must be something around the piece of code that ask for first workbook. – Foxfire And Burns And Burns Oct 26 '22 at 21:08
  • Well, but does it crash on the line where you open the workbook? :) – Pearli Oct 26 '22 at 21:23
  • @Pearli It does not. It only crashes if there is no stop or executed as usual. If I do it step-by-step or with a stop as posted, it works perfectly. – Foxfire And Burns And Burns Oct 27 '22 at 07:41
  • And if you insert the `Stop` at a later line does it crash? If not what's the last line you can put the `Stop` at? – GWD Oct 27 '22 at 07:49
  • @GWD There is a comment in my code at the end that says `'the code never reachs this part when it crashes` because if I place a stop there, it crashes before reaching it. – Foxfire And Burns And Burns Oct 27 '22 at 08:08
  • Yes but that comment is literally at the very end of your code. All we know now is that it crashes somewhere in between these two lines, which doesn't narrow it down very much. My question was, if you place the stop a little further down in the code, for example somewhere inside the first `For` loop, does it still crash, and what's the last line you can place `Stop` at to prevent it from crashing. – GWD Oct 27 '22 at 08:32
  • @GWD I see your point. I need to make some runs and check if there is a specific line. – Foxfire And Burns And Burns Oct 27 '22 at 09:50
  • 1
    @GWD If I don't put any stop right after opening the workbook, it crashes. Mde several tests. The opened workbook has no macros or big data or complex formulas. It's just some numbers to be grabbed. – Foxfire And Burns And Burns Oct 31 '22 at 09:31
  • I see. Can you try to check what happens if you use [`Application.Wait`](https://learn.microsoft.com/de-de/office/vba/api/excel.application.wait) instead of `Stop` to pause execution for a few seconds? Then you can also try to use the Windows API function [`Sleep`](https://stackoverflow.com/a/74262120/12287457) instead of `Stop` to pause the code for a few seconds and check if that makes a difference. – GWD Oct 31 '22 at 11:06
  • 1
    @GWD Using Wait or Sleep does not prevent from crashing – Foxfire And Burns And Burns Nov 02 '22 at 09:11
  • Can you try to create a minimum reproducible example, I.e. delete parts of your code until it doesn't crash anymore and try to distill all that's necessary for the crash. Is the `Workbooks.Open` line, without any of the other stuff enough to make it crash? – GWD Nov 02 '22 at 17:20

0 Answers0