How to I pass string arguments as a range in a function.

I want to change myfunction1 who is working as shown in myfunction2. In order to succeed, i need to pass string arguments in my function and convert it as a range argument. In order words, in want to pass a column name and and a value instead of a range containing it.

How do i do that?

**********************************************
Public Function myfunction1(mydb As Range, argu1 As String, mycriteria As Range)

myfunction1 = Application.WorksheetFunction.DSum(mydb, argu2, mycriteria)

End Function
*********************************************
Public Function myfunction2(mydb As Range, argu1 As String, argu2 As String, argu3 As String)

'HOW DO I BUILT mycriteria as a Range with string arguments collected in the function???
'Where argu2 = "name" and argu3 = "jack" as exemple

myfunction2 = Application.WorksheetFunction.DSum(mydb, argu2, mycriteria)

End Function
***********************************************
allosylAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wsteegmansCommented:
So, if I understand your question, you're asking to fake a Range with your criteria ... ?
In your Sheet, there are no rows wich holds your criteria? But, that's how DSUM works ...

So, I don't think you have to use the DSUM-function, but the SUMIF-Function

Try something like this?
SUMIF(MyNameRange,argu3,MyTotalsRange)
Where your MyNameRange is the columns with all names, and MyTotalsRange is the columns with all the totals ...

If you have more parameters for name, you can also try the sum ...

If Name = "Jack" or Name = "James" =>
SUMIF(MyNameRange,"Jack",MyTotalsRange) + SUMIF(MyNameRange,"James",MyTotalsRange)
0
allosylAuthor Commented:
I known that this solution will work but only with one criteria only. What happen if I have more that one creteria (ex. name = "jack" + age > 35 + city = "new york" etc...)

I really need to fake a range as you said.

To be more precise, here is the function in macro excel 4.0 that a try to convert in VB.
In this example, we have 3 criterias. The function can define the value of cells, define cells as criteria and execute de DBSUM() function. How come can't we do that anymore in a VB function? I'm desparate. Please HELP.

v3sumdb
=RESULTAT(17)
=ARGUMENT("considérer_record1";3)
=ARGUMENT("considérer_record2";3)
=ARGUMENT("considérer_record3";3)
=ARGUMENT("Database";8)
=ARGUMENT("nom_du_champs_record1";2)
=ARGUMENT("nom_du_champs_record2";2)
=ARGUMENT("nom_du_champs_record3";2)
=ARGUMENT("champs_à_additionner";2)
=POSER.VALEUR($B$1;nom_du_champs_record1)
=POSER.VALEUR($C$1;nom_du_champs_record2)
=POSER.VALEUR($D$1;nom_du_champs_record3)
=POSER.VALEUR($B$2;considérer_record1)
=POSER.VALEUR($C$2;considérer_record2)
=POSER.VALEUR($D$2;considérer_record3)
=POSER.VALEUR($B$3;champs_à_additionner)
=POSER.NOM("criteria";$B$1:$D$2)
=BDSOMME(Base_de_données;champs_à_additionner;Criteres)
=RETOUR(A110)
0
allosylAuthor Commented:
Here is the same macro excel 4.0 but in english. Sorry for that..

v3sumdb
=RESULT(17)
=ARGUMENT("considérer_record1";3)
=ARGUMENT("considérer_record2";3)
=ARGUMENT("considérer_record3";3)
=ARGUMENT("Database";8)
=ARGUMENT("nom_du_champs_record1";2)
=ARGUMENT("nom_du_champs_record2";2)
=ARGUMENT("nom_du_champs_record3";2)
=ARGUMENT("champs_à_additionner";2)
=SET.VALUE($B$1;nom_du_champs_record1)
=SET.VALUE($C$1;nom_du_champs_record2)
=SET.VALUE($D$1;nom_du_champs_record3)
=SET.VALUE($B$2;considérer_record1)
=SET.VALUE($C$2;considérer_record2)
=SET.VALUE($D$2;considérer_record3)
=SET.NAME("criteria";$B$1:$D$2)
=DSUM(Database;champs_à_additionner;Criteria)
=RETURN(A104)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

wsteegmansCommented:
Hi allosyl,

Did some homwork ;-) This is what I've got ...
I do it step by step ... so you can create an example Application ..

- Create first a new ExcelFile ...
  In the first WorkSheet, drop this data (first row are columnheaders)
        Tree    Height  Profit
        Apple   18      150
        Pear    12      200
        Cherry  13      15
        Apple   18      32
        Pear    11      150
        Apple   12      200
