function to convert String to upper case

Question

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.

Well that explains a lot. At least I'm not crazy. Thanks again simoco. — , Mar 07 '14 at 19:14

score 16 · Accepted Answer · edited Mar 13 '14 at 12:59

16

Just the wrong function. You want

sInput = UCase(sInput)

Hope that helps

edited Mar 13 '14 at 12:59

Smandoli

6,919
3
49
83

answered Mar 09 '14 at 06:22

Hutch

301
2
5

Answer accepted and the title of the question changed. Hopefully that will make it easy to find for users in the future. – Mar 09 '14 at 22:03

score 0 · Answer 2 · answered May 28 '16 at 14:14

Confirm the function UCase(...) is working. Here is another example "Capitalize the first letter in the 2nd column from 2nd row till the end":

Sub UpCaseMacro()

' Declare variables
Dim OldValue As String
Dim NewValue As String
Dim FirstLetter As String
Dim i As Long

' Select values
lastRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
ActiveSheet.Range(Cells(2, 2), Cells(lastRow, 2)).Select

' Update data
For i = 2 To Selection.Rows.Count
    If Not IsEmpty(Cells(i, 2).Value) Then
        OldValue = Cells(i, 2).Value
        FirstLetter = Left(Cells(i, 2).Value, 1)
        NewValue = UCase(FirstLetter) & Right(OldValue, Len(OldValue) - 1)
        Cells(i, 2).Value = NewValue
    End If
  Next i
 End Sub

function to convert String to upper case

2 Answers2

Linked