有了上了一次的介紹,遍歷整個工作表(Sheet)應該沒問題了,今天要來談的是如何遍歷整個活頁簿,也就是整個Excel檔,以及儲存格錯誤要如何尋找。
由於Excel的低嚴謹性,容易造成錯誤而不自知,本日要以IsError與CVErr函數來檢測整個Excel檔案是否有儲存格有錯誤。:
Sub Day4_尋找儲存格錯誤()
Dim Rng As Object
Dim i As Integer
For i = 1 To Sheets.Count
For Each Rng In Sheets(i).Range(Sheets(i).Cells(1, 1), Sheets(i).Cells(1, 1).SpecialCells(xlLastCell))
If IsError(Rng.Value) Then
errval = Rng.Value
Select Case errval
Case CVErr(xlErrDiv0)
Debug.Print Sheets(i).Name & " - " & Rng.Address & ": " & "#DIV/0!"
Case CVErr(xlErrNA)
Debug.Print Sheets(i).Name & " - " & Rng.Address & ": " & "#N/A"
Case CVErr(xlErrName)
Debug.Print Sheets(i).Name & " - " & Rng.Address & ": " & "#NAME?"
Case CVErr(xlErrNull)
Debug.Print Sheets(i).Name & " - " & Rng.Address & ": " & "#NULL!"
Case CVErr(xlErrNum)
Debug.Print Sheets(i).Name & " - " & Rng.Address & ": " & "#NUM!"
Case CVErr(xlErrRef)
Debug.Print Sheets(i).Name & " - " & Rng.Address & ": " & "#REF!"
Case CVErr(xlErrValue)
Debug.Print Sheets(i).Name & " - " & Rng.Address & ": " & "#VALUE!"
End Select
End If
Next
Next i
End Sub
程式中,透過Sheets.Count來取得活頁簿的總數,並使用for迴圈來遍歷所有活頁簿,每個活頁簿都有索引值,而程式中以i作為變數,使用Sheets(i).Range()時,就是指第i個活頁簿的哪個範圍值。
Sheets(i).Cells(1, 1).SpecialCells(xlLastCell)指的是第i個活頁簿最後一個使用的儲存格範圍,作用跟在工作表上使用鍵盤Ctrl+End的方式跳到最後一格相同。而整句套起來For Each Rng In Sheets(i).Range(Sheets(i).Cells(1, 1), Sheets(i).Cells(1, 1).SpecialCells(xlLastCell))指的就是遍歷整個活頁簿的每個儲存格。
透過If IsError(Rng.Value) Then來檢查儲存格的數值是否異常,如果異常的話,透過Select Case與CVErr的方式,帶出錯誤類型資訊,並列印出來。列印時,使用Sheets(i).Name的方式,將有錯誤的活頁簿名稱帶出,以便查詢。
以上可運用於大型活頁簿管理,降低人工除錯的時間成本,希望對各位有幫助。
建議將Sheets
改為Worksheets
。
因為圖表
也有可能是Sheets
之一,而圖表
這種Sheet是沒有Range物件的。
原來Worksheets與Sheets的差異在這裡,感謝告知!