You are here:

Excel/Run macro consecutively

Advertisement


Question
Dear Damon,

Thanks for your prompt respond on my last question, the answer you provided was
  For i = 1 To 3
     Worksheets(i).Calculate
  Next i

I used it in my workbook as below:
  Dim WS_Count As Integer
    WS_Count = ActiveWorkbook.Worksheets.Count
    For i = 3 To WS_Count
      Worksheets(i).Calculate
      SendKeys "^%s"
    Next i
And calculating in my workbook means sendkeys #Alt+Ctrl+s# which is the hot keys of my reporting tools.

The issue now is: excel calculated one sheet only from my whole workbook then it's stoped. without calculating the rest of sheets.

Is there a way to keep excel calculating all sheets within my workbook one by one ? #because if I run it for all sheets together, excel will hang#.
In another word, is there a way fo force excel execute #Alt + Ctrl + s# for each sheet separately #but do it for all sheet at the end#.

Thanks in Advance

Answer
Hi again Mahmoud,

Yes, adding the SendKeys does not help because it only calculates the currently active worksheet, not worksheet(i), and it is always preferable to use the VBA method rather than SendKeys.  Your other modifications to the code should be just fine.

When I tested it with a simple set of worksheets it worked fine.  The only reason I can think of for it not working for you would be if the sheets must be calculated in a particular order (that is different from the tab order of the sheets, which is what my code does) in order to work properly.  If this is the case then let me know the necessary order and I'll rewrite the code to force the correct order.

Also, there is a reason why it hangs when you do the calculations all at once, and this indicates that the calculation order IS necessary to having the sheets calculate properly.

Damon

PS.  I just thought of another reason it may not be working.  If your worksheets retrieve data from a web data feed (you mentioned "reporting tools") that returns control to the calling program before returning its data this would definitely cause such a problem. The only solution I can think of for this possible problem would be to introduce a wait statement in the code to provide time for the results to be returned, or to loop in the code until the desired data becomes available.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Damon Ostrander

Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

Education/Credentials
B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 About.com. All rights reserved.