VBA セルの初期状態を保持して置いて、処理後にその情報をもとにセルデータを元に戻したいです。

Question

現在VBAにて下記のような処理を考えています。

前提　Excelの数式タブ>名前の管理にて、
Range("A1:D3")を"InitDataRange"と命名していることとします。

今回の処理としてテンプレートシートを起点に下記のような処理がループで行われます。

１、テンプレートシートの初期状態を回避
２、テンプレートシートのデータを編集（ここの処理ではいろいろやっていますが、割愛）
３、テンプレートシートを別Bookに出力
４、テンプレートシートを初期状態に戻す

ここで今悩んでいるところが、１の処理と４の処理のところです。

以下、プログラムを見ていただきたく思います。

Sub test()
    '初期状態のRangeを回避します。
    Dim initRange As Range
    Set initRange = Range("InitDataRange")
    '2の処理の代わりに内部データをいじっています。出力処理は割愛。
    Range("A1").Value = "a"
    Range("A2").Value = "b"
    Range("D3").Value = "c"
　　'初期状態に戻します。
　　Range("InitDataRange") = initRange
End Sub

ここでできていないのが、最終行のRange~の部分です。
もともとデータが入っているのにもかかわらず、この処理を走らせると最終的に
Range("InitDataRange")の範囲は空になってしまします。

Excelの背景色の設定も含めて初期状態を回避し、
最後に戻すという処理を組み込みたいのですが、
簡単なやり方はありますでしょうか。

よろしくお願いいたします。

Answer 1

使える場面が限られますが、以下のようにValueを取得・適用すると、書式データ込みで情報を取得・適用することが出来ます。

XlRangeValueDataTypeは省略できます。

'保存
Dim rngDataXML As String
rngDataXML = initRange.Value(XlRangeValueDataType.xlRangeValueXMLSpreadsheet)

'復帰
initRange.Value(XlRangeValueDataType.xlRangeValueXMLSpreadsheet) = rngDataXML

Comments

[King_of_Flies]

こちらは便利ですね！
書式コピーも考えていたので助かります。

Answer 2

テンプレートって？

「テンプレートに直接書き込む」ということ自体に抵抗を感じるので、私もttyp03さんの考え方の方がしっくりきます。

「変更したあと初期状態に戻したい。そのために初期状態を退避しておきたい。」
テンプレートとは、もともとそのために用意するものではないでしょうか。

せっかくテンプレートを用意しているのに、直接書き込んでテンプレートとしては使えなくなってしまうから、書き換える前に退避しておくって、ちょっと無駄を感じます。

テンプレートは参照するだけ

「テンプレートを変更しない」方針で考えると、以下のような処理になると思います。

１、出力用シートを作成する
２、テンプレートシートの内容を出力用シートにコピーする（出力シートの初期化処理）
３、出力用シートのデータを編集（ここの処理ではいろいろやっていますが、割愛）
４、出力用シートを別Bookに出力
５、出力用シートを初期化する（つまり２に戻る）

テンプレートシートの内容は終始参照されるだけで、変更はしません。

出力シートは毎回作成した方がきれいな状態ではじめられそうですが、速度を懸念されるようなら最初に作ったものを使いまわして、毎回テンプレートからのコピペでリセットだけすればよいと思います。

シートをキレイにするという意味で、対象範囲だけでなく以下のようにシート全体のコピペでまるごとリセットしてしまうのも手です。

Dim shTmpl As WorkSheet
Dim shOutput As WorkSheet
Set shTmpl = Sheets("Template")   'テンプレートシート　※シート名は適当
Set shOutput = Sheets("Output")   '出力用シート　※シート名は適当

'シート全体のコピペ
shTmpl.Copy shOutput

参考になれば幸いです。

Comments

[rinren]

おっしゃる通りだと思います。テンプレートをコピーして、複製に対して目的の処理をするべきです。
もう少し大きなプログラムになると、やってるうちに、「あれ？どっちがテンプレート」なんて初歩的なミスが生じてくるはずです。(私がそうでした)
ちなみに技術的な問題として、ブックを跨ぐ処理を考えておられる場合、ブックオブジェクトも捕捉して、明示してrangeを指定してやらないと、意図しない動作になります。
こんな感じがいいと思います

```vba
Set NewBook = Workbooks.Add(xlWBATWorksheet)
BaseSheet.Copy After:=NewBook.Worksheets(1)
Set TargetWS = NewBook.ActiveSheet
```
質問者様が、別ブックへの出力を考えておられようでしたので、この形にさせていただきました。

Answer 3

範囲をコピーしておいて何やかんやするよりも次のようにしてみてはいかがでしょうか。
0. テンプレートシートを別名で複製
0. 複製したシートを編集
0. 編集したシートを別ブックに移動（コピー？）
0. コピーした場合は複製したシートを削除

Comments

[King_of_Flies]

出力されるファイルが数百という場合における処理速度が、
シート複製、シート削除の処理分余計にかかってしまうということで、
その方法では行わないという方針になっています。

記述漏れ申し訳ありません。

[ttyp03]

方針でそうなっているのであれば仕方ないですが、画面の更新をオフにして処理すればそれほど時間的な負担にはならないと思いますけどね。
一番いいのは、両方のパターンを作ってどちらがよいかを決めることです。
私が提案した方がそうでない場合に比べて数秒程度の遅れであるなら、作りこみがシンプルな方を採用したほうが余計なバグを仕込む可能性が少なくなるというメリットがあります。
圧倒的な速度差があるなら私の案は却下ですがｗ

Answer 4

Range("InitDataRange") = initRange

これだと書式などは対象にならなかったと思います。

コピーメソッドならどうでしょう。

initRange.Copy Range("InitDataRange")

Comments

[King_of_Flies]

さっそくありがとうございます。
Contentsのコピーはできましたが
罫線のコピーが行われていないという状態です。
これはCopyプロパティで解決できないようです。

[sazi]

罫線はコピーできないようですね。
こちら参考になれば、
http://blog.sbe.tokyo/20150115/203/

[King_of_Flies]

ありがとうございます。
罫線に関しては、参考のURLのようにコピーするよりは、
罫線を再設定するほうが楽そうなので、対処方法としては自前のプログラムで罫線設定することにしました。

ちなみになのですが、セルの条件書式について、
削除方法と、初期状態のセル書式を再設定というような方法はありますでしょうか。

[King_of_Flies]

それとも　.Copyは[書式のコピー]なので、セルの条件付き書式についてもコピーされるのでしょうか。

[sazi]

条件付き書式もコピーされたはずです。
条件付き書式の設定については、以下参考に
https://www.tipsfound.com/vba/07020
まあ、楽なのはテンプレート作っておいてコピーでしょうけど。