On Fri, 12 Feb 2016 23:08:34 -0000, "Peter T"
wrote:


"Phrank" wrote in message
On Fri, 12 Feb 2016 16:55:00 -0000, "Peter T" wrote:


"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


Thanks Peter. The large macro is in the main workbook, and it goes
out and opens three other workbooks on our SharePoint network and
copies (i.e., updates) all of the data from one sheet in the network
file into a sheet in the main workbook. The copy code is the most
efficient I've found (see below). It doesn't add a new sheet or delete
sheets, and it does close the other workbooks. There is part of the
routine that maintains formats (row highlights) in the main workbook
to the updates.

With wksSource
.Cells.Copy wksTarget.Cells(1)
End With

It may or may not be efficent depending on all sorts of things about both
the source and target sheets and workbooks. Eg Names, CFs, formulas with
dependancies etc, and not least large unnecessary size if copying the entire
sheet.

The most efficient way if you only need the data would be
rngTarget.Value = rngSource.Value
(assumes rngTarget is sized to rngSource and rngTarget is the minimum size
that contains the data)

Though even then with a very large sheet it might be worth doing in chunks.

You might try clearing the clipboard with app.cutcopymode = false, though
probably won't make any difference.

It sounds like you could replicate your macro manually. If you haven't
already try, and compare if any difference with doing it programatically.
For the copy part, rather than the whole sheet select A1 then
Shift-Ctrl-End, Copy, then Paste

The 8 global variables are simply strings for network pathways and
filenames. And those variables are already passed between routines.

That's nothing, but any unclosed "connections" ?

The filesize is just shy of 10mb, and it doesn't bloat.

Have you (or anyone) had any experience with MS Office (Excel) 2013 or
2016? We don't recall having these issues with Office 2010.

All versions to 2013 but not 2016.

Regards,
Peter T

Thanks Peter. I actually clearcontents on the target sheet before
bringing the data over from the source sheet, so will that affect the
rngTarget.Value = rngSource.Value code?

And the only connections I have are when the other three workbooks are
opened, but they all get closed.

I don't think this is necessarily connected with Office 2016, as it
seemed to start with Office 2013. Our company just upgraded to Office
365 (with Office 2013), and when these issues started, our IT wanted
to try upgrading me to Office 2016 to see if that resolved the issues.
It didn't. I still can't help but think it has something to do with
the changes to Office 2013/2016, because as I said, we don't recall
there being these issues with Office 2010. But I'm trying to cover
all the bases. Thank you!

Frank.