【20170926】- 你會使用VBA進行多表數據查詢嗎？

前兩天SUT學習交流群中有位廣東的小夥伴在編寫VBA進行多表數據查詢的時候遇到了一個「奇怪」的問題，具體問題截圖如下：

如果只是從表面來看，這個問題的確玄乎，直到看到了該小夥伴寫的代碼後就知道為啥會出現這個問題，具體的代碼如下：

Private Sub Worksheet_change(ByVal Target As Range)n Application.EnableEvents = Falsen Sheet1.Range("d5:f22") = ""n Dim arr1, arr2(1 To 1000, 1 To 3), k%, x%, y%, z%, mystr$n mystr = Range("c5")n Application.ScreenUpdating = Falsen For x = 2 To Sheets.Countn arr1 = Sheets(x).UsedRangen For y = 2 To UBound(arr1, 1)n If arr1(y, 2) = mystr Thenn k = k + 1n For z = 1 To 3n arr2(k, z) = arr1(y, z + 2)n Next zn End Ifn If arr1(y, 7) = mystr Thenn k = k + 1n For z = 1 To 3n arr2(k, z) = arr1(y, z + 7)n Next zn End Ifn Next yn Next xn Application.ScreenUpdating = Truen [d5].Resize(k, 3) = arr2n Application.EnableEvents = TruenEnd Subn

看到上面的代碼，是不是大家就能知道為啥會出現這種事件不觸發的問題了。我們一起來再現一下事件不觸發的情景，詳見如下動態圖：

通過如上動態圖，可以看到，第一個工作表是【查詢表】，可以在C5單元格選擇不同的品名，然後右側自動從剩下的工作表中查找對應品名的產品信息。其實該問題就是從多個工作表批量查詢數據並顯示出來。

因為品名為F的產品沒有對應的信息，所以上面的代碼因查詢不到數據而報錯，所以提示如下錯誤：

因為該錯誤會導致上面的代碼執行一半就結束了，此時Application.EnableEvents的值為False。所以後面再選擇不同的品名，也不會響應工作表Change事件了。為了讓代碼能夠繼續運行，可以把Application.EnableEvents賦值為True，這樣就能繼續響應事件了，具體操作如下動態圖所示：

怎麼樣？是不是就是代碼執行到一半，導致最後的代碼並未執行，也就是下圖中紅色框的代碼未執行。

既然代碼在沒找到數據的時候會報錯，可以通過增加On Error GoTo ×××來解決，具體代碼如下：

Private Sub Worksheet_change(ByVal Target As Range)n Application.EnableEvents = Falsen Sheet1.Range("d5:f22") = ""n Dim arr1, arr2(1 To 1000, 1 To 3), k%, x%, y%, z%, mystr$n mystr = Range("c5")n Application.ScreenUpdating = Falsen For x = 2 To Sheets.Countn arr1 = Sheets(x).UsedRangen For y = 2 To UBound(arr1, 1)n If arr1(y, 2) = mystr Thenn k = k + 1n For z = 1 To 3n arr2(k, z) = arr1(y, z + 2)n Next zn End Ifn If arr1(y, 7) = mystr Thenn k = k + 1n For z = 1 To 3n arr2(k, z) = arr1(y, z + 7)n Next zn End Ifn Next yn Next xn Application.ScreenUpdating = Truen On Error GoTo 100n [d5].Resize(k, 3) = arr2n Application.EnableEvents = Truenn Exit Subn100:n Application.EnableEvents = Truen MsgBox "抱歉，找不到此產品!!!", 64, "溫馨提醒"nEnd Subn

如上的代碼演示如下：

此時就沒有錯誤彈窗提示了，繼續選擇其他品名，仍然可以從多個工作表中查詢出相應的數據來。

其實細心的小夥伴一定看到Worksheet_change事件有個參數Target參數，可以利用這個參數來定位響應某一個單元格的change事件，就不需使用Application.EnableEvents屬性了。具體如何編寫代碼就當給各位小夥伴留的作業。如果大家在學習過程中遇到任何問題，歡迎加群（QQ群：615356012）交流，需要完整代碼的小夥伴可以在公眾號回復「20170926」得到哦，歡迎下載學習，代碼未完全測試，如有疏漏，歡迎指正^_^Written by Steven in 20170926^_^

微信公眾號：SaveUTime

SUT學習交流群：615356012，入群審核人：Steven

關注公眾號，提高效率，節約您的時間！