Go Premium for a chance to win a PS4. Enter to Win


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

Posted on 2003-11-19
Medium Priority
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..

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


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 2000 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, http://oldlook.experts-exchange.com/Applications/MS_Office/Excel/Q_20802091.html


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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

824 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