[Access VBA] 数式エラーの発生しているExcelシートをテーブルに取り込むときの注意点とサンプルコード

Jan 22, 2022 · この記事は約11分で読めます · VBA Access ·

Share on:

概要

この記事について

かんたんな概要と結論

数式エラーが発生しているセルに対しては、
エラーをキャッチし、代わりの値を設定するなどの対処が必要です。

こんにちは、dedeです。

この記事では、
AccessからExcelシートのデータを取り込む際に、
数式エラーが発生しているセルがあった場合を取り扱います。

その場合の注意点と、
エラーの回避方法について、
サンプルコード付きで解説します。

環境

以下は、
Office 2019のAccess環境で検証済みです。

※2022/1時点の最新バージョンのOfficeでも内容は変わりません。

現象

数式エラーとは

数式エラーについては、
Excelでテーブルの作成や集計、分析などを行った経験がある人であれば
見たことがあるかと思います。

セルに値を入力した後、
その値が不正なデータであった場合には、
#（シャープ）から始まるエラー値が表示されます。

数式エラーの一例

それぞれのエラー値には、
別々の原因が存在し、
VBAで参照した際には個別の「エラー番号」を含む特殊な値を返すため、
戻り値を受け取る変数は、Variant 型である必要があります。

表示されるエラー	エラー番号
#DIV/0!	2007
#VALUE!	2015
#NAME?	2029
#REF!	2023

数式エラーを持つセルを取り込む場合

数式エラーは、
シートの作業時になるべく排除しておくべきではあるでしょう。

しかしながら、
別のシステムで自動作成されたシートに含まれる数式エラーなどは、
あらかじめ排除しておくことが困難でしょう。

そのようなときに、
Accessでシートのテーブルデータを取り込むと、
予期しないエラーが発生し、
悩まさせるかもしれません。

例えば、
次のように、ある列のデータに数式エラーが発生している売上管理テーブルを取り込むことを考えます。

Excelデータサンプル

Access側のテーブル構造もシートに寄せます。

フィールド名	データ型
管理番号	数値型
店舗	短いテキスト
売上金	数値型
調整後売上金	数値型

TransferSpreadsheetメソッド使用デモ

まず、
AccessのDoCmd.TransferSpreadsheetメソッドを利用して
取り込むデモを行います。

1Sub Excelインポート_Docmd使用_エラー発生デモ()
2    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
3                              "売上管理テーブル", Application.CurrentProject.Path & "\" & "数式エラーのある表.xlsx", True, "売上管理テーブル!"
4End Sub
5

実行すると、
「キー違反」のアラートが表示されてしまいます。

「キー違反」のアラート

「はい」をクリックすると、
数式エラーの発生したセルの値だけがからっぽのデータが挿入されます。

アラート後のテーブル

エラーメッセージが発生することや、
からっぽのデータが挿入されることは、
おそらく望んでいた挙動に沿う処理ではないでしょう。

DAOで手続き的に取り込むデモ

DAOを用いて、
手続き的に取り込む場合は、
挙動が少々異なります。

手続き的とは

「手続き的（Imperative ）」とは、
処理の記述の方法の種類の一つで、
行いたい処理を細かく一行ずつ書いていくことを指します。

対義語は宣言的（Declarative ）で、
処理の記述の際に、
行いたい処理の内容を端的に示す名前を持つ構文や関数名だけを書けば処理が行われることを指します（SQLなど）。

宣言的な関数を使うことは便利ですが、VBAにおいては自由度は手続き的な方が高いように感じます。

 1
 2Sub Excelインポート_DAO使用_エラー発生デモ()
 3    Const xlUp = -4162
 4    Dim excelPath As String
 5    Dim exApp As Object
 6    Dim wb As Object
 7    Dim sheetValues() As Variant
 8    Dim rs As Recordset
 9    Dim i As Long
