DSUM - Modifying the function...





I am not sure if everyone knows the DSUM function but I am trying to modify
this so the criteria and the field names are in two separate ranges.



The Syntax of the DSUM is



DSUM( table , column , criteria ) where the table is the range of all data
elements, column is the field you will like to sum and the criteria is the
range (including the field names in the table) you will like to define for
the criteria.



Criteria is where I want to change the function so the label and the
Criteria is in two different ranges. In order to define the criteria you
will first have to have the label and under the label you must define your
criteria.



Well - here is my question, Is there anyway I can combine the two arrays to
one array and send that in to the DSUM function?



This is the function I am trying to create.



Function CSUM(ByVal MyDB As Variant, ByVal MyFNToSum As Variant, ByVal
MyFields As Variant, ByVal MyCriteria As Variant) As Variant



'MyDB is the range where allof the data and fields exist

'MyFNToSum is the field name you will like to sum

'MyFields is the range where the field names exist

'MyCriteria is the array that includes tthe criteria for the sum function.



Dim MyRange As Excel.Range



MyRange = Excel.Union(MyFields, MyCriteria)



MyRange = MyCriteria



CSUM = Application.WorksheetFunction.DSum(MyDB, MyFNToSum, MyRange)



End Function



No matter what I do I keep getting "#VALUE" in my excel sheet cell. It
seems like that I touch the variable excel does not like it. I thought this
was an array but it looks like an Excel.Range object. However, that still
did not work.



ANYONE - HELP!!!!!!

A possible alternative is to use sumproduct. Assuming your table is in A1:B8
and your criteria is in F3, and the values you want summed are in column B:

=SUMPRODUCT(--(A2:A8=F3),B2:B8)

Even though you unioned the two ranges, they are still non-contiguous. It
appears DSUM does not work if the criteria range is non-contiguous and , if
the DSUM function (called from an excel worksheet) doesn't work w/a
non-contigous range, there's no reason to expect it should if called via
VBA.



"DKIM" wrote:

> DSUM - Modifying the function...
>
>
>
>
>
> I am not sure if everyone knows the DSUM function but I am trying to modify
> this so the criteria and the field names are in two separate ranges.
>
>
>
> The Syntax of the DSUM is
>
>
>
> DSUM( table , column , criteria ) where the table is the range of all data
> elements, column is the field you will like to sum and the criteria is the
> range (including the field names in the table) you will like to define for
> the criteria.
>
>
>
> Criteria is where I want to change the function so the label and the
> Criteria is in two different ranges. In order to define the criteria you
> will first have to have the label and under the label you must define your
> criteria.
>
>
>
> Well - here is my question, Is there anyway I can combine the two arrays to
> one array and send that in to the DSUM function?
>
>
>
> This is the function I am trying to create.
>
>
>
> Function CSUM(ByVal MyDB As Variant, ByVal MyFNToSum As Variant, ByVal
> MyFields As Variant, ByVal MyCriteria As Variant) As Variant
>
>
>
> 'MyDB is the range where allof the data and fields exist
>
> 'MyFNToSum is the field name you will like to sum
>
> 'MyFields is the range where the field names exist
>
> 'MyCriteria is the array that includes tthe criteria for the sum function.
>
>
>
> Dim MyRange As Excel.Range
>
>
>
> MyRange = Excel.Union(MyFields, MyCriteria)
>
>
>
> MyRange = MyCriteria
>
>
>
> CSUM = Application.WorksheetFunction.DSum(MyDB, MyFNToSum, MyRange)
>
>
>
> End Function
>
>
>
> No matter what I do I keep getting "#VALUE" in my excel sheet cell. It
> seems like that I touch the variable excel does not like it. I thought this
> was an array but it looks like an Excel.Range object. However, that still
> did not work.
>
>
>
> ANYONE - HELP!!!!!!
>
>
>
>
>
>
>