Excel VBA 一時退避用のセルオブジェクトを作成したい

質問

Excel VBA 一時退避用のセルオブジェクトを作成したい

お世話になります。

環境は
WindowsXpPro Sp3
Excel2002 Sp3
です。

まず、「やりたいこと」をエクセルではなく通常の変数で書いてみます。

-------------------------------------------
aの値が20を超えていたら元の値に戻したい
-------------------------------------------
Dim a As Long
Dim temp As Long

a = 10 '元の値
temp = a '元の値を保存
a = 30 '変更後の値
If a > 20 Then
a = temp
Else
End If
-------------------------------------------

これと同じことをエクセルのCell（単一）に対して行いたいです。
うまくいかないので、Copy,Pasteを使ってみましたが、参照コピーになるようで元に戻せません。
元に戻したいのは、値だけではなく、そのセルに含まれる情報全てです。
なので「セルオブジェクトを退避させて復帰させる」というのを実現させたいです。

うまくいかないので、試しにCopy,Pasteを使って以下のコードを実行してみました。
しかし、参照コピーになっているようで元の値には戻りませんでした。
-------------------------------------------
Cells(10, 5) = 3
Cells(10, 5).Copy '<---- 元の値を保存
Cells(10, 5) = 20 '<---- 値を変更
If Cells(10, 5) > 10 Then
ActiveSheet.Paste '<---- 元の値に復帰させたいけれど変更後の「20」になってしまう。
Else
End If
-------------------------------------------
ペースト先をcells(10,6)等に変えて実行してみると、明らかに参照コピーになっていると思われる動作でした。

処理対象のシート上やブック上へ退避用のセルは作りたくないです。
退避領域のセルを単独で作りたいです。

方法をご存じの方、お手数ですがサンプルコードを提示頂けませんでしょうか。

よろしくお願いします。

回答No.3

masa_019 · ベストアンサー

こんにちは。

ブックやシート無しに、セル単独では存在しないのだから、
>処理対象のシート上やブック上へ退避用のセルは作りたくないです。
これは無理でしょう。
一時的にでも、シートを作って、そこに退避させるしかないと思います。
おそらく、以下のようなことがしたいんじゃないかと思いまが、いかがでしょう？

'***********************************************************
'標準モジュール

Option Explicit

Sub sample()
Dim newClass As New Class1

Sheets("Sheet1").Cells(10, 5).Value = 3 '元の値
Sheets("Sheet1").Cells(10, 5).Copy newClass.tmp '<---- 元の値を保存

Sheets("Sheet1").Cells(10, 5).Value = 20 '<---- 値を変更
If Sheets("Sheet1").Cells(10, 5).Value > 10 Then
newClass.tmp.Copy Sheets("Sheet1").Cells(10, 5) '<---- 元の値に復帰
End If

End Sub

'**************************************************************************

'以下クラスモジュール

Option Explicit
Private r As Range
Private Sub Class_Initialize()
Set r = ThisWorkbook.Worksheets.Add.Range("A1")
End Sub

Public Property Get tmp() As Range
Set tmp = r
End Property

Private Sub Class_Terminate()
Application.DisplayAlerts = False
r.Parent.Delete
Application.DisplayAlerts = True
End Sub

お礼

詳細に書いていただいてありがとうございます。
やはり、セルひとつだけの退避でもシートを作るしかなさそうですね。
ありがとうございました。

回答No.2

imogasi

長年この質問コーナーの質問を読んでいるが、この質問のようなのは、見たことが無い。私の勉強不足
理解不足か、質問者の根本を思い至らなくて、考えが珍奇なのか。
エクセルVBAではシートということを離れて、１セル（Rangeオブジェクト）だけのオブジェクトを、そっくりのオブジェクトを持った存在として作ることは考えてないはず。
形式を選択して貼り付けの画面に例示される
（１）セルの値
（２）書式
（３）表示形式
（４）コメント
（５）入力規則
（６）列幅・行高
や
その他オブジェクトの台紙（シートの属性かも）としての役割などがオブジェクトとして持たされている。
この（１）－（６）などの全体を１セルだけ取り出すのは不可能だろう。
（１）必要ならあらかじめの時点で別のワークのシートにそのセル全部を全的にコピーしておいて、必要あれば１セルだけを全的なコピーしてコピー元のそのセルに戻す。
（２）上記のセルの値や数式などのうち重要と思うもの（文字列や値）を直前に採って変数に一時退避しておいて、その情報で元のセルの属性を復元するとかする。
しかなかろう。
セルの属性にはセルの「値」以外にも沢山あることを意識したことがありますか。無いのでは。
エクセルなどの最近のウインドウプログラムのオブジェクトは
http://sky.ap.teacup.com/naotan/383.html
のように上下・包摂関係になっていて、VB.NETでいうInherit継承的な考えがとられるため、セルだけのオブジェクトを作ることは難しいのではと思う（この点私も自信なく見当ハズレかも。質問者が今後勉強してください。）VB６（VBAの基）にはなくても、エクセルがC++で作られるとそういう機能は使われているのでは）。

お礼

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

やはり、難しいですか。

回答No.1

soixante

E10セルに値を入れる前に、変更後の値が20を超えてるかどうかを、先に判定してはだめなのですか？

「20を超えてたら元に戻す」って考えじゃなくて、「20以下だったら変更後の値を入れる」
ではだめなのでしょうか。

変更後の値が20以下だったら、変更後の値を、Cells(10,5).Value にセット。
変更後の値が20を超えてたら、何もしない。

-------------------------------------------------------------
Sub aaa()

'変更後の値をｂとする
Dim a As Long, b As Long

If b <= 20 Then
Cells(10, 5).Value = b
Else
End If

End Sub

お礼

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

ちょっと例が単純すぎました。
もう少し具体的に書きます。

cell(10,5)へ文字列"=sum(a1:a10)"（関数ではありません）が入っていたとします。
ここでマクロ
Sub aaa()
Cells(10, 5) = StrConv(Cells(10, 5), vbWide)
End Sub
を実行すると、Cells(10, 5)が文字列から関数へ変換されてしまうのです。

ですので、
strConvを実行する前のセル情報を保存しておき、
実行結果次第で元のセル情報に戻す。
ということを考えました。