0

In a standard module in a .xlam add-in I have the following UDF:

Public Function SheetExists(sheetToFind As String) As Boolean
    SheetExists = False
    Dim sht As Worksheet
    For Each sht In Worksheets
        If sheetToFind = sht.Name Then
            SheetExists = True
            Exit Function
        End If
    Next sht
End Function

When I call the function with =SheetExists() in a worksheet it works fine, as do all other UDF's in the add-in. However, when I call the function from a macro in a standard module in the personal workbook, I get an error. I call it as follows:

For i = 1 To iNumberOfSheets
    If SheetExists(arrSheetList(i)) = True Then
        arrSheetExists(i) = True
    End If
Next i

I get the "Compile error: sub or function not defined" error directly on 'SheetExists' in the 2nd line.

Additionally, this hasn't given an error when I've been testing the wider macro for the last two days. It weirdly started after I created a new standard module today in the personal workbook to hold some public strings.

I haven't amended the UDF or any of the code around where I call it. I'm using Excel for Microsoft 365.

Any help would be greatly appreciated.

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
David Vinnicombe
  • 37
  • 7
  • I'm not sure it does, as my function needs a variable when I call it, and theirs doesn't appear to need one. It also doesn't explain why my method of calling no longer works, even though I'm not sure what I've done that would invalidate it. – David Vinnicombe Jul 27 '23 at 11:34
  • 2
    For code you need to set a reference to the add-in project, or use `Application.Run` - which will allow you to pass arguments, as in at least one of the answers in the linked question. – Rory Jul 27 '23 at 11:53
  • I've changed my code to include Application.Run and it works now (thank you), but I don't understand why my code worked before. – David Vinnicombe Jul 27 '23 at 12:27

0 Answers0