ここから本文です

エクセルマクロについての質問です。 マクロを勉強し始めて2か月になります。 ...

ぽっちゃり性善説さん

2019/9/1021:59:11

エクセルマクロについての質問です。
マクロを勉強し始めて2か月になります。

下記の配列と再起呼び出しのマクロ動作について不明な点があります。

下記のような設問において、
------
・添付画像の表の全組み合わせを作成して下さい。
サンプルデータでは、組み合わせの数は、8*3*6*5=720通りです。
・各項目はカンマ(,)で区切って下さい。
・出力先は、新規シートを追加し、
1行目に見出しとして"組み合わせ文字"と入れ、
2行目より出力して下さい。
------

これに対しvbaコードが、

------
Sub sample()
Dim ary() As String
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Application.ScreenUpdating = False
Set ws1 = ActiveSheet
Set ws2 = Worksheets.Add
ws2.Range("A1") = "組み合わせ文字"
With ws1
ReDim ary(1 To .Cells(1, Columns.Count).End(xlToLeft).Column)
Call sample_sub(ws1, ws2, ary, 1)
End With
Application.ScreenUpdating = True
End Sub
Private Sub sample_sub(ByVal ws1 As Worksheet, ByVal ws2 As Worksheet, _
ByRef ary() As String, ByVal n As Integer)
Dim i As Integer
With ws2
For i = 2 To ws1.Cells(ws1.Rows.Count, n).End(xlUp).Row
ary(n) = ws1.Cells(i, n)
Debug.Print ary(n)
Debug.Print n
If n >= UBound(ary) Then
.Cells(.Cells(.Rows.Count, 1).End(xlUp).Row + 1, 1) = Join(ary, ",")
Else
Call sample_sub(ws1, ws2, ary, n + 1)
End If
Next
End With
End Sub

になるのですが、添付画像にあるステップイン時にnの値が3に減る理由がわかりません。
今まではn=4 の繰り返しで、ary(n)がA1→B1→C1→D1→D2→D3→D4→D5と来ていたのですが、突如n=3 に減るのはなぜなのでしょうか。

雑駁な質問で恐れ入りますが、回答いただけますと幸いです。

Debug.Print ary,As String,End Sub,Debug.Print n,2 To ws1.Cells,End With,1 To UBound

閲覧数:
51
回答数:
2
お礼:
25枚

違反報告

ベストアンサーに選ばれた回答

30246kikuさん

2019/9/1307:40:44

> 突如n=3 に減るのはなぜなのでしょうか。

再帰 したことによる、単なる勘違いかと??
これを疑問に思うのなら、
n = 4 で呼び出された sample_sub 内で、
D1→D2→D3→D4→D5 と処理した後の i は 7 で終わったいるのに、
i = 3 で処理が続くのは何故??と、同様に思うのでは???

再帰 関係無く、関数内の変数は誰が変更できる??を常に考えられたらと・・・

n = 3 で呼び出された sample_sub 内で、n を変更している?
変更していなければ、End Sub するまでは、n = 3 の状態
n + 1 と n = 4 として再帰呼び出した sample_sub 内の変数とは別物


提示記述で直した方が良さそうな部分

.Cells(.Cells(.Rows.Count, 1).End(xlUp).Row + 1, 1)

.Cells(.Rows.Count, 1).End(xlUp).Offset(1)

効率とか色々考えて記述されたらと・・・
例えば、
書き出す位置を覚えておけば、毎回最終行を求める必要はないとか
シートとの接点(参照・書き出し)を少なくすれば速くなったりとか


勉強中ということなので、以下参考になるところでもあれば・・・
※ いろんな記述・処理方法がありますが・・・

Samp1:
再帰で書きなおしてみた、組合せを1つの文字列にして・・・
展開元を変数に読み込んでおいて、
書出し位置を Range で渡して、随時更新しながら、
毎回 Join で4つ → 1つ・・・じゃなく、ひっ付けるだけにして、
まとめて書き出せるのなら、まとめるようにして・・・

Samp2:
再帰しないで、組合せを元の列数で(1つの文字列にしない)
※ ★~ ~★部分を有効にすると、出来上がったものから文字列へ

※ 元の各列のデータ量を、2倍・3倍・4倍等に増やしてやってみる
※ ☆ 行は、バフッとした時間測定用

Samp2 は結構速いかも・・・
でも、結果を文字列化すると Samp1 より遅くなるみたい



Public Sub Samp1()
   Dim rng As Range
   Dim vA As Variant
   Dim st As Single ' ☆
   st = Timer() ' ☆

   With ActiveSheet
      With .Range("A1").CurrentRegion
         vA = Intersect(.Cells, .Offset(1)).Value
      End With
   End With

   Application.ScreenUpdating = False
   With Worksheets.Add
      With .Range("A1")
         .Value = "組み合わせ文字"
         Set rng = .Offset(1)
         Call Samp1Sub(1, vA, rng, "")
      End With
   End With
   Application.ScreenUpdating = True
   MsgBox Timer() - st ' ☆
End Sub


Private Sub Samp1Sub(ByVal n As Long, vA As Variant _
               , rng As Range, ByVal sSrc As String)
   Dim v As Variant
   Dim i As Long

   If (n > 1) Then sSrc = sSrc & ","
   If (n = UBound(vA, 2)) Then
      ReDim v(1 To UBound(vA), 1 To 1)
      For i = 1 To UBound(vA)
         If (vA(i, n) = "") Then Exit For
         v(i, 1) = sSrc & vA(i, n)
      Next
      i = i - 1
      rng.Resize(i).Value = v
      Set rng = rng.Offset(i)
   Else
      For i = 1 To UBound(vA)
         If (vA(i, n) = "") Then Exit For
         Call Samp1Sub(n + 1, vA, rng, sSrc & vA(i, n))
      Next
   End If
End Sub



Public Sub Samp2()
   Dim rngs As Range, rng As Range, r As Range
   Dim i As Long, j As Long, k As Long, n As Long
   Dim st As Single ' ☆
   st = Timer() ' ☆

   With ActiveSheet
      With .Range("A1").CurrentRegion
         Set rngs = Intersect(.Cells, .Offset(1))
      End With
   End With
   k = rngs.Columns.Count

   Application.ScreenUpdating = False
   With Worksheets.Add
      Set rng = .Cells(2, k)
   End With

   rngs.Columns(k).SpecialCells(xlCellTypeConstants).Copy rng

   For j = k - 1 To 1 Step -1
      Set rng = rng.CurrentRegion
      n = rng.Rows.Count
      i = 0
      For Each r In rngs.Columns(j).SpecialCells(xlCellTypeConstants)
         With rng.Offset(n * i)
            If (i > 0) Then rng.Copy .Cells
            r.Copy .Columns(1).Offset(, -1)
         End With
         i = i + 1
      Next
   Next

'' ★~
'   Dim sF As String
'   Set rng = rng.CurrentRegion.Resize(, k + 1)
'   sF = "=RC[-" & k & "]"
'   For j = k - 1 To 1 Step -1
'      sF = sF & "&"",""&RC[-" & j & "]"
'   Next
'
'   With rng.Columns(k + 1)
'      .FormulaR1C1 = sF
'      .Value = .Value
'   End With
'   rng.Resize(, k).Delete xlShiftToLeft
'' ~★

   rng.Worksheet.Range("A1").Value = "組み合わせ"
   Application.ScreenUpdating = True
   MsgBox Timer() - st ' ☆
End Sub

この回答は投票によってベストアンサーに選ばれました!

ベストアンサー以外の回答

1〜1件/1件中

プロフィール画像

カテゴリマスター

tra********さん

2019/9/1101:41:10

>今まではn=4 の繰り返し
最初にSub sample_subが呼び出される時には、1。
そこから、再帰呼出しされるときには、+1されて2。
さらに、再帰呼出しされ+1されて3。
という繰り返し、で4になったということですよね?
都合、4回のCallを実行したということですね。

>突如n=3 に減るのはなぜなのでしょうか。
4回目のCall先で、 n >= UBound(ary) の条件が成立すると、5回目のCallは実行されません。ので、必然的に4回目のCall先のプロシジャは終了し、3回目のCall先に戻ります。そこのプロシジャでは、nの値は3のまま。
「ByVal n As Integer」とCall先のプロシジャのなかで定義されているので、呼び出し側の変数とは独立したものとして管理されますね。
そもそも、Call sample_sub(ws1, ws2, ary, n + 1)という渡し方なので、呼び出し側の変数値は変更されないですよね?

あわせて知りたい

みんなで作る知恵袋 悩みや疑問、なんでも気軽にきいちゃおう!

Q&Aをキーワードで検索:

Yahoo! JAPANは、回答に記載された内容の信ぴょう性、正確性を保証しておりません。
お客様自身の責任と判断で、ご利用ください。
本文はここまでです このページの先頭へ

「追加する」ボタンを押してください。

閉じる

※知恵コレクションに追加された質問は選択されたID/ニックネームのMy知恵袋で確認できます。

不適切な投稿でないことを報告しました。

閉じる