Excel VBA オーバーフローというエラーが出てくるとき

ココナラでExcel VBA関連のサービスをやっているさべろくと申します。

閲覧いただきありがとうございます。

いきなり本題ですが、Excel VBAで下記エラーが出るときがあります。

これは指定したデータ型の範囲を超えた場合に出てくるエラーです。

例えばInteger型で変数を宣言した場合は、-32,768～32,767の範囲に収まっていないと上記エラーが出ます。

直す方法はもっと大きな範囲を扱える型にすることです。

例えばLong型にすれば-2,147,483,648〜2,147,483,647の範囲の数値を扱えます。

ここまではよく記事に書かれている内容です。

ちょっとだけ変わった事例のお話をします。

それはLong型を超えた場合にどうすればいいのか？という話です。

仕事でLong型を超えるケースに遭遇しました。

数学で扱われる組み合わせを列挙する問題です。

例えば、10個のボールから4個選んだときの組み合わせのパターンを列挙しなさいといった問題です。

実際には10個から4個選んだ組み合わせの場合は、セルに「1 2 3 4」「1 2 3 5」のように組み合わせを記載していくというものです。

下記の通りExcelを用意しました。

総数を指定して抜き取り数を指定すると、通り数が算出されます。

通り数はExcelの関数であるCOMBINを使って算出しています。

実行ボタンを押すと1シートに下記のように表示されます。

※これは10通りから3つ選んだときの結果です。

総数と抜き取り数を自由に設定できるようにしているため、ここで問題が起きました。

通り数がエクセルの1シートのセルの数（約171億）を超えてしまい、書ききれずにエラーになってしまうのです。

他のシートに書き出すようにすれば問題ないのですが、ここでは1シートに収まらないときはメッセージを出力してプログラムを終えるという対処をすることにしました。

そこで、下記のように書きました。

Dim max As Long

max = Worksheets("Sheet1").Range("F1").Value

これでmaxという変数にシートのF1セルに書かれている通り数を取得して、if文でチェックしてセルの総数(約171億)より多ければメッセージを出して終了としようとしました。

しかし、通り数が4.4089E+36通りあったりして、Long型（最大約21億）を超えてしまい、上記のコードの段階でオーバーフローエラーになってしまいました。

ここで問題です。あるセルの値が170億を超えているかチェックしたいです。Long型は約21億までしか入らないため格納してチェックすることはできません。

どうすればこのチェックを行うことができるでしょうか？

色々と方法はあるかもしれませんが、私がやった対処方法を紹介します。

それは「String型に格納して文字列の長さをチェックする」です。

170億ですと11桁の数値になります。文字列は何文字あるかというと11文字です。

そのため、下記のように書いてチェックしました。

If Len(max) > 11 Or (Len(max) = 11 And CInt(Left(max, 2)) >= 17) Then

        MsgBox "1シートに書き出せる最大量を超えています。終了します。"

        Exit Sub

End If

Lenで文字列の長さを求めて11文字より大きければメッセージを出します。

また、11文字のときは文字列の左から2文字を取り出して、それが17という数値より大きければメッセージを出すという具合です。

このようにセルの数値を数値として処理するのではなく、文字列として処理してあげることでオーバーフローを回避しました。

この方法ですとString型は約 20 億 (2^31) 文字を格納できると言われているので、20億桁の数値に対してチェックすることができます。

※厳密にはExcel 2019では、1つのセルに書き込める文字数の最大値は32,767 文字ですので、32,767桁まで大丈夫ということになります。

「数値を文字列として考えて処理する」

言われてみれば簡単なことですが、忙しいときなどにはパッと思いつかないかもしれません。そのため、ここに書き記して置きました。

誰かの参考になったなら幸いです。

普段Excel VBAに関する相談などを請け負っております。

困ったことがあればお声がけください。

お読みいただきありがとうございました<(_ _)>