Excel Function not working

Question

I am not new to coding but I am new to Excel and since this is a new platform for me it is difficult for me to understand what the problem is.

I am trying to write my own function but whenever I debug I get the "ding" sound and since there is no error code, just a "ding" sound I have no idea what I am doing wrong. Here is the function.

Function userItems(Range)
    Set userObj = CreateObject("Scripting.Dictionary")
    For Each Cell In Range
        If Not userObj.Exists(Cell.Value) Then
            userObj.Add Cell.Value, Cell.Address
            End If
    Next Cell
userItems = userObj(1)
End Function

Added second Argument

Answer 1

You have several issues.

1. You have to declare the variable name for the function
2. You forgot to declare your dictionary object
3. Not sure what you want to do with the dictionary result, but here is a fun trick to output all keys/items into columns

hope this helps!

Sub test()

Call testFunction(Range("A1:A100"))

End Sub

and the code:

'You need to declare the variable name and type
Sub testFunction(myRange As Range)

'First need to declare the object
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
'Good practice to declare variables, otherwise they are Variants
Dim cell As Range

For Each cell In myRange
    If Not dict.Exists(cell.Value) Then
        dict.Add cell.Value, cell.Address
    End If
Next

'Do something with the result, transpose is good fun
Range("B1").Resize(dict.Count, 1).Value = WorksheetFunction.Transpose(dict.keys)
Range("C1").Resize(dict.Count, 1).Value = WorksheetFunction.Transpose(dict.items)

End Sub

I am a huge fan of the dictionary object, but be aware this code will not run on Office for Mac.