Excelは空じゃない？ OLE DB型推論の落とし穴

はじめに

自己の立場を軽く。
新卒から2年目までWEB系のバック、フロントエンドをRubyで経験。
4年異業種を経験し、再度IT職のSESへ転職しました。
そこでVue、REACTなどモダンな言語を数カ月経験した後に、
現在のASPclassicにアサインという流れになります。

この記事の対象者

•モダンな言語から、レガシー言語へ向き合う方
•基幹業務などの開発案件にアサインされた方
•エンジニア経験の浅い方（己）
•過去の自分に向けて

Excel取込で「空セルなのに値あり判定される」

1. 背景
   Excel取込機能を改修中、
   Excel上では値を消している（数値のクリア）にも関わらず、
   「入力がない」or「ひっかからない」という問題が発生しました。

2. 問題のコード
   必須項目チェックの処理になります。

サンプルコード
Dim blnOK As Boolean
Dim strErrMsg As String

blnOK = True
strErrMsg = ""

If strField1 = "" Or strField2 = "" Then
    blnOK = False
    strErrMsg = "必須項目が入力されていません。"
End If

If strField3 = "" Then
    blnOK = False
    strErrMsg = "項目3が入力されていません。"
End If

問題なさそうに感じましたが、
Excel取込では上記判定が正しく機能しなかったです。

Excelには「Null」が存在しない

Excelのセルは、見た目が空白だとしても下記として扱われます。

• 空文字 ""
• 数値型の 0
• 書式だけが残ったセル
  → そのため、Excel上の空白＝ プログラム上の「空」ではない

2 OLE DB、 ADO による型推論

Excelを OLE DB / ADO で読みんだ場合、下記として扱われます。

• 列全体の内容から 型が決定される
• 数値が多い列 → 数値型
• 空セル → 数値型なので 0 として返却

その結果として、何も入ってないのに0として扱われる場合があります。

strField3  = 0

3 "" 判定では検出できない

If strField3 = "" Then

• "" → 検出できる
• 0 → 検出できない

⇛「何か入力されている扱い」になる

実際に起きていた現象

Excelの状態 VBA側の値 判定結果
空白→"" NG（想定通り）
数値クリア→0 OK（想定外）
数式で ""→"" NG

対策

If Trim(strFieldA & "") = "" Or Trim(strFieldB & "") = "" Then
    blnOK = False
    strErrMsg = "必須項目Aまたは必須項目Bが入力されていません。"
End If

If Trim(strFieldC & "") = "" Then
    blnOK = False
    strErrMsg = "必須項目Cが入力されていません。"
End If

• & "" により Null を文字列化し、Trim() で空白を除去します。
• Null、空文字、空白文字列」を空扱いにできます。

共通関数化

関数

Function IsValueEmpty(v As Variant) As Boolean
    IsValueEmpty = (IsNull(v) Or Trim(v & "") = "")
End Function

使用例

If IsValueEmpty(strFieldA) Or IsValueEmpty(strFieldB) Then
    blnOK = False
    strErrMsg = "必須項目Aまたは必須項目Bが入力されていません。"
End If

0を空扱いする場合

ただ上記の改善のみだと、0は除外できません。

v = 0

0 & ""      → "0"
Trim("0")   → "0"
"0" = ""    → False

→ 0は空扱いにならないです。

• 0も空扱いする場合

Function IsValueEmpty(v As Variant) As Boolean
    If IsNull(v) Then
        IsValueEmpty = True
    ElseIf IsNumeric(v) And v = 0 Then
        IsValueEmpty = True
    ElseIf Trim(v & "") = "" Then
        IsValueEmpty = True
    Else
        IsValueEmpty = False
    End If
End Function

まとめ

• Excelの「数値のクリア」は プログラム的には空ではない
• OLE DB 経由の取込では 型推論に注意
• IsNull や = "" だけの判定は不十分
• Excel取込では 正規化処理が必須