+ Reply to Thread
Results 1 to 7 of 7

Writing own SUMIF in VBA

  1. #1
    Registered User
    Join Date
    09-17-2009
    Location
    Russia, St. Petersburgh
    MS-Off Ver
    Excel 2003/2007
    Posts
    62

    Writing own SUMIF in VBA

    Hi xl gurus

    Actually I know how to use SUMIF function both in excel spreadsheet and in VBA.
    I would like to know how it was written.
    For example I can create something like VLOOKUP in VBA. But I cannot find a way to write SUMIF2 code which will do the same thing.

    I actually was looping through all cells, but as I found later, it's not a good idea, because when I selected whole range E:E, UDF was running almost 1 minute. But when we use built-in SUMIF function it makes everything in milliseconds. On the assumption of that, I think that built-in SUMIF code doesn't loop through all selected cells..

    So How was this done???

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Writing own SUMIF in VBA

    Hello bambino_32,

    I'm guessing you have never taken a computer science course or done low level programming, correct?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    09-17-2009
    Location
    Russia, St. Petersburgh
    MS-Off Ver
    Excel 2003/2007
    Posts
    62

    Re: Writing own SUMIF in VBA

    Hi Leith Ross
    Thank you for quick response.
    Yes have never attended any computer courses and have very basic knowledge in programming.
    Why?
    Is it very silly Question?????

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,672

    Re: Writing own SUMIF in VBA

    If you were writing your own SumIf function, you would axiomatically intersect the formula ranges with the used range on the worksheet, and not process an entire column unless necessary.
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Writing own SUMIF in VBA

    Hello bambino_32,

    Not a silly question. Knowing your level of understanding helps others when answering your question.

  6. #6
    Registered User
    Join Date
    09-17-2009
    Location
    Russia, St. Petersburgh
    MS-Off Ver
    Excel 2003/2007
    Posts
    62

    Re: Writing own SUMIF in VBA

    Quote Originally Posted by shg View Post
    If you were writing your own SumIf function, you would axiomatically intersect the formula ranges with the used range on the worksheet, and not process an entire column unless necessary.
    Can I find similar code from somewhere...????

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Writing own SUMIF in VBA

    Quote Originally Posted by bambino_32
    ...when we use built-in SUMIF function it makes everything in milliseconds. On the assumption of that, I think that built-in SUMIF code doesn't loop through all selected cells.
    My understanding is such that the same is true of nearly all worksheet functions with the most notable exceptions being Arrays and SUMPRODUCT
    which is why (like your own approach) these are so much more expensive in terms of performance

    As shg has already outlined the first step to replicating SUMIF (without using SUMIF!) would be to limit the range appropriately.

    The next might be to load those values into a VBA Array and process via the Array rather than via cell iteration ?

    Another approach might be to use an Evaluate array along the lines of a basic test, pseudo-terms

    Please Login or Register  to view this content.
    EDIT: the above is obviously flawed in terms of handling wildcards, non-equal operators etc... it's meant purely as a basic demo.
    Last edited by DonkeyOte; 05-04-2010 at 06:51 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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