使用 VBA 转换为句子大小写
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10978560/
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
Converting to sentence case using VBA
提问by seegoon
I've been trawling through page after page on Google and here looking for a solution to this seemingly simple request, but to no avail. Does anyone know a reliable way to convert a string to sentence case using vba?
我一直在谷歌上一页一页地浏览，在这里寻找这个看似简单的请求的解决方案，但无济于事。有谁知道使用vba将字符串转换为句子大小写的可靠方法吗？
Ideally I would build it into a sub rather than a function, so it is easier to call from the GUI.
理想情况下，我会将其构建为子而不是函数，因此从 GUI 调用更容易。
For reference, I would want:
作为参考，我想要：
HERE IS A LONG, UGLY UPPERCASE SENTENCE. PLEASE AMEND ME IMMEDIATELY.
这是一个又长又丑的大写句子。请立即修改我。
to become:
成为：
Here is a long, ugly uppercase sentence. Please amend me immediately.
这是一个又长又丑的大写句子。请立即修改我。
Converting to Title Case I found extremely simple (as there's a built-in function for that) but converting to sentence case has proven really difficult indeed.
转换为标题大小写我发现非常简单（因为有一个内置函数），但事实证明转换为句子大小写确实非常困难。
I have tried some of the following methods but come up with errors at every turn:
我尝试了以下一些方法，但每次都出现错误：
- http://www.vbforums.com/showthread.php?t=536912
- http://vbamacros.blogspot.com/2007/09/sentence-case.html
- http://www.vbforums.com/showthread.php?t=536912
- http://vbamacros.blogspot.com/2007/09/sentence-case.html
How can I get this to work?
我怎样才能让它工作？
回答by brettdj
You could use a RegExp to more efficiently run the parsing
您可以使用 RegExp 更有效地运行解析
Something like this
像这样的东西
Sub Tested()
Call ProperCaps("HERE IS A LONG, UGLY UPPERCASE SENTENCE. PLEASE AMEND ME IMMEDIATELY." & vbCrLf & "next line! now")
End Sub
Function ProperCaps(strIn As String) As String
Dim objRegex As Object
Dim objRegMC As Object
Dim objRegM As Object
Set objRegex = CreateObject("vbscript.regexp")
strIn = LCase$(strIn)
With objRegex
.Global = True
.ignoreCase = True
.Pattern = "(^|[\.\?\!\r\t]\s?)([a-z])"
If .test(strIn) Then
Set objRegMC = .Execute(strIn)
For Each objRegM In objRegMC
Mid$(strIn, objRegM.firstindex + 1, objRegM.Length) = UCase$(objRegM)
Next
End If
MsgBox strIn
End With
End Function
回答by Oli 'Albatraous' Newton
Thanks for this, useful bit of code. Why VB has proper case and not sentence case is very strange. I have tweaked it for my purpose, as the original won't capitalise the first letter if there is a space in front of it, hope you don't mind me sharing my few changes.
感谢这个，有用的代码。为什么 VB 有适当的大小写而不是句子大小写很奇怪。我已经根据我的目的对它进行了调整，因为如果前面有空格，原文不会大写第一个字母，希望你不介意我分享我的一些变化。
To remove any unwanted spaces at the start or end of the sentence, I have added another function that is called from the above.
为了删除句子开头或结尾的任何不需要的空格，我添加了另一个从上面调用的函数。
Public Function DblTrim(vString As String) As String
Dim tempString As String
tempString = vString
Do Until Left(tempString, 1) <> " "
tempString = LTrim(tempString)
Loop
Do Until Right(tempString, 1) <> " "
tempString = RTrim(tempString)
Loop
DblTrim = tempString
End Function
Public Function ProperCaps(strIn As String) As String
Dim objRegex As Object
Dim objRegMC As Object
Dim objRegM As Object
Set objRegex = CreateObject("vbscript.regexp")
strIn = DblTrim(strIn)
strIn = LCase$(strIn)
With objRegex
.Global = True
.ignoreCase = True
.Pattern = "(^|[\.\?\!\r\t]\s?)([a-z])"
If .test(strIn) Then
Set objRegMC = .Execute(strIn)
For Each objRegM In objRegMC
Mid$(strIn, objRegM.firstindex + 1, objRegM.Length) = UCase$(objRegM)
Next
End If
ProperCaps = strIn
End With
End Function
You can call ProperCaps(Yourstring) to get the sentence back with the first letter as a capital, with all spaces removed.
您可以调用 ProperCaps(Yourstring) 以将第一个字母作为大写字母取回句子，并删除所有空格。
You can also use DblTrim(Yourstring) to remove all spaces at the front and back of the string (without altering the sentence case), regardless of how many spaces there are.
您还可以使用 DblTrim(Yourstring) 删除字符串前后的所有空格（不改变句子大小写），无论有多少空格。
