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!!!!!!

DKIM wrote...
DSUM - Modifying the function...
....
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?

No.

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
....
Dim MyRange As Excel.Range

MyRange = Excel.Union(MyFields, MyCriteria)

This should be throwing a runtime error. You'd need to use

Set MyRange = Excel.Union(MyFields, MyCriteria)

to *set* the range variable MyRange to the union of the two ranges.
MyRange would then refer to a 2-area range. Note that *BOTH* MyFields
*AND* MyCriteria *MUST* refer to ranges IN THE SAME WORKSHEET in order
for Union to work.

MyRange = MyCriteria

This should throw another runtime error. Again you're missing Set. Even
if you'd added Set, this would be a logic error. You already set
MyRange to the union of the ranges MyFields and MyCriteria above, and
now you've replaced that with a duplicate reference to MyCriteria.

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

And here's where everything breaks down. The criteria argument to DSUM
must be a reference to a single area range.

End Function
....

DSUM won't do what you want. You may want to read up on Microsoft's
SQL.REQUEST add-in function.