Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Excel 2013)
WorksheetのChangeイベントで別シートのセル範囲を参照したい
投稿日時: 21/11/23 22:02:14
投稿者: kuromame

WorksheetのChangeイベントで、別シートのセル範囲を参照する方法を教えてください。
WorkbookのChangeイベントに記述すると実行できるのですが、Worksheetに記述すると希望どおりに動作しません。
「入力シート」=データ入力シート(Worksheet_Change記述対象)
「基本情報」=参照リストを置いてあるシート
「簡易入力リスト」=「基本情報」シートにある参照リストのセル範囲を名前定義 範囲はブック
希望動作は、「入力シート」のI列に特定のデータを入力した場合に、「簡易入力リスト」から決まったデータを入力する。
おそらく変数myListに「簡易入力リスト」のデータを入れられていないのだと思います。
WorkbookのChangeイベントで使えてはいるのですが、Sheetイベントなのに変な気がするので、変更できるのであれば方法をご教示いただけますか。
 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim myList As Variant
    Dim myRange As Range
    Dim myWS As Worksheet
    Dim buf As Variant
    Dim i As Long
    Dim j As Long
 
    If Sh.Name <> "入力シート" Then Exit Sub
    If Target.Rows.Count <> 1 Then Exit Sub
    If Target.Columns.Count <> 1 Then Exit Sub
    If Target.Column = 9 Then GoTo ClumI
    Exit Sub
 
ClumI:
    myList = Range("簡易入力リスト").Value
    For i = 1 To UBound(myList)
        If Target.Value = myList(i, 1) Then
            データセットを作成→「入力シート」にデータ貼り付け
        End If
    Next i
End Sub
 
これを「入力シート」の
Private Sub Worksheet_Change(ByVal Target As Range) に記述して
' myList = Range("簡易入力リスト").Value 
を下記のように変更してみましたが、すべてだめでした。
データが入力されるはずのセルのデータが消えます。
' If Sh.Name <> "入力シート" Then Exit Sub は削除
 
    Set myWS = Sheets("基本情報")
    With myWS
        Set myRange = .Range("簡易入力リスト")
    End With
    myList = myRange.Value
 
    With Worksheets("基本情報")
        Set myRange = .Range("簡易入力リスト")
    End With
    myList = myRange.Value
 
    Set myRange = Range("簡易入力リスト")
    myList = myRange.Value
 
よろしくお願いいたします。

回答
投稿日時: 21/11/23 23:04:06
投稿者: simple

たぶん、基本的にはそれでよいと思いますが、

ClumI:
    myList = Worksheets("基本情報").Range("簡易入力リスト").Value
    For i = 1 To UBound(myList)
        If Target.Value = myList(i, 1) Then
            Application.EnableEvents = False '■■■
            'データセットを作成→「入力シート」にデータ貼り付け
            Application.EnableEvents = True '■■■
        End If
    Next i
End Sub

としますが、もし
Application.EnableEvents = False
などの調整をしていないとすると、セルを更新したときに、
再度イベントプロシージャが実行され、
想定外のことが起きている可能性があります。

投稿日時: 21/11/24 00:08:28
投稿者: kuromame

simple様、ありがとうございます。
長すぎると思ってコード省略してしまいました。
一応 Application.EnableEvents = Falseは入れています。位置がおかしいのかな?
 
ClumI:
    If Cells(Target.Row + 1, "L") <> Empty And Cells(Target.Row, "L") <> Empty Then
        Exit Sub
    End If
    ReDim buf(1, 4)
    myList = Range("簡易入力リスト").Value
    For i = 1 To UBound(myList)
        If Target.Value = myList(i, 1) Then
            For j = 1 To 4
                buf(1, j) = myList(i, j + 1)
            Next j
            Application.EnableEvents = False
            Worksheets("入力シート").Activate
            Range(Cells(Target.Row, "I"), Cells(Target.Row, "L")).Value = buf
            Application.EnableEvents = True
            Exit For
        End
    Next i
 
「入力シート」にはデフォルトで値が入っているのですが、Worksheet_Changeイベントで実行すると入力済みのデータも消えます。
なので、myListに値が入らない→bufに値が入らない、なのかと思っているのですが。
Sheetイベントは使ったことがなくて、初めは別シートは参照できない仕様かと思ったのですが、それも変と思いお尋ねしました。

投稿日時: 21/11/24 00:45:33
投稿者: kuromame