- So you have now three columns with data.
- Save it to for example C:\Temp\book1.xls
- Open MS Access and choose this menu-option: Insert -> Class Module
- You created a new Class Module
- Drop this code in the new created module:
        Option Compare Database
        Option Explicit
       
        Private appExcel As Excel.Application
        Private wrkBook As Excel.Workbook
        Private wrkBookSource As Excel.Workbook
        Private wrkSheet As Excel.Worksheet
        Private wrkSheetSource As Excel.Worksheet
        Private varCurrentColumn As Variant
        Private intMaxNumberOfRows As Integer
       
        Private Sub Class_Initialize()
            varCurrentColumn = 0
            intMaxNumberOfRows = 0
           
            Set appExcel = New Excel.Application
            Set wrkBook = appExcel.Workbooks.Add
            Set wrkSheet = wrkBook.Sheets.Add
           
            appExcel.Visible = True ' You can put this off, but it's now easy for test-reasons and debugging ...
        End Sub
       
        Private Sub Class_Terminate()
            Set wrkSheet = Nothing
            wrkBook.Close False
            appExcel.Quit
           
            Set wrkSheet = Nothing
            Set wrkBook = Nothing
            Set appExcel = Nothing
           
        End Sub
       
        Public Function GetRange() As Excel.Range
       
            Set GetRange = wrkSheet.Range(Chr(65) & "1", Chr(65 + varCurrentColumn) & intMaxNumberOfRows)
       
        End Function
       
        Public Sub AddCriteria(ColumnName As String, Values As String)
       
            Dim varCounter As Variant
            Dim arrStrings() As String
           
            varCounter = 1
       
        ' - Take a new column to store the criteria
            varCurrentColumn = varCurrentColumn + 1
           
        ' - Set the columnname (criteria field)
            wrkSheet.Cells.Item(varCounter, varCurrentColumn).Value = ColumnName
           
        ' - Set the different values for the criteria field
            arrStrings = Split(Values, ",")
            If intMaxNumberOfRows < (UBound(arrStrings) + 2) Then intMaxNumberOfRows = (UBound(arrStrings) + 2)
           
        ' - Set all CriteriaValues
            For varCounter = LBound(arrStrings) To UBound(arrStrings)
                With wrkSheet.Cells
                    .Item(CVar(varCounter + 2), varCurrentColumn).Value = arrStrings(varCounter)
                End With
            Next
       
        End Sub
       
        Public Sub OpenSourceSheet(FilePath As String)
       
            Set wrkBookSource = appExcel.Workbooks.Open(FilePath)
            Set wrkSheetSource = wrkBookSource.Sheets.Item(1)
       
        End Sub
       
        Function GetDSum(DBRange As Excel.Range, FieldName As String)
       
            GetDSum = appExcel.WorksheetFunction.DSum(DBRange, FieldName, Me.GetRange)
       
        End Function
       
        Property Get SourceWorkSheet() As Excel.Worksheet
       
            Set SourceWorkSheet = wrkSheetSource
           
        End Property
       
- Save the module and call it 'clsDBRange'
- Insert an other New Module in Access (Menu: Insert -> Module)
- Drop this code in your new module:
        Sub test()
            Dim objDBRange As clsDBRange
            Dim objExcelRange As Excel.Range
            Dim varDSum As Variant
           
            Set objDBRange = New clsDBRange
           
            objDBRange.AddCriteria "Tree", "Apple,Pear"
            objDBRange.AddCriteria "Height", "12,12"
           
            objDBRange.OpenSourceSheet "c:\temp\book1.xls"
           
            Set objExcelRange = objDBRange.SourceWorkSheet.Range("A1", "C7")
           
            varDSum = objDBRange.GetDSum(objExcelRange, "Profit")
            MsgBox varDSum
           
            Set objDBRange = Nothing
        End Sub

- Save this new created Module and call it modTestDBRange
- Just run the SubProcedure Test and see what happens ...

Hope this helps!

Like you see, I created a new class that does the stuff for me ... (just look at the code ...)
Some info about the procedure AddCriteria
For every criteria, just call this procedure, the first parameter is the fieldname (columnname), the second parameter is a comma-separated list with your criteria ... (look also at the example code ...)

What I do, to fake the range, I create just a new workbook and worksheet where are drop my criteria. When calling the DSUM, the Criteria Range comes from this new created (Temporary) ExcelSheet.

Just look and try ...

I come back if you want additional information ...

Regards!
Wouter
0
allosylAuthor Commented:
First, thanks for your hard work. As I understood, this is a MSAccess SUB but what I want is realy a EXCEL FUNCTION. In this function I want to pass a range as cells value instead of cell's reference (normal range). In the function that I have in language excel macro 4.0 (see above), that is what I do. I take these values and built a range on a hidded sheet. Can we do that in Excel VB while in a FUNCTION? I have done some test, I can do it in a SUB but not in a FUNCTION! I' am right?
0
DaveCommented:
Hi alloysl,

I just stumbled on this, its really an Excel TA question

If you want to sum a range based on multiple criteria, SUMIF will not work but you can use an array formula or SUMPRODUCT.

ie, assume data in in A1:D10

A1:A10 is name
B1:B10 is age
C1:C10 is city
D1:D10 is number

then

=SUMPRODUCT((A1:A10="Jack")*(B1:B10>35)*(C1:C10="New York")*(D1:D10))
will sum all the numbers in D that have the corresponding match in A, B & C (all matches must be met)

Let me know if you want an example spreadsheer

Cheers

Dave
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DaveCommented:
Hi Mods,

Do the points move if the question does?

I dont think they did when this one got moved, http://oldlook.experts-exchange.com/Applications/MS_Office/Excel/Q_20802091.html

Cheers

Dave
0
DaveCommented:
Hi C101,

Its no big deal, I was just curious as I didn't think the one above did.

I guess i'll know if this one is moved, as the points for this question are the only points I've got in VB Controls. Although the points movement is secondary to having the question in the best place for PAQ searches.

Thanks for your time

Cheers

Dave
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.