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
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.
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.