10
11    On Error GoTo Err
12    
13    excelPath = Application.CurrentProject.Path & "\" & "数式エラーのある表.xlsx"
14    
15    '//Excelアプリを立ち上げる
16    Set exApp = CreateObject("Excel.Application")
17    Set wb = exApp.Workbooks.Open(excelPath, , True)
18    '//売上管理シートを参照する
19    With wb.Worksheets(1)
20        '//売上管理シート上のテーブルデータを、行数のぶんだけ参照し、
21        '//その内容を二次元配列にパースする
22        sheetValues = .Range( _
23                      .Cells(2, 1), _
24                      .Cells( _
25                      .Cells(.Rows.Count, 1).End(xlUp).Row, _
26                      4 _
27                      ) _
28        ).Value
29    End With
30    
31    '//売上管理テーブルのレコードセットを開く
32    Set rs = CurrentDb.OpenRecordset("売上管理テーブル", dbOpenDynaset)
33    
34    With rs
35        For i = LBound(sheetValues, 1) To UBound(sheetValues, 1)
36            '//対応するフィールドに、データを入れていく
37            .AddNew
38        
39            .Fields("管理番号").Value = sheetValues(i, 1)
40            .Fields("店舗").Value = sheetValues(i, 2)
41            .Fields("売上金").Value = sheetValues(i, 3)
42            '//※※　エラーの発生
43            .Fields("調整後売上金").Value = sheetValues(i, 4)
44        
45            .Update
46        Next i
47        
48    End With
49
50Exits:
51
52    rs.Close
53    wb.Close
54    exApp.Quit
55    
56    Exit Sub
57
58Err:
59
60    MsgBox Err.Description, vbExclamation, Err.Number
61    GoTo Exits
62    
63End Sub
64
65

これを実行すると、
「Fields2オブジェクトのエラー」という文言で、
調整後売上金を挿入する際にエラーが発生します。

DAO使用処理のエラー

どうすればいいのか？

では、どうすればエラーを回避する、
あるいは利用者にエラーについてアラートしてあげることができるのか？

それについて、次のセクションで見ていきます。

回避策

ABOUT

発生したエラーを検知するには、
TransferSpreadsheetメソッドではなく、
DAOを用いてコードを記述する必要があります。

エラー発生箇所において、
VBA.InformationのメンバーであるIsErrorメソッドを利用して、
エラー発生有無を検知します。

IsError(value)は、valueがエラーの場合のみTrueを返します。

1'//※※　例：エラーの発生箇所で条件分岐を行う
2.Fields("調整後売上金").Value = IIF(IsError(sheetValues(i, 4)),エラー発生した場合の値,エラー発生なしの場合の値)

なお、
説明で使用したファイルについて、
こちらからダウンロードできます。

1. エラーの場所を教えてあげる

エラー検知した際にエラーの発生箇所を教えてあげるためには、
次のようにエラー情報を変数に退避し、メッセージなどに渡す必要があります。

なお、このケースでは、エラー発生時はテーブルにはデータを格納しないものとします。

