+ Reply to Thread
Results 1 to 10 of 10

VBA Aggregate Function

  1. #1
    Forum Contributor Gregor y's Avatar
    Join Date
    10-24-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2010 32-Bit
    Posts
    212

    VBA Aggregate Function

    Is it possible to write an aggregate function in MS Access VBA? (I'm on 2013)

    I'm trying to do something like this function:

    Please Login or Register  to view this content.
    Except I want to be able to call it within my queries just like any of the other built in aggregate functions: Sum(), Avg(), Min(), Max(), Count(), ...
    If it'd make you feel better using my answer because of my street cred, then you can go ahead and click Add Reputation below to improve it.

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016 (preparing to update to 2021)
    Posts
    7,393

    Re: VBA Aggregate Function

    You can write a UDF (User Defined Function) and use this in the querry.

    The UDF does the same as the macro but reruns a value
    in your Query you use it like you would Sum(), you use it myAggregate()
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Forum Contributor Gregor y's Avatar
    Join Date
    10-24-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2010 32-Bit
    Posts
    212

    Re: VBA Aggregate Function

    Can you give me an example of what the arguments would be for an aggregate UDF? I understand you can use a regular UDF in a query when it acts on only one record, but my problem is how to create an aggregate result from the consolidated records of the group by clause.

  4. #4
    Forum Contributor Gregor y's Avatar
    Join Date
    10-24-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2010 32-Bit
    Posts
    212

    Re: VBA Aggregate Function

    Here's an example of the result I'm trying to accomplish:

    InvTable
    Shelf
    Bin
    Product
    100 A 30399
    100 A 50199
    100 B 10199
    100 B 40699
    100 B 50599
    200 A 51599
    200 B 51299
    200 C 50799
    200 C 50899
    300 A 61699
    300 A 61799

    Please Login or Register  to view this content.
    BinListQuery
    Shelf
    Bin
    ProductList
    100 A 30399, 50199
    100 B 10199, 40699, 50599
    200 A 51599
    200 B 51299
    200 C 50799, 50899
    300 A 61699, 61799

  5. #5
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016 (preparing to update to 2021)
    Posts
    7,393

    Re: VBA Aggregate Function

    The UDF would require the Invtable as input and return an array as the result.
    I would create an array with three elements and then loop thought testing if the combination Shelf and Bin are already present.
    I will see if I can do something for you, I have an idea how I would approach it but it is difficult to explain.
    Don't hold your breath, it might take a little too long

  6. #6
    Forum Contributor Gregor y's Avatar
    Join Date
    10-24-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2010 32-Bit
    Posts
    212

    Re: VBA Aggregate Function

    Don't go to any great trouble on this one example, my question is more "Is it possible to write an Aggregate UDF?", as there are a hand full of them I'd like to make for general use. But it kinda sounds like it's just not possible.

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016 (preparing to update to 2021)
    Posts
    7,393

    Re: VBA Aggregate Function

    It is possible and if you sue a table like your example then the UDF would should accept the table as input and export the array.
    Processing the results would be interpreting the array returned.

    I will put it together using Excel so that I can explain it easier the UDF can the be used in Access since VBA can be used in all Office Apps.

  8. #8
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016 (preparing to update to 2021)
    Posts
    7,393

    Re: VBA Aggregate Function

    I put together a small sample, there is more code than necessary but the AggregateModule returns the array using the contents of the Invtable as source.
    It servers to give you the idea how the UDF (Aggregate) can be written.
    Attached Files Attached Files

  9. #9
    Forum Contributor Gregor y's Avatar
    Join Date
    10-24-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2010 32-Bit
    Posts
    212

    Re: VBA Aggregate Function

    Not bad, although if I must go that route here's my MS-Access VBA solution:


    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Last edited by Gregor y; 02-26-2017 at 08:32 PM. Reason: Fix _tst to match thread example

  10. #10
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016 (preparing to update to 2021)
    Posts
    7,393

    Re: VBA Aggregate Function

    But does this access solution work? If so, what is it you require?
    If not and you want something else then I suggest you attach a copy of the access db, makes it easier to really see what you have and what you require.

+ 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. [SOLVED] Aggregate function is not woring
    By shukla.ankur281190 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-19-2016, 07:15 AM
  2. Function AGGREGATE and FREQUENCY
    By XLalbania in forum Excel General
    Replies: 9
    Last Post: 02-22-2016, 05:11 PM
  3. [SOLVED] AGGREGATE Function
    By chief_abound in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-03-2015, 12:48 AM
  4. [SOLVED] AGGREGATE function, If 0 show no value
    By antexity in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-07-2015, 11:59 AM
  5. Aggregate Function Using Sum
    By gtbaseball7 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-01-2015, 02:19 PM
  6. Aggregate Function
    By nav505 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-04-2013, 03:20 AM
  7. Aggregate function
    By stefantem in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-19-2006, 04:47 AM

Tags for this Thread

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