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

質問

チョット的が外れているかも知れませんが、教えてください。VBAはマクロの記録をちょこっと訂正しながら使える程度です。
ワークシートのA1に商品１、B1に商品２、C1に商品３という範囲名（名前）を定義しました。
次にユーザーフォームにテキストボックス１とテキストボックス２を配置してテキストボックス１に「１」を入れるとテキストボックス２には商品名１の内容が「２」を入力すると商品名２の内容が表示されるようにしたいのです。多分、IF文などで処理する方が合理的だとは思うのですが、

SyouHin = "商品" & me.textbox1.Value
me.textbox2.Value = SyouHin

みたいに、範囲名を変数にして扱うことはできるのでしょうか？その時にはSyouHinの変数のタイプは文字列（string）とするのでしょうか？

少し、背伸びをして勉強を始めたのですがうまくいきません。
もしかして、全然ピントのはずれている質問かも知れません。また、文章が下手で伝わらないかも知れませんが、よろしくお願いします。

回答No.5

Wendy02 · ベストアンサー

こんばんは。#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

お礼

Wendy02さん、ありがとうございます。
VBAの可能性を広げていただける解答で、一言「すごい」！でした。内容をまだ理解できませんでしたが、コピペさせていただき、使ってみたら、想像以上のことができるのでビックリしました。
ただ、前にも書きましたように、名前を多用しているためか、動きがぎこちないのです（遅い）。名前の多用が原因かどうか見解りませんが・・・
ですので、根本的に最初から考え直すことにしました。また、何かとお世話になると思いますが、よろしくお願いします。
本当にありがとうございました。

また、この場を借りて解答していただいた皆さんありがとうございました。

回答No.4

imogasi

多分やりたいことは
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)のように指定して、あたかも配列のように扱えるので、この質問は必要性が薄いように思う。
テキストボックス云々のところは、本件質問と本質的に関係ないと
思いましたが、この回答でいかがですか。

お礼

早速の解答ありがとうございます。
質問のしかたが悪かったようですね。ごめんなさい。
ワークシートに不規則にあるセルに名前をつけて、それを範囲名を指定することで、参照しようと考えていました。そこで、名前を変数的に参照できないかと考えました。ただ、名前の多用があまり良くないとの指摘やその他の方法がありそうなので、もう少し考え方を整理したいと思います。
ただ、OffsetやCellsなど参考になりました。ありがとうございました。

まだまだ、勉強が足りず「ちからわざ」で処理しようと考えてしまう癖があり処理が遅くなったり、無駄なメモリを使ったりしているようです？？？

もう少し勉強したいと思いますので、どこかで悩んでいるのを見かけたら救ってください。お願いします。

みなさん、色々な解答ありがとうございました。

回答No.3

Wendy02

こんにちは。

>範囲名（名前）を定義しました。

時々、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 '読み込むだけ
に替えてもよい。

お礼

早速の解答ありがとうございます。
＞Excel VBAでは、名前－定義は、あまり用いないほうがよいです。
そうなんですか。実は名前を多用するつもりだったのですが、少し考え直す必要があるのかも知れません。（数十個の名前）
また、例文については・・・・申し訳ありません。理解できませんでした。ただ、すごいことができるんだという感覚はあります。勉強を重ね読み解けるように早くなりたいと思います。頑張りますので、またお力をお貸しいただければと思います。解答いただいたのに不甲斐ない質問者で申し訳ありませんでした。ただただ感謝します。本当にありがとうございました。
今後ともどこかで迷っていたら、手をさしのべてくださいよろしくお願いします。

回答No.2

onlyrom

こんにちは。

＞SyouHin = "商品" & me.textbox1.Value
＞me.textbox2.Value = SyouHin

おしい、もちょいでしたね。
名前をつけたセルを参照するときは、

　Range(名前).Value　となりますから

Dim SyouHin As String
SyouHin = "商品" & TextBox1.Text
TextBox2.Text = Range(SyouHin).Value

お礼

早速の解答ありがとうございます。
やはり、基本的なことが理解できていなかったのですね。(^^;)
勉強を重ねたいと思います。そうなんですか～それで理解できました。時々？テキストボックス１の内容がテキストボックス２に直接入ったりしていたんです？？？（これも的はずしていますか？）
名前を代入するには　Range(名前).Value　ですね。参考になりました。ありがとうございました。VBAってやっぱり奥が深いですね。頑張ります！
また、どこかで迷っているのを見かけたらよろしくお願いします。

回答No.1

ham_kamo

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

と書いてはだめなのでしょうか？
実際には商品名が入っているセルの位置がばらばらで規則性がなく、名前をつけてそのセルにアクセスしようということなのでしょうか。

お礼

早速の解答、ありがとうございます。
＞Syohin = ActiveWorkbook.Names("商品" & Me.TextBox1.Value).RefersToRange.Value
でできるのですね。助かりました。
＞実際には商品名が入っているセルの位置がばらばらで規則性がなく、名前をつけてそのセルにアクセスしようということなのでしょうか。
その通りなのです。名前が付いているセルに規則性が無くしかも１～３の数字だけではなく文字もつけて直接参照したいと思っているので・・・でも、そんなに沢山の名前を付けることが良いのかどうかは解りませんが・・・。
でも、変数として扱えると言うことだけでも解り仕事が前に進みそうです。
まだ、まだつまずくことがあると思います。また、見かけたらよろしくお願いします。