配列 変数の宣言 ＶＢＡ

質問

こんばんは。

Sub test()
Dim myStr(200) As String
For 行 = 0 To Cells(Rows.Count, 1).End(xlUp).Row
myStr(行) = Cells(行 + 1, 1)
Next
MsgBox Join(myStr, "_")
End Sub

のようなコート゛を作成し、
アクティブシートのＡ列の最終行までを取得し、一つにまとめたいのですが
「Dim myStr(200) As String」の部分で
最終行を取得することは不可能でしょうか？

今回は２００行なので大丈夫なのですが
場合によっては1行～65536行までさまざまです。

なので
Dim myStr(Cells(Rows.Count, 1).End(xlUp).Row) As String
としたらエラーになりました。

最初から
Dim myStr(65536) As String
とするべきでしょうか？

しかしそうすると
myStrの最後がずっと「________」となってしまいます。

どうするのが適切なのかわかりません。
ご教授よろしくお願いします。

回答No.6

Wendy02 · ベストアンサー

こんにちは。

通常は、動的配列にするのが一般的ですが、実際の内容を教えていただかないと、なんとも言えません。コードは現実的な内容ではありません。ループして、最後にJoin でつなぐ必要性が認められません。なお、配列変数は、データ型を決めたほうが格納できます。

>myStrの最後がずっと「________」となってしまいます。

Sub test2()
　　Dim myStr(65535) As String
　　Dim i As Long
　　Dim LastRow As Long
　　Dim buf As String
　　LastRow = Cells(Rows.Count, 1).End(xlUp).Row
　　'最終行までデータ入っていたら一つしか入らない
　　For i = 0 To LastRow
　　　　myStr(i) = Cells(i + 1, 1)
　　Next
　　buf = Join(myStr, "_")
　　buf = Mid(buf, 1, InStr(buf, "__"))
　　MsgBox buf
End Sub

配列を使わない法

Sub test3()
　　Dim i As Long
　　Dim LastRow As Long
　　Dim buf As String
　　LastRow = Cells(Rows.Count, 1).End(xlUp).Row
　　If LastRow < 2 Then
　　　'フルに入っているかチェックする
　　　　If Cells(Rows.Count, 1).Value <> "" Then
　　　　　　LastRow = Rows.Count
　　　　Else
　　　　　　Exit Sub
　　　　End If
　　End If
　　For i = 1 To LastRow
　　　　buf = buf & "_" & Cells(i, 1).Value
　　Next
　　buf = Mid(buf, 2)
　　MsgBox buf
End Sub

お礼

配列を使わない方法もあるのですね。ありがとうございます。

回答No.5

myRange

変数をVariantで宣言する方法もあります。

'---------------------------------　
Sub Test333()
　Dim myStr
　myStr = Range("A1", Cells(Rows.Count, "A").End(xlUp)).Value
　MsgBox Join(Application.Transpose(myStr), "_")
End Sub
'--------------------------------

この場合、変数myStrは配列になってますので、要素を取り出すときは

For R = LBound(myStr) To UBound(myStr)
　　MsgBox myStr(R)
Next R

とします。
以上です。

お礼

ありがとうございます。

回答No.4

DOUGLAS_

＞場合によっては1行～65536行までさまざまです。
　[動的配列変数]・[ReDim ステートメント] につきましては回答が出ておりますので、[ReDim ステートメント] のヘルプを参照なさってください。

　小さなことかも知れませんが、お示しのコードで要素数の指定を
Cells(Rows.Count, 1).End(xlUp).Row
のままで
Dim myStr(200) As String
を
Dim myStr() As String　または　Dim myStr As Variant
ReDim myStr(要素数)
に変えますと、
MsgBox Join(myStr, "_")
の最後に余分な "_" が付きますよね。

　せっかく「For 行 = 0 To」としていらっしゃるのでしたら、要素数の設定は、最後に「- 1」を付けて
Cells(Rows.Count, 1).End(xlUp).Row - 1
とされた方が、後々問題も生じにくいかと存じます。

　ついでに荒技ですが、
Sub oioi()
　MsgBox Join(Application.WorksheetFunction.Transpose( _
　　Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row) _
　　), "_")
End Sub
みたいなこともできます。

　しかし、いずれにいたしましても、対象範囲内に「#VALUE!」などが入るとエラーになりますので、注意が必要です。

　配列の宣言でデータ型を誤ったり、要素数を指定しなかったり（空の括弧を付けたり）、配列に格納し損ねると
＞コンパイル エラー　　配列がありません。
＞実行時エラー '13':　　型が一致しません。
＞実行時エラー '9':　　インデックスが有効範囲にありません。
などの エラー メッセージ が表示され、結構面倒ですが、「何でだろぉ～、何でだろぉ～．．．」と原因を究明していくと、結構面白いですよね。

・・・以上、ど素人の独り言でした。

お礼

なんだか解読不能ですがすごいですね！
ありがとうございます。

回答No.3

Sinogi

動的配列で対応します。
宣言では要素数を明示せず、要素数を確定してから再宣言します。
配列にデータを代入後、さらに要素数を増やすときPreserveを使用すればデータが保持できます。

要素数を変数にして、変数で最終行を取得してください。

Dim 要素数
Dim myStr() As String
・・・

Redim myStr(要素数)

お礼

先ほどもありましたが「動的配列」というのですね。
覚えます。ありがとうございます。

回答No.2

lovesens

動的変数を使ってはどうでしょう。
ReDim Preserve 句を使うと内容を保持したまま配列の要素を増やすことができます。

Dim myStr() As String
For 行 = 0 To Cells(Rows.Count, 1).End(xlUp).Row
ReDim Preserve myStr(行)
myStr(行) = Cells(行 + 1, 1)
Next
MsgBox Join(myStr, "_")

お礼

ありがとうございます。

回答No.1

fujillin

配列の大きさは後で変更することが可能です。
　Dim myStr() As String
　r = Cells(Rows.Count, 1).End(xlUp).Row
　ReDim myStr(r)
みたいなことができます。

ReDim Preserve　を利用すると、既に配列に入っている値を保存したまま最宣言ができます。（条件がありますが）

詳しくは、ヘルプ等をご覧ください。

お礼

できました。ヘルプもちゃんと見てみます。ありがとうございます。