Rangeオブジェクトを一時的に作成することは出来ますか。

質問

こんにちは。
いままでは特定のシートにタイトルや数値、書式を入れて、そのRangeオブジェクトを参照してVBA作成を行っていました。

そこで今度はRangeオブジェクトを新たに作成して、特定セルのセルオブジェクトを、一時的にそこに格納して利用したら便利かと思いまして、色々と方法を考えましたが出来ませんでした。

もちろん個々のセル情報を一時的に保管することは出来ると思いますが、このような使い方が出来れば、今後のVBA作成作業で幅が出来ると思いまして、ご質問をさせて頂きます。

例；
new X名 Rangeオブジェクト作成
X名 = Range("A1")
Cells.Clear
Range("B1").Formula=X名.Formula
Range("B1").Interior.Color=X名.Interior.Color
A1のオブジェクトを一時的に保管して、シートクリア後にその情報を利用してB1へ戻す。
こんな感じの使い方です。

宜しくお願い致します。

回答No.4

Wendy02

i_september さんへ

このままだと、申し訳ないので、「私も考えたことがあるからです」という、私の考えた時のサンプルを再現させていただきます。

'--------------------------------------------
'オブジェクト変数に確保(失敗）
Sub TestSample1()
'サンプル作成用
Range("A1:A10").FormulaLocal = "=Row()"
Range("A1:A10").Value = Range("A1:A10").Value
'--------------
Dim myRange As Range
Set myRange = Range("A1:A10")
Range("A1:A10").Clear
Range("B1:B10").Value = myRange.Value

End Sub

'コレクション方式(失敗）
Sub TestSample2()
'サンプル作成用
Range("A1:A10").FormulaLocal = "=Row()"
Range("A1:A10").Value = Range("A1:A10").Value
'--------------
Dim myRangeCollection As New Collection
Dim c As Range
Dim i As Integer
For Each c In Range("A1:A10")
　myRangeCollection.Add c
Next
Range("A1:A10").Clear
For i = 1 To myRangeCollection.Count
　Cells(i, 2).Value = myRangeCollection.Item(i)
Next
End Sub

'=================================
'配列方式(成功）
Sub TestSample3()
'サンプル作成用
Range("A1:A10").FormulaLocal = "=Row()"
Range("A1:A10").Value = Range("A1:A10").Value
'--------------
Dim myRangeValue As Variant
myRangeValue = Range("A1:A10").Value
Range("A1:A10").Clear
Range("B1:B10").Value = myRangeValue
End Sub

'--------------------------------------
試してみれば分りますが、配列方式しか出来ません。

今回、改めて作ったものです。
'応用編
Sub TestSample4()
'サンプル作成用
Dim j As Integer
Range("A1:B1").CurrentRegion.Clear
For j = 1 To Range("A1:A10").Count
　Cells(j, 1).FormulaLocal = "=" & j & "*2"
　Cells(j, 1).Interior.ColorIndex = j
Next
'--------------
Dim i As Integer
Dim myRangeArray(1 To 2, 1 To 10) As Variant
For i = 1 To Range("A1:A10").Count
　myRangeArray(1, i) = Cells(i, 1).FormulaLocal
　myRangeArray(2, i) = Cells(i, 1).Interior.ColorIndex
Next
　Range("A1:A10").Clear
For i = LBound(myRangeArray, 2) To UBound(myRangeArray, 2)
　Cells(i, 2).FormulaLocal = myRangeArray(1, i)
　Cells(i, 2).Interior.ColorIndex = myRangeArray(2, i)
Next

End Sub

　なかなか、煩雑なコードになってしまいました。

　私のVBAのキャリアは、たかだか２年程度しかありませんが、一通りは、基礎的なことは終わったつもりです。しかし、たぶん、擬似的なRangeオブジェクトをメモリ上に確保するということは、出来ないと言っても間違いではないと思います。もちろん、Rangeオブジェクトというのを、オートメーション・オブジェクトのメモリ上のワークシートに、ワークブックを　Visible=False で、確保するという人が出てくるかもしれません。現実にそういう人がいらっしゃったら申し訳ないのですが、それは、決して誉められた方法ではありません。メモリを圧迫しますし、もし、そこまでするなら、なぜ、Worksheets.Add をしてテンポラリーシートを作らないのか、ということになってしまいます。

こんなところで、ご了解願えますでしょうか？

お礼

ご回答有り難う御座います。
内容了解しております。
キャリア２年と仰っていますので、
私も簡単に紹介致します。
社内SEで汎用機からオフコンに移りCOBOLを中心に長らくやってきました。
４，５年前からパソコン系も手掛けています。
VBAを本格的にやり始めたのは１、２年と言ったところでしょうか。やらなければと思いながら月日がたってしまいました。

回答No.3

Wendy02

訂正します。

i_september　様

# ご質問のレベルからすると、ベーシックの最初の段階が危ういように読めます。・・・

その段落の部分は、ご質問の内容からは外れて、失礼な内容でした。申し訳ありません。少し、テンションが高すぎました。

回答No.2

Wendy02

こんにちは。i_septemberさん、
For Each ～ In で書いた　Wendy02です。

　私は、覚えている限りは、あまり同じことは書かないつもりではいるのですが、まず、一度、ご自分のランクを確認してみてください。前回と同様に、ご質問のレベルとコードがあまりに開きがありすぎます。

日本VBA協会　Excel 2002 VBA ベーシック
http://www.vbaa.jp/vbae/level_excel_bs.htm
項目を見て、全て習得したと思うなら、次のスタンダード
それが出来たなと思うなら、その次のプロフェッショナル
（試験代は高いし、プライベート資格ですから、好きな人だけがすればよいと思います。）

　ここの掲示板のルールは、終始回答に徹することを求められますが、やはり書いているのは人間ですから、いろいろ思うことがあります。

　ご質問のレベルからすると、ベーシックの最初の段階が危ういように読めます。もう少し、基礎段階を固めることをお勧めします。独創的な発想は、オールラウンドに学習してから後のことです。少なくとも、スタンダードの項目、プロフェッショナルの項目の一部を治めたぐらいからです。それで、初めて新たなスタートラインに立つのだと思います。

＞そこで今度はRangeオブジェクトを新たに作成して、特定セルのセルオブジェクトを、一時的にそこに格納して利用したら便利かと思いまして、色々と方法を考えましたが出来ませんでした。

　結論から言えば、答えは、VBAでは、そのようなことは出来ません。これは、私も考えたことがあるからです。

　オブジェクト(Object)というのは、プロパティ(Property)の集まりだといっても過言ではありません。Rangeオブジェクトのプロパティには、セルの位置など、さまざまな情報が入っているわけですから、「数値、書式」を入れるためだけの入れ物とは違います。また、名前定義(Names)を使う人がいますが、私は、賛成しかねます。その入れ物は、ある意味でワークシート側の文字列定数だからです。もし入れる場合は、自分で、そういう入れ物（構造体）やクラス(Class)を作らなくてはなりません。仮に、Public 変数にRangeオブジェクトを置いたところで、Rangeオブジェクトは、1つのコンテナ(Container)として、実際の位置情報（セル番地）を持った存在ですから、単に区画を区切っただけであって、それ自体を切り離し、メモリ上に置くことは出来ません。ある程度、VBAに慣れてくれば、出来ないことに気が付きます。ただし、Rangeオブジェクトから、定数を抜き出した２次元配列のみが、仮想メモリ上に置くことが可能ですが、今回のような場合は役に立ちません。

私には、せいぜい、以下のようにしか考えられません。ひとまとめに、構造体を用いてもよいかもしれませんが、そのようなことをしても、手数が増えるだけで定数(Const)で十分だと思います。

例:

＞Range("B1").Formula=X名.Formula
＞Range("B1").Interior.Color=X名.Interior.Color

　　↓
Const MY_FORMULA As String = "=SUM(R10C2:R[-1]C)"
Const MY_INTER_COLOR As Integer = 3

With Range("B1")
　　.FormulaLocal = MY_FORMULA
　　.Interior.ColorIndex = MY_INTER_COLOR
End With

なお、モジュールレベルやプロジェクトレベルの定数にしても良いかもしれません。もちろん、一般的な変数(Valuables)としても、モジュールレベルやプロジェクトレベルで、プロパティの値は、確保できます。
.

お礼

ご回答ありがとう御座います。

Wendy02さんも「私も考えたことがあるからです」と
仰っていますから、同様に疑問を感じることは
自然のように思います。
コードは誤解が無いよう補助的に単純にしています。
不特定多数の人が参加していますので、
難しいところがあるようですね。

回答No.1

taocat

こんにちは。

オブジェクトを変数に保存したいということですね？
その場合は次のように
オブジェクト変数を宣言して、
Setステートメントを利用します。

-------------------------------------------
Sub Test

　Dim X名 As Range

　Set X名 = Range("A1")

　Cells.Clear
　Range("B1").Formula=X名.Formula
　Range("B1").Interior.Color=X名.Interior.Color
End Sub
----------------------------------------------

勘違いでしたらご容赦願います。
以上です。

お礼

ご回答有難う御座います。

この方法は試してみましたが、
X名は"A1"の参照となるため、
Clearの時点でX名の内容も
消えてしまいます。