[算表] VBA 暗號解碼比對抓錯運算 需提高效率

[Kamikiri (☒☒)]

軟體:OFFICE 365 EXCEL

版本:1908

目前正在編寫的功能是直接把一個工作表當成暗號表
A欄=暗號，B欄=解碼後的對應詞
另一個工作表則是雜亂無章的暗號跟解碼後的詞彙
然後要用暗號表去抓出文章內是否有出現暗號
而且是否解碼後的詞彙是否是正確的

例如
暗號表
A1=9527  B1=唐伯虎
A2=88    B2=掰掰
(下略)
解碼表
A1=pppij"9527"clizxj  B1=pppij"唐伯X"clizxj
C1=解碼偵錯：出現暗號：9527  解碼遺漏：唐伯虎

大概是這種感覺

目前編寫的代碼大致如下：
For Row1 = 2 To 10000
For Row2 = 4 To 10000

Sheets("解碼").Activate

If Sheets("暗號").Cells(Row1, 1).value <> "" Then
If Sheets("解碼").Cells(Row2, 1).value <> "" Then
Code = Sheets("暗號").Cells(Row1, 1).value '暗號
Decry = Sheets("暗號").Cells(Row1, 2).value '解碼對應詞
Decode1 = InStr(1, (Cells(Row2, 1)), Code) '判斷暗號文章是否有暗號
Decode2 = InStr(1, (Cells(Row2, 2)), Decry) '判斷解碼後是否有對應詞

If Decode1 <> 0 And Decode2 = 0 Then '若暗號文章有暗號但解碼後無對應詞
For Col = 7 To 52
If Cells(Row2, Col).value = "" Then
Cells(Row2, Col).value = "解碼遺漏：" & Decry
'在該文章列的7～52欄個別記下解碼遺漏的對應詞(因為可能一格內有多個不同暗號)

Exit For
End If
Next
End If

End If
End If

Next
Next

最後再用Textjoin把紀錄在7～52欄的遺漏紀錄整合在同一格內
然後為了方便辨識雜亂文章中的各個暗號
也去網路上找了幫特定字串上色的寫法
https://access-excel.tips/excel-vba-change-partial-text-color/
我是用Macro 1 – Change partial text color using VBA Macro這個

目前的寫法雖然可以正常運作
但速度上還是有點慢，在有600條暗號的情況下，至少也要60秒才能檢查完
不知道是否有其他方式可以加快運算的速度？

而且現在還有個問題是如果暗號中有兩個9527
但解碼後只有一個唐伯虎，這種情況也不會被判斷成解碼缺失
所以在想是不是應該寫另一個用資料剖析功能運算的版本來測試看看.....
把每個暗號個別分開，就可以連同數量不一致的也抓出來

希望有人可以提供意見，感謝

--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 123.193.189.222 (臺灣)
※ 文章網址: https://www.ptt.cc/bbs/Office/M.1570466433.A.3AB.html
→ soyoso: 1.減少巢狀迴圈次數range.end().row來取得最後一筆有值的
→ soyoso: 儲存格列號
→ soyoso: 2.只針對出現暗號的儲存格，range.find或range.autofilter
→ soyoso: 3.減少range.value的寫入，而是先寫入變數內，最後再一次
→ soyoso: 性的寫入範圍內
→ soyoso: 暗號有2個9527，只有1個唐伯虎方面(len(儲存格)-len(
→ soyoso: replace(取代9527[變數])))/len(9527[變數])，或是ubound(
→ soyoso: split())，這樣可得知字串內有出現多少次
→ soyoso: 再判斷是否次數有相符
啊  忘了修文  迴圈上限的部分我後來有修正了
這樣就不用多檢查沒有值的欄位
range.find或range.autofilter我會再研究看看
另外  寫入變數內再拿出來的做法有範例嗎？  網站也可

然後雖然可能不會用到了，但還是想問一下
range.value=值 跟 range="值"  在運算速度上是有差的嗎？
※ 編輯: Kamikiri (60.248.94.55 臺灣), 10/08/2019 10:16:46
→ soyoso: 寫入再拿出來，不確定所謂的拿出來是指？一次性寫入範圍內
→ soyoso: 嗎？如果是的話寫入變數的話，變數(索引值,索引值[二維])=
→ soyoso: 判斷內原本要寫入儲存格的值；最後迴圈都執行完畢，在
→ soyoso: range(和變數維度、個數相同的範圍）=變數 的方式寫入
→ soyoso: range.value 或 range=值方面測試寫入10次10萬資料上
→ soyoso: 二者看來是差不多的 https://i.imgur.com/ShXYHag.jpg

感謝，剛剛在看到你的推文前上網查了一下
目前只想出以下寫法(還沒寫好寫正確  但應該類似)？

Dim Array(1,52) as Variant
if 解碼有誤
Array(Row2,Col) = "解碼遺漏：" & Decry

不知道創個Dictionary寫進去之後再整批拿出來是否也相同
總之目前大致上已經有方向，應該沒問題了，感謝解答
※ 編輯: Kamikiri (60.248.94.55 臺灣), 10/08/2019 10:22:03
→ soyoso: 如非一維的話，這要看dictionary是否可產生二維以上
→ soyoso: 一維的話，一樣range= dictionary.keys的方式
→ soyoso: dictionary.key寫入列的話，應需用工作表函數transpose轉
→ soyoso: 置
了解，我晚點再GOOGLE研究看看
最後一問，如果你還有看到的話再麻煩解答

假如我想在解碼中使用萬用字元*
例如  95*7  讓9507~9597全部都視為應該被解碼為唐伯虎的話
是否只能使用Range.Find才有辦法？
發現其他寫法似乎都只會把*視為一般字元而非萬用字元

萬分感謝
※ 編輯: Kamikiri (60.248.94.55 臺灣), 10/08/2019 11:33:39
→ soyoso: like或range.autofilter 方面可用萬用字元
推 foolkids: 一樣推薦Dictionary，30萬筆的比較時，效率還可以；超過
→ foolkids: 的時候一樣有效能問題，要再多做些手段