字典方法添加Sheet

ning84

1. Sub deldel()
   
2.     Dim Sht As Worksheet
   
3.         Set Sht = DictAddSheet(ThisWorkbook, "A1")
   
4.         Debug.Print Sht.Name
   
5.         Set Sht = ForEachAddSheet(ThisWorkbook, "A2")
   
6.         Debug.Print Sht.Name
   
7. End Sub
   
8. 
   
9. Function DictAddSheet(Wk As Workbook, Str)
   
10.    Dim Dict As Dictionary
    
11.        Set Dict = New Dictionary
    
12.    Dim Sht As Worksheet
    
13.      For Each Sht In Wk.Sheets
    
14.          Dict(Sht.Name) = ""
    
15.      Next Sht
    
16.      If Not Dict.Exists(Str) Then
    
17.           Set AddSheet = Sheets.Add(After:=Worksheets(Worksheets.Count))
    
18.           AddSheet.Name = Str
    
19.      Else
    
20.           Set DictAddSheet = Sheets(Str)
    
21.      End If
    
22. End Function
    
23. 
    
24. Function ForEachAddSheet(Wk As Workbook, Str)
    
25.    Dim Sht As Worksheet
    
26.      For Each Sht In Wk.Sheets
    
27.         If Sht.Name = Str Then
    
28.             Set ForEachAddSheet = Sht
    
29.             Exit Function
    
30.         End If
    
31.      Next Sht
    
32.      
    
33.      Set ForEachAddSheet = Sheets.Add(After:=Worksheets(Worksheets.Count))
    
34.      ForEachAddSheet.Name = Str
    
35. End Function
    

复制代码

ynzsvt

1. Sub deldel()
   
2. Dim Sht As Worksheet
   
3. Set Sht = DictAddSheet(ThisWorkbook, "A1")
   
4. Debug.Print Sht.Name
   
5. Set Sht = ForEachAddSheet(ThisWorkbook, "A2")
   
6. Debug.Print Sht.Name
   
7. End Sub
   
8. 
   
9. Function DictAddSheet(Wk As Workbook, Str)
   
10. Dim Dict As Dictionary
    
11. Set Dict = New Dictionary
    
12. Dim Sht As Worksheet
    
13. For Each Sht In Wk.Sheets
    
14.   Dict(Sht.Name) = ""
    
15. Next Sht
    
16. If Not Dict.Exists(Str) Then '用输出的工作表变量名
    
17.   Set DictAddSheet = Sheets.Add(After:=Worksheets(Worksheets.Count))
    
18.   DictAddSheet.Name = Str
    
19. Else
    
20.   Set DictAddSheet = Sheets(Str)
    
21. End If
    
22. End Function
    
23. 
    
24. Function ForEachAddSheet(Wk As Workbook, Str)
    
25. Dim Sht As Worksheet
    
26. For Each Sht In Wk.Sheets
    
27.   If Sht.Name = Str Then
    
28.    Set ForEachAddSheet = Sht
    
29.    Exit Function
    
30.   End If
    
31. Next Sht
    
32. 
    
33. Set ForEachAddSheet = Sheets.Add(After:=Worksheets(Worksheets.Count))
    
34. ForEachAddSheet.Name = Str
    
35. End Function

复制代码