|
|
本帖最后由 renahu 于 2014-9-27 06:54 编辑
Private Sub CommandButton5_Click()
tim = Timer
Application.ScreenUpdating = False
w = 1 '把选择区中非对应表中的单词保存到工作表中
With ListBox1
For s = 0 To .ListCount - 1
For t = 1 To UBound(Arr)
If Arr(t, 1) = .List(s, 0) Then GoTo line0
Next
Sheets("单词库").Cells(w, "H") = .List(s, 0)
Sheets("单词库").Cells(w, "I") = .List(s, 1)
w = w + 1
line0:
Next
End With
ListBox1.Clear
If TextBox2.Value = "" Then
ListBox1.List = Arr
TextBox3 = UBound(Arr)
Else
ListBox1.List = BRR
TextBox3 = UBound(BRR)
End If
With ListBox1 '把工作表中的外表词回填入选择区中
For s = 1 To w - 1
k = TextBox3.Value
.AddItem
.List(k, 0) = Sheets("单词库").Cells(s, "H")
.List(k, 1) = Sheets("单词库").Cells(s, "I")
Sheets("单词库").Cells(s, "H") = ""
Sheets("单词库").Cells(s, "I") = ""
TextBox3.Value = k + 1
Next
End With
With ListBox2
R = .ListCount - 1
For i = 0 To R
If i > R Then Exit For
If .Selected(i) Then
.Selected(i) = False
k = TextBox3.Value
For j = 0 To ListBox1.ListCount - 1 '查看是否重复
If ListBox1.List(j, 0) = .List(i, 0) Then
ListBox1.Selected(j) = True
.RemoveItem (i)
i = i - 1
R = R - 1
GoTo line1
End If
Next
k = TextBox3.Value
ListBox1.AddItem
ListBox1.List(k, 0) = .List(i, 0)
ListBox1.List(k, 1) = .List(i, 1)
ListBox1.Selected(k) = True
TextBox3.Value = k + 1
.RemoveItem (i)
i = i - 1
R = R - 1
Else
m = ListBox1.ListCount - 1
For j = 0 To m '查看是否重复
If j > m Then Exit For
If ListBox1.List(j, 0) = .List(i, 0) Then
TextBox3.Value = TextBox3.Value - 1
ListBox1.RemoveItem (j)
j = j - 1
m = m - 1
End If
Next
End If
line1:
Next
End With
TextBox4.Value = R + 1
Application.ScreenUpdating = True
MsgBox "总计用时 " & Format(Timer - tim, "0.00") & " 秒!!", 64, "提示"
End Sub
换成用字典的方法：
Private Sub CommandButton5_Click()
Dim BRR()
tim = Timer
Application.ScreenUpdating = False
w = 1 '把选择区中非对应表中的单词保存到字典中
With ListBox1
For s = 0 To .ListCount - 1
For t = 1 To UBound(Arr)
If Arr(t, 1) = .List(s, 0) Then GoTo line0
Next
D(.List(s, 0)) = .List(s, 1)
line0:
Next
End With
With ListBox2 '根据打印区的内容调整字典，打印区“选中的”字典中没有就加入字典，有的不管；“未选中的”字典中有的就删除，没有的不管
For i = 0 To .ListCount - 1
If .Selected(i) Then
If Not D.exists(.List(i, 0)) Then D(.List(i, 0)) = .List(i, 1)
Else
If D.exists(.List(i, 0)) Then D.Remove .List(i, 0)
End If
Next
End With
s1 = D.KEYS '把字典中的单词列在选择区中
t1 = D.ITEMS
ReDim BRR(1 To D.Count, 1 To 2)
For i = 0 To D.Count - 1
BRR(i + 1, 1) = s1(i)
BRR(i + 1, 2) = t1(i)
Next
With ListBox1
.Clear
.List = BRR
End With
TextBox3 = D.Count
With ListBox2 '把打印区中选择了的单词取消选择，选择区中有的要选中
R = .ListCount - 1
For i = 0 To R
If i > R Then Exit For
If .Selected(i) Then
.Selected(i) = False
For j = 0 To ListBox1.ListCount - 1 '查看是否重复
If ListBox1.List(j, 0) = .List(i, 0) Then
ListBox1.Selected(j) = True
.RemoveItem (i)
i = i - 1
R = R - 1
GoTo line1
End If
Next
End If
line1:
Next
End With
TextBox4.Value = R + 1
Application.ScreenUpdating = True
MsgBox "总计用时 " & Format(Timer - tim, "0.00") & " 秒!!", 64, "提示"
End Sub
这是用字典方法的附件（同10楼附件，含大数据量）
按表选择需要的单词打印成单词条12.rar
(168.04 KB, 下载次数: 16)
请数组或字典高手给优化一下，但要保留原有功能，不能以牺牲功能来提高速度
|
|
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2014-9-25 14:31
楼主