エラーの場所を教えてあげる処理フロー

  1
  2Sub Excelインポート_エラー発生箇所を通知()
  3    Const xlUp = -4162
  4    Dim excelPath As String
  5    Dim exApp As Object
  6    Dim wb As Object
  7    Dim sheetValues() As Variant
  8    Dim myWorkspase As Workspace
  9    Dim myDB As DAO.Database
 10    Dim rs As Recordset
 11    Dim i As Long
 12    Dim errAlertText As String
 13    Dim isUpdatable As Boolean
 14    Dim isCommit As Boolean
 15
 16    On Error GoTo Err
 17    
 18    excelPath = Application.CurrentProject.Path & "\" & "数式エラーのある表.xlsx"
 19    
 20    '//Excelアプリを立ち上げる
 21    Set exApp = CreateObject("Excel.Application")
 22    Set wb = exApp.Workbooks.Open(excelPath, , True)
 23    '//売上管理シートを参照する
 24    With wb.Worksheets(1)
 25        '//売上管理シート上のテーブルデータを、行数のぶんだけ参照し、
 26        '//その内容を二次元配列にパースする
 27        sheetValues = .Range( _
 28                      .Cells(2, 1), _
 29                      .Cells( _
 30                      .Cells(.Rows.Count, 1).End(xlUp).Row, _
 31                      4 _
 32                      ) _
 33        ).Value
 34    End With
 35    
 36    '//トランザクションの開始
 37    Set myWorkspase = DBEngine.Workspaces(0)
 38    myWorkspase.BeginTrans
 39    isCommit = False
 40    
 41    '//DBの取得
 42    Set myDB = myWorkspase.Databases(0)
 43    
 44    '//売上管理テーブルのレコードセットを開く
 45    Set rs = myDB.OpenRecordset("売上管理テーブル", dbOpenDynaset)
 46    
 47    With rs
 48        errAlertText = ""
 49        For i = LBound(sheetValues, 1) To UBound(sheetValues, 1)
 50            '//対応するフィールドに、データを入れていく
 51            .AddNew
 52            
 53            '//サブ関数を呼び出し、エラーが発生しなかった場合のみフィールドに値を代入する
 54            If Excelインポート_エラー発生箇所を通知_サブ関数_エラー検知(i, sheetValues(i, 1), "管理番号", errAlertText) Then
 55                .Fields("管理番号").Value = sheetValues(i, 1)
 56            Else
 57                isUpdatable = True
 58            End If
 59            
 60            '//管理番号と同様
 61            If Excelインポート_エラー発生箇所を通知_サブ関数_エラー検知(i, sheetValues(i, 2), "店舗", errAlertText) Then
 62                .Fields("店舗").Value = sheetValues(i, 2)
 63            Else
 64                isUpdatable = True
 65            End If
 66            
 67            '//管理番号と同様
 68            If Excelインポート_エラー発生箇所を通知_サブ関数_エラー検知(i, sheetValues(i, 3), "売上金", errAlertText) Then
 69                .Fields("売上金").Value = sheetValues(i, 3)
 70            Else
 71                isUpdatable = True
 72            End If
 73            
 74            '//管理番号と同様
 75            If Excelインポート_エラー発生箇所を通知_サブ関数_エラー検知(i, sheetValues(i, 4), "調整後売上金", errAlertText) Then
 76                .Fields("調整後売上金").Value = sheetValues(i, 4)
 77            Else
 78                isUpdatable = True
 79            End If
 80            
 81            '//エラー発生時は新規行の挿入をキャンセル
 82            If isUpdatable Then
 83                .Update
 84            Else
 85                .CancelUpdate
 86            End If
 87            
 88        Next i
 89        
 90    End With
 91    
 92    '//変更をコミットする
 93    '//エラー発生時はコミットせず、エラーメッセージを表示する
 94    If errAlertText = "" Then
 95        myWorkspase.CommitTrans
 96        isCommit = True
 97    Else
 98        errAlertText = "下記の行・項目において数式エラーが発生しました。" & vbLf & vbLf & errAlertText
 99        MsgBox errAlertText, vbExclamation, "数式エラー"
100    End If
101    
102
103Exits:
104    
105    '//コミットしていなければロールバックする
106    If Not isCommit Then myWorkspase.Rollback
107    
108    rs.Close
109    wb.Close
110    exApp.Quit
111    
112    Exit Sub
113
114Err:
115
116    MsgBox Err.Description, vbExclamation, Err.Number
117    GoTo Exits
118    
119End Sub
120
121
122'******************************************************************************************
123'*機能      ：指定した値のエラーを検知する
124'*引数      ：行番号
125'*引数      ：調査するセル値
126'*引数      ：項目名
127'*引数      ：エラー情報格納用テキスト
128'*戻り値    ：True > エラーなし、False > エラー発生
129'******************************************************************************************
130Function Excelインポート_エラー発生箇所を通知_サブ関数_エラー検知(ByVal rowNumber As Long, ByVal sheetValue As Variant, _
131ByVal tgtItem As String, ByRef errAlertText As String) As Boolean
132    
133    If IsError(sheetValue) Then
134        '//エラー発生時、エラーテキストに情報を追加し、戻り地をFalseとする
135        errAlertText = errAlertText & rowNumber & "行目：" & tgtItem & vbLf
136        Excelインポート_エラー発生箇所を通知_サブ関数_エラー検知 = False
137    Else
138        Excelインポート_エラー発生箇所を通知_サブ関数_エラー検知 = True
139    End If
140
141End Function
142

