Search code examples
excelvbaformula

Function in excel contains unrecognized text from a code I made in microsoft VBA

I used a function in visual basic for applications and it worked previously but not anymore (first time I did this in excel). Here is the code:

Function CheckForFloodInRange(rng As Range) As Integer
    Dim cell As Range
    Dim found As Boolean
    found = False
    
    For Each cell In rng
        If InStr(1, cell.Value, "flood", vbTextCompare) > 0 Then
            found = True
            Exit For
        End If
    Next cell
    
    If found Then
        CheckForFloodInRange = 1
    Else
        CheckForFloodInRange = 0
    End If
End Function

The code is supposed to filter texts on the word 'flood' over several columns, but it gives the error instead:

enter image description here

And this is what the VBA loos like:

enter image description here

In the end I would also like to be able to save the code to this specific excel file, but for now it would be great if I can apply the function again. Let me know if anything is unclear. Thanks!

Solution

  • #NAME indicates that Excel cannot find the function. There might be different reasons for that (function is Private, typo in the name), but in your case it's a different reason: You cannot call a function as UDF that is defined in a worksheet or workbook module.

    Simply create a new standard module (according to your screenshot, you don't have one yet) and put the function into that module.