I can't get my first function-formula to work

Question

I am trying to create "my first" formula-function, but can't get it to work.
It returns an error.

First I tested the function as a standard module and rendered the output in the debug console to test the outcome. All worked well.

However I can't get the same module to work as a function

Below the function: It is called in this way =TEST(val;rng) | resulting in #value!

Function TEST(val As String, rng As Range) As String
    Dim a, b As String
    Dim cel, itm, tst, s As Range
    Dim row, l, i As Integer
    '-----------------------------------------------------
    a = ""
    b = ""
    val = "ROX.RFL.avi.Rmd.ice"
    Set rng = Range(Sheets("DGR").Cells(3, 3), Cells(3, 34))
    '-----------------------------------------------------
    For Each cel In rng.Cells
        If InStr(UCase(val), UCase(cel)) Then
            a = a & UCase(cel) & ","
            row = Sheets("DGR").Cells(Rows.Count, cel.Column).End(xlUp).row
            If row <> 3 Then
                For Each itm In Range(Sheets("DGR").Cells(4, cel.Column), Cells(row, cel.Column))
                    b = b & UCase(itm) & ","
                Next itm
            End If
        End If
    Next cel
    '-----------------------------------------------------
    For Each tst In Split(a, ",")
        If InStr(b, tst) > 0 Then TEST = tst
    Next tst
End Function

Below the tested function as a module: (this worked properly)

Sub MKDGR()
    Dim val, a, b As String
    Dim rng, cel, itm, tst, s As Range
    Dim row, l, i As Integer
    '-----------------------------------------------------
    a = ""
    b = ""
    val = "ROX.RFL.avi.Rmd.ice"
    Set rng = Range(Sheets("DGR").Cells(3, 3), Cells(3, 34))
    '-----------------------------------------------------
    For Each cel In rng.Cells
        If InStr(UCase(val), UCase(cel)) Then
            a = a & UCase(cel) & ","
            row = Sheets("DGR").Cells(Rows.Count, cel.Column).End(xlUp).row
            If row <> 3 Then
                For Each itm In Range(Sheets("DGR").Cells(4, cel.Column), Cells(row, cel.Column))
                    b = b & UCase(itm) & ","
                Next itm
            End If
        End If
    Next cel
    '-----------------------------------------------------
    For Each tst In Split(a, ",")
        If InStr(b, tst) > 0 Then Debug.Print tst
    Next tst
End Sub

Answer 1

There are lot of things which will or can go wrong. Let's address them one by one

1. Val is a reservd word in VBA. Avoid using that. Use something which is not a reserved word. For example inptS
2. Unlike Vb.Net, in VBA when you declare variables you have to explicitly declare each of them. Otherwise they are declared as Variant
3. When you are dealing with rows in Excel, use Long instead of Integer else you may get an Overflow Error
4. Use Error handling. This way the code will not break down and gracefully complete the execution and also let you know the problem if any.
5. Use Line Numbers in your code so that you can use Erl to get the line causing the error. Get MZTools Ver 3. It is free.
6. Fully qualify your objects. For example, If you don't qualify your range objects then the range object will refer to the Activesheet and the Activesheet may not be the sheet you think it is.

Now let's incorporate all the above in your code.

Code

Option Explicit

Function TEST(inptS As String, rng As Range) As String
          Dim a As String, b As String
          Dim cel As Range, itm As Range
          Dim tst As Variant
          Dim row As Long, l As Long, i As Long

          '~~> Use error handling
10            On Error GoTo Whoa

          '~~> Fully qualify your range objects
20            With Sheets("DGR")
30                For Each cel In rng.Cells
40                    If InStr(UCase(inptS), UCase(cel)) Then
50                        a = a & UCase(cel) & ","
60                        row = .Cells(.Rows.Count, cel.Column).End(xlUp).row

70                        If row <> 3 Then
80                            For Each itm In .Range(.Cells(4, cel.Column), .Cells(row, cel.Column))
90                                b = b & UCase(itm) & ","
100                           Next itm
110                       End If
120                   End If
130               Next cel
140           End With

150           For Each tst In Split(a, ",")
160               If InStr(b, tst) > 0 Then
170                   If TEST = "" Then
180                       TEST = tst
190                   Else
200                       TEST = TEST & vbNewLine & tst
210                   End If
220               End If
230           Next tst

240           Exit Function
Whoa:
250           TEST = "Unable to calculate value (" & _
          Err.Description & _
          ", Error in line " & Erl & ")"
End Function