実行時エラー ‘1004’: アプリケーション定義またはオブジェクト定義のエラーです。

今日も見に来てくださって、ありがとうございます。
お仕事で、ちょっとつまずいたので、まとめます。誰かのお役にたつとうれしいです。

　あ、お急ぎの方に、結論から言いますと、Microsoft Excel VBAマクロでセルに正しくない計算式をセットするとこのエラーを発生させてしまいます。
　例：

Range("A1").Value = "=SUM([3:[10)"

　業務でマクロ、普通に作られていますね。ある日、「実行時エラー ‘1004’:」なんて発生して動かなくなると、ショックですよねぇ。このエラー、Google先生に聞いてみたのですけど、どうして発生するのかという条件がありすぎるせいか、原因を特定するのが難しいエラーのようです。通常のエラーだと、マクロが発生個所を教えてくれるのですが、関数内で「On Error GoTo 」が記述されてあったので、このエラー、どこで発生したのか分かりませんでした。なので1行ずつ自分でステップ実行していって、発生個所を特定する必要がありました。
　今回エラーが発生したマクロのイメージは以下のような感じです。商品ごとに毎月の売り上げがあって、1年間の合計欄を作成するようなケースです。

Sub 年間合計欄をセット()
    ' 開始行、開始列から下にある12か月分の合計セル(=SUM())を商品店数分セットします。
    Dim i As Integer
    Dim 商品点数 As Integer
    Dim 開始行 As Long
    Dim 開始列 As String
    Dim 出力列 As String
    Dim 計算式 As String

    商品点数 = 5
    開始行 = 3
    開始列 = "C"
    For i = 1 To 商品点数
        出力列 = Chr(Asc(開始列) + i - 1)
        計算式 = "=SUM(" & 出力列 & Format(開始行) & ":" & 出力列 & Format(開始行 + 12 - 1) & ")"
        Range(出力列 + Format(開始行 + 12)).Value = 計算式
    Next
 End Sub

　ここでのポイントは、「出力列」の算出方法です。「開始列」”C”は、Asc関数で67になります。これは”C”をASCII文字コードに置き換えています。これに順次数字を加えていって、商品点数5の場合、”C”(67)、”D”(68)、”E”(69)、”F”(70)、”G”(71)が出力列として算出されます。できあがる一つ目の「計算式」は、この場合「=SUM(C3:C14)」になります。一見問題なさそうに見えますね。
　問題点は、列が”Z”(90)までは見つかりません。問題は列がその隣にうつった時、”AA”≠(91)のため発生するのです。今回これを回避するためにエクセルの機能を利用して以下のように関数を作成しました。

'*****************************************************************
'*関数名　：Number2Letter
'*機能概要：入力されたカラム位置の数値からカラム文字の英数字に変換します
'*引数  　：カラム位置（1～16384）
'*戻り値　：カラム文字
'*****************************************************************
Function Number2Letter(iCol As Integer) As String
    Number2Letter = Split(Columns(iCol).Address(True, False), ":")(0)
End Function

　あるいは、数値をA～Zの26文字で表現する26進数に変換する、と考えると以下のような関数をつくってもよいかも知れませんね。

Function Number2Letter(iCol As Integer) As String
    If iCol < 1 Then Number2Letter = "": Exit Function
    Number2Letter = Number2Letter(Int((iCol - 1) / 26)) & Chr(Asc("A") + ((iCol - 1) Mod 26))
End Function

　これで、スッキリ解決です。

　ちなみに、今回の問題点をわかりやすくするために、単純化して書いてありますけど、実際にはちょっとした落とし穴があって、エラーは以下のような行で発生していました。簡単にしてますけど、”A:A”,”AA:AA”は複雑な変数で記載されていましたよ。

Range("A:A","AA:AA").Value = Range("A:A","AA:AA").Value

　同じ範囲のレンジを代入しているだけなので、エラーが起きる意味が分かりませんよね。今回、対象商品が増えてエラーが発生したので、すぐに「AA」が怪しいな、とあたりを付けて、「Range(“A:A”,”Z:Z”).Value」と「Range(“AA:AA”).Value」に分けて実行したところ、前者は成功して、後者が失敗したのです。それで、エクセルは「AA」セルのコピーを失敗する不具合があるのかな、と、一つ目の落とし穴に落ちたのでした。
　このValueへのセット、実は、文字列の式を値に変換するために代入しているのでした。そのため、文字列の式に誤りがあると、今回のエラーが発生するわけです。同様のエラーを発生させるステップは以下の通りです。文章だと意味がわかりにくいと思いますので実際に実行してみてくださいね。

Sub test()
    Range("A1").NumberFormat = "@" ' 文字列書式に変更
    Range("A1").Value = "=SUM(]3:]14)" ' 正しくない式を代入
    Range("A1").NumberFormat = "General" ' 書式を戻す
    Range("A1").Value = Range("A1").Value ' エラーが発生します
End Sub

　と、いうことで、処理データが増えたときにカラム方向へセルを拡張させるときには、気を付けましょう、というお話でした。

追記

　このページ、ぼくのブログの中では一番アクセスが多いので、きっとこの問題で悩んでいる人は多いのでしょうね。ということで、またしても発生しましたので原因を追記いたします。

　再現するには、イミディエイトウィンドウで、以下のように実行します。

? Sheet1.Cells(0,1).Value

　セルの行列の数値を正しくセットしていない場合にも発生します。オフィス2013のエクセルの場合、行の値の範囲は、1～1048577、列の値の範囲は1～16384の制限があるようです。なので、VBAで変数を使って行列をセットしている場合は注意が必要です。

　不覚にもこのエラーがまた出てしまいましたが、原因は、if文の条件ミスでした。変数に値をセットできておらず、0で実行してしまったのでした。みなさんも、気をつけましょう。

さらに追記

　ここまで読んでくださって、ありがとうございます。さて、ここまで読まれたあなたは、問題が解決したでしょうか？いやいや、まだ解決していない、ということでもしよけらばコメントいただけますでしょうか。Excelの記事は数えるほどしかないホームページ ですが、前述しましたとおり、このページは中でもアクセス数が多いページです。訪れられた方の問題が少しでも多く解決するといいな、という気持ちでやっておりますので、上記以外の問題でも解決できるようにしたいと思います。新たな問題、お待ちしております。