function to convert String to upper case

user2993456 picture user2993456 · Mar 7, 2014 · Viewed 32.6k times · Source

I have been trying to make a user defined function I wrote return it's value in all upper case, using the String.ToUpper() method in VBA. When I try to use my UDF in excel, I get a compiler error that just highlights the top line of my UDF:

Function removeSpecial(sInput As String) As String

Here is the code in it's entirety:

Function removeSpecial(sInput As String) As String
    Dim sSpecialChars As String
    Dim i As Long
    sSpecialChars = "\/:*?™""®<>|.&@# (_+`©~);-+=^$!,'" 'This is your list of characters to be removed
    For i = 1 To Len(sSpecialChars)
        sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), "")

    Next
    sInput = sInput.ToUpper()
    removeSpecial = sInput
End Function

The code works fine to remove special characters, but I would like it to also convert the inputted String to upper case.

I started receiving this error when I tried to add:

sInput = sInput.ToUpper()

If this code is commented out, my UDF works, but without returning the inputted string in all Upper.

Answer

Hutch picture Hutch · Mar 9, 2014

Just the wrong function. You want

sInput = UCase(sInput)

Hope that helps