前コメントはWorkbook_SheetChangeの記述からのコピーです。
Worksheet_Changeの記述の現状です。現象は同じです。
「簡易入力リスト」のセル範囲=基本情報!$H$3:$L$11
 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myList As Variant
    Dim myRange As Range
    Dim myWS As Worksheet
    Dim buf As Variant
    Dim i As Long
    Dim j As Long
 
    If Target.Rows.Count <> 1 Then Exit Sub
    If Target.Columns.Count <> 1 Then Exit Sub
    If Target.Column = 9 Then GoTo ClumI
    Exit Sub
 
ClumI:
    If Cells(Target.Row + 1, "L") <> Empty And Cells(Target.Row, "L") <> Empty Then
        Exit Sub
    End If
    ReDim buf(1, 4)
    myList = Worksheets("基本情報").Range("簡易入力リスト").Value
    For i = 1 To UBound(myList)
        If Target.Value = myList(i, 1) Then
            For j = 1 To 4
                buf(1, j) = myList(i, j + 1)
            Next j
            Application.EnableEvents = False
            Range(Cells(Target.Row, "I"), Cells(Target.Row, "L")).Value = buf
            Application.EnableEvents = True
            Exit For
        End If
    Next i
    Exit Sub
End Sub

回答
投稿日時: 21/11/24 08:36:34
投稿者: simple

ああそうでしたか、じゃあ違う原因ですね。
ところで、デバッグの仕方はご存じなんですか?
こうした自動実行のプロシージャでもステップ実行は可能ですよ?
 
If Sh.Name <> "入力シート" Then Exit Sub
ブレークポイントを設定しておいて(もしくは、STOPを挿入してもよいでしょう)、
F8キーを押して一行ごとに実行してみてください。
 
ローカルウインドウで、myListやbufにどんな値が設定されるかを確認してください。
こちらであれこれ推測するより、あなたが確認するのが確実です。

回答
投稿日時: 21/11/24 11:44:29
投稿者: sk

引用:
ReDim buf(1, 4)

・そのモジュールのモジュールレベルに
 Option Base 1 ステートメントが記述されていない限り、
 上記の ReDim ステートメントによって再定義された配列 buf の
 1 次元目の添え字の最小値は 0 、最大値は 1 であり、
 2 次元目の添え字の最小値は 0 、最大値は 4 である。
 (つまり 2 行 5 列分の領域がある)
 
--------------------------------------------------------------
 
ReDim buf(0, 3)
 
引用:
おそらく変数myListに「簡易入力リスト」のデータを入れられていないのだと思います。

引用:
For j = 1 To 4
    buf(1, j) = myList(i, j + 1)
Next j

・buf の 0 行目には何の値も代入されていない。
 (代入されているのは 1 行目の 1 列目から 4 列目であり、
 0 列目は空のままである)
 
--------------------------------------------------------------
 
buf(0, j - 1) = myList(i, j + 1)
 
引用:
If Target.Column = 9 Then GoTo ClumI

引用:
Range(Cells(Target.Row, "I"), Cells(Target.Row, "L")).Value = buf

・buf の代入先であるセル範囲の行数が 1 行である場合、
 代入されるのは buf の最初の行(空の 0 行目)のみである。
 値が格納されている buf の 1 行目はどこにも代入されない。
 
・上記のロジックにおいて、Cells(Target.Row, "I") と Target は
 同一のセルへの参照であるため、Target の値は buf(0, 0) の値で上書きされる。
 
--------------------------------------------------------------
 
Range(Cells(Target.Row, "J"), Cells(Target.Row, "M")).Value = buf
 
--------------------------------------------------------------
 
配列の添え字の範囲を明示的に指定したいのであれば、
次のようになさればよろしいでしょう。
 
--------------------------------------------------------------
 
ReDim buf(1 To 1, 1 To 4)
 
--------------------------------------------------------------

回答
投稿日時: 21/11/24 13:59:35
投稿者: simple

大変わかりやすい回答コメントをいただき、ありがとうございます。
もちろん私も気づいていましたが、ご自分で気づいていただきたいと思い、
あえてデバッグをお願いしたところです。
説明用のコメントは用意してありました。以下です。折角なので書いておきます。
 
VBAでの配列のインデックスは 0から開始するのがデフォルトになっています。
従って、
Redim buf(1,4)と宣言すると、これは
Redim buf(0 To 1,0 To 4)と宣言したのと同じです。2行5列の配列になってしまいます。
 
そのうえで、bufの2行目の、2列以降にデータをセットしたことになりますから、
Range(Cells(Target.Row, "I"), Cells(Target.Row, "L")).Value = buf
は、何も入っていない1行目の値を指定セルに書き込むことになります。
 
対応策は、
(1) Redim buf(1 To 1,1 To 4)と宣言するか(これを推奨します)、
(2) モジュールの先頭に、Option Base 1 とするかです。
    これは、配列のインデックスが1から始まることを強制する効果があります。
    Redim buf(1,4)と宣言すると、
    Redim buf(1 To 1,1 To 4)と宣言したのと同じことになります。
 
=====
質問者さんは既に実行されているかもしれませんが、時間の関係でまだでしたら、
実際にデバッグをして、ローカルウインドウの使い方など是非マスターしていただきたいと思います。
なぜ空白になるんだろうか、という疑問を持ってながめれば、判明することだからです。
今後も色々な場面できっと役に立つものとなるものと思います。
 
# もちろんskさんの回答には感謝しているわけで、誤解が無いようにお願いします。

回答
投稿日時: 21/11/24 15:52:48
投稿者: Suzu

思い通りにならない 原因については、既にコメントがありますので、そちらには触れませんが
行いたい事に対しての別案と言いますか、こんな事も出来ますよ。と参考までに。
 
 
結局行いたい事は、VLOOKUP関数の動作ですよね。
 
その動作条件について
・I列入力に限定
・1セルづつ入力
・L列 の 入力セルと同じ行と その次の行 が 空白の場合
を満たした時に、VLOOKUPを動作させたい。
 
ただ、上記を 数式にすると結構複雑で長い式になるのでVBA で行うという意図であれば。。
動作条件を VBAで判定し、値の取得自体は、VLOOKUP関数を使えば良いかと。
 
 

Private Sub Worksheet_Change(ByVal Target As Range)
  '1セルのみ以外の入力、I(9)列目以外のとき Exit
  If Target.Cells.Count <> 1 Or Target.Column <> 9 Then Exit Sub
  'L列の、入力行とその次の行の値の BLANK数が 2以外 の時 Exit
  If Application.WorksheetFunction.CountBlank(Cells(Target.Row, "L").Resize(2)) <> 2 Then Exit Sub
  '簡易入力リストの 1列目に 入力した値が含まれる数を数え、0 の時 Exit
  If Application.WorksheetFunction.CountIfs( _
          Worksheets("基本情報").Range("簡易入力リスト").Range("簡易入力リスト").Columns(1), _
          Target _
                ) = 0 Then Exit Sub

  Application.EnableEvents = False
  '入力セルの 次の列を含め 4列 に対し
  With Target.Offset(, 1).Resize(, 4)
    'VLOOKUP関数 の数式を代入 (数式を代入した列の -8 は、簡易入力リスト の 2列目〜5列目を指定する為)
    .FormulaR1C1 = "=VLOOKUP(RC9,簡易入力リスト,COLUMN()-8,0)"
    '数式の結果を、値として代入
    .Value = .Value
  End With
  Application.EnableEvents = True
End Sub

投稿日時: 21/11/24 21:42:19
投稿者: kuromame

sk様、simple様、ありがとうございます。
Worksheetの先頭にOption Base 1を書いたら希望通り動きました。
Workbookの先頭には記述してありました。
標準モジュールでは常に記述して1から数えるのが癖になっているので、記述もれしているとは思っていなかったです。
同じコードなのにSheetとBookで結果が違うので訳が分かりませんでしたが、単純なミスが原因とは。
すっきりしました。ありがとうございました。
 
simple様、デバッグの方法ありがとうございました。
本当にこれは知りたかったのです。
シートに入力しないと動かないし、動かすと一瞬で終わるので、デバッグの方法がわかりませんでした。
ウォッチ式見たらbuf(0)、myList(1)から始まっていて一目瞭然でした。
ブレイクポイントの使い方初めて実感しました。
 
そしてsimple様の代替案、非常に勉強になりました。
実行しながら追加していった動作条件が、シンプルにまとまっている!
セルに関数を入れて計算させて値に置き換えるとか、考えもしませんでした。
VBAよりシート上の関数を使う方が多い私には、R1C1形式に慣れればわかりやすいかもと思いました。
 
お二方とも本当にありがとうございました