+ Reply to Thread
Results 1 to 9 of 9

Performance For vs While

  1. #1
    Registered User
    Join Date
    02-14-2020
    Location
    Paris
    MS-Off Ver
    2016 on Mac
    Posts
    81

    Performance For vs While

    Hello All,

    Which is better for my Macros performance for running through arrays While or For ? I have the feeling since I changed my loops from While to For my Macros are more efficient in terms performance as it is taking less time to give results ? or I am totally wrong ?

    thx for the advise.

    Najwa.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,498

    Re: Performance For vs While

    It depends...

    While makes a repeated comparison for each loop which may or may not be expensive.

    While x <> y may be expensive if x or y are objects (depending on the object type).

    For i = 1 to 10000 would be inexpensive

    For Each Cell in Range("A1:A10000") would be very expensive.

    One thing that is expensive in a loop (While or For-Next) is repeated references to a cell or Range.
    Last edited by AlphaFrog; 04-15-2020 at 10:10 AM.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    02-14-2020
    Location
    Paris
    MS-Off Ver
    2016 on Mac
    Posts
    81

    Re: Performance For vs While

    Hi AlphaFrog,

    Actually while is counter and my range is can go 1000.

    While I<= X
    Comparing each cell in a range of 1000

    I think in that case it is better to use While.

    Thank you.
    Najwa.

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,498

    Re: Performance For vs While

    Quote Originally Posted by Najwa_X View Post
    Hi AlphaFrog,

    Actually while is counter and my range is can go 1000.

    While I<= X
    Comparing each cell in a range of 1000

    I think in that case it is better to use While.

    Thank you.
    Najwa.
    Comparing each cell in a range of 1000 is the bigger issue. More than While v. For. Each time you access the sheet, it is very expensive, and it increases quickly as your range size increases

    If you read the range into an array
    MyArray = Range("A1:A1000").Value

    Then loop through the Array with either While or For-Next, you would notice a significant improvement because you only access the sheet once to read the range.

  5. #5
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    6,226

    Re: Performance For vs While

    If you just want to process every cell once, you should use a For loop.
    Rory
    I drink, and I know things

  6. #6
    Registered User
    Join Date
    02-14-2020
    Location
    Paris
    MS-Off Ver
    2016 on Mac
    Posts
    81

    Re: Performance For vs While

    Hi AlphaFrog,

    Actually this what I did I read the Range into an array and started my several comparison based on a condition and splitting the array into several arrays.

    However, when my condition is not met my array value is empty and when I have to put back my array into my excel sheet I need need to delete the empty cells which is very consuming as my defined ranges goes to 1000.

    I couldn't find a way where to have a clean array when the condition is not met. The upload of the file failed. Below is the code.

    Thank you.

    Najwa.

    Please Login or Register  to view this content.
    Last edited by Najwa_X; 04-15-2020 at 11:24 AM.

  7. #7
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,498

    Re: Performance For vs While

    Have seperate row counters for the result arrays

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 04-15-2020 at 12:53 PM.

  8. #8
    Registered User
    Join Date
    02-14-2020
    Location
    Paris
    MS-Off Ver
    2016 on Mac
    Posts
    81

    Re: Performance For vs While

    No words can express my feelings !
    Thanx a lot AlphaFrog !

  9. #9
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,498

    Re: Performance For vs While

    You're welcome. Thanks for the feedback.

+ 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. Excel Performance: Tips for optimizing performance obstructions
    By dangelor in forum Tips and Tutorials
    Replies: 0
    Last Post: 08-06-2019, 06:33 PM
  2. current moth MTD performance with last year same month same days performance
    By satyanarayana in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-03-2015, 03:36 AM
  3. Performance
    By speedup in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-19-2015, 11:55 AM
  4. Replies: 4
    Last Post: 11-23-2014, 05:54 PM
  5. [SOLVED] Sum last year's year-to-date performance as we enter this year's performance...
    By LSR1011 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-02-2013, 11:31 AM
  6. ADO performance
    By rebooot in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-12-2010, 04:18 PM
  7. [SOLVED] XLL performance in NT and XP
    By fasyousaf@yahoo.co.uk in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-21-2005, 06:40 AM

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