Solved

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

Posted on 2003-11-19
11
987 Views
Last Modified: 2013-12-25
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
***********************************************
0
Comment
Question by:allosyl
  • 3
  • 3
  • 2
11 Comments
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9780407
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
 

Author Comment

by:allosyl
ID: 9781764
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
 

Author Comment

by:allosyl
ID: 9781795
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
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9787308
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:allosyl
ID: 9787657
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
 
LVL 50

Accepted Solution

by:
Dave Brett earned 500 total points
ID: 9815753
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
 
LVL 50

Expert Comment

by:Dave Brett
ID: 9854751
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
 
LVL 50

Expert Comment

by:Dave Brett
ID: 9886793
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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now