Excel VBAのコーディング事例をご紹介しています。
事例のテーマを決めて、その機能を実現するためのコーディングを動く形でお示したいと思います。
今回のテーマは「ワークシート上の不特定なセル範囲に数値以外のデータが存在しないか?を確認する」になります。
なおコーディング自体は「構造化・標準化は考慮しつつも極力コーディング量は減らし、その上で可読性を維持する」という思いは持ちつつも「テーマに沿って動くことを優先して実装」しています。
また本コーディングはExcel XLSX形式ファイルで実行する事を想定しているため、実行結果はイミディエイトウィンドウに出力いたします。
Excel 2016バージョン2010(ビルド 13328.20356)を使用しています。
確認するワークシートはどのようなものを想定しているか?
「数値入力に誤りが無い」とか「数値以外のデータが存在しないか」とか述べているのですが、想定しているワークシートを次なような表形式のものになります。
- 横(列)方向に項目が並び、縦(行)方向には項目の並びが同じデータ群が連続して配置・蓄積されている。
- 項目には連番もしくはデータ群を分類するためのもの(「キーとなる項目」)があってもなくても確認できる。
- テキスト項目は少なく、数値項目の方が多い事を想定している。
- IT表現的には「マスターテーブルというよりかはトランザクションテーブル」
- 項目見出しの行はあってもなくても確認できる。
数値以外のデータとは?
①数値が文字列として保存されている場合
この「数値が文字列として保存されている」場合で割と良く起こりえるパターンとしては、下記の２つになる認識です。
- セルの書式設定で「文字列」が設定されている状態に気付かなかった時
- 先頭にアポストロフィー(‘)が付いているデータを気付かずにコピペした時
どちらも状況によってはなかなか気付き難いパターンになります。
特に気付き難いパターンとは下記のような設定・操作がされている場合です。
- セルの書式設定で配置に「右寄せ」が設定されていた。
- ExcelデータがMicrosoft Accessの表からコピペされていた。
ワークシートではエラーインジケータを有効になっている時は、「数値が文字列として保存されている」該当セルの左上に緑の三角マークが表示されています。
ここで注意しなけばならないポイントがあります。
それは前章でご説明した「キーとなる項目」のなかに「数値が文字列として保存されている」場合があることです。
例えば数字だけで構成されていて先頭にゼロが付く場合があるような行政区域コードや口座番号、ハイフォン無の電話番号などがあげられますが、その他商品コードや顧客コードなど様々なコードでも使われている場合があることと存じます。
従って「数値が文字列として保存されている」場合を無条件で抽出する訳にはいかず、何かしら列での除外条件をVBAコーディングの中で実装する必要があります。
詳細は後述いたします。
②入力ミスに気付けていない場合
数値を入力しているはずが、誤って文字を入力しているケースは少なからずあることと存じます。
例えばテンキーの周りに良く配置されている事が多い「/*-+．」などのキーを誤って触ってしまったり、あとは普段からご自分でカンマ区切りをして入力されている場合などでは、カンマの周りのキーに触れてしまう事があるかもしれません。
ただし「数値ではない」と判定されたものすべてを抽出してしまうと漢字情報や英数字で構成されたコード類などがすべて抜き出されてしまい収集が付かなくなってしまう恐れがあります。
そうは言いながら、例えば「最初の一文字目は文字では無い」と決めつけてしまった場合に「本当にそれで大丈夫なのか?」という一抹の不安がよぎるのではないでしょうか?
そうなると、指定するのは面倒ではありますが、見出し行を除外指定したり、英数字で構成されたコード類を列で除外指定したりすることで対応するのが一番確実です。
除外指定を漏らしてVBAコーディングを実行した時は確かに数多くのセルが該当して出力されてしまいますが、それはそれで漏れに気付き除外指定を見直してやり直しをする事ができます。それに対して決めつけてしまった場合には漏れている事に気付く事ができなくなってしまいます。
ただし、そうは言いながらも抽出される対象を少しでも少なくしたいので、「対象セルに数字が含まれていない場合」には抜き出さない仕様にしています。
逆に言いますと、すべてが漢字ではない、例えば「2020年度」とか「項目1」とかがヘッダー行に設定されている場合には、それは抽出対象になりますのでお含み置きいただければ幸いです。
③セルがエラーになっている場合
表の中でエラーのままになっているセルは基本的には無い事と存じますが、もし万が一エラーになっているセルが存在した場合は「そのセルは数値ではない」と判断されます。
エラーになっているセルの値はVBAコーディングでは「”エラー”+エラー番号」(“エラー”は漢字でエラー番号は数字)の形で取得できます。
今回はコーディング量を削減するために、エラーの場合は「#N/A」や「#VALUE!」のような表記に変換はせず、上記のままで出力しますのでお含み置きいただければ幸いです。
数値以外の判定に含めていないもの
Excelではセルの書式設定の表示形式を変更する事で見え方が変わる数値がいくつかあります。
今回このような「見え方が変わる数値」につきましては判定に含めていません。
含めない理由も含めて代表的な表示形式「日付」「時刻」「通貨」についてご説明いたします。
セルの書式設定の表示形式が「日付」「時刻」になっている場合
ご存じの方が多いとは思いますが、入力された「時刻」や「日付」は実際の値としては、「時刻」は小数点以下の数値で「日付」は整数で「日付と時刻」の場合は整数と小数点以下の数値の両方で保管されています。
従いまして、数値を入力する列のどこかのセルの表示形式が「日付」や「時刻」になっている事に気付かずにその列の合計をSUM関数で計算したとしても、見え方がおかしいだけで合計値は正しいはずです。
もっとも合計だけではなく表も一緒に報告時に提出する必要がある場合は、表示形式を間違えていたら指摘を受けてしまうかもしれません…
ところで話が変わりますが、VBAコーディングで数値判定をするIsNumeric関数ですが、実は「日付」はFalseと判定するのに対して、「時刻」はTrueと判定します。
なぜ両者の扱いが異なるのか?、Falseという事は「日付」は数値ではないのか?、それとも『「日付」は判定をしない』という意味でのFalseなのか?、大変申し訳ございませんがこれらの疑問に関しては「Microsoftのみが知る」状況です。
しかしながら、IsNumeric関数が「日付」をスルーしたとしても、別にIsDate関数が用意されているために「日付」を拾い出して数値に含めるコーディングは簡単に実装する事ができます。
それに対して「時刻」についてはIsTime関数がVBAでは用意されていないので、セルの表示形式が「時刻」である事を判定するためには少し長いコーディングが必要になります。
という事で、いろいろごちゃごちゃと書きましたが、「日付」と「時刻」を数値以外に含めない理由を一文にまとめると下記のようになります。
数値計算の中に「日付」や「時刻」が現れるのはおかしい事と存じますが、セルの書式設定の表示形式から「時刻」と判断するためにはコーディングが長くなるため、「日付」も含めて数値以外には含めない事にいたします。
なお表示形式から「時刻」を判定するためにどのような課題があるのか?につきましては『Excelセルの書式設定が「時刻」になっている事をVBAで確認する』をご参照いただければ幸いです。
セルの書式設定が「通貨」になっている場合
「通貨」にした時に表示される通貨記号には通貨に応じて様々な記号を設定することが可能です。
ただし、この通貨記号を判定に含めるとなると、表示形式が「通貨」の列をすべて抽出しなければならなくなってしまいます。
そのなると「数値入力に誤りが無いか確認する」という本来の目的が達成できなくなってしまいます。
例えば「\」以外の場合は抽出する仕様にする事は考えられるのですが、ただその場合でもExcelでは列を分けていないと通貨単位の違いを吸収する事は難しく列に混在しているとは考えにくいので判定には含めないことに致しました。
実際のVBAコーディング
下記が実際のVBAコーディングになりますが、70行近くありますのでご容赦ください。
なお実行する際に値をセットしていただく事で動き方を変える事ができる変数が4つ程ありますので、それにつきましては後述いたします。
まずはコーディングの中身が分からないものを動かすのは不安がある事と存じますので中身を簡単にご紹介いたします。
- 10-15行目：3つの変数の中のスペースを削除しデフォルトだったらデフォルト値をセット
- 16行目：Excelブック名を出力
- 17行目：ワークシート上の入力があるセルをすべて選択
- 18-24行目：選択範囲で行(縦)方向に順番に最後まで見て行くが行除外に該当する場合はスキップ
- 25-32行目：選択範囲で列(横)方向に順番に最後まで見て行くが列除外に該当する場合はスキップ
- 33行目：コーディング量削減のため、セル番地とHYPERLINK関数をそれぞれの変数に格納
- 34-35行目：対象セル値がエラーの場合、イミディエイトウィンドウに出力
- 36行目：空文字のセルはスキップ
- 37-45行目：対象セル値がIsNumeric関数Falseの場合
- 38行目：セル値がIsDate関数TrueでかつCell関数format値が「日付」の場合はスキップ
- 40-44行目：セル値に数字がある事を確認し、あればイミディエイトウィンドウに出力
- 46-55行目：「混じりっけのある数値」の場合
- 48行目：対象セルがエラーインジケータに該当しない場合、イミディエイトウィンドウに出力
- 51行目：対象セルがエラーインジケータに該当する場合、イミディエイトウィンドウに出力
- 52-54行目：更新変数がTrueに設定されている場合、対象セルを修正
- 56行目：最初の数値セルのセル番地を変数に保存
- 62-65行目：数値セルのセル番地がセットされていて、かつ区切字が設定されている場合、数値セルのセル番地に対して区切り位置指定ウィザードの区切り文字に区切字の値をセット
- 66行目：ワークシートのA1セルを選択
Option Explicit
Sub NumericCheck()
Dim i As Integer, rw As Long, cl As Long, r As Integer, rMax As Integer, c As Integer, cMax As Integer, cnt As Integer: cnt = 0
Dim NowCell As Range, cellrefer As String: cellrefer = ""
Dim hyplnk As String, cladrs As String, strwk As String, judge As Boolean
Dim 行除外 As String: 行除外 = "" '【①】行でチェックを除外する時、行番号(数字)をカンマ区切りでセット。無ければ""(空文字)
Dim 列除外 As String: 列除外 = "" '【②】列でチェックを除外する時、列番号(英半角大文字)をカンマ区切りでセット。無ければ""(空文字)
Dim 区切字 As String: 区切字 = "" '【③】区切字にスペース以外の1文字を入力した時、区切り位置指定ウィザードに区切字をセット
Const 更新 As Boolean = False '【④】文字列数値を修正する場合はTrueに変更
行除外 = Replace(行除外, " ", ""): If 行除外 = "" Then 行除外 = " "
列除外 = Replace(列除外, " ", ""): If 列除外 = "" Then 列除外 = " "
区切字 = Replace(区切字, " ", ""): 区切字 = Left(区切字, 1)
If 区切字 = "" Then
judge = True: 区切字 = "＿" '【※】区切字にデフォルト値＿(全角アンダースコア)をセット
End If
Debug.Print ActiveWorkbook.Name
ActiveSheet.UsedRange.Select
For rw = Selection(1).Row To Selection(Selection.Count).Row
rMax = -1
For r = 0 To UBound(Split(行除外, ","))
If StrComp(Split(行除外, ",")(r), rw, vbTextCompare) = 0 Then Exit For
rMax = r
Next r
If rMax = UBound(Split(行除外, ",")) Then
For cl = Selection(1).Column To Selection(Selection.Count).Column
Set NowCell = Cells(rw, cl) '【※】処理対象セルをオブジェクト変数にセット
cMax = -1
For c = 0 To UBound(Split(列除外, ","))
If StrComp(Split(列除外, ",")(c), Split(NowCell.Address, "$")(1), vbTextCompare) = 0 Then Exit For
cMax = c
Next c
If cMax = UBound(Split(列除外, ",")) Then
cladrs = NowCell.Address(False, False): hyplnk = "=HYPERLINK(""#"" & """ & cladrs & """,""" & cladrs & """)"
If IsError(NowCell.Value) Then
cnt = cnt + 1: Debug.Print Format(cnt, "000") & "-E" & 区切字 & hyplnk & 区切字 & "' " & CStr(NowCell.Value)
ElseIf NowCell.Value = "" Then 'Skip
ElseIf IsNumeric(NowCell.Value) = False Then
If IsDate(NowCell.Value) = True And Left(Evaluate("Cell(""Format""," & cladrs & ")"), 1) = "D" Then '日付はSkip
Else
strwk = NowCell.Value
For i = 0 To 9: strwk = Replace(strwk, i, ""): Next i
If strwk <> NowCell.Value Then
cnt = cnt + 1: Debug.Print Format(cnt, "000") & "-1" & 区切字 & hyplnk & 区切字 & "' " & NowCell.Value
End If
End If
ElseIf NowCell.Value <> NowCell.Value * 1 Then
cnt = cnt + 1
If NowCell.Errors.Item(xlNumberAsText).Value = False Then
Debug.Print Format(cnt, "000") & "-2" & 区切字 & hyplnk & 区切字 & "' " & NowCell.Value & 区切字
Else
Debug.Print Format(cnt, "000") & "-3" & 区切字 & hyplnk & 区切字 & "' " & NowCell.Value & 区切字 & NowCell.Value * 1
If 更新 = True Then
NowCell.Value = NowCell.Value * 1: NowCell.NumberFormatLocal = "G/標準" '【※】抽出セルの訂正処理
End If
End If
ElseIf cellrefer = "" Then cellrefer = cladrs
End If
End If
Next cl
End If
Next rw
If cellrefer <> "" And judge = False Then '【※】区切り位置指定ウィザードの区切り文字に区切字の値をセットする処理
ActiveSheet.Range(cellrefer).Select
Selection.TextToColumns Destination:=Range(cellrefer), DataType:=xlDelimited, Other:=True, OtherChar:=区切字
End If
ActiveSheet.Range("A1").Select
End Sub
値をセットする事で動き方を変える事ができる変数
変数は全部で４つありますので順番にご説明いたします。
行を除外するためのセル行番号を指定する変数
コーディング6行目に「行除外」という変数が定義されています。
Dim 行除外 As String: 行除外 = ""
タイトル行のように行(横)方向で数値チェックを除外したい時に、行番号(数字)をカンマ区切りでセットします。無ければ””(空文字)でセットします。
例えば、1行目と20行目を除外する時は下記のように指定してください。
Dim 行除外 As String: 行除外 = "1,20"
列を除外するためのセル行番号を指定する変数
コーディング7行目に「行除外」という変数が定義されています。
Dim 列除外 As String: 列除外 = ""
コード列や備考のように列(縦)方向で数値チェックを除外したい時に、列番号(英半角大文字)をカンマ区切りでセットします。無ければ””(空文字)でセットします。
例えば、3列目と10・11列目と27列目を除外する時は下記のように指定してください。
Dim 列除外 As String: 列除外 = "C,K,L,AA"
列が連続している場合でも、お手数ですが1列づつ指定してください。
区切り文字を指定して区切り位置指定ウィザードに区切り文字を設定する変数
コーディング8行目に「区切字」という変数が定義されています。
Dim 区切字 As String: 区切字 = ""
区切字が空文字の場合、区切り文字はデフォルトで全角アンダースコア(＿)が設定されます。
Dim 区切字 As String: 区切字 = "＿"
もしも区切字に全角アンダースコア(＿)を設定した場合、区切り文字はデフォルトと変わりませんが、区切り位置指定ウィザードの区切り文字に区切字の値をセットする処理が動く事になります。
区切字を空文字以外にセットした時は区切り位置指定ウィザードの区切り文字に区切字の値をセットする処理が動きますので、ご注意ください。
なお区切字に「スペース」は使用できませんので、あらかじめお含み置きいただければ幸いです。
「数値が文字列として保存されている」場合に「数値に変換」する更新を「するか・しないか」を設定する変数
コーディング9行目に「更新」という変数が定義されています。
Const 更新 As Boolean = False
デフォルトはFalse(更新しない)になっています。
最初にFalseで実行して、次の章でご説明する「判定3」が大量に見つかった場合に、まずコード項目が設定されている列を除外指定してみてください。
それでもまだ大量に見つかった場合は、「更新」変数をTrueに設定すると、『「混じりっけのある数値」でかつエラーインジケータが付いている』セルの元の数値に×１をして修正し、該当セルの書式設定を「G/標準」に修正します。
「更新」をする場合は「判定3」の時にイミディエイトウィンドウに出力する「対象セルの数値に×１を掛けた値」を事前にご確認いただきますようお願いいたします。
一度更新を実行して保存をしてしまうと、元に戻すことはできません。必要に応じてバックアップファイルを作成してから実行してください。
Const 更新 As Boolean = True
イミディエイトウィンドウに表示されるデータの出力フォーマット
判定した結果はデフォルトではイミディエイトウィンドウに出力するだけにしています。
その理由としては、対象セルに色を付けたり、罫線を引いたりすることは表の持つもともとの外観を損ねる事になるのでやるべきではないという判断です。
そのため「どこのセルがおかしいのか?」はイミディエイトウィンドウに出力されるセル番地を頼りに探す必要があります。
ただそれはそれで大変な場合もあるので、セル番地にHYPERLINK関数(ワークシート関数)を付けて出力しています。
この使い方につきましては後述いたしますが、まずは出力フォーマットをご紹介いたします。
出力フォーマット
出力項目は全部で最大4項目あり、それぞれを区切り文字としてデフォルトでは全角アンダースコア(＿)を使用して区切っています。なお区切り文字は変数で変更できますが、変更すると「区切り位置指定ウィザード」の区切り文字としてセットされますのでご注意ください。
- 出力の「連番」と「判定内容を表す英数字(E,1,2,3)」をハイフォン(-)を先に付けて出力
- E–IsError関数でTrue
- 1–IsNumeric関数がFalse(数値ではない)でかつ対象セルに数字が含まれている。
- 2–「混じりっけのある数値」でかつエラーインジケータが付いていない。
- 3–「混じりっけのある数値」でかつエラーインジケータが付いている。
- 「更新」変数がTrueの場合は元の数値に×１をして修正し、セルの書式設定を「G/標準」に修正するので、4項目目に×１した時の値をセットしています。
- 対象セルをHYPERLINK関数付きで出力
- 対象セルの元の値に「’ 」(シングルコーテーションとスペース2つ)を付けて出力
- これによりExcelに貼り付けた時に自動変換されて元の値が変わってしまうのを防いでいます。
- 対象セルの数値に×１を掛けた値
※判定内容「３」以外では4項目目の出力はありません。
イミディエイトウィンドウに出力され結果をワークシートに貼り付ける
イミディエイトウィンドウに出力された結果をすべて選択(ctrl + A)してコピー(Ctrl + C)して、VBAコーディングを実施した対象のワークシート上で4列連続で空になっている場所を見つけて貼り付け(Ctrl + V)をしてください。
「区切字」変数が空文字(デフォルト状態)のままの場合
- イミディエイトウィンドウに出力された結果をすべて選択してコピーします。
- VBAコーディングを実施した対象のワークシート上で4列連続で空になっている場所を見つけて貼り付けます。
- メニューの「データ」からデータツールにある「区切り位置」で「区切り位置指定ウィザード」を開き、「コンマやタブなどの区切り文字にって…」を選択して「次へ」をクリックします。
- 「区切り文字」の「その他」をチェックして右隣の入力ボックスに全角アンダースコア(＿)を入力して、ウィザードの途中ですが「完了」ボタンをクリックします。
- 入力ボックスには全角も入力できます。
- 貼り付けたデータが区切り文字で分割されてそれぞれの列にデータが入ります。
「区切字」変数に値をセットした場合
「区切字」変数にデフォルトの区切り文字である全角アンダースコア(＿)をセットした場合、前章の「１」の次は「５」の形でイミディエイトウィンドウに出力された結果がワークシートに貼り付きます。
これはコーディングの62-65行目で、「区切字」変数に値をセットした場合は区切り位置指定ウィザードの区切り文字に区切字の値をセットしているためです。
ただし、このコーディング部分が動くためにはもう一つ条件がありまして、それは表の中に最低1か所「数値」と判定されるセルがある事です。
つまり、存在した「数値」のセルをコーデングが自動的に選択をした上で、そのセルに対して「区切り位置指定ウィザード」と同じ操作を実施しています。
当然「数値」のセルには対象となる「区切り文字」は存在しないので処理自体は空振りするのですが「区切り位置指定ウィザード」に設定した条件はそのまま起動しているワークブックにキープされているために、次にイミディエイトウィンドウに出力された結果を貼り付けた際に、その条件が自動的に適用されて各列に区切り文字で分割されてセットされることになります。
従いまして、結果的に区切り位置指定ウィザードの処理を削減する事ができます。
なお「区切り位置指定ウィザード」の設定を変えたくない場合は、必ず「区切字」変数を空文字にして実行してください。
HYPERLINK関数が設定されている対象セルの使い方
イミディエイトウィンドウに出力された結果がワークシートに貼り付いた状態です。
ここで「対象セル」にカーソルを合わせるとポインターの形が変わり、「リンク先に移動するには、クリックします。」のメッセージが表示されます。
左図①でフォーカスの当たっているセルは「I6」セルですが、ここでマウスのポインターが当たっている「(対象セル)A3」をクリックすると、左図②のように「A3」セルにフォーカスが移動します。
この場合は一つの画面内に収まる範囲でのフォーカス移動でしたが、これが遠く離れたセルの場合は当然出力結果を貼り付けた場所が隠れてしまい不便です。
そこで次にご説明する「クイック アクセス ツール バー」に「戻るボタン」を設定していただくと便利です。
「クイック アクセス ツール バー」の設定
Excel画面の一番上(メニューよりも上)の左側に「▼」のようなボタンがありますので、そのボタンをクリックしていただくと左図のようなメニューが表示されます。
この中から「その他のコマンドM)…」をクリックします。
すると「Excelのオプション」画面で「クイック アクセス ツール バー」をカスタマイズする画面が開きます。
- 「コマンドの選択」で「リボンにないにコマンド」をクリックします。
- コマンドが沢山リスト表示されますが、スクロールバーで一番下まで移動すると、「戻る」コマンドが現れますので、選択をして、画面中ほどの「追加(A)>>」ボタンをクリックします。
- すると画面右のユーザ設定の方に「戻る」コマンドが表示されますので、画面右下の「OKボタン」をクリックして画面を閉じます。
- 「戻る」ボタンを取り消したい時は、「戻る」ボタンをクッリクして画面中ほどの「<<削除(R)」ボタンをクリックすることで削除できます。
「クイック アクセス ツール バー」戻るボタンの使い方
- 「クイック アクセス ツール バー」戻るボタンは無効化されている。この状態でマウスのポインターが当たっている「(対象セル)A3」をクリックします。
- フォーカスがA3セルに移動し、戻るボタンは有効化されますので、戻るボタンをクリックします。
- フォーカスは「(対象セル)A3」をクリックした状態に戻り、戻るボタンは無効化されます。
このように、「クイック アクセス ツール バー」に戻るボタンがあれば、どこにフォーカスが移動しても、移動前にフォーカスがあったセルに画面が戻りますので、対象セルが複数ある場合は便利です。
最後に
Excelにはエラーインジケータというチェック機能がバックグラウンドで動いていますが、その中に「数値が文字列として保存されている」セルを調べる機能があります。
見た目は数値に見えるのですがSUM関数で合計をとった時にエラーインジケータが付いているセルの値は合計に含まれていないので注意が必要です。
それに加えて、ちょっとした間違いで文字列になってしまっていた場合にはエラーインジケータは働かず合計にも含まれないので更なる注意が必要です。
一方で、セルの書式設定の表示形式が数値ではないものがあるとおかしいと思うので、それらを含めて当初は30行ぐらいのステップ数を想定して本テーマのコーディングを始めたのですが、「数値判定の仕方につまづき」、「時刻判定の仕方につまづき」しながら、最終的には表示形式のチェックは含めない形に変更したにもかかわらず、結果的に70行に近いコーディング量になってしまいました。
後先を考えずにズルズルと時間を費やしてしまった事は反省をしておりますが、何かしら皆様のお役に立てることがあれば幸いです。
以上最後までご一読いただき誠にありがとうございました。
