UDF in Excel 2007 for Filter or Query

[likwidmack]

Hello, I'm new to programming in Excel but I have in VB.net. I was having
problems finding solutions in VBA and decided to try VSTO. Still lost...
Here's the problem:

I need to create a function (that can be added into a cell within the
workbook on a seperate worksheet). Our company is currently working with a
Program that uses an Excel workbook as it's data source and
formulaes/functions.

The Spreadsheet/Data table is already within the Excel workbook, what I need
is to be able to query the data or filter using criteria(es) from another
part of the workbook and then with the results of the array/filtered table
get the MIN/MAX/AVG of one specific column (which is actually being called by
the external Program, this is why I need a UDF). The query/criteria changes
multiple times in multiple areas of the workbook which will then be exported
by the external program.

I'm becoming very familiar with vba and can't figure out to how program
Excel in VB.net. HELP!!!
Thanks

 

[SmartbizAustralia]

you can do most of that with formulae.

That being said a simple vba loop can add up all the numbers whilst
doing a count and get an average by dividing these.

ALso getting a max or min based on some filter critieria is just a
loop.

Dim ws as worksheet
Dim dblTotal as double
Dim lngCount as long
Dim dblAverage as double
Dim dblMin as double
Dim dblMax as double

set ws = thisowkrbook.worksheets("my data")
dblTotal = 0
lngCount = 0
For lngRow = 1 to LastRowInSheet
if ws.cells(lngrow,1) = SomeCriteria then
dblTotal = dblTotal + ws.cells(lngrow,2)
lngCount = lngCount + 1
if dblMin > ws.cells(lngrow,2) then dblMin = ws.cells(lngrow,2)
if dblMax < ws.cells(lngrow,2) then dblMin = ws.cells(lngrow,2)
end if

next lngRow
dblAverage = dblTotal/lngCount

Regards,
Tom Bizannes
Excel Development
Sydney, Australia
Industrial Strength Excel Development
http://www.macroview.com.au

 

[likwidmack]

Thank You for your response. Unforetunately my main problem is converting
the code that I have into a function.

This is what I have and it works... as a sub procedure:

Sub FinalTest2()
'
Dim wksht As Worksheet
Dim tblResults, sMax As Variant
Dim rngData, tblRow As Range
Dim tbFilterData As ListObject
Dim rngResults As Range
Dim rngRow, rngCol As Integer
Dim vaFields As Variant
Dim vaCondition1 As Variant, vaCondition2 As Variant
Dim i As Long, r As Long
Dim cNum As Integer
Dim vaResult()
''
'Populate the arrays
vaFields = Array(16, 28, 4)
vaCondition1 = Array("6", "4", "<>")
vaCondition2 = Array("6", "6", ">4000")

Sheets("test1").Range("rngname").Select
'Set range parameters
Set wksht = ActiveWorkbook.Worksheets("test1")
Set rngData = wksht.Range("rngName")

wksht.ListObjects.Add(xlSrcRange, Range("rngName"), , xlYes). _
Name = "fltTbl"
Set tbFilterData = wksht.ListObjects("fltTbl")
'turn off Table styles
With tbFilterData
.TableStyle = ""
.ShowTableStyleRowStripes = False
.ShowTableStyleColumnStripes = False
'Use Autofilter to find criterias; field = Column#,
'[multiple] criteria(#) = Array("1","2")
i = 0
For i = 0 To UBound(vaFields, 1)
If Not IsMissing(vaCondition2) Then
.Range.AutoFilter Field:=vaFields(i), _
Criteria1:=vaCondition1(i), _
Operator:=xlOr, Criteria2:=vaCondition2(i)
Else
.Range.AutoFilter Field:=vaFields(i), _
Criteria1:=vaCondition1(i)
End If
Next i
End With

rngRow = tbFilterData.ListRows.Count
'rngCol = tbFilterData.ListColumns.Count

cNum = 18
'Set new Array for printing
Set rngResults = tbFilterData.ListColumns(cNum).DataBodyRange. _
SpecialCells(xlCellTypeVisible)

'Loop to re-number rngResults array count
ReDim vaResult(1 To rngRow, 1 To 1)
For Each tblRow In rngResults.Rows
If tblRow.EntireRow.Hidden = False Then
For r = 1 To rngRow
vaResult(r, 1) = rngResults(r, 1)
Next r
End If
Next tblRow
'Name the Array
Names.Add Name:="StoredFilterResults", _
RefersTo:=rngResults

'Test results
sMax = WorksheetFunction.Max(rngResults)
MsgBox sMax

'Convert Table back to Range & Undo Style/Filter
tbFilterData.ShowAutoFilter = False
tbFilterData.Unlist

End Sub

------------------------------------------------------------------------

But I am unable to convert it into a Public Function. The end user will NOT
be able make any changes in VBA. They have to be able to call a function and
input the filter criteria within the worksheet cell.
Please Help