0

(first of all sorry for my english). Maybe someone experienced a similar issue and could help. I wrote some VBA code for simple tasks and put all of it in a personal workbook in the XLSTART. It's quite a small workbook, not more than 200-300 lines of code and some small tables in the worksheets of the personal that the macro takes info from. The problem is that excel is starting to freeze after copying something or in the middle of writing simple formulas. When it freezes I need to force close it via task manager. When I delete the personal workbook excel returns to work just fine so I know it has something to do with the personal. It's strange that the presence of a personal workbook in the background has that big of an effect on excel's performance. Has someone encountered this issue?

braX
  • 11,506
  • 5
  • 20
  • 33
idan hinkis
  • 1
  • Without sight of the code in question and whatever you are trying to do, I don't think we can help much since it will be all guesses. – Raymond Wu Nov 17 '21 at 08:02
  • [How To Ask](https://stackoverflow.com/help/how-to-ask) – braX Nov 17 '21 at 08:11
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. – Community Nov 19 '21 at 16:18

1 Answers1

0

This may help or not depending on what causes your excel to hang.

Try to put these lines at the top of your code:

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

and this at the bottom:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

This will make your macro more prioritized as excel will stop recalculating formulas between every cell update.

You could also take a look at this answer How to avoid using Select in Excel VBA to see if your code can be more efficient

tnavidi
  • 1,043
  • 6
  • 12
  • Thanks for your answer but Actually the problem is not with the efficiency of the code. Excel hangs while doing operations that are not related to the code (copying and pasting, select cells with the keyboard when typing formulas, ctrl+n, etc). It seems that the existence of the personal workbook somehow slows down excel. – idan hinkis Nov 17 '21 at 08:55
  • As Raymond commented above, without proper descriptions or any indication to what the private workbook contains or how it is set up, all we can do is guess. – tnavidi Nov 17 '21 at 09:33