I think the question was to provide a template for converting a VBA function to SQL. The short answer is that there isn't one template that will be best the approach. It depends on what you want the function to accomplish. If this is a part of an exercise where you're converting the whole application from Access to SQL Server, then take a long look at your functions and try to get some economy of scale by coding similar functions in a single one and return multiple values in a table. You don't want to code the same thing 30 times in 30 different scalar functions.

Also, having 150 scalar UDFs sounds like a recipe for disaster. UDFs suffer a performance problem when compared to ITVFs, but nothing everything is suited to an ITVF. Again, it all depends on what you're trying to do.