Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Mar 2002
    Location
    All Over, CA, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Custom VBA function to sum or count (97/2000/2002)

    This is really a plea for help in understanding how the For Each command works. I want to pass various ranges to a function, test the condition, and learn to count or sum the values that meet the condition. I know that I can use pivot tables, and filters, combinations of array formulas and db functions, but I want to learn how to build a custom function. I have read a couple of books on VBA for Excel and they don't go into enough detail in this area.

    I have included a spreadsheet and a couple of attempts at creating the functions. I see my problem is that I keep looking at a single range that is a column of data instead of looking at all the data as a big array. I just don't know how to get there.

    Any and all help, suggestions, or direction will be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom VBA function to sum or count (97/2000/2002)

    See the attached workbook. It is not necessarily the most elegant solution, and it could certainly be generalized, but it may give you some idea of how to calculate values in code.
    Attached Files Attached Files

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Custom VBA function to sum or count (97/2000/2002)

    Hans has given you your answer, but you can also use array formulas to accomplish this, for example:

    {=SUM((A2:A30="NW")*(B2:B30="Coupon")*(C2:C30="Foo d")*G2:G30)}

    array entered, that is, enter the formula without {} and then use Ctrl-Shift-Enter to tell Excel to enter it as an array formula.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Lounger
    Join Date
    Mar 2002
    Location
    All Over, CA, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom VBA function to sum or count (97/2000/2002)

    Simplicity is elegance. I like your solution.

    Since the table becomes a 2-dimensional array, it is easy to step through each record. I could have as many criteria as I can add nested if statements. I modified it so I can pass the values for the criteria as well.

    Unfortunately I learned basic back in the DOS days and sometimes the object oriented stuff throws me. I missed the array like properties for a range you used ->rngTable.Cells(intRow, 1). It seems so obvious now.

    If I want to apply this to a range that is a pivot table, is there anything special I need to watch out for?

  5. #5
    Lounger
    Join Date
    Mar 2002
    Location
    All Over, CA, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom VBA function to sum or count (97/2000/2002)

    I am of that small minority that hate array formulas. I can't help it. I think it was a bad upbringing. They seem like a hack that got left in and no one ever went back and wrote the better functions.

    Anyway, my goal is to learn VBA so I was using it as an example. I pine for the old Lotus Macro commands. Not as powerful, but everyone could use them.

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Custom VBA function to sum or count (97/2000/2002)

    In particular, I liked the ability to write "scratch macros" to do quick on the fly repetitive tasks in 1.2.3 and Symphony. Most of which I now emulate with creative uses of Find-and-Replace, but still ...

    It will be worth your while to learn array formulas, as they can do a lot of things elegantly, particularly as a substitute for =Dxxx() formulas. And now that most CPU hardware is pretty powerful, they are not as costly in recalc times as they used to be.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom VBA function to sum or count (97/2000/2002)

    If the layout of the pivot table is static (that is, you use the pivot table to present a certain view on the underlying data, and that view won't be changed), code like in my sample will work without modification.

    But if the user moves things around in the pivot table, referring to a fixed column won't work.

Posting Permissions

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