Macro problem

[Will85]

I have a pretty simple macro, but when I run it and then try to run any other macro, or write a forumla in a cell my Excel, sorta, freezes.

After running the macro, if I type a formula (2+2) and hit enter, or run another macro, my excel doesn't crash, but I cant click on any cells. If I click file in the upper left hand corner it brings up a blank window (no info, new, open, save, save as etc etc, just a blank window).

When I go to close excel, it closes like normal (asks if I want to save) and when I re-open the workbook everything is fine. Additionally, that formula that I tried to enter right before it froze is there, and working.

I do use screenupdating in my macro, but it Ive never had a problem with it.

Thoughts?

 

[Joe4]

I think you would need to post your code so we can see what it is doing.
Most importantly, do you have any Event Procedure code in your workbook?
This is VBA code that is automatically triggered via some event happening (data entry, screen selection, etc).

 

[Will85]

Sub Hide_Blank_Rows()


Application.ScreenUpdating = False


Dim ary As Variant
Dim sht As Variant

With Sheets("Macro")
ary = .Range("B1", .Range("B" & Rows.Count).End(xlUp)).Value
End With
For Each sht In ary
With Sheets(CStr(sht))
.Unprotect "password"
.Range("A1:A425").AutoFilter 1, "1"
.Protect "password"
End With
Next sht
Sheets("Control Tab").Select

Application.ScreenUpdating = True
End Sub

 

[Joe4]

You missed the second part:

Most importantly, do you have any Event Procedure code in your workbook?
This is VBA code that is automatically triggered via some event happening (data entry, screen selection, etc).

 

[Will85]

None that I intended, but I am not sure how to check. I have macros that call other macros, but I dont think I have macros that auto run, for example, when a value is changed or an action is completed.

Its almost as if excel is running perfectly, its just not displaying it on the screen. At first I thought maybe it was resources, but excel isnt crashing, and when i re-open the file, its not opening a previously auto saved version as it normally would when the file crashes.

 

[Joe4]

Search the "ThisWorkbook" and all the "Sheet" modules in the VB Editor to see if any of them have any code. This is where Event Procedure code would be.

To see if it is some of your other code that may be causing issues, you can place break points near the beginning of all your macros, then repeat the actions that cause the "freeze" and see if it goes into a macro.
If it does, then you can continutally hit F8 to progress in your code one line at a time to see what it is doing and where it is hanging up.

 

[Will85]

The macro seems to run fine, once its done running I can click, scroll, select, save etc etc. The macro is not freezing the workbook, its only my actions afterwards that is causing the issue.

My macro goes through a group of sheets (Dept 1, Dept 2, Dept 3, etc) (each sheet has the 12 calendar months going across columns) and hides rows if the sum of the months is zero.

The values in each of the 12 months on these tabs is a sumif formula, that links to another corresponding tab (Calculations 1, Calculations 2, Calculations 3 etc) with corresponding 12 months.

Its on these tabs that I enter my calculations. I am just now realizing that if I dont run another macro, or dont put a calculation in the calculations tab, then the file doesnt freeze. Its only when I run another unrelated macro, or type a formula on the calculations tab (which is being referenced by the sumif formula on the dept tab) that my workbook freezes.

Is there a way to clear the memory, or simulate saving and closing the workbook then re-opening without doing so?

 

[Joe4]

It sounds like there is a ton of data and calculations going on here. Is that correct? If so, you may just be having performance lags.
What happens if you temporarily turn Calculation Mode to Manual?
Do you still experience that kind of issue if you follow the same steps?

 

[Will85]

Ill test, but it only freezes after running the macro, not before. Yes, there is a decent amount of calcs, but the workbook runs smooth prior to running the macro, and the macro does not change the number of calcs.

I am just starting to experiment with screenupdating to make my macros run faster, I feel like either I have some type of permanent screen updating going on, or the resources it takes to run the macro is somehow not being released after the macro is complete.

 

[Joe4]

I have some type of permanent screen updating going on,

That is actually the standard default behavior. So leaving it on doesn't make it any worse, that is the default state.
And that would only affect things while the macro is running.

Please try what I said regarding Manual Calcs. Excel can only track a specific amount of formula dependencies. After a certain point (like 1 million), it needs to be a complete re-calc with every change! I have seen someone hit that limit before, and it brought the performance down to painful levels. If you ever hit that limit, that is a big red flag that you are using the wrong tool for the job, you most likely have a database and should be using a database program like Access, SQL, MySQL, or Oracle.