vba 将字符串转换为大写的函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22258729/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
function to convert String to upper case
提问by Hutch
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:
我一直在尝试使用String.ToUpper()VBA 中的方法使我编写的用户定义函数以全部大写形式返回它的值。当我尝试在 excel 中使用我的 UDF 时，我收到一个编译器错误，它只是突出显示了我的 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.
如果这段代码被注释掉，我的 UDF 可以工作，但不会在所有 Upper 中返回输入的字符串。
回答by Hutch
Just the wrong function. You want
只是功能错误。你要
sInput = UCase(sInput)
Hope that helps
希望有帮助
回答by Andrii
Confirm the function UCase(...) is working. Here is another example "Capitalize the first letter in the 2nd column from 2nd row till the end":
确认函数 UCase(...) 正在工作。这是另一个示例“从第 2 行到末尾将第 2 列中的第一个字母大写”：
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
