iT邦幫忙

DAY 4
3

Excel VBA 的眉眉角角系列 第 4

Excel VBA 的眉眉角角Day4: IsError與CVErr來檢測儲存格是否有錯誤發生

有了上了一次的介紹,遍歷整個工作表(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的方式,將有錯誤的活頁簿名稱帶出,以便查詢。

以上可運用於大型活頁簿管理,降低人工除錯的時間成本,希望對各位有幫助。


上一篇
Excel VBA 的眉眉角角Day3: For Each...Next 陳述式與模糊搜尋
下一篇
Excel VBA 的眉眉角角Day5:關於function與函數,以相似度程式作為討論
系列文
Excel VBA 的眉眉角角30

1 則留言

1
paicheng0111
iT邦高手 1 級 ‧ 2018-04-12 22:34:00

建議將Sheets改為Worksheets
因為圖表也有可能是Sheets之一,而圖表這種Sheet是沒有Range物件的。

Andy Chiu iT邦研究生 3 級 ‧ 2018-04-13 06:17:08 檢舉

原來Worksheets與Sheets的差異在這裡,感謝告知!

我要留言

立即登入留言