"Phrank" wrote in message
...
Hi,

I'm running Office 2016 on Windows 7 (64 bit OS with 8gb RAM), and
I've got a fairly large VBA macro that I run for my departments
workload management tool. I've got 8 global variables, a main module,
and 7 other sub-routines are called as the overall macro is processed.
We've noticed, though, that after the macro runs, Excel is not very
responsive, and oftentimes the dropdown submenus from the ribbon are
not functional. If the workbook is closed and reopened, then
everything works fine again.

I stepped through the macro with the Locals window open to see what
happened with my variables, and they all get cleared. And at the end
of the project I clear the global variables.

Does anyone have any thoughts as to what may be going on here? I
think I've provided the necessary info; please let me know if you need
more.

Thank you.

Without knowing what your what your macro does can only guess, what does a
"large" macro mean?

You say closing the workbook restores resources, did the macro do anything
that added to the size of this workbook in memory, usedrange or formats
perhaps. Add a new sheet to the workbook, then delete the others one at a
time, checking memory each time (give it a while each time), then close the
workbook.

Those 8 global variables, what are they? Simple data variables are trivial,
but big arrays or collections, or objects possibly with circular references
might not be. FWIW if those variables are only required for the duration of
the macro, chances are most of them can be removed from module level and
passed between routines.

Regards,
Peter T