EXCEL VBAで Arrayの使い方が判りません

質問

あらかじめ、セル上にシート名を入れておき、
Arrayでシートを指定したいのですが、
うまく行きません

内容

Sub Macro2()

　　 'Cells(2, 2) に　「"Sheet1","Sheet3","Sheet6"」
　　と入力されています

　　Dim hani As String
　　Dim hani2 As Variant

　　hani = Cells(2, 2)
　　 hani2 = Array(hani)

'下記がエラーになります

　　Sheets(hani2).Select
　　Sheets(hani2).Copy

'以下略

End Sub

１）セル上にあらかじめ、配列の中身を入力しておく
２）その値を取得後、arrayでシートを指定する
この２件はどうしても避けられないのです

回答No.3

Wendy02 · ベストアンサー

こんばんは。

＞１）セル上にあらかじめ、配列の中身を入力しておく
＞２）その値を取得後、arrayでシートを指定する
＞この２件はどうしても避けられないのです

というリクエストに対して、ご質問のコードの目的が書かれていないので、回答する側にあまり正確には伝わっていないような気がします。なるべく、ご質問される時は、その最終目的を書いてくださいね。

＞１）セル上にあらかじめ、配列の中身を入力しておく

まず、配列の中身などは、存在しません。セル上のデータでしかありませんし、データとしては文字列か数値か、数式しかありません。そして、それは文字列です。

また、通常、セルに書いたシート名を、Array 関数で配列にすることは、めったにしません。

VBA上に、Const SheetNames As String ="Sheet1,Sheet3,Sheet6"

定数をおき、その後で、Split 関数で、配列にするのが普通です。

＞２）その値を取得後、arrayでシートを指定する

たぶん、記録マクロでみたので、そう思い込んだのだと思います。
Sub TestArry()
　d = Array("a", "b", "c")
End Sub
このように、それぞれが独立して、コンマ区切りの構造になっていれば出来ますが、1つのセルでは、データが独立していません。

""Sheet1","Sheet3","Sheet6""

既に、文字列になってしまっているので、クォーテーションマークが、二重になります。

セル上は、以下のように入力します。
B2:
　Sheet1,Sheet3,Sheet6　

このようなデータ構造になっていたら、Split で配列に出来ます。

その上で、Sheets(Hanni2).Select
とします。

以下は、そのサンプルコードです。

Sub TestMacro1()
Dim Hanni As String, Hanni2 As Variant

最初に文字列のチェック
If VarType(Hanni) <> vbString Then Exit Sub
Hanni = Worksheets("Sheet1").Range("B2").Value

'エラーチェックをします。
If InStr(Hanni, """") > 0 Then
　 SheetName = Replace(Hanni, """", "")
End If
'切り分け
　Hanni2 = Split(Hanni, ",")

　On Error Resume Next
　Worksheets(Hanni2).Select
　On Error GoTo 0

　If Err.Number > 0 Then
　　MsgBox Err.Number & " : エラーが発生しています。", vbInformation
　End If

End Sub

なお、Excel97は、
　 SheetName = Replace(Hanni, """", "")
が使えませんので、WorksheetFunction.Substituteを代用します。

お礼

ありがとうございました。
ご指摘の通り、自動記録したマクロのArrayに惑わされていたような気がします。
無事動くようになりました。

回答No.4

imogasi

Sub test07()
Cells(2, 2) = "Sheet1,Sheet2,Sheet3"
s = Split(Cells(2, 2), ",")
For i = 0 To UBound(s)
MsgBox s(i)
Worksheets(s(i)).Cells(10, 1) = "aa"
Next i
End Sub
をやってみてください。
Sheet2を対象にするときはS(1)の１をプログラマがプログラムの中で指定します。

お礼

ありがとうございました。
無事動くようになりました。

回答No.2

maruru01

こんにちは。maruru01です。

まず、Array関数でリストの区切り記号とする「,」ですが、これはあくまでコード上で使用する記号なので、コード上で入力していなければなりません。
セルからの値を直接使用することは出来ません。
文字列を括る記号の「"」も同様です。
したがって、配列化するためには、Split関数を使用します。

hani2 = Split(hani, ",")

これで、配列hani2に
「"Sheet1"」
「"Sheet3"」
「"Sheet6"」
が格納されます。
しかしこれでは、「"」も含んだ文字列がシート名になってしまいます。
したがって前もって「"」を削除しておかなければなりません。
削除するタイミングはセルから値を取得する時がいいでしょう。

hani = Replace(Cells(2, 2), """","")

以上、2行を替えれば上手くいくと思います。

回答No.1

hana-hana3

＞hani2 = Array(hani)

見せかけの代入は出来ません。
hani2 = Array(Cells(2, 2), Cells(2, 3), Cells(2, 4))
のようにするか、

Cell(2,2)に、「Sheet1,Sheet3,Sheet6」と記入して
hani2=Split(Cells(2,2),",") とすると希望の事が出来ます。

＞　　Sheets(hani2).Select
　　Sheets(hani2).Copy

意味不明です。
Sheets(hani2(0)).Select と言う使い方が正しいです。
　配列名（添字）　という使い方をしないと配列の値を得ることが出来ません。
ですから、シートを個々に選択するならループ文等で処理する必要があります。

シートをグループとして扱うなら、
　　 'Cells(2, 2) に「"Sheet1","Sheet3","Sheet6"」
　　Dim hani As String
　　Sheets(hani).Copy
で可能かと思います。