不用遍历工作表，直接替换整个工作簿区域

perfect131

        例子：https://club.excelhome.net/threa ... tml?_dsign=7596f606

        用 Application.Dialogs(xlDialogFormulaFind).Show , 2, 2 再结合 vbs 就能实现 整个工作簿区域 替换 （不遍历工作表）

perfect131

不用 vbs ，用 API 异步 关闭对话框

1. #If VBA7 Then
   
2.     Private Declare PtrSafe Function SetTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As Long
   
3.     Private Declare PtrSafe Function KillTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal uIDEvent As Long) As Long
   
4.     Private Declare PtrSafe Sub keybd_event Lib "user32" (ByVal bVk As Byte, _
   
5.                                                           ByVal bScan As Byte, _
   
6.                                                           ByVal dwFlags As Long, _
   
7.                                                           ByVal dwExtraInfo As LongPtr)
   
8. #Else
   
9.     Private Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
   
10.     Private Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal uIDEvent As Long) As Long
    
11.     Private Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
    
12. #End If
    
13. Const KEYEVENTF_KEYUP = &H2
    
14. Const VK_ESCAPE = &H1B
    
15. Sub 一键整个工作簿替换()
    
16.     '''异步关闭窗口
    
17.     Call ClosePrintPreview(Close_In_HowMany_Seconds_FromNow:=0) '<== Close PPreview in 0 sconds - Change argument value to suit
    
18.     Application.Dialogs(xlDialogFormulaFind).Show , 2, 2
    
19.     ThisWorkbook.Activate
    
20.     '''被替换内容 What  ，，新内容 Replacement
    
21.     Cells.Replace What:="bbb", Replacement:="aaa", LookAt:=xlPart, SearchOrder _
    
22.         :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    
23.     MsgBox "已全部替换！！！"
    
24. End Sub
    
25. Private Sub ClosePrintPreview(ByVal Close_In_HowMany_Seconds_FromNow As Long)
    
26.     Call SetTimer(Application.hwnd, 0, Abs(Close_In_HowMany_Seconds_FromNow) * 1000, AddressOf CloseNow)
    
27.     '按下 ESC
    
28.     keybd_event VK_ESCAPE, 0, 0, 0
    
29.     '释放 ESC
    
30.     keybd_event VK_ESCAPE, 0, KEYEVENTF_KEYUP, 0
    
31. End Sub
    
32. Private Sub CloseNow()
    
33.     Call KillTimer(Application.hwnd, 0)
    
34. End Sub

复制代码