Hi Harish, What I am thinking on this that you are putting all of the execution work on the event of closing of the workbook. Keeping this thing in mind, can you try and look forward to move some of the execution(s) before closing of the workbook. Might be like change of some values or happenning of some other events may be..!!
Also workbook size and presence of the formula in the entire workbook is also a reason for slowness. If you have the same formula in the entire row or column or the range, then you can do one thing..-> Keep the formula in one row or column only, which can be used later to formulate everything and for rest of the formulas you can do - copy -> paste special values. This will also reduce the considerable calculation time. Thanks, -- DILIP KUMAR PANDEY MBA-HR,B COM(Hons.),BCA Mobile: +91 9810929744 dilipan...@gmail.com dilipan...@yahoo.com New Delhi - 110062 On Fri, Jun 5, 2009 at 12:38 PM, Harish Bhati <bhatibablu.ms...@gmail.com>wrote: > Hi all, > > Thanks for reply.it still doesn't work for me. > > Dilip I wrote following formula in Workbook_BeforeSave() > Application.ScreenUpdating = False > Application.Calculation = xlCalculationManual > Application.EnableEvents = False > after writing these formula in Workbook_BeforeSave() ,I tried to save the > file.It took same time as previous time in saving. > > Dave I pressed f9 to check recalculation time.It was very time (less than > one sec).I also disabled option "recalculation before saving"(Go to > Tools->options->calculation),then i tried to save the excel file.it took > same time as previous time in saving. > > I still don't know the reason for which it takes so much time in saving > > Thanks and regards, > Harish > > > > On Thu, Jun 4, 2009 at 8:53 PM, Dilip Pandey <dilipan...@gmail.com> wrote: > >> Hi Harish >> >> If this is related to excel VBA code, then you can use following which I >> use generally to fasten the execution of the code. >> >> Speed up code and stop screen flickering: >> ========================================== >> Sub NoScreenRePainting() >> Application.ScreenUpdating=False >> 'Your code here. >> Application.ScreenUpdating=True >> End Sub >> >> Preventing calculation while executing code: >> =================================================== >> Sub NoCalculations() >> Application.Calculation = xlCalculationManual >> 'Your code here. >> Application.Calculation = xlCalculationAutomatic >> End Sub >> >> Speeding up code if you have Worksheet or Workbook Events. Also stops >> endless loops in Events >> >> =========================================================================================== >> Sub StopAllEvents() >> Application.EnableEvents = False >> 'Your code here. >> Application.EnableEvents = True >> End Sub >> >> Thanks & Regards, >> Dilipandey >> -- >> DILIP KUMAR PANDEY >> MBA-HR,B COM(Hons.),BCA >> Mobile: +91 9810929744 >> dilipan...@gmail.com >> dilipan...@yahoo.com >> New Delhi - 110062 >> >> >> On Thu, Jun 4, 2009 at 3:52 PM, Harish Bhati < >> bhatibablu.ms...@gmail.com> wrote: >> >>> Hey Dilip, >>> >>> Thanks for reply, but it doesn't work for me. >>> >>> I have observed that this is problem with *only *this excel >>> file.I tried on *different system*(more than 10 system), same problem >>> occurs.I also tried to save this excel file when no other file was being >>> used,but same problem was occurred.I concluded that it is not a system >>> problem. >>> This excel file has to be distributed widely.So i need a >>> solution which works on any system. >>> >>> I think this problem because of some variable which is used in >>> macro, but i don't know which variables/methods create this problem. >>> >>> >>> Thanks, >>> Harish >>> >>> >>> >>> >>> On Thu, Jun 4, 2009 at 2:14 PM, Dilip Pandey <dilipan...@gmail.com>wrote: >>> >>>> Hi Harish, >>>> >>>> Check if this problem occurs only with Excel. If not, then go to Task >>>> Manager (Alt + Ctrl + Del) and check how many programs are running in >>>> background and how much is the memory consumption by them. Now you can >>>> control them as per your needs. >>>> For windows, Go to run and type "msconfig" and now you can control the >>>> programs to run automatically or to run in background >>>> >>>> Other way could be, check your startup folder and remove the un-wanted >>>> programs from there. >>>> If you find issues with this, then Alternatively, you can go to run and >>>> type "regedit" and search that program and remove it from the machine. >>>> >>>> There may be other issues and then there may be other solutions as well. >>>> Let me know if you have some. >>>> >>>> Thanks,. >>>> -- >>>> DILIP KUMAR PANDEY >>>> MBA-HR,B COM(Hons.),BCA >>>> Mobile: +91 9810929744 >>>> dilipan...@gmail.com >>>> dilipan...@yahoo.com >>>> New Delhi - 110062 >>>> >>>> On Tue, Jun 2, 2009 at 5:19 PM, Harish <bhatibablu.ms...@gmail.com>wrote: >>>> >>>>> >>>>> I have a 2 MB workbook containing multiple sheets, formulas. A toolbar >>>>> have been also implemented and status bar also is being used in VBA >>>>> code. >>>>> >>>>> My problem is that I try to save the excel file, it takes too much >>>>> time(around 40 sec) in saving. >>>>> Machine also hang-ups when this excel file is being saved. >>>>> >>>>> Initially I thought that Workbook_BeforeSave() takes much time,but >>>>> after analyzing I came to know that this function also consumes very >>>>> less time(around 1 sec). >>>>> >>>>> Please help me to solve this issue. >>>>> >>>>> Thanks in advance, >>>>> Harish >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>> >>> >>> >> >> >> >> >> > > > > --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