こちらを実行すると、
数式エラーの発生箇所を記録し、
次のようにエラーメッセージが表示されます。

記録されたエラー情報のメッセージ

ただAccess標準のエラーが出るよりも、
こちらのほうがユーザフレンドリーですね。

2. エラー時に代わりに特定の値を入れる

エラー発生時に逐一メッセージを出すよりも、
あらかじめ決められた規則で代打の値を代入したい場合もあるでしょう。

そのような場合のサンプルコードを記しました。

エラー時に代わりに特定の値を入れる処理フロー

 1
 2Sub Excelインポート_エラー発生時に代わりの値を代入()
 3    Const xlUp = -4162
 4    Dim excelPath As String
 5    Dim exApp As Object
 6    Dim wb As Object
 7    Dim sheetValues() As Variant
 8    Dim rs As Recordset
 9    Dim i As Long
10
11    On Error GoTo Err
12    
13    excelPath = Application.CurrentProject.Path & "\" & "数式エラーのある表.xlsx"
14    
15    '//Excelアプリを立ち上げる
16    Set exApp = CreateObject("Excel.Application")
17    Set wb = exApp.Workbooks.Open(excelPath, , True)
18    '//売上管理シートを参照する
19    With wb.Worksheets(1)
20        '//売上管理シート上のテーブルデータを、行数のぶんだけ参照し、
21        '//その内容を二次元配列にパースする
22        sheetValues = .Range( _
23                      .Cells(2, 1), _
24                      .Cells( _
25                      .Cells(.Rows.Count, 1).End(xlUp).Row, _
26                      4 _
27                      ) _
28        ).Value
29    End With
30    
31    '//売上管理テーブルのレコードセットを開く
32    Set rs = CurrentDb.OpenRecordset("売上管理テーブル", dbOpenDynaset)
33    
34    With rs
35        For i = LBound(sheetValues, 1) To UBound(sheetValues, 1)
36            '//対応するフィールドに、データを入れていく
37            .AddNew
38            
39            '//エラー発生時には、代わりに現在のテーブルの最大の管理番号よりも1だけ大きい管理番号を挿入する
40            .Fields("管理番号").Value = IIf(IsError(sheetValues(i, 1)), Nz(DMax("管理番号", "売上管理テーブル"), 1) + 1, sheetValues(i, 1))
41            '//エラー発生時には、代わりに不正な店舗名であることを示す
42            .Fields("店舗").Value = IIf(IsError(sheetValues(i, 2)), "※不正な店舗名です", sheetValues(i, 2))
43            '//エラー発生時には、売上金はゼロとする
44            .Fields("売上金").Value = IIf(IsError(sheetValues(i, 3)), 0, sheetValues(i, 3))
45            '//エラー発生時には、代わりに売上金の90%の金額を設定
46            .Fields("調整後売上金").Value = IIf(IsError(sheetValues(i, 4)), .Fields("売上金").Value * 0.9, sheetValues(i, 4))
47            
48            .Update
49        Next i
50        
51    End With
52
53Exits:
54
55    rs.Close
56    wb.Close
57    exApp.Quit
58    
59    Exit Sub
60
61Err:
62
63    MsgBox Err.Description, vbExclamation, Err.Number
64    GoTo Exits
65    
66End Sub
67
68

こちらを実行すると、
調整後売上金として、
数式エラーの行は売上金の90%の金額を設定するようになります。

代わりの値が設定されたテーブル

サンプルファイル

上にも記載しましたが、
説明で使用したファイルについて、
こちらからダウンロードできます。

終わりに

エラー発生時の分岐処理については、
VBAマクロを扱う限り避けては通れない問題となるでしょう。

様々なケースを想定し、
なるべくユーザに優しいマクロを作ることができるようになることが、
マクロの性能向上にとって重要となります。