ExcelのVBA 「定義されている設定に違反する」のエラー

みなさん、こんにちは。


たまぁ～に、遊び半分でプログラムのコードを書いたりしています。(へっぽこですど)

するとコンピュータのすべてのカテゴリーに精通していると勘違いされます。
マックの設定や使い方を聞かれたりとか、エクセルで超高度なグラフの作り方を聞かれたりとか・・・・・。

エクセルなんて実は表を作成して簡単なグラフしか作れないレベルなんですけどね。
スプレットシートとして考えるならそれで十分だと自分では思っているのですが、世の中、グラフィカルなグラフが氾濫しすぎですよ。

で、「エクセルなら何でもできる」「プログラムを書くなら何でもできる」と考えてる人が少なからずいて、そういった人は偉い人だったりします。

まっ、そんなこんなでいろいろ苦労したりするハメになってます。


どうせならキチンとした環境で書きたいんだけど、やっぱエクセルで書くことになっちゃうんだよねぇ。
慣れないから結構ハマってますので、備忘録と同様な環境で苦労されている同志のためにハマった点をアップしておきます。


シートのデータを RecordSet に取得して操作すると、結構な頻度で下記エラーが出現する

[Microsoft][ODBC Excel Driver]入力した値がこのテーブルまたはリストに対して定義されている設定に違反するため（たとえば、値が最小値未満か、最大値より大きい）、変更を記録できません。エラーを修正してやり直してください。

書いていたコードは以下のとおり。
データの入っているシートは使いまわしがきくように変数(sheetName)にしています。

'接続文字列の作成
Dim PathStr As String
PathStr = ThisWorkbook.Path & "\" & ThisWorkbook.Name

cnStr = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ="
cnStr = cnStr + PathStr
cnStr = cnStr + "; ReadOnly=False;Extended Properties=" Excel 8.0; HDR=YES;""


'データの入っているシートは変数化、実際は引数で与える
Dim sheetName As String
sheetName = "DataSheet"

'RecordSet取得
Dim SQLstr As String
SQLstr = "SELECT * FROM [" & sheetName & "$]"

Dim ShtConn As ADODB.Connection
Set ShtConn = New ADODB.Connection

ShtConn.ConnectionString = cnStr
ShtConn.Open

Dim dbRes As ADODB.Recordset
Set dbRes = New ADODB.Recordset

dbRes.Open SQLstr, ShtConn, adOpenKeyset, adLockOptimistic


'何かRecordSetを操作する処理を行う
dbRes.MoveFirst
Do Until dbRes.EOF
dbRes.MoveNext
Loop


dbRes.Close
Set dbRes = Nothing
ShtConn.Close

「定義されている設定に違反する」とあるので、まず最初に考えられるのが変数の型の間違い。

エクセルだと文字列の「1」と入れたハズが、数値の「1」になっているのはよくある話です。
データ入力の時に入れた値からエクセルが自動的に判断してくれるようですが、意図しない型になっていることがあります。

しかしながらシートのデータを確認してもおかしなデータは入力されていないようです。


次に考えられるのがいわゆる空白セルってやつ。
データを編集した後にエラーが出やすいので、RecodeSetのデータを確認すると Fields.Value がすべて NULL のレコードを発見。

これが今回のエラーの原因のようです。

エクセルの場合、最初にシートを作成した状態ではセルには何もない状態ですが、一度データを入力した後に、範囲選択してデータをDELETEキーでキレイにしても値がクリアされるだけで最初の状態には戻りません。

値のクリアではなく削除処理をキチンとしないと何かのタイミングで Fields.Value がすべて NULL のレコードを RecordSet が拾ってしまいエラーが出てしまいます。

イメージとしては、最初にシートを作成した時はセルには何もない状態。
データを入力した時にセルにデータを入れる箱ができて、その箱の中にデータが収納される。
で、データをクリアすると箱の中の値はなくなるけど、箱は空箱の状態でセルに残った状態。

その状態で RecordSet を操作しようとするとエラーが発生する。

こんな流れのようです。


使用者にセルをキチンと削除するようにしてもらえばエラーは発生しないと思うけど、クリアと削除を使い分けながら入力作業をするのは鬱陶しいと思われるので、別の方法を検討しなければなりません。

一応データベースを模した入力用のシートになっているので、1列目は入力必須項目になってます。
よって1列目が NULL でない行のみを RecordSet に取得するようにコードを修正いたしました。

'接続文字列の作成
Dim PathStr As String
PathStr = ThisWorkbook.Path & "\" & ThisWorkbook.Name

cnStr = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ="
cnStr = cnStr + PathStr
cnStr = cnStr + "; ReadOnly=False;Extended Properties="" Excel 8.0; HDR=YES;"""

'データの入っているシートは変数化、実際は引数で与える
Dim sheetName As String
sheetName = "DataSheet"

'RecordSet取得
Dim SQLstr As String
Dim ColName As String
ColName = Sheets(sheetName).Range("A1").Value
SQLstr = "SELECT * FROM [" & sheetName & "$] WHERE " & ColName & " IS NOT NULL"

Dim ShtConn As ADODB.Connection
Set ShtConn = New ADODB.Connection

ShtConn.ConnectionString = cnStr
ShtConn.Open

Dim dbRes As ADODB.Recordset
Set dbRes = New ADODB.Recordset

dbRes.Open SQLstr, ShtConn, adOpenKeyset, adLockOptimistic


'何かRecordSetを操作する処理を行う
dbRes.MoveFirst
Do Until dbRes.EOF
dbRes.MoveNext
Loop


dbRes.Close
Set dbRes = Nothing
ShtConn.Close

これでエラーは回避できました。
めでたしめでたし。


ではでは～☆ミ