VBAで Set wb = Sheets(1).Copyができないわけ？

質問

つい先ほどの質問　4150169　は掲示したコードが抜けておりましたので無視して、こちらにご回答ください。
ほんと抜けた話です。すみません。

以下のマクロtest01はエラーになります。

Sub test01()
Dim wb As Workbook
Set wb = Sheets(1).Copy 'エラー「オブジェクトが必要です」
End Sub

もちろん
Dim wb As Workbook
Sheets(1).Copy
Set wb = ActiveWorkbook
と修正すればエラーにならないことは存じていますが、ふと疑問がわきました。
Sheets(1).Copyの段階であたらしいWorkbookが誕生していますよね。
ならば、そのWorkbookはオブジェクトではないのでしょうか？

Workbooks.Add　で誕生したWorkbookは
Set wb = Workbooks.Add
と変数wbにSetできるのに
Set wb = Sheets(1).Copy
ができないのが不思議です。

Set wb = ActiveWorkbook
としないでもSheets(1).CopyをwbにSetする書き方はないのでしょうか？

回答No.6

Wendy02 · ベストアンサー

こんばんは。

少し、全体的に考えさせてもらいました。ご質問の最終的な終着点がどういうところにあるのかはあまりはっきりしませんし、私が書いたところで、この手の話は、あまり分かってもらえるという自信はありません。merlionXXさん、ご自身のVBAのコーディングの問題であって、個々の問題ではないからでもあります。

今回の質問は、merlionXX さん自身が、解答した中で考えたことでしょうし、まさか、サブルーチンや特殊なコードで解決をしようと思ったわけではないと思います。

実際のコードとして、

　Set wb = Sheets(1).Copy

というのは出来ないのはご存知なわけですが、なぜ「そうしたいと思った」か、ということがポイントではないでしょうか。出来ないのは、VBAの製作者の恣意的な設計だったのか、それとも、他の要因があったのかと思います。それを、私たちユーザーレベルでは分かりません。

あえて理由を言えば、もともと、Copy メソッドには、戻り値にオブジェクトではない、ということです。

実際に、
ret = Sheets(1).Copy

で返るのは、True です。失敗すれば、実行時エラーが起こります。

質問は、前回のコード( http://oshiete1.goo.ne.jp/qa4138541.html ) から、思いついた事だとは思っていますが、Sheets(1).Copy の時点で、Application側の暗黙的な Workbookのインスタンスの生成を与えてしまっているわけで、そこで、ActiveWorkbook で、オブジェクトを取得することに、なにかしら不自然さを感じていたとしたら、その感覚は正しいのではないかと思います。私も同じように思います。

しかし、その方法で、ブックオブジェクトを取得する方法は、ActiveWorkbook をオブジェクトとして取る方法以外には、新たに Copy メソッド自体から手を加えるぐらいしか、実現する方法はないような気がします。そうすると、その親オブジェクトのSheet のClass 設定から、手を入れなくてはなりません。それは、遥か上級の話だろうし、VBAレベルで可能かどうかさえ分かりません。それに、そんなに難しいコードが、この種のもので必要だと思えません。また、一行、二行の問題で、特別なワザもなく、わざわざFunction プロシージャで取り出すというのは、理屈ではあっても、実務的にはまず考えられません。

VBAは、合理的に出来ているようでいて、はっきりとしないものがいくつもあり、「やって見なければ分からない」としかいいようがありません。本当に単純なコードでも、VBに出来て、VBAには出来ないものがあります。自分は、プロだと豪語している人に限って、こういうところでね凡ミスをするのが常です。VBAのコーディングは、よほど初歩的なこと以外は、戻り値を想定するというような「推測」で済まされるようなことはできません。

ただ、私は全体を通してみて、もし、もう一度、考える余裕があるなら、個々のメソッドやそれに関わるワザよりも、全体のプロセス自体に手を付けたほうがよいのではないか、と思います。

前のコードを、私自身で考えてみました。これは、定番のコードではあるので、私のアレンジあるものの、それ自体がオリジナルというわけではありません。シートモジュールにあるマクロコードをコピーしたいという特例の場合は、以下のコードを少し変更して可能です。これは一例ですが、こういうものは自力で編み出したわけではなく、VBAの入門時に、教わっているものです。6年経っても、そのとき覚えたものは忘れていません。

http://oshiete1.goo.ne.jp/qa4148672.html

----------------------------------------
Sub 保存3()
　　Dim orgShCnt As Integer
'　　Dim shName As String
　　Dim fn As Variant
　　'ブックのシートの規定数
　　orgShCnt = Application.SheetsInNewWorkbook
　　Application.SheetsInNewWorkbook = 1
　　With Worksheets("Sheet1")
'　　　　shName = .Name
　　　　.Cells.Copy
　　End With
　　With Workbooks.Add　　'※
　　　　.ActiveSheet.Paste
　　　　Range("A1").Select
　　　　Application.CutCopyMode = False
　　　　'規定数を戻す
　　　　Application.SheetsInNewWorkbook = orgShCnt
　　　　'shName → は、"" になっている
　　　　fn = Application.GetSaveAsFilename("", "Excelブック(*.xls),*.xls", 1, "保存")
　　　　If VarType(fn) = vbBoolean Then
　　　　　　.Close False
　　　　　　MsgBox "キャンセルしました。", 48
　　　　　　Exit Sub
　　　　End If
　　　　.SaveAs fn
　　　　.Close False
　　End With
　　MsgBox "終了しました。", 64
End Sub

----------------------------------------
※　Workbooks.Add というメソッドで、明示的なブックを生成していればこそ、そのオブジェクトは確実に取得できるわけです。Application の　NewBook イベントで、ブックオブジェクトは取得は出来ますが、それは、ひとつのプロセスの中にはありません。

はたして、本当に、Sheets(1).Copy でオブジェクトを捕らえることが必要なのか、と思います。私は、今まで、VBAのコードを2～3千件書いていますが、ほとんど、その方法は、記録マクロを写した程度にしか、そのような方法は出てこないように思います。

失礼な言い方があったら、お許しください。

お礼

Wendy02さま、ご丁寧な解説を有難うございます。
また、わたしのこれまでの回答までご覧になっていただき、その上での適切なご回答、とても勉強になりました。
これからもご指導くださいますようお願い申し上げます。

回答No.5

rexfan

Ｎｏ４です。

訂正です。
Set wb = Workbooks.Add(ActiveWorkbook.Sheets(1).Copy)
という形でTemplateに指定します。

ただし「Sheet1」というブックが何故か余分にできてしまい
理由がわからないので完全な回答になっていません。

お礼

ありがとうございます。
今、自宅の2003で試しましたら＃3のコードも＃4のコードもエラーになりませんね
でも両方とも余分なBookができてしまうのは困りものです。

やはり、素直にそういうものなんだと覚えることにします。

回答No.4

rexfan

こんにちは。
こんなコードは絶対エラーになると思って
試したことがありませんでしたが投稿者さんの
疑問ももっともですね。

コピー先の指定が無いとSheets(1).Copy の前に
「Workbooks.Add」が動作してるのでしょう。
そしてブックの中身を指定したシートのみに入れ替えています。

次のように明示的に指定してみると動作しました。
Set wb = Workbooks.Add(Sheets(1).Copy)
ただしブックの名前が「Sheet1」になりますが・・・。

回答No.3

keirika

多少無理のある説明かもしれませんが・・・・

同じプロシージャでもSubとFunctionがあり、Subは値を返しませんが
Function（関数）は値を返します。

つまり、SubをFunctionに変更しても同じ結果が得られますがSubとFunctionはやはり別物です。

結果としてオブジェクトが生成されるとしても、値を返すものと返さな
いものがあり、返されないものを格納は出来ないのでエラーになります。

お礼

ありがとうございます。
そういうものだと覚えておくしかないようですね。

回答No.2

imogasi

Sub test01()
Dim wb As Workbook
Set wb = Sheets(1).Copy 'エラー「オブジェクトが必要です
End Sub
は何をしたいのか。
ワークブックを増やして、増えたブックの第１シートをコピーしたいのか。
シートを増やせば、とかコピーすれば、ブックが増えるという発想がわからない。
それにしても
Dim Newbook As Workbook
Set Newbook = Workbooks.Add
Newbook.SaveAs FileName:="新規ブック"
または
Workbooks.Add.SaveAs Filename:="新規ブック.xls"
がないのはどうしてか。
＞Sheets(1).Copyの段階であたらしいWorkbookが誕生していますよね
＞Set wb = Sheets(1).Copy
＞Set wb = Sheets(1).Copyができないのが不思議です
ができないのが不思議です。
コピーしてもオブジェクトが新しく作られるとか、既存オブジェクトが捉えられないのは、Ｃｏｐｙメソッドはそういうものじゃ無いのだから当たり前。
なんか独りよがり的な点が見えて、聞いていることが理解しにくい。

お礼

いえ、別にしたいことがあるわけじゃないんです。
ただ、目の前に新しいBookが出来たのに「オブジェクトが必要です」とエクセルから叱られると釈然としなかったもので・・・・・。
Copyメソッドはそういうものじゃ無いと言われてしまえばそれまでの話なんですが。

回答No.1

bonaron

> Workbooks.Add　で誕生したWorkbookは
> Set wb = Workbooks.Add
> と変数wbにSetできるのに
------------------------------------------------------------
ヘルプより
Workbooks オブジェクトの Add メソッド

オブジェクトを返すメソッドです。新しいブックを作成します。新しいブックが作業中のブックになります。ブック (Workbook オブジェクト) を返します。
------------------------------------------------------------
> Set wb = Sheets(1).Copy
> ができないのが不思議です。
------------------------------------------------------------
Chart、Charts、Sheets、Worksheet、および Worksheets オブジェクトの Copy メソッド

シートをブック内の他の場所にコピーします。
------------------------------------------------------------
Copy メソッド はオブジェクトを返さないので
右辺には、wb に代入すべきオブジェクトがありません。
で、「オブジェクトが必要です」 と言われます。
仮に Copy メソッド がオブジェクトを返したとしても
おそらく Workbook ではなく、Worksheet でしょうね。

お礼

さっそくありがとうございます。

> Copy メソッド
> シートをブック内の他の場所にコピーします。

Sheets(1).Copy Before:=Sheets(1) のように書けばその通りですが、例えばBook1で、Sheets(1).Copy　だけで実行すると、Book2が出来てしまいます。このBook2はWorkbookでオブジェクトですよね？

補足

いま試しましたが、
Sub test()
Sheets(1).Copy
MsgBox Workbooks.Count
End Sub
これを実行するたびにWorkbookの数は一つずつ増えていきます。