EXCEL VBA で範囲名を変数として扱えますか？

Question

チョット的が外れているかも知れませんが、教えてください。VBAはマクロの記録をちょこっと訂正しながら使える程度です。
ワークシートのA1に商品１、B1に商品２、C1に商品３という範囲名（名前）を定義しました。
次にユーザーフォームにテキストボックス１とテキストボックス２を配置してテキストボックス１に「１」を入れるとテキストボックス２には商品名１の内容が「２」を入力すると商品名２の内容が表示されるようにしたいのです。多分、IF文などで処理する方が合理的だとは思うのですが、

SyouHin = "商品" & me.textbox1.Value 
me.textbox2.Value = SyouHin

みたいに、範囲名を変数にして扱うことはできるのでしょうか？その時にはSyouHinの変数のタイプは文字列（string）とするのでしょうか？

少し、背伸びをして勉強を始めたのですがうまくいきません。
もしかして、全然ピントのはずれている質問かも知れません。また、文章が下手で伝わらないかも知れませんが、よろしくお願いします。

Wendy02 · Accepted Answer

こんばんは。#3 のWendy02です。

>ワークシートに不規則にあるセルに名前をつけて、それを範囲名を指定することで、参照しようと考えていました。そこで、名前を変数的に参照できないかと考えました。

もう少し、具体的に教えていただいたほうがよかったかもしれませんね。少し、ユーザーフォームで行うには、無理があるような設計かもしれません。ユーザーフォームは、メニューのデータのフォームのように使うのが、一番良いようです。

VBAでは、そこに規則性を見出すことを考えます。あまり、とびとびのものに行うのなら、テキストボックスに、アドレスを書いたほうが良いこともあります。

ただ、名前-定義とは別のものを、通常、VBAでは使います。
Rangeを、Collectionで繋ぐ方法はありますが、数値で呼び出す場合は、以下のようにするほうが楽です。

'--------------------------------------------------------

'配列変数
Private Ar As Variant
'配列のソース(特に数に制限はないけれども、コンマで区切る)
Private Const MYRANGES As String = "A1,B1,C1,A5,B5,C5"

Private Sub UserForm_Activate()
　Worksheets("Sheet1").Select　'シートを指定する
　Ar = Split(MYRANGES, ",")　'配列に変更させる
End Sub


Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
　Dim i As Variant
　i = TextBox1.Text
　If Not IsNumeric(i) Then Exit Sub
　If KeyCode = 13 Then　'Enter キーで、そのシートの場所のセルを呼ぶ
　　If LBound(Ar) <= (i - 1) And UBound(Ar) >= (i - 1) Then
　　　TextBox2.ControlSource = Ar(i - 1)　'添え字が、0からなので、-1
　　End If
　　KeyCode = 0　'TextBox2に飛ばないようにしている
End If

imogasi · Answer

多分やりたいことは
Sub test02()
Dim cl As Range
Range("a1:A5").Name = "商品1"
For Each cl In Range("商品1")
MsgBox cl.Value
Next
End Sub
のようなことではないですか。
範囲名をつけられるのも、エクセルの世界での機能ですし、これでよいかと。
「商品1」の後に配列の添え字のようなものをつけてやろうとしましたが、とりあえず、私ではできませんでした。
ただOffsetで
Sub test03()
Dim a As Range
Range("a1:A5").Name = "商品1"
Set a = Range("商品1").Cells(1, 1)
MsgBox a.Address
For i = 0 To 4
MsgBox a.Offset(i, 0).Value
Next i
End Sub
エクセルのセルはCells(i,j)のように指定して、あたかも配列のように扱えるので、この質問は必要性が薄いように思う。
テキストボックス云々のところは、本件質問と本質的に関係ないと
思いましたが、この回答でいかがですか。

Wendy02 · Answer

こんにちは。 >範囲名（名前）を定義しました。時々、VBAでそういう方がいらっしゃいますが、Excel VBAでは、名前－定義は、あまり用いないほうがよいです。私の経験の範囲ですが、名前(Names)の親の属性の問題で、Application, Workbook, Worksheet 側と、それぞれあるので、外れることがあったような気がします。それと、その内容自体は、オブジェクトではなく、数式文字列なので、そこから、範囲を取り出すにしても、ワークシート側の数式なので、親の属性によって、一旦、VBAに直したりしなくてはならないことが多く面倒なのです。VBAでは扱いにくいもののひとつです。もちろん、　i = Me.TextBox1.Value 　MsgBox Range("商品" & i).Value 　このような形でも、単純なものなら、同じブック内で可能です。ところで、行はどのようにして読むのですか？メニューのツール－のフォームに似せてみました。ためしにサンプルを作ってみました。これは、同様の質問にあった時に考えたものですが、未公表です。現状のコントロールは以下だけとしたら、　　TextBox1 　　TextBox2 本来は、TextBox1 は、数値だけを指定させるようにしてもよい。の後に、　　スクロールバー(ScrollBar) 　　ラベル(Label1) をスクロールバーの近くに貼り付けてください。　　 '------------------------------------------------------- Private rngSource As Range Private i As Variant Private j As Integer Private Sub ScrollBar1_Change() 　 j = ScrollBar1.Value 　 TextBox2.ControlSource = rngSource.Cells(j, CInt(i)).Address 　 Label1.Caption = CStr(j) & "/" & CStr(rngSource.Rows.Count) End Sub Private Sub ScrollBar1_Scroll() 　 j = ScrollBar1.Value 　 TextBox2.ControlSource = rngSource.Cells(j, CInt(i)).Address 　 Label1.Caption = CStr(j) & "/" & CStr(rngSource.Rows.Count) End Sub Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) 　i = TextBox1.Text 　j = ScrollBar1.Value 　If Not IsNumeric(i) Then Exit Sub 　If KeyCode = 13 And j > 0 Then 　 If 1 <= i And i <= rngSource.Columns.Count Then 　　 TextBox2.ControlSource = rngSource.Cells(j, CInt(i)).Address 　　 Label1.Caption = CStr(j) & "/" & CStr(rngSource.Rows.Count) 　 End If 　 KeyCode = 0 　End If End Sub Private Sub UserForm_Activate() 　Worksheets("Sheet3").Select 　Set rngSource = Range("A1").CurrentRegion 　i = 1 '列　j = 2 '行　With ScrollBar1 　　 .Min = 2 'スクロールバーの最小値(1行目はタイトル行) 　　 .Max = rngSource.Rows.Count 　End With 　Label1.Caption = CStr(j) & "/" & CStr(rngSource.Rows.Count) 　TextBox1.Text = j End Sub '------------------------------------------------------- なお、読み込むだけなら、　TextBox2.ControlSource = rngSource.Cells(j, CInt(i)).Address 　の代わりに、　TextBox2.Text = rngSource.Cells(j, CInt(i)).Value '読み込むだけに替えてもよい。

onlyrom · Answer

こんにちは。

＞SyouHin = "商品" & me.textbox1.Value
＞me.textbox2.Value = SyouHin

おしい、もちょいでしたね。
名前をつけたセルを参照するときは、

　Range(名前).Value　となりますから

Dim SyouHin As String
SyouHin = "商品" & TextBox1.Text
TextBox2.Text = Range(SyouHin).Value

ham_kamo · Answer

Syohin = ActiveWorkbook.Names("商品" & Me.TextBox1.Value).RefersToRange.Value

と書けば、できることはできますが、

Dim v As Integer
v = TextBox1.Value
If v >= 1 And v <= 3 Then
　　TextBox2.Value = Worksheets("Sheet1").Cells(1, v)
End If

と書いてはだめなのでしょうか？
実際には商品名が入っているセルの位置がばらばらで規則性がなく、名前をつけてそのセルにアクセスしようということなのでしょうか。

EXCEL VBA で範囲名を変数として扱えますか？

こんばんは。

多分やりたいことは

こんにちは。

こんにちは。

Syohin = ActiveWorkbook.Names("商品" & Me.TextBox1.Value).RefersToRange.Value

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング