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

Posted on 2003-11-19
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
Question by:allosyl
  • 3
  • 3
  • 2

Expert Comment

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?
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)

Author Comment

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.


Author Comment

ID: 9781795
Here is the same macro excel 4.0 but in english. Sorry for that..

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.


Expert Comment

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


Author Comment

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?
LVL 50

Accepted Solution

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


=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


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,


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



Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question ( in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

830 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