PDA

View Full Version : User Defined Function not working when entered in Excel



mp3909
09-06-2016, 02:44 PM
I attempted creating my own function that is supposed to filter data and then copy and paste it in the cell where I have typed the function.
When I step through the code it works fine.
When I type the function in a cell it returns 0 which is wrong.
I don't know what's wrong.
I have attached the excel file.
Any ideas please?
Thank You.


Option Explicit
Function GetData()
Dim wksheet As Worksheet
Set wksheet = ThisWorkbook.ActiveSheet
Dim rng As Range
Set rng = ActiveCell
Worksheets("Data").Range("GetResults").AutoFilter field:=4, Criteria1:="BMW"
Worksheets("Data").Cells.SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants).C opy Destination:=wksheet.Range(Split(rng.Address, "$")(1) & rng.Row)
End Function

SamT
09-06-2016, 03:18 PM
Function GetData() returns? Nothing, of course.

Example Functions that return something

Function GetData() As Long
GetData = 42
End Function

Function Add2And2() As Integer
Add2and2 = 2 + 2
End Function

Function SheetList() As Variant
Dim ShtNames as String
Dim Sht As Worksheet

For Each sht in Worksheets
Shtnames = ShtNames & sht.Name & ";"
Next Sht

ShtNames = Left(shtNames, Len(ShtNames) - 1) 'remove ending semicolon

SheetList = Split(ShtNames, ";") 'Convert string to array
End Function

But I don't think any Function Procedure is going to accomplish what you want. A Sub Procedure probably can.

Paul_Hossler
09-06-2016, 04:02 PM
I attempted creating my own function that is supposed to filter data and then copy and paste it in the cell where I have typed the function


Functions, including UDFs, can only return values; they can't manipulate the worksheet. Like you can't have a UDF color a cell based on something

BTW, congrats on using Code Tags and attaching a sample workbook :thumb

mp3909
09-07-2016, 12:24 PM
I attempted creating my own function that is supposed to filter data and then copy and paste it in the cell where I have typed the function


Functions, including UDFs, can only return values; they can't manipulate the worksheet. Like you can't have a UDF color a cell based on something

BTW, congrats on using Code Tags and attaching a sample workbook :thumb

There must be a way where a UDF should be able to return data say from cells A1 to D2.

offthelip
09-07-2016, 02:56 PM
Tell us a bit more about your problem, when are trying to filter and copy the data, what triggers the copying? Are the criteria for the filter always the same.

If your code works when you step through it, change it from a function to a subroutine and then work on how to trigger the subroutine.
e.g can you put something into one of the workbooks events which will trigger the subroutini automatically?

mikerickson
09-07-2016, 04:02 PM
One of the issues is that if a UDF uses .SpecialCells, it will fail when called from a worksheet formula.
Another is that the posted UDF tries to apply a filter and copy and paste cells. These are other things that will work when called by VBA, but fail when called from a worksheet formula.

SamT
09-07-2016, 04:15 PM
There must be a way where a UDF should be able to return data say from cells A1 to D2.
A UDF can return any data that one cell on a spreadsheet can contain. That is the purpose of a UDF.

For example here is a UDF that returns the sum of SumIfs from many different sheets

Public Function SumIfAllSheets(MatchRange As String, _
Criteria As Range, _
SumRange As String) _
As Double

Dim i As Long
Dim Temp As Double
Dim WSF As Object

Temp = 0

On Error Resume Next
If CostSheets(0) = "" Then
Initialize_CostSheets
End If
On Error GoTo 0

Set WSF = Application.WorksheetFunction

For i = LBound(CostSheets) To UBound(CostSheets)
With Sheets(CostSheets(i))
Temp = Temp + WSF.SumIf(.Range(MatchRange), Criteria, Range(SumRange))
End With
Next i

SumIfAllSheets = Temp

End Function
And here is a formula that uses it
=SumIfAllSheets("A:A",B13,"F:F")

That formula appears in over a hundred cells in column E on one sheet where only the row number in the second parameter changes