当前位置: 动力学知识库 > 问答 > 编程问答 >

vba - function to convert String to upper case

问题描述:

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.

网友答案:

Just the wrong function. You want

sInput = UCase(sInput)

Hope that helps

网友答案:

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
分享给朋友:
您可能感兴趣的文章:
随机阅读: