Excel VBA Sheet1に記述していたのですがうまく動きません。

質問

シート１にコマンドボタンを配置し、Sheet1にそのままコードを記述していました。そこにマクロで記録したシート３の並び替えのコードを追記したのですがうまく動きません。
もしかしてこれは、Sheet1に記述していたためなのでしょうか。
このままSheet1に一連の作業として記述していきたいのですが、
どうしたらよいのでしょうか。
Excel2003を使用しています。

またModule1にSheet1のコードをコピーして移動させた場合は、どのようにすればコマンドボタンから実行させることができるのでしょうか。
宜しくお願い致します。

回答No.2

end-u · ベストアンサー

>このままSheet1に一連の作業として記述していきたい...

例えば
Sheets("Sheet3").Range("A1").CurrentRegion.Sort Key1:=Range("B2"), _
　　　　　　　　　　　　　　　　　　　　　　　　Order1:=xlAscending, _
　　　　　　　　　　　　　　　　　　　　　　　　Header:=xlYes, _
　　　　　　　　　　　　　　　　　　　　　　　　OrderCustom:=1, _
　　　　　　　　　　　　　　　　　　　　　　　　MatchCase:=False, _
　　　　　　　　　　　　　　　　　　　　　　　　Orientation:=xlTopToBottom, _
　　　　　　　　　　　　　　　　　　　　　　　　SortMethod:=xlStroke, _
　　　　　　　　　　　　　　　　　　　　　　　　DataOption1:=xlSortNormal

このコードが標準モジュールにあり、Sheets("Sheet3")をアクティブにして実行すればうまくいきます。
でもSheet1のシートモジュールにあれば、Sheets("Sheet3")をアクティブにしていても確実に失敗します。

理由は、
標準モジュールのコードでRangeの親オブジェクトを省略するとアクティブシートのRangeと見なされ、
シートモジュールのコードでRangeの親オブジェクトを省略するとそのモジュールがあるシートのRangeと見なされます。

Sheet1のシートモジュールの場合、
Sheets("Sheet3").Range("A1").CurrentRegion...でSheet3の範囲をソートしようとしているのに、
Key1:=Range("B2")...ここでSheet1のRange("B2")をキーに設定しているため、エラーになります。

Sheets("Sheet3").Range("A1").CurrentRegion.Sort Key1:=Sheets("Sheet3").Range("B2"), _...

...というように、Rangeの親を省略せずに書いて、どのシートのB2セルか指定してあげる事で
標準モジュールにあっても、シートモジュールにあっても、エラーなく動作します。
実際には変数を使ったり、Withステートメントを使ったりします。
http://www.asahi-net.or.jp/~ef2o-inue/vba_k/sub04_060.html

>またModule1にSheet1のコードをコピーして移動させた場合は、どのようにすればコマンドボタンから実行させることができるのでしょうか。

前述した事に気をつけてコードを修正し、Sheet1モジュールには
Private Sub CommandButton1_Click()
　　Call Macro1
End Sub
...などとして標準モジュールのマクロを呼び出すようにすると良いです。

お礼

ありがとうございました。
おかげさまで解決しました。
色々なHPを参考にして何とか作っていってはいますが、
基本をしっかり抑えとかなければならないということが
身にしみる今日この頃です。

回答No.3

nag0720

Sheet1上のマクロはSheet1で実行することを前提にしていますから、Sheets("Sheet1")が省略されています。（デフォルトになってます）
同様にSheet3上のマクロはSheets("Sheet3")が省略されています。
ですから、Sheet3上のマクロをそのままSheet1にもってきても動かないか動いてもエラーになります。

対応策は、Sheet3上のマクロをSheet1にもってくるときに、Sheets("Sheet3").Rangeのように　Sheets("Sheet3").　を付加してください。

またModule1にSheet1やSheet3のコードを移動させた場合でも、Sheets("Sheet3").RangeのようにどのSheetかを指定しないとエラーになります。

回答No.1

nag0720

うまく動かないとは、
書いたコードが実行されないのか、
実行されても思い通りの結果にならないのか、
どっちですか？

補足

エラーメッセージが出て実行できませんでした。
Sheet1でSheet3の並び替えやら、Sheet3を主体に計算させていたことが問題ではないのでしょうか。
「オブジェクトは、このプロパティまたはメソットをサポートしていません。」
とメッセージが出た気がします。
マクロの記録でSheet3に作成されたコードをSheet1移動さただけなのです。Sheet3のまま単体で実行する分には、問題ありません。　
直前のシート３をアクティブにさせて、ソート（マクロの記録部分）させる直前までは問題なく動きます。
宜しくお願い致します。

やはりModule1につくり直したほうが良いのでしょうか。

宜しくお願い致します。