Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Excel application terminates abruptly while running a macro.

0

Hi,

Attached is the macro that is built to create a report.The run time is about 30 mins.But during the code execution, excel automatically closes all the workbooks abruptly. Looks like this issue is related to cache/buffer since I can see a clipboard pop-up appearing on the right bottom corner showing 16 of 24- Clipboard in the middle of execution. I'm assuming once the clipboard is filled up, the macro terminates and closes the application. I had once entountered with an error message that says "the picture is too large and will be truncated". The clipboard content is a list of 'no preview available' 

I found some solution on the internet and found the code for clearing the clipboard programatically, it didn't help. Application.cutcopymode=false is not helping either.

Any suggestion would be appreciated.

Thanks

Answer
Discuss

Discussion

Dr Liss

You forgot to attach your file. Also, given the message "...the picture is too large and will be truncated", what file format is your report made in (and does it contain very large worksheets, charts or images)?
John_Ru (rep: 6092) Jan 18, '22 at 6:43 am
I have added the attachments. Yes, it creates multiple different worksheets while looping but no images or charts.
Dr Liss (rep: 26) Jan 18, '22 at 7:03 am
Thanks but you added one file only. I assume your problem occurs when running the macro Main() (assigned to the MAM Report button on the second sheet) but that won't run without the file you have saved as C:\Users\MBL\MBL 1_17.xlsx.

Not sure I have time to look as (what appears to be a compex macro) but please attach MBL 1_17.xlsx anyway.
John_Ru (rep: 6092) Jan 18, '22 at 8:23 am
Thanks John_Ru. It is a complex macro which was developed by someone else. I have attached the supporting excel file with dummy data due to privacy issue, which usually contains 52k+ records
Dr Liss (rep: 26) Jan 18, '22 at 9:16 am
Dr Liss I believe that the 2 files you provided cannot run together (there's some missing information, even though I tried to add some ifno in the DATA sheet to match the second file you sent). t fails that the line in bold (within the function BolverifyMasterData called by Main):
If .Range("I1") > 0 Then
            .UsedRange.AutoFilter Field:=3, Criteria1:="=DOJ Missing", Operator:=xlOr, Criteria2:="=Emp Data Missing"
            MsgBox "Please check Mam_Master sheet and update DOJ for all Emp of my TE Data " & vbNewLine & "And then run the macro again", vbCritical, "MAM_Master Update Requried"
            BolverifyMasterData = False
       Else
Sorry but I don't have the time to dissect the code (or determine its intention) so I can't help.
John_Ru (rep: 6092) Jan 18, '22 at 1:32 pm
Add to Discussion



Answer the Question

You must create an account to use the forum. Create an Account or Login