Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

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

Posted on 2003-11-19
Medium Priority
1,002 Views
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
Question by:allosyl
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 3
• 2

LVL 7

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

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)
=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.NOM("criteria";\$B\$1:\$D\$2)
=RETOUR(A110)
0

Author Comment

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)
=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)
=RETURN(A104)
0

LVL 7

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

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

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

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

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

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

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.

Cheers

Dave
0

## Featured Post

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) 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…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
###### Suggested Courses
Course of the Month11 days, 2 hours left to enroll