VBAエラー「スタック領域が不足しています」

質問

C2とD2のセルに英文字を入力した時，先頭文字を大文字にするVBAを組んだのですが，いざ実行すると「スタック領域が不足しています」と出てしまいます。以下にコードを示します。

'Sheet1
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
　　If Target.Row = 2 And Target.Column = 3 Then
　　　　Range("C2").Value = StrConv(Range("C2").Value, vbProperCase)
　　End If
　　If Target.Row = 2 And Target.Column = 4 Then
　　　　Range("D2").Value = StrConv(Range("D2").Value, vbProperCase)
　　End If
End Sub

なぜこのようなエラーが出るのでしょうか。考えれば考えるほど合っているように思えて仕方なくなったので，ミスがあればご指摘をおねがいします。

回答No.1

emsuja · ベストアンサー

簡単です
一回目に実行された時に C2 の文字が置き換わって、さらに置き換わったことに関して再度同じイベントが起きて・・・・・
この繰り返しでこの部分が無限に繰り返されているからです

こう書きかえればすぐにわかると思います

Private Sub Worksheet_Change(ByVal Target As Range)
　　If Target.Row = 2 And Target.Column = 3 Then
　　　　Range("C2").Value = StrConv(Range("C2").Value, vbProperCase)
　　End If
　　If Target.Row = 2 And Target.Column = 4 Then
　　　　Range("D2").Value = StrConv(Range("D2").Value, vbProperCase)
　　End If
Msgbox "事象発生"
Stop
End Sub

C2 だけに関しては下記のように書き換えればオッケイと思います

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 2 And Target.Column = 3 Then
If Range("C2").Value = StrConv(Range("C2").Value, vbProperCase) Then Exit Sub
Range("C2").Value = StrConv(Range("C2").Value, vbProperCase)
End If
End Sub

さらに、 D2 のセルに関しても同じ事象が発生していますから、そのあたりは Flag を立てるなりして問題解決してください。

お礼

なるほど！！ 確かに言われてみればループしそうですね。なぜ質問する前に気づけなかったのか。。。
回答ありがとうございます。

回答No.7

imogasi

ＶＢＡで、シートのセルのChangeイベントを使うのは、初めてらしいね。「スタック領域が不足しています」を見ても気づかないのは、他言語もやったことないのかな。
私も時々、まづ不注意で失敗する。ただ再々経験しているので驚かない。
１つのＣｈａｎｇｅイベントが発生して、何か処理をして、セルの値を書き換える（代入する。他のセルへの代入の場合も含めて。）前に、この処理が終わるまではChangeイベントを受けつかないようにする必要がある。ぐるぐる周りが発生しないようにね。
そういうこと（抑止）がＶＢＡで可能です。Application.EnableEvents = False というものです。
Application.EnableEvents = False
ActiveWorkbook.Save
Application.EnableEvents = True
のように。
Ａｐｐｌｉｃａｔｉｏｎとあるようにエクセルアプリ全体に対するもので（他シートのセルも）、このChangeイベント処理が終わってＥｎｄＳｕｂの前にApplication.EnableEvents = Trueを入れて脱出しないと、その後、イベントがすべて効かなくなるので注意。
＞スタック領域が不足しています
はスタックオーバーフローとも言い、システムで用意しているスタック領域は限りがあり、サブルーチン的飛び方をするとき、そこにその戻り番地を積んで行く。１つサブルーチンから戻るごとに戻り先を１つ使って消す（崩す）。それでサブルーチンに飛ぶ深さが深くなっても、戻り先を間違えないで済む古典的な手法です。典型的なのが再帰（リカーシーブ）処理の場合に起こりやすい。どの言語でも戻り先の管理には、同じようなスタックを使うやり方をするようだ。
＞If Target.Row = 2 And Target.Column = 3 Then
　　　　Range("C2").Value = StrConv(Range("C2").Value, vbProperCase)
　　End If
　　If Target.Row = 2 And Target.Column = 4 Then
の部分（セル範囲限定）で２つのセルをＩＦ文で２度聞いているが、
http://officetanaka.net/excel/vba/tips/tips118.htm
に解説されているように、Intersectメソッドを使える。
まだそういう使用経験がないようだ。
参考　WEBで照会すると記事がたくさん出る　
スッタクとキュー
http://akita-nct.jp/yamamoto/lecture/2005/2E/test_4/html/node2.html　ほか
再帰処理とスタック
http://www.geocities.co.jp/SiliconValley-Bay/7437/c/c2b.htm　ほか
ーー
参考
単語の先頭を大文字にする１方法
エクセル関数と同じくProperが使えるようです。
Sub test01()
Range("c1") = Application.WorksheetFunction.Proper(Range("a1"))
End Sub

お礼

回答ありがとうございます。
仰るとおりchangeの記述は今回が初でした。
properも使えるんですね，まだまだ勉強の余地があると実感しました。。。

回答No.6

chie65536（@chie65535）

誤字訂正。

誤：再起呼び出し

正：再帰呼び出し

回答No.5

chie65536（@chie65535）

追記。

以下のように、再起呼び出しをさせない方法もあります。

Private Sub Worksheet_Change(ByVal Target As Range)
　　Application.EnableEvents = False
　　For Each Tgt In Target
　　　　If Tgt.Row = 2 And (Tgt.Column = 3 Or Tgt.Column = 4) Then
　　　　　　Tgt.Value = StrConv(Tgt.Value, vbProperCase)
　　　　End If
　　Next
　　Application.EnableEvents = True
End Sub

「Application.EnableEvents = False」によって「Worksheet_Changeの中でセルの値を書き換えても、Worksheet_Changeが再び呼び出されない」ので「既に書き換え済みかどうか？」を調べる必要がなくなります。

そして、関数から戻る前に「Application.EnableEvents = True」によって、セルの値が書き換えられたらWorksheet_Changeが呼ばれるように、元に戻しています。

「既に書き換え済みかどうか？」を調べる必要が無いので、こちらの方がスマートです。

回答No.4

dogs_cats

数値変更はセル１つしかありませんよね。
Targetはrangeオブジェクト変数なのでセルアドレスを指定する必要はありません。

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 2 And (Target.Column = 3 Or Target.Column = 4) Then
Target.Value = StrConv(Target.Value, vbProperCase)
End If
End Sub

お礼

なるほど！ その手があったんですね。
全く思いつきませんでした。非常に参考になりました。
回答ありがとうございます。

回答No.3

chie65536（@chie65535）

Worksheet_Changeの中でセルの値を書き換えると、再帰的にWorksheet_Changeが発生します。

すると、再帰的に呼ばれたWorksheet_Changeの中でセルの値が「同じ物」に書き換えられ、再び再帰的にWorksheet_Changeが発生します。

すると、再び再帰的に呼ばれたWorksheet_Changeの中でセルの値が「同じ物」に書き換えられ、更に再び再帰的にWorksheet_Changeが発生します。

すると、更に再び再帰的に呼ばれたWorksheet_Changeの中でセルの値が「同じ物」に書き換えられ、更に更に再び再帰的にWorksheet_Changeが発生します。

「再帰的な呼び出し」では「スタック領域が消費される」ので、これの繰り返しで、どんどんスタック領域が減っていきます。

そして、スタック領域を使い切ると「スタック領域が不足しています」のエラーで止まります。

以下のようにしましょう。

Private Sub Worksheet_Change(ByVal Target As Range)
　　Dim Str As String
　　For Each Tgt In Target
　　　　Str = StrConv(Tgt.Value, vbProperCase)
　　　　If Tgt.Value <> Str And Tgt.Row = 2 And (Tgt.Column = 3 Or Tgt.Column = 4) Then
　　　　　　Tgt.Value = Str
　　　　End If
　　Next
End Sub

このプログラムでは「コピペによって、一気に複数セルが変更された時」にも対応しています。

また「書き換えによって再帰的に呼ばれても、書き換え済みなら２度は書き換えないので、再び再帰的には呼ばない」ので、スタックは消費しません。

お礼

回答ありがとうございます。
確かに，自分で変換したその改変にも反応していたら無限ループになってしまいますよね。
示していただいたコードのように，さまざまな状況下で適切に処理できるようなものを目指していきたいです。
複数回答をいただいたようですが，このお礼コメントをもって複数分とさせていただきます。