Results 1 to 15 of 15
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Increasing memory performance of Excel without moving to 64-bit Office

    I would like to know whether there is a way to optimise the memory performance of Office 2013 without upgrading to Office 64 Bit. I have 16 GB of Ram and use Windows 10 64 bit. The processor is 2.30GHZ

  2. #2
    5 Star Lounger petesmst's Avatar
    Join Date
    Dec 2009
    Location
    Cape Town, South Africa
    Posts
    789
    Thanks
    38
    Thanked 43 Times in 33 Posts
    What makes you think your system is "too slow"? Do you have particularly large and complex spreadsheets and/or are running spreadsheets with many complex macros?
    (My Setup: Custom built: 3,70GHz Intel Core i7-4820K CPU; MSI Military Class iii X79A-GD45 Plus Motherboard; Win 10 Pro (64 bit) - (UEFI-booted); 16GB RAM; 512GB SAMSUNG SD850 PRO SSD; 120GB SAMSUNG 840 SSD; Seagate 2TB Barracuda SATA6G HDD; GeForceGTX 980 4GB Graphics Card; Office 2013 Prof (32-bit); MS Project 2013 (32-bit); Acronis TI 2015 Premium, NIS 2016, VMWare Workstation12 Pro, etc). WD My Book 3 1TB USB External Backup Drive). Samsung 24" Curved HD Monitor.

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks for the reply. My system is fast, but I often run out of resources when running a macro to open several files and then run macros within those files

    I am looking for a way to prevent this from happening

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Howard,

    See this MS Article as a start.

    Do the files have to be open at the same time? If not open then one at a time to save resources.

    When you close a file if you have assigned it to an object variable do you clear the object e.g. Set objectname = Nothing.

    Are you using global or module level variables? If so look at eliminating them by passing arguments to subroutines and functions instead.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. The Following User Says Thank You to RetiredGeek For This Useful Post:

    HowardC (2016-02-19)

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Howard,

    Here's an even better MS Article on solving your problem.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks for your input and advise RG

  8. #7
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,396
    Thanks
    445
    Thanked 404 Times in 376 Posts
    Quote Originally Posted by RetiredGeek View Post
    When you close a file if you have assigned it to an object variable do you clear the object e.g. Set objectname = Nothing.
    I once had to troubleshoot a program which would crash at random times. You never knew how far it would go before it would crash, but it would never successfully finish.

    I found that there was a subroutine in the program which would allocate memory and then do its processing, but it would never free up the memory it allocated. Hence, the program used up all available memory and then crashed. I added a line of code in the subroutine to free up the allocated memory after it did its processing, and the program no longer crashed.

    That was in C, and this is in VB, but it sounds like the same concept.

  9. #8
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks for your input

    Hopefully one of the loungers will be able to provide with a line of code to free up the allocated memory after the processing has been done

    Please post your code so that one of the loungers proficient in VBA, will be able to adapt your code for VBA purposes

  10. #9
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,396
    Thanks
    445
    Thanked 404 Times in 376 Posts
    Quote Originally Posted by HowardC View Post
    Please post your code so that one of the loungers proficient in VBA, will be able to adapt your code for VBA purposes
    That was ages ago; I don't remember the exact coding.

    As RetiredGeek described in post #4, the idea is to do good housekeeping in your program: if you create something, remove it when you are done with it; if you allocate memory, free it up when you are done with it. Also, limit the scope of things to only where they are needed: don't make things active for the entire program, but just in the areas that they are needed. Anyone proficient in VB will know how to do these things. I don't know VB, or I'd take a stab at it.

  11. #10
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    I am going to upgrade to Office 2013 64 Bit and will advise if this resolves my problem

  12. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FYI, setting objects to Nothing doesn't really achieve anything unless you are using Public variables. You can't control when/if garbage collection occurs and the references to local variables will be removed automatically when the relevant code finishes.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #12
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    I have upgraded to Office 64 bit today on my Laptop and have had many files open and ran several macros , without running out of memory resources

    I will test for a few weeks, before upgrading to Office 64 on my work PC

    Will keep you informed

  14. #13
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Office 2013 64 bit has been great as one can run macro on large workbooks without any memory issue. 64 Bit office can use more the max memory on the system as opposed to 2GB max on 32 bit office

    The only downside is that the majority of add-ins only can used on 32 bit office

  15. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Howard

    ..perhaps your issue is more to do with optimising workbook size rather than the 2GB memory limit.
    For example, many workbooks are 'bloated' with excessive used ranges that are not really required, poor construction of formulas, poor optimisation etc etc etc.
    You could perhaps start by saving workbooks in .xlsb format for typically 50% or more workbook size saving etc etc etc.
    ..unless you really are working with thousands of megabytes of data???? ..In which case I would use the Excel2016 version

    zeddy

  16. #15
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Delaware, US
    Posts
    1,159
    Thanks
    19
    Thanked 99 Times in 88 Posts
    Quote Originally Posted by HowardC View Post
    I am going to upgrade to Office 2013 64 Bit and will advise if this resolves my problem
    Unless you have a lot of memory, it probably won't make any difference. Microsoft even recommends the 32bit version for 64bit machines as a general rule.

    https://support.office.com/en-us/art...E-6C6F49B8D261
    Graham Smith
    DataSmith, Delaware
    "For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •