VBA Range methods not working from function call

Question

I am trying to develop a custom VBA function that manipulates ranges, namely to remove duplicate values from a list and place the list of unique values in a specific column of the worksheet. The reason I am using a custom function call is that I would like the unique values list to update anytime a new entry is made the original list.

I can get the code to work when I run it manually as a subroutine (e.g., running just the Sub RemoveDuplicateValues() segment below). However, when it is called from the custom function through the workbook (e.g., by entering =ManageLists() in a worksheet cell), the code exits without doing anything at the first instance of trying to use one of the Range methods, namely at the line NewList.Value2 = OriginalList.Value2.

Function ManageLists() As Double
    Call RemoveDuplicateValues
End Function

Sub RemoveDuplicateValues()
    Dim OriginalList, NewList As Range

    Set OriginalList = Worksheets("App 4 Input").Range("A4:A503")
    Set NewList = Worksheets("Grazing Group Manure Calculator").Range("CG4:CG503")

    NewList.Value2 = OriginalList.Value2

    NewList.RemoveDuplicates Columns:=Array(1)
End Sub

Your code seems working for me please check your sheet name **App 4 Input** and **Grazing Group Manure Calculator** maybe you did a **typo** — TourEiffel, Jul 24 '19 at 14:39
@Dorian Did you "enter =ManageLists() in a worksheet cell" as described? — GSerg, Jul 24 '19 at 14:42

3-14159265358979323846264 · Accepted Answer · 2019-07-24T14:36:53.770

You can't use a UDF in that fashion. Taken from Chip Pearson's excellent website:

A User Defined Function (or UDF) is a Function procedure that typically (but not necessarily) accepts some inputs and returns a result. A UDF can only return a value to the cell(s) whence it was called -- it must not modify the contents or formatting of any cell and must not modify the operating environment of Excel. If you attempt to change anything, the function will terminate immediately and return a #VALUE error to the calling cell.

It seems that you want to execute your function everytime a value changes on a worksheet, and so you should use the Worksheet_Change event of your App 4 Input worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    'if a value in column 1 (A:A) changes
    If Target.Column = 1 Then
        Call RemoveDuplicateValues
    End If
End Sub

VBA Range methods not working from function call

1 Answers1