vasupfeel.blogg.se - My excel keeps crashing when recording a macro

MY EXCEL KEEPS CRASHING WHEN RECORDING A MACRO UPDATE
MY EXCEL KEEPS CRASHING WHEN RECORDING A MACRO CODE

'Change 10000 to maximum number of rows if > 10000 rowsįor Each Cell2 In Worksheets("COF").Range("A2:A10000")ĭo While businessday = False And shift < 6 'Finds the closest business date to a lease date, solving for weekendsįor Each Cell1 In Worksheets("Sheet1").Range("B2:B10000") Unfortunately, Excel completely freezes every time I try to run the macro and I have to force quit, without an error message or anything. The contents in the cells are formatted identically as dates. So if a date on the general list is not on the business dates list, my macro should try to find the closest business date to the date on the general list and assign it to the column to the right. in a network share environment) it should now open without any macro-enable dialog.I'm writing a macro that compares a general list of dates (Sheet1) with a list of "business dates" where the office is open(COF). Now you should be able to open it without crashing (since obviously the initial compile run on startup prevents the crash somehow).įor others using the file (e.g.

MY EXCEL KEEPS CRASHING WHEN RECORDING A MACRO CODE

prevents it from beeing saved accidentially with compiled code causing the crash on next open again.

may be only useful if updates are infrequent.

optional: write protect file on file-level.

(=> the code, although only changed on comment-level, will be internally marked as uncompiled and will be recompiled on next open with enabled macros).

if already existing: just change anything there, e.g.

MY EXCEL KEEPS CRASHING WHEN RECORDING A MACRO UPDATE

Write or update some only-existing comment line inside, e.g. open foo2.xlsm and do not enable macros.foo.xlsm to foo2.xlsm (untrusted name/file) Our final 6 to 8 step solution is quite simple since it seems to be a bug with the runtime on compiled macro code workbooks that have been saved and opened anew: as if it slowly internally crashes before it will disappear automatically (also from task manager) (sometimes without the immediate auto-restart dialog). When one debugs/inspects the variables, strange things happen (other objects shown than expected, visual sections of excel elements get completely white/broken). Our problem occurs at definite, always reproducible code locations. So it must be some pretty bad compiled-code processing bug. (see solution below if you don't care about this :) )įor us it happened in code that compiles and runs successful in Excel 2010, 20. I experienced today that this can also resolve "Can't find project or library" crashes where no reference is actually missing and Excel crashes before you can even get to the reference dialog in the first place. Close Excel, re-open the previously crashing workbook, and Enable Macros.Revert any changes you made to Macro Security in steps 3-5.Save the VB project again and then save the Excel workbook.In the VB Editor, save the project and then click Debug > Compile VBAProject.Do not click "Enable Macros" - instead go to the Developer Tab and.Go to Trusted Documents and check the “Disable all Trusted Documents” box.

Go to the Trusted Locations tab on the left and check the “Disable all Trusted Locations” box at the bottom.Click the bullet option entitled “Disable all macros with notification”.Go to the Developer Tab and click “Macro Security” in the Code section of the Ribbon.Open a new Excel workbook, not your crashing workbook.To fix it, use the following steps (all credit to alulla at Ozgrid, whose directions I've lightly adapted for clarity): This is caused by a macro with a compile error.