vba 防止 Range.Find() 更改“匹配整个单元格内容”用户界面设置
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19283546/
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
Prevent Range.Find() from changing the "match entire cell content" user interface setting
提问by stenci
After running the first line of the following code, Excel changes the Match entire cell contentssetting in the user interface. The result is that the next time the user presses Ctrl+Fthe search is done in the whole cell content. I don't like a VBA macro that changes a setting that affects the user experience, so I always execute the second line, which performs another search and sets the Match entire cell contentsback to its default.
运行以下代码的第一行后，Excel 会更改用户界面中的匹配整个单元格内容设置。结果是，下次用户按下Ctrl+F 时，将在整个单元格内容中进行搜索。我不喜欢改变影响用户体验的设置的 VBA 宏，所以我总是执行第二行，它执行另一个搜索并将匹配整个单元格内容设置回其默认值。
'execute the find and changes the entire cell content setting
Set C = MyRange.Find(SomeText, LookAt:=xlWhole)
'put the setting back to the default value (not the previous value)
MyRange.SomeText ParName, LookAt:=xlPart
The problem is that it sets it back to the default, not to the value the user set it last.
问题是它将它设置回默认值，而不是用户最后设置的值。
I would rather have something like:
我宁愿有类似的东西：
'store the current entire cell content setting
OldLookAt = Application.FindLookAt
'execute the find
Set C = MyRange.Find(SomeText, LookAt:=xlWhole)
'restore the original setting
Application.FindLookAt = OldLookAt
I made up the Application.FindLookAtbecause I wasn't able to find it.
我编的，Application.FindLookAt因为我找不到它。
Is there a way to Range.Findsomething without affecting the user experience?
有没有办法在Range.Find不影响用户体验的情况下做某事？
采纳答案by enderland
I spent a fair bit of time looking through the Excel object library and MSDN documentation and it doesn't seem this is possible to retrieve.
我花了很多时间浏览 Excel 对象库和 MSDN 文档，但似乎无法检索。
Unfortunately it also looks like Excel actually overrides and sets your user setting if you don't include the parameter to the default.
不幸的是，如果您不将该参数包含在默认值中，那么 Excel 似乎实际上会覆盖并设置您的用户设置。
Private Sub testXLLookAT()
'Setup fake falues
Dim mCurLookup As XlLookAt
Range("A1").value = "Test1"
Range("A2").value = "Test"
'get current setting, but this doesn't work as it defaults...
If Range("A1:A2").Find("Test").Address = "A1" Then
mCurLookup = xlPart
Else
mCurLookup = xlWhole
End If
'your find here
'note - mCurLookup always becomes xlWhole...
Range("A1:A2").Find What:="Test", LookAt:=mCurLookup
End Sub
Otherwise, the above code would work - but it doesn't as the default gets overwritten even when it's not included.
否则，上面的代码会起作用 - 但它不会因为默认值被覆盖，即使它没有被包含在内。
回答by Jerry Sullivan
Here's a function that can be used to return the current LookAt default value so it may be reset.
这是一个可用于返回当前 LookAt 默认值的函数，因此它可以被重置。
Function lGetCurLookAt() As Long
'--returns current default value for Range.Find method's
' LookAt parameter.
'--use helper cell after all data in col A
With Cells(Rows.Count, "A").End(xlUp)(3)
.Value = "TestPart"
lGetCurLookAt = IIf( _
.Find(What:="Part", SearchFormat:=False) Is Nothing, _
xlWhole, xlPart)
.Value = vbNullString
End With
End Function
My first post, so I'm not able to add comment to enderland's answer. In spirit of being helpful, that code didn't work as expected because it used A1 instead of $A$1.
我的第一篇文章，所以我无法在 enderland 的答案中添加评论。本着乐于助人的精神，该代码没有按预期工作，因为它使用 A1 而不是 $A$1。
