コード記入の効率化のために

質問

VBA初心者です。今まで自動マクロ以上のことをしていなかったのですが、最近コード記入を始めたところ、その便利さに驚いております。
今作成しているマクロにて処理記入がとても長くなるのでどうしたらよいか試しているのですがうまくいきません。
excel VBA に関して2件ご質問いたしますのでご教授お願いします。

(1)Sheetの選択に関して、1つの新規bookの複数のsheetを移動させたいので下記のようにコードを書きましたが、sheet数の条件毎に処理を書くと下記の様に長くなってしまいます。

Select Case Worksheets.Count
Case Is = 22
Worksheets(21).Move
Case Is = 23
Worksheets(Array(21, 22)).Move
Case Is = 24
Worksheets(Array(21, 22, 23)).Move
Case Is = 25
Worksheets(Array(21, 22, 23, 24)).Move
そこで、変数宣言して　WorkSheets(a)　～　WorkSheets(b)
と出来るのでしょうか？　個別指定でなく、範囲指定する方法があるのならお教えください。

(2)　checkbox1　checkbox2　それぞれの真偽ごとに処理を分岐するのに

If UserForm_001open.CheckBox1.Value = True Then
Worksheets("sheet1").Copy before:= _
Worksheets("back")
上記のようにcheckbox毎に処理を記入するのでなく、CheckBoxの番号を変数宣言して、上記のコードの挿入する方法があればお教えください。

質問が長くなりましたが、いかにコードを短く、整理されたものにするかに注意して勉強しております。ぜひご教授願います。

回答No.1

taseki · ベストアンサー

(1)ようするに、Array関数は要素を一つ一つ指定しなければいけないので、範囲で指定できないか、ということですよね？
無い関数は作ってしまえば良いので、
「範囲（最小値と最大値）を与えると、連続した配列を返す」関数を作ってはどうでしょうか。
たとえば以下サンプルです。

-----------------------
Private Function ArraySeq(Min As Integer, Max As Integer) As Variant

Dim intValue() As Integer
Dim intCnt As Integer

ReDim intValue(Max - Min)
For intCnt = Min To Max
intValue(intCnt - Min) = intCnt
Next intCnt
ArraySeq = intValue

End Function
-----------------------

上記を適当な場所（末尾など）に貼り付けておくと、
Worksheets(Array(21, 22, 23, 24))
と書く代わりに
Worksheets(ArraySeq(21, 24))
と書けばよくなります。

※ MaxよりMinが大きい場合のチェックなど、必要に応じて追加してください。

ちなみに蛇足かもしれませんが、書かれたコードは「シートが21枚を超えたら、超えた分のシートを移動」という処理でしょうか。
もしそうなら、Select文の部分は以下のようにも書けます。

-----------------------
If Worksheets.Count > 21 Then
Worksheets(ArraySeq(21, Worksheets.Count - 1)).Move
End If
-----------------------
これでシートが100枚あっても対応可能です。

(2)VBなら便利な「コントロール配列」というのがあるのですが…。
Controls コレクションを使うという手もありますが、解りやすく手っ取り早い方法として、チェックボックスを配列変数に割り当て（代入）してしまうのが簡単だと思います。
-----------------------
Dim chkTest(2) As Control

Set chkTest(0) = CheckBox1
Set chkTest(1) = CheckBox2
Set chkTest(2) = CheckBox3

For intCnt = 0 To 2
If chkTest(intCnt).Value Then
MsgBox (chkTest(intCnt).Name & " はオンです。")
Else
MsgBox (chkTest(intCnt).Name & " はオフです。")
End If
Next intCnt
-----------------------

はじめにSet文がチェックボックスの数だけ必要なのが難点ですが、これははじめに1回だけ行えばいいので、場合によってはFormLoadイベントでフォームを開いた直後にやってしまうのも手です。

ちなみにまた蛇足ですが、

If UserForm_001open.CheckBox1.Value = True Then

↑上記について、True = True は True なので、

If UserForm_001open.CheckBox1.Value Then

と書くだけで良いです。

お礼

まず最初に返事が送れたことをお詫び申し上げます。function　Control宣言や変数の有効期限(範囲）等調べてからお礼をかねて質問するつもりでしたが、手間を取り遅れてしまいました。（言い訳ですね）
丁寧な回答に対し礼を欠き申し訳ありませんでした。

ArreySeqは非常に助かりました。おかげでコードがとても短くなり、例外対応も可能となりました。　Redim　を"再設定"と勝手に思い込んでしまい理解が遅れてしまいました。「動的配列のサイズ」を変数にして、それを基準に範囲を指定するのですね。(2)の　chktest(0)～(2)の意味が解ったのと併せてやっと理解が出来ました。

(2)に関しては　CheckBox　等を変数にするには　Controlで変数宣言するということなのですね　CheckBox" & a & "と書ければ便利だなと思ったのです。ちなみに他の人の質問に対する回答に

If Controls("OptionButton" & a）.Value = True　とあったので
試したがうまくいかなかったので、どうすればよいのか質問させていただきました。
　１つの質問で、何倍もの事を知ることが出来ました。コツコツと順序立てて学ぶ時間と環境がなく、つまみ食いのようなことばかりしてますのでこのお礼自体、内容理解が間違っているかもしれませんが、また機会がありましたらご指導願います。