+ Reply to Thread
Results 1 to 8 of 8

Pausing formula in name manager

  1. #1
    Forum Contributor
    Join Date
    03-05-2015
    Location
    norwich
    MS-Off Ver
    365
    Posts
    107

    Pausing formula in name manager

    Hi,

    I have a large workbook which uses VBA to import data.

    As the workbook has grown through time into a frankenstein the speed at which this import happens has fallen dramatically (10 seconds taking nearly 10 minutes). My old method of fixing this would be to use VBA to convert CPU/memory intensive formulas into values which alwas seemed to work. The workbook is still fast (or appears so) because of using values, but when VBA is running it's very slow...

    I think this because there are so many graphs in dashboards which use the name manager to be "dynamic" that when VBA is running this is slowing it down. I think the because If I go into the name manager and delete everything then it's as quick as ever.

    My question is - can you disable everything in the name manager while VBA is running and then turn it back on after? I can't be sure but I'd be confident this would solve my problem.

    Thanks for your time

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,607

    Re: Pausing formula in name manager

    How about disabling recalculations while macro imports data

    something like:
    Please Login or Register  to view this content.
    Best Regards,

    Kaper

  3. #3
    Forum Contributor
    Join Date
    03-05-2015
    Location
    norwich
    MS-Off Ver
    365
    Posts
    107

    Re: Pausing formula in name manager

    Hey Kaper,

    Thanks for the reply. It's still slow using your code. Is your method any different to having:

    application.calculation = xlmanual

    I already have that in the code

    Best regards,
    Sean

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,607

    Re: Pausing formula in name manager

    No, it's the same, but how could I know that you already have it ?
    Of course you could try to delete the names in name manager (or change them from "formula based" to "range based") form before starting the main part of the code and restore after the import is done.

  5. #5
    Forum Contributor
    Join Date
    03-05-2015
    Location
    norwich
    MS-Off Ver
    365
    Posts
    107

    Re: Pausing formula in name manager

    Sorry Kaper I meant no offence, I wasn't too clear in my explaination

    So you can change the formulas in the name manager in VBA? That would probably solve it. Deleting them isn't an option for me

  6. #6
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,607

    Re: Pausing formula in name manager

    So you can display in immediate window (but the same will be to store in a string variable) a name in such a way:
    Please Login or Register  to view this content.
    you shall see the formula it refers to
    then you can set a new reference like:
    Please Login or Register  to view this content.
    and after importing use the same way to restore it

    If your names have a workbook-wide scope then ommit Worksheets() part.

    No, no offence. I know it's not easy to give just enough details (too few - there will be too obvious answers, too many -> TLDR)
    A yellow box at the top of this page gives quite useful (often) hint

  7. #7
    Forum Contributor
    Join Date
    03-05-2015
    Location
    norwich
    MS-Off Ver
    365
    Posts
    107

    Re: Pausing formula in name manager

    This is genius!!

    I've only just got enough time at work to try this out. Works an absolute charm! Thank you Kaper

  8. #8
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,607

    Re: Pausing formula in name manager

    Glad to hear it worked for you.

    Thanks for marking thread solved and for appreciation

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Writing formula using Names from Name Manager
    By GregM56 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-18-2019, 09:30 PM
  2. Name manager for formula
    By nikko027 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-20-2019, 05:57 AM
  3. Formula in Name Manager
    By Imbizile in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-24-2017, 08:12 AM
  4. [SOLVED] How to fix the offset formula in Name Manager
    By Jenny_Truong in forum Excel General
    Replies: 5
    Last Post: 08-10-2016, 03:23 AM
  5. sales manager & purchase manager sheets command button error
    By rana19 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 05-24-2016, 07:26 AM
  6. [SOLVED] Creating a formula in Name Manager
    By BobBlooms in forum Excel General
    Replies: 8
    Last Post: 12-15-2014, 04:42 PM
  7. [SOLVED] How to refresh Formula in Name Manager?
    By Bobbii in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-15-2014, 05:29 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1