Excel VBA 勝手に他のsubに移動しちゃう

###困っていること
Call文にて移動したSubの処理をしている際に、EndSubに到達する前に、元のSubに戻ってしまいます。
戻るのを禁止してSubの処理を継続する場合には、どのような記述が必要ですか？

Private Sub Worksheet_Change(ByVal Target As Range)
  Select Case ActiveCell.Column
    Case 1
      '配達先情報を処理する
      Call Haitatsu
    Case 2, 3
’品名の処理をする
      Call nimotsu
  End Select
End Sub

Call文にて標準モジュールに記述したnimotsuというSubに移動します。

'品目コードに基づき用度品名を出力、もしくは、用度品名に基づき品目コードを出力する
Public Sub nimotsu()
'アクティブ（品目コードか用度品名を設定した）セルの行・列番号を変数に取得する
ro = ActiveCell.row: co = ActiveCell.Column
Select Case co
  '商品コードが指定された場合
  Case 2
    '選択された商品コードを変数に記録する
    num(0) = ActiveCell.Value
    If num(0) <> "" Then
      '荷物一覧シートに移動する
      Set sheet1 = Worksheets(3): sheet1.Activate
      '１行目の商品コードを変数に記録する
      i = 1: num(1) = Cells(i, 1)
      '指定された商品コードと一覧の商品コードが一致するまで繰り返す
      Do While num(0) <> num(1)
        '次の行に移動する
        i = i + 1: num(1) = Cells(i, 1)
      Loop
      '一覧に記載されている品名を変数に記録する
      mei(1) = Cells(i, 2).Value
      '１の入数を変数に記録する
      Contents = Cells(i, 3).Value
      '配達できる上限値を変数に記録する
      Limit = Cells(i, 4).Value
      Set sheet1 = Worksheets(1): sheet1.Activate
      Cells(ro, 3).Value = mei(1)
      Cells(ro, 4).Value = Contents
      Cells(ro, 5).Value = Limit
    End If
  End Select
nimotsuの処理を行っていると、WorkSheet（１）に戻って、１行処理しただけで、元のSubに戻ってしまいます。
End Sub

行動規範の内容に同意します

回答4件

Worksheet_ChangeがWorksheets(1)内に記載されていると想定して回答します。

問題を引き起こした主な要因はCells(ro, 3).Value = mei(1)などでセルの値を変更していることです。
これにより、シート上のセルが変更されたためWorksheet_Changeが再度呼び出されていると思われます。

Application.EnableEventsをFalseに設定することでWorksheet_Changeなどの呼び出しを無効化できるため、これを使うのが常套手段でしょう(処理が終わったらTrueに戻しておいてください。)

また間接的に問題を引き起こしかねない要因として、Worksheet_Change内でTargetを使用せずActiveCellを使用していることです。
nimotsu内でそのシートのActiveCellは変わっていないため、同じ処理が何度も繰り返される可能性があります。

投稿2019/08/01 11:49

imihito

総合スコア2166

Yoshikun_0945

2019/08/01 11:52

回答いただきましてありがとうございます。

行動規範の内容に同意します

ベストアンサー

Call文にて移動したSubの処理をしている際に、EndSubに到達する前に、元のSubに戻ってしまいます。

戻っているように「見える」のは、
セルに値を入れたため、「新しくプログラムが実行された」からです。
手動で値を入れようが、マクロで値を入れようが、
セルの値が変わったなら、changeイベントが発生します。
つまり、
セルの値が変わった→changeイベント発生→セルに値を入れる→changeイベント発生→セルに値を入れる→changeイベント発生→
と無限にイベントの連鎖が起きてしまい、処理が終了できません。

changeイベントのプロシージャ内で、さらにセルに値を入れたい場合は、
Application.EnableEvents = False
として、イベントの発生を抑制することが出来ますので、
以下のようにしてみたらいかがでしょうか？

ExcelVBA
1Option Explicit
2
3Private Sub Worksheet_Change(ByVal Target As Range)
4    Select Case Target.Column
5        Case 1
6            '配達先情報を処理する
7            Call Haitatsu
8        Case 2, 3
9            '品名の処理をする
10            Call nimotsu(Target)
11    End Select
12End Sub
13
14'品目コードに基づき用度品名を出力、もしくは、用度品名に基づき品目コードを出力する
15Public Sub nimotsu(ByRef rngTarget As Range)
16    Select Case rngTarget.Column
17            '商品コードが指定された場合
18        Case 2
19            If IsEmpty(rngTarget.Value) = False Then
20                i = 1
21                '商品コードの検索
22                Do While rngTarget.Value <> Worksheet(3).Cells(i, 1).Value
23                    '次の行に移動する
24                    i = i + 1
25                    
26                    '商品コードが無かったら処理を抜ける
27                    If IsEmpty(Worksheet(3).Cells(i, 1).Value) = True Then Exit Sub
28                Loop
29
30                'セルに値を入れるときに、またchangeイベントが発生するのを抑制する。
31                Application.EnableEvents = False
32
33                '値の転記
34                Me.Cells(ro, 3).Value = Worksheets(3).Cells(i, 2).Value
35                Me.Cells(ro, 4).Value = Worksheets(3).Cells(i, 3).Value
36                Me.Cells(ro, 5).Value = Worksheets(3).Cells(i, 4).Value
37
38                'イベント処理の再開
39                Application.EnableEvents = True
40            End If
41    End Select
42End Sub