vba 选择一部分 Excel 单元格（非完整单元格）并通过运行宏将其转换为大写
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19318666/
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
Select a part of Excel Cell (Not Complete Cell) and convert it to Uppercase by running a macro
提问by Kamichetty Siva
Select a part of Excel Cell (Not Complete Cell) and convert it to Uppercase by running a macro.
选择一部分 Excel 单元格（非完整单元格）并通过运行宏将其转换为大写。
I have a piece of code which will convert a selected cell or selected range of cells to Uppercase, but my requirement is, Selection will be a part (Sub String) of a cell and it should be converted to uppercase if I execute the macro.
我有一段代码可以将选定的单元格或选定的单元格范围转换为大写，但我的要求是，选择将是单元格的一部分（子字符串），如果我执行宏，它应该转换为大写。
For example, I have a cell with value "Thanks for Helping"
例如，我有一个值为“感谢帮助”的单元格
Now my code is:
现在我的代码是：
Convert_To_Upper()
Selection.value = UCase(Selection.Value)
end Sub
If user selects the cell and runs the macro, cell will be changed to "THANKS FOR HELPING"
如果用户选择单元格并运行宏，单元格将更改为“感谢帮助”
Now my requirement is..
现在我的要求是..
If user selects only "Helping" from the cell and Runs the macro cell should be changed to "Thanks for HELPING"
如果用户仅从单元格中选择“帮助”并运行宏单元格，则应将其更改为“感谢帮助”
Is it possible achieve this?
有可能实现这一目标吗？
回答by Jon Carlstedt
In short: No, it cant be done.
简而言之：不，它不能完成。
An extremely ugly hackto kind offix it is the following:
一个极其丑陋的黑客攻击，以一种固定它是以下情况：
Get the user to select the text and hit CTRL+C to copy it to clipboard, then run macro.
让用户选择文本并按 CTRL+C 将其复制到剪贴板，然后运行宏。
Pull string from clipboard to a variable (you have to add MSForms Reference)
从剪贴板拉字符串到一个变量（你必须添加 MSForms 参考）
'Clipboard -> variable S
Dim DataObj As New MSForms.DataObject
Dim S As String
DataObj.GetFromClipboard
S = DataObj.GetText
remove that from the text in the cell with some kind of diff or regex, convert it to upper and concat it with the cell.
使用某种差异或正则表达式从单元格中的文本中删除它，将其转换为 upper 并将其与单元格连接。
It gets it done, but it is not exactly best practice...
它完成了，但这并不是最佳实践......
回答by mucio
try this:
尝试这个：
Private Sub test()
' n is the number of letter you want to have to upper case
n = 2
Cells(1, 1) = UCase(Left(Cells(1, 1), n)) + Mid(Cells(1, 1), n + 1)
End Sub
UCase(string) = transform a string to upper case
UCase(string) = 将字符串转换为大写
Left(string, int) = take the first int characters of string starting from the left side
Left(string, int) = 从左边开始取字符串的第一个 int 字符
Cells(x,y) = returns a Cell in position (x,y)
Cells(x,y) = 返回位置 (x,y) 的 Cell
Mid(string, int) = returns the content of a string starting from the character in position int
Mid(string, int) = 返回从 int 位置的字符开始的字符串内容
回答by Jon Carlstedt
You can pick part of the string in a cell with MID
您可以使用 MID 在单元格中选择部分字符串
Dim var As String
var = Mid(ActiveCell.Text, 1, 10)
Mid(string, start, number of characters you want)
Mid(string, start, 你想要的字符数)
EDIT:
编辑：
So, the user needs to select how many characters to change:
因此，用户需要选择要更改的字符数：
'Throw up a popup box
numbVar= InputBox(Prompt:="Please input how many characters to turn into UPPER", _
Title:="Anything goes here", Default:="Enter number here")
' Exit if cancel is pressed
If numbVar= "" Then Exit Sub
then put numbVar instead of 10
然后把 numbVar 而不是 10
