[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『RowSource 範囲指定の方法』（あみな）
はじめまして、今年からVBAの勉強を始めた
あみなと申します。
難しい事ばかりなので、教えていただけたら嬉しいです。
/////////////////////////////////////////////////////////////////////////
目次
1. ComboBox へのコーディングはこれでいいのでしょうか？
2. ComboBox が複数の時はどうやって書けばいいのでしょうか？
3. RowSource って、そもそも必要なのでしょうか？
4. 可読性は大事ですが、10行も同じ事を書きたくない？
/////////////////////////////////////////////////////////////////////////
◆RowSource用の、サンプルテーブルです。
|[A] |[B] |[C] |[D]
[1]|No. |B1 |C1 |D1
[2]|No.1|B001|C001|D001
[3]|No.2|B002|C002|D002
[4]|No.3|B003|C003|D003
[5]|No.4|B004|C004|D004
[6]|No.5|B005|C005|D005
[7]|No.6|B006|C006|
[8]|No.7|B007| |
1. ComboBox へのコーディングはこれでいいのでしょうか？
ユーザーフォームへ、ComboBoxの配置が3個程度なら可読性も
考慮すると、これでいいのでしょうか？
Option Explicit Private Sub UserForm_Initialize()
Me.StartUpPosition = 0
Me.Top = 280
Me.Left = 500
Dim lRow(1 To 3) As Long ' lRow : 行数カウント用の変数
Dim myData(1 To 3)
With Worksheets("Sheet2")
lRow(1) = .Range("B" & Rows.Count).End(xlUp).Row
lRow(2) = .Range("C" & Rows.Count).End(xlUp).Row
lRow(3) = .Range("D" & Rows.Count).End(xlUp).Row
myData(1) = .Range("B2:B" & lRow(1)).Value
myData(2) = .Range("C2:C" & lRow(2)).Value
myData(3) = .Range("D2:D" & lRow(3)).Value
End With
With ComboBox1
.Locked = False
.ColumnCount = 1
.ColumnWidths = "60"
.ListWidth = "63"
.List = myData(1)
'.RowSource = "Sheet2!B2:B" & lRow(1)
.SelectionMargin = 0
.ListIndex = 0
End With
With ComboBox2
.Locked = False
.ColumnCount = 1
.ColumnWidths = "60"
.ListWidth = "63"
.List = myData(2)
'.RowSource = "Sheet2!C2:C" & lRow(2)
.SelectionMargin = 0
.ListIndex = 0
End With
With ComboBox3
.Locked = False
.ColumnCount = 1
.ColumnWidths = "60"
.ListWidth = "63"
.List = myData(3)
'.RowSource = "Sheet2!D2:D" & lRow(3)
.SelectionMargin = 0
.ListIndex = 0
End With
'Debug.Print lRow(1): Debug.Print lRow(2): Debug.Print lRow(3)
End Sub
2. ComboBox が複数の時はどうやって書けばいいのでしょうか？
複数の時とは、ComboBoxが5個以上になる時です。
勿論、ColumnWidthsや、ListIndex等は変更しない事とします。
補足ですが下記のコードでは、RowSourceは消してしまいました。
Microsoft.comにて、MACで rowsource がない場合にの記事を参考に書きました。
※URLが長いので検索するなら下記でお願いします。
mac2011userform,comboboxでrowsourceがない。
3. RowSource って、そもそも必要なのでしょうか？
消しても反映されるならこれでいいのでしょうか？
Private Sub UserForm_Initialize()
Me.StartUpPosition = 0
Me.Top = 280
Me.Left = 500
Dim lRow(1 To 3) As Long
Dim myData(1 To 3)
With Worksheets("Sheet2")
lRow(1) = .Range("B" & Rows.Count).End(xlUp).Row
lRow(2) = .Range("C" & Rows.Count).End(xlUp).Row
lRow(3) = .Range("D" & Rows.Count).End(xlUp).Row
myData(1) = .Range("B2:B" & lRow(1)).Value
myData(2) = .Range("C2:C" & lRow(2)).Value
myData(3) = .Range("D2:D" & lRow(3)).Value
End With
Dim i As Integer
For i = 1 To 3
With UserForm1.Controls("ComboBox" & i)
.Locked = False
.ColumnCount = 1
.ColumnWidths = "60"
.ListWidth = "63"
.List = myData(i)
'.RowSource = myData(i)' ERROR
.SelectionMargin = 0
.ListIndex = 0
End With
Next i
End Sub
4. 可読性は大事ですが、10行も同じ事を書きたくない？
仮に、ComboBoxが10個になった時は、下記の部分を簡略してコーディング
した方がいいのでしょうか？ 変数だらけになりそうです。
With Worksheets("Sheet2")
lRow(1) = .Range("B" & Rows.Count).End(xlUp).Row
lRow(2) = .Range("C" & Rows.Count).End(xlUp).Row
lRow(3) = .Range("D" & Rows.Count).End(xlUp).Row
myData(1) = .Range("B2:B" & lRow(1)).Value
myData(2) = .Range("C2:C" & lRow(2)).Value
myData(3) = .Range("D2:D" & lRow(3)).Value
End With
難しいですね。挑戦してますが…失敗の連続です。
ご教授いただけたら、嬉しいです。
よろしくお願いします。
＜ 使用 Excel：unknown、使用 OS：unknown ＞
必要な人には必要かもしれないけど、
私は使った事があるような気もするけど、使った記憶がない程度。
ほぼ使わない。
（JPS） 2021/06/08(火) 22:44
コーディングの良し悪しは私にはちょっと言えませんけど..
>RowSource って、そもそも必要 RowSource指定なら、途中でリストの並び順を変えたい時に参照元のSortで対応できる! という。(あんまり無い話ですが) リストの内容を書き換えたい場合にも参照元の内容を書き換えれば連動してくれるし!! (あ、それはListでも手間同じだった...) あ! ColumnHeads欲しい時とか。複数列のリストを表現したい時、たまーに欲しいです! (ComboBoxではあんまり無いか) んとね、1列目が文字列項目で2列目が桁区切り付き右揃え数値のリストとかを作りたい時、シート上の方が作り易い!! ...とか
...いや、あの。 結構便利な場面もあるんですよ? (たまに...^^;)
>10行も同じ事を書きたくない Forループで何とか出来る様ならそれで。って感じですかね。 そもそもComboBoxの名前をループに対応できる名前にすれば、その部分だけは割と短く書けると思います。 但し、そのせいで他の部分の可読性を損なう様なら、やめておくべきでしょうけどね。 (何のComboBoxかを分かり易い名前にした方が、結果全体としては可読性が高かったりしますし)
（白茶） 2021/06/08(火) 23:07
プロパティウィンドウで設定したらどうですか。
設定した内容をコピーしていけば同じものができますよ。
その分コードは省けます。
但し RowSource は行が増えた時を見込んで範囲を多めにとっておいた方が良いです。
＞RowSource って、そもそも必要なのでしょうか？
リストが変動するものはこれを使用し変動しないものは Add Item を使用しています。
これは私流のやり方です。
（れれ） 2021/06/08(火) 23:19
配列にぶち込んでやれば簡単では？
例えば、
cblt = array(1,2,3,4,5,6,7,8,9)
コンボボックス１.list = cblt
2列以上なら、2次元配列を使えばいいし。
RowSource は、増えた場合再設定してやればいいし。
1度クリアしなければダメだったら、
RowSource = vbnullstring
とかで消して再設定。
（JPS） 2021/06/08(火) 23:38
皆さま、昨夜は遅い時間にご意見をいただきまして
ありがとうございました。
＞RowSource 私は使った事があるような気もするけど、使った記憶がない程度。
ほぼ使わない。（JPS）さん
はい、臨機応変にだけど…必要を感じないご意見ありがとうございました。
私も、要らないのではないかと思っておりますが…勉強します。（あみな）
＞RowSource...いや、あの。 結構便利な場面もあるんですよ? (たまに...^^;) （白茶）さん
RowSource の必要性がある場面もある...ご意見ありがとうございました。
もっと多くのパターンを勉強します。（あみな）
＞プロパティウィンドウで設定したらどうですか…（れれ）さん
そうですよね。1回設定しちゃえば、そうそう変更しないですよね。
ColumnWidths等は、プロパティウィンドウで設定することにします。
と言うことで、下記の部分は出来るだけスッキリさせちゃいます。（あみな）
Dim i As Integer
For i = 1 To 3
With UserForm1.Controls("ComboBox" & i)
.List = myData(i)
.ListIndex = 0
End With
Next i
.RowSource = myData(i)
RowSource 上記の部分は書き方が解らないのでERRORが出ちゃうので取っ払います。
ListIndex は、折角Withで書いたので残しておきます。(∀｀*ゞ)ｴﾍﾍ
＞RowSource リストが変動するものはこれを使用し変動しないものは Add Item を使用しています。（れれ）さん
リストが変動するものですね…ウーンComboBox連動系の時なのかな？いや違うかな？
WorksheetFunction.VLookup のような時なのかな？… 勉強します。（あみな）
＞可読性は大事ですが、10行も同じ事を書きたくない？
＞Forループで何とか出来る様ならそれで。って感じですかね。（白茶）さん
＞配列にぶち込んでやれば簡単では？（JPS）さん
はい、ガンバッてしてますが… 笑わないでください。
どっちも失敗してますww
↓こうしてみたり
Private Sub UserForm_Initialize()
Me.StartUpPosition = 0
Me.Top = 280
Me.Left = 500
Dim lRow As Long
Dim myData(1 To 3)
Dim i As Integer: Dim j As Integer
For i = 1 To 3
For j = 2 To 4
With Worksheets("Sheet2")
lRow = .Range(Cells(2, j), Cells(Rows.Count).End(xlUp)).Row
myData(1) = .Range("B2:B" & lRow).Value
myData(2) = .Range("C2:C" & lRow).Value
myData(3) = .Range("D2:D" & lRow).Value
End With
With UserForm1.Controls("ComboBox" & i)
.List = myData(i)
End With
Next j
Next i
End Sub
↓ちょっとハッキリわすれましたが…こうしてみたり
For Each v In Array("B2:B" & lRow, "C2:C" & lRow, "D2:D" & lRow)
.RowSource = v
無謀でしたね >< …勉強します。（あみな）
（あみな） 2021/06/09(水) 12:58
RowSource = "文字列"
が基本だからいけると思います。
んで、シートも指定。
（JPS） 2021/06/09(水) 13:41
この部分で
lRow = .Range(Cells(2, j), Cells(Rows.Count).End(xlUp)).Row
Range(Cell1, Cell2) Cell1とCell2は、
「セル範囲の左上隅と右下隅のセルを指定」
と考えてしたのですが...
Debug.Print lRow をすると lRow = 1 あれれ？
↓確認するとやはり
For Each v In Array("B2:B" & lRow, "C2:C" & lRow, "D2:D" & lRow)
Debug.Print v 範囲が v = B2:B1 上に向かってしまいました？
|落|Å｀*)･ﾟ･｡o○(落ちこみギミ・・)
現在のコードは下記になってます。
Private Sub UserForm_Initialize()
Me.StartUpPosition = 0
Me.Top = 280
Me.Left = 500
Dim lRow As Long
Dim myData As Variant
Dim i As Integer: Dim j As Integer: Dim v As Variant
For i = 1 To 3
For j = 2 To 4
With Worksheets("Sheet2")
lRow = .Range(Cells(2, j), Cells(Rows.Count).End(xlUp)).Row
Debug.Print lRow ' lRow = 1
For Each v In Array("B2:B" & lRow, "C2:C" & lRow, "D2:D" & lRow)
Debug.Print v ' v = B2:B1
myData(i) = v.Value
Next
End With
With UserForm1.Controls("ComboBox" & i)
.List = myData(i)
.ListIndex = 0
End With
Next j
Next i
End Sub
アドバイスをいただけませんでしょうか？
よろしくお願いします。
（あみな） 2021/06/09(水) 16:58
Cells(Rows.Count).End(xlUp)
どこの列を指定しているのか解らないし。
まずは、どんな結果が返ってくるのか部分的に試して理解してからコードに組み込む事をお勧めします。
（JPS） 2021/06/09(水) 17:12
ヒントをいただいてから、いっぺんにすると
どうせできないから途中まで...
範囲の参照を把握してから次を検討することにしました。
イミディエイトウィンドウに下記のコードを実行すると
2～11,2～9,2～7の値が入るのでなんか良さそうな気がしてきました。
Private Sub UserForm_Initialize()
Me.StartUpPosition = 0
Me.Top = 280
Me.Left = 500
Dim lRow(1 To 3) As Long
Dim myData As Variant
Dim i As Long: Dim j As Integer: Dim v As Variant: Dim x As Variant
For j = 2 To 4
For i = 2 To Cells(Rows.Count, j).End(xlUp).Row
Debug.Print i
Next i
Next j
End Sub
さて...ここからどうしよう。
閲覧いただいている方もお見えでしたら、方向性はこれで良いのか？
アドバイスをお願いします。
（あみな） 2021/06/09(水) 21:43
>方向性はこれで良いのか？
ええと、最初はドベタコードが書ける事が前提です。 で、ドベタコードを見てこれ繰り返せるんじゃないか？ とかがきっかけになります。 それから、同じ繰り返しをする規則性のあるパターンを探す事から始まります。 パターンが見つかったら、それを当てはめる。 （JPS） 2021/06/09(水) 22:31
なるほど...ドベタコードって言うのですね。大事なんですね。
ﾒﾓﾒﾓ… φ(・Å・´*)ヵｷヵｷ♪
ここから時間がかかるかも知れません。
出来るか解りませんが...頑張ります。
（あみな） 2021/06/09(水) 22:53
ドベタコードから少し前進したような気がします。
...が、引き出しの狭い私ではここから先に進む気がしません。＞＜
下記のコードを実行すると、Debug.Printには
ComboBox1～3に表示したい情報が全部入ります。
B001～B007
C001～C006
D001～D005
全部の情報をMyDataに格納したんだからそうでしょ!!
ってなりますが...
プロパティの配列のインデックスが無効です (エラー 381)
ですと言われても、何をどうしたら良いのか？解りません。
1列ずつ格納できないから最初のコードの下記の書き方に
なってしまいます。
Dim lRow(1 To 3) As Long Dim myData(1 To 3)
With Worksheets("Sheet2")
lRow(1) = .Range("B" & Rows.Count).End(xlUp).Row
lRow(2) = .Range("C" & Rows.Count).End(xlUp).Row
lRow(3) = .Range("D" & Rows.Count).End(xlUp).Row
myData(1) = .Range("B2:B" & lRow(1)).Value
myData(2) = .Range("C2:C" & lRow(2)).Value
myData(3) = .Range("D2:D" & lRow(3)).Value
End With
ドラえもんのポケットような広い引き出しをお持ちの上級者の皆さま...
どうしたら良いでしょうか？アドバイスをお願いします。
現在のコード↓ここからArrayを使用するなんて無理ですよね？
現在のコードを掲示します。
Private Sub UserForm_Initialize()
Me.StartUpPosition = 0
Me.Top = 280
Me.Left = 500
Dim myData
Dim lRow As Long ' 最終行格納
Dim MyCol As Integer ' 列格納
Dim i As Integer
For MyCol = 2 To 4
With Worksheets("Sheet2")
For lRow = 2 To Cells(Rows.Count, MyCol).End(xlUp).Row
myData = .Range(Cells(lRow, MyCol), Cells(lRow, MyCol)).Value
Debug.Print myData
Next lRow
End With
Next MyCol
For i = 1 To 3
With UserForm1.Controls("ComboBox" & i)
.List = myData
.ListIndex = 0
End With
Next i
End Sub
よろしくお願いします。
（あみな） 2021/06/10(木) 09:20
最後のコードだけ読んで「こんな感じなのかなぁ...」ってちょっと書いてみました。 やろうとなさってる事からハズれてる様ならスルーして下さい。 (途中あんまり読んでないもので... スミマセン^^;)
Private Sub UserForm_Initialize()
Dim myData
Dim lRow As Long
Dim MyCol As Long
Dim i As Long
Const START_ROW = 2& 'データは2行目からの意
Const START_COL = 2& 'データは2列目からの意
For i = 1 To 3
MyCol = START_COL + i - 1
With Worksheets("Sheet2")
lRow = .Cells(.Rows.Count, MyCol).End(xlUp).Row
If lRow >= START_ROW Then
myData = .Range(.Cells(START_ROW, MyCol), .Cells(lRow, MyCol)).Value
If Not IsArray(myData) Then myData = Array(myData) 'データ1件しか無かった時の対策
With UserForm1.Controls("ComboBox" & i)
.List = myData
.ListIndex = 0
End With
End If
End With
Next i
End Sub
（白茶） 2021/06/11(金) 00:18
アドバイスをいただいた、（JPS）さん（れれ）さん
ありがとうございました。
（白茶）さんがコーディングを完成してくださいました。
目指したとおりのアプリケーションです。
ComboBoxは、利用頻度が高いので嬉しいです。
ComboBox が5個になっても10個になっても、1箇所変更するだけで済みます。
.+ﾟ♪｡これで楽チン(∀｀人●)(★人´∀)ﾙﾝﾙﾝ｡♪ﾟ+.
（白茶）さんへ…もしお時間あれば下記の解釈が間違っていたら教えてください。
よろしくお願いします。
▼定数宣言して… Const START_COL = 2&
▼代入するんですね。MyCol = START_COL + i - 1
▼なるほど…ここからFor ~ Next で回すんですね。
勉強になりました。
私が言うのも可笑しい話しですが、綺麗なコーディングだと思います。
▼そしてIF文で条件判定ですか…下のコード部分は解釈はこうですかね？
※もし…2行目以降が lRowによって選択されていたら？
myDataは、その範囲の Value ですよ？ちゃんと格納してね…
If lRow >= START_ROW Then myData = .Range(.Cells(START_ROW, MyCol), .Cells(lRow, MyCol)).Value
▼配列か判定。データ1件しか無かった時の対策
If Not IsArray(myData) Then myData = Array(myData)
(myData)が配列で無ければ？…頭の中が…プチ「こんがらがる」です。
なるほどしか言葉が出てこない…笑
本当にありがとうございました。
ネットで結構探しても無かったんですよね。
うるうる...｡°(´∩ω∩`)°｡｡. ( ´｡•ω•｡` )感動っ
また、解らないことが有れば質問をさせていただきます。
よろしくお願いします。
（あみな） 2021/06/11(金) 14:06
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.