Can my custom application be an Excel add-in? Are there any restrictions to the type of application I can use the NumXL SDK with?

No. There is no restriction on the type of the application you are using NumXL SDK with be it utility, server, add-in or specialized SDK/API.