はじめに
セルに数値だけを入力させたい場合、多くの人が「入力規則」を使って設定する人が多いと思います。
しかし、厳密に数値だけの入力になるかというと疑問が残ります。
例えば、入力規則を数値だけに設定していても数式マクロの入力は許可されてしまうので、他のセルを参照するようなケースでは、文字列が表示されるようになってしまいます。
その他、書式設定で通貨を指定すると、\マークが付加されても指摘はありません。
（これに関しては、その方が便利な場合もありますが、、、）
今回は、セルに対して数値だけが入力可能になるような監視プログラムを書いてみようと思います。
セル入力の特徴
Excelのセルに値を入力する際、必要に応じて型変換が発生し、更に書式設定が変更される場合があります。
・入力の際、型変換が起こる
・型変換によりセルの書式設定が変更されることがある
・VarType関数では整数も小数もDoubleになる
型変換の例としては、全角の数値を入力すると、半角の数値となり文字列ではなく数値に変換されます。
また、日付や通貨などと書式が合致している場合は、書式設定も変更されます。
そして、セルの値をVarType関数で調べると、Int や Long などの整数は、小数でなくても Double として認識されます。
数値入力プログラムの仕様
以上を踏まえて、数値のみ入力できるプログラムの仕様を考えてみます。
・空白は許可する —> 何もしないで抜ける
・数式マクロを許可しない —> 空白にする
・整数と小数を区別したい —> 整数のみ、小数のみ、両方可 を指定できるようにする
・文字列と数値を区別したい（文字列は許可しない） —> 文字列なら空白に変更
・全角の数値はセルで自動変換される —> そのまま
・セルの書式設定 —> 書式設定は決めておき、変更があった場合は戻す
プログラムは、シートモジュールのイベントプロシージャである Worksheet_Change に書く事になります。
プログラムコード
'//シートモジュールに記述します。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim formatLocal As String
'セルの書式設定を指定
formatLocal = "G/標準"
'formatLocal = "0_ "
If Intersect(Target, Range("E2:E9")) Is Nothing Then
Exit Sub
Else
'空白なら抜ける
If VarType(Target.Value) = 0 Then Exit Sub
'数式マクロを入力させない
If Target.HasFormula Then
MsgBox "数式マクロを入力できません。"
Target.Value = "" 'セルを変更するので再度Worksheet_Changeが呼ばれる
End If
'数値が入力されたか？
If VarType(Target.Value) = 5 Then
Debug.Print TypeName(Target.Value) & " " & VarType(Target.Value)
If IsInt(Target.Value) Then
MsgBox "整数入力"
Else
MsgBox "小数入力"
End If
Else
MsgBox "数値を入力してください。"
Target.Value = "" 'セルを変更するので再度Worksheet_Changeが呼ばれる
End If
'セルの書式設定 標準 Or 数値
If Target.NumberFormatLocal <> formatLocal Then
Target.NumberFormatLocal = formatLocal
End If
End If
End Sub冒頭部分は、セルの書式設定を何にするか設定します。これは日付や通貨になったしまった場合に、標準や数値に戻すための設定です。書式設定を数値にしたい場合は、コメントアウトを外して使ってください。
Intersect関数は、変更されたRangeが適用範囲であるか調べる関数です。今回は Range(“E2:E9”) となっていますが、それ以外の場合はプログラムを抜けます。
セルが対象範囲であれば、判定を行います。
まずは、空白がどうかを調べて空白の場合は処理を終えます。空白判定がこの位置にあるのには意味があります。
（理由は後述します）
空白以外の場合は、数式マクロが設定されていあにかを HasFormula で調べています。数式マクロが設定された場合は、メッセージボックスと共に、セルの値を空白に設定します。
尚、 Target.Value = “” とした途端に Worksheet_Change が再度呼ばれます。このために、空白の場合の処理を前方に置いたのです。
続いてセルの値が数値であるかを調べています。 VarType(Target.Value) が 5 であれば Double になります。セルに数値が入力された場合、セルの場合、整数でも小数でも 5(=Double)が返されるようなので、これで判定しています。
数値でない場合は、空白にセットして処理を抜けます。この場合も Worksheet_Change が再度呼ばれます。
数値の場合、整数値か小数値かを判定します。判定関数は別関数を用意しました。
'引数の Double の値が整数であれば、Trueを返します。
Function IsInt(d As Double) As Long
Dim b As Boolean
b = False
If Fix(d) = d Then
b = True
End If
IsInt = b
End Function こちらは、標準モジュールに記述してもOKです。
この関数は引数に Double の数値を受けてその数値が整数の場合はTrueを返し、小数の場合はFalseを返すようにしています。
特徴としては、Fix関数を利用ている点です。整数の判定には Int関数を使う事が多いのですが、Int関数の場合、小数を引数に取ると繰り上げが起こる場合があるので、Fix関数を使用しています。
Sub test()
Dim data As Double
data = -50000.55
Debug.Print Int(data) '-50001
Debug.Print Fix(data) '-50000 こっちの方が良い
End Sub整数と小数を区別しない場合は、If IsInt(Target.Value) Then 以下は不要となります。
まとめ
数値入力に限定する方法は、意外に面倒です。今回のプログラムで全てをカバーしているわけではないかもしれませんが、このような方針でプログラムを組んでいけばよろしいかと思います。
参考にしてみてください。
コメント