VBA 配列を使ったコードに直していただけますか

"3種の板があります。 板の巾はWaミリ、Wbミリ、Wcミリです。 同じ巾の板は5枚まで使える事とします。 全ての板の組み合わせをワークシートsh1のA1から書き出す事とします。 また、表記はWaＸ1､WbＸ0､WcＸ5 という風にします。下記コードで動くのですが、板の種類や、同じ板を使える枚数を増やすと、シートへの書き出し時間が何時間も掛かってしまいます。下記コードを配列に直していただけないでしょうか。私は配列が苦手なものですので。

Sub 全ての板の組み合わせを書き出す()

Dim Wa As Integer, Wb As Integer, Wc As Integer '板の巾
Dim i As Integer, j As Integer, k As Integer 'カウンター変数

Sheets(""sh1"").Range(""A1"").Value = ""組み合わせ"" '表の見出しを書き出す

'すべての板の組み合わせを書き出す
For i = 0 To 5 'Waの板の数を0から5まで変化させる
For j = 0 To 5 'Wbの板の数を0から5まで変化させる
For k = 0 To 5 'Wcの板の数を0から5まで変化させる
Sheets(""sh1"").Range(""A"" & Rows.Count).End(xlUp).Offset(1, 0).Value = _
""Wa"" & ""x"" & i & "", Wb"" & ""x"" & j & "", Wc"" & ""x"" & k '組み合わせを書き出す
Next k
Next j
Next i

End Sub"

No.3 ベストアンサー 回答者： fujillin 回答日時： 2023/05/06 16:45

こんにちは

単に組み合わせを書き出しているだけのようですが、この処理自体に意味があるのかどうかも疑問ですね。

板の種類をｍ、枚数上限をｎとするなら、0枚を含めた場合、一般的にその組み合わせ数は (n+1)^m になります。
ご提示の3種類の場合で、99枚を超えると1列には表示しきれなくなりますね。
仮に3種類で99枚としても、100万回の計算になるのでそれなりの時間が掛かります。
配列を利用した場合、ご提示の方法よりはかなり速くなることは期待できますが、VBAそのものが決して速くはありませんので、ある程度の時間はかかります。

＞下記コードで動くのですが、～
クォーテーションがおかしいようで、私の環境では動作しません。

以下、3種類限定で枚数に関しては可変にしてみましたが、私の環境では上限99枚の場合で、4～5秒程掛かりました。
（時間はPCの性能に依存しますので、あくまでも参考です。）
種類数も可変にしたければ、値を再帰的に作成すればできると思いますが、すぐにオーバーブローしそうなのと、処理そのものに意味があるとも思えないので省略します。

※ アクティブシートのA列に書き出しますのでご注意。

Sub 全ての板の組み合わせを書き出す()
Dim a, b, c, cnt, v
Const n = 9 ' ←　枚数上限

If n > 99 Then MsgBox "一列に入らない": Exit Sub
cnt = 2
Columns(1).ClearContents
v = Columns(1).Value
For a = 0 To n
　For b = 0 To n
　　For c = 0 To n
　　　v(cnt, 1) = "Wa×" & a & ",Wb×" & b & ",Wc×" & c
　　　cnt = cnt + 1
　　Next c
　Next b
Next a
v(1, 1) = "組み合わせ"
Columns(1).Value = v
End Sub


※ 勝手にな想像をすると、「目的とする幅に近い組み合わせを求めたい」のではないかと思いますが、以前のご質問でも回答したようにエクセルのソルバー機能を利用するのが簡単だと思います。
（VBAをいじくる必要も生じませんので）
https://oshiete.goo.ne.jp/qa/13440077.html

この回答へのお礼

配列のご提示ありがとう御座いました。
そして、私のコードがＷクォーテーションがおかしくなっていまして申し訳ありませんでした。
ｎを99（同じ種類の板を使える最大枚数）にして私のコードで実行すると、18分16秒。ご提示いただいた配列で実行するとなんと3秒で稼働出来ました。
なお、エクセルのソルバー機能は使った事がないのですが、これを機会に使ってみます。エクセルより早く稼働させるには、Pythonで計算させて、エクセルに出力させるという事になるのでしょうね。

お礼日時：2023/05/06 23:20

No.4 回答者： fujillin 回答日時： 2023/05/09 20:26

No3です。

以前のご質問でも、今回でもなさりたいことが明確には示されていないので、よくわからないのですが・・・

＞エクセルのソルバー機能は使った事がないのですが、これを機会に使ってみます。
ソルバーでは複数解を得ることはできませんが、以前の回答でも述べたように組み合わせを選定するにしても、実際には「最小値」だけが判断根拠ではないのではと想像します。

よくわからないので、勝手な想像で、
「目的の幅を数種類の板で埋める際の余りの少ない組み合わせを求めたい」
ということだと仮定すれば、添付図のような方法も考えられます。
・Ｂ1に目標とする幅
・Ｂ2にピックアップする条件
（成り行きで、最小板幅×係数としましたが、添付図の場合 180×0.5＝90以下となる組み合わせをピックアップします）
・Ａ～Ｃ列5行目以降に、使用できる板と幅、枚数上限（個別に指定）を入力
（図のWc、We、Wfのように幅、枚数が0あるいは空白の場合、不使用と判断します）

上記の条件で計算し、解となる組み合わせをＥ列以降にピックアップしています。
例示の場合は、条件に該当する組み合わせは12組で、5行目以降にその組み合わせと残余（＝超過寸法）を列挙しています。
図からもわかるように、この場合には最小値（たまたまピッタリで0ですが）となる組み合わせが2組あることが示されています。
※ 「埋める」のが目的なので、「少しだけ足りない」組み合わせは無視しています。

※ A:C列とE列以降で、縦横を変えてしまいましたが、揃えておいた方が見やすいかも知れません。
なんとなく、数が増加する方向を下方向にしてしまったため、表示内容を入れ替えることになってしまいましたが・・・

なさりたいこと自体が「当たるも八卦」の想像なので、詳細は省きますが、方法としては以前の回答に記した通りの方法です。

なお、
＞エクセルより早く稼働させるには、Pythonで計算させて、
＞エクセルに出力させるという事になるのでしょうね。
計算量にもよりますが、総当たりで組み合わせを計算する方法をとるのならそうかもしれません。
とはいえ、前回回答にも示したように「目標幅／板幅」で当たりを付けることにより、無駄な組み合わせを相当に省くことが可能なはずですので、効率の良いロジックを採用することの方が、手段（＝言語）を変える事よりも遥かに効果的と思います。


※ 勝手な想像ですので、ご参考にもならないのかも知れませんが、「こんなのもあり得るよ」という一例までに。

No.2 回答者： tatsumaru77 回答日時： 2023/05/06 16:40

No1です。

失礼しました。

「提示されたマクロをコピペしてこちらで実行しようとするとエラーになってしまします。」

は

「提示されたマクロをコピペしてこちらで実行しようとするとエラーになってしまいました。」

の誤りです。訂正いたします。

No.1 回答者： tatsumaru77 回答日時： 2023/05/06 16:38

提示されたマクロをコピペしてこちらで実行しようとするとエラーになってしまします。

あなたが実際に実行してきちんと動作しているマクロを提示していただけませんでしょうか。