Excelマクロ(VBA)CELL形式とA1形式の使い分け

普段CELL形式でコードを書いていたのですが、A1形式で書いてあるコードをよく見かけるのですがA1形式でコードを書いた方がいいのでしょうか？
また、A1形式で対象のシートをアクティブせずにセルの結合や入力の規則の設定をしているコードを見かけたのですがCELL形式でもシートをアクティブにしなくても設定可能なコードがあるのでしょうか？
A1:C5セル範囲など複数セルでの別シートから設定可能なCELL形式のコードがありましたら御教授お願いいたします。

質問者からの補足コメント

• 質問が分かりづらくてすみません。
  下記のようにコードを書いた場合はエラーになりませんでしたが『Range(Cells(1, 1), Cells(5, 3))』に書きかえるとSheet1を予めSelectし直さないとエラーになってしまいます。
  この違いが分からなくて何が原因でしょうか？
  
  
  Sheets("Sheet2").Select
  With Sheets("Sheet1").Range("A1:C5").Validation
  .Delete
  .Add Type:=xlValidateList, Operator:=xlEqual, Formula1:="◯,✕,△"
  End With

    補足日時：2022/08/28 08:42

• 御教授ありがとうございます。
  分かりづらくてすみません。
  捕捉追記致しましたので御教授頂けたら幸いです。

  No.1の回答に寄せられた補足コメントです。 補足日時：2022/08/28 08:46

No.2 ベストアンサー 回答者： Qchan1962 回答日時： 2022/08/28 13:50

横から失礼します。

珍しいことに　シートをアクティブせずに　を見落としたのでしょうか？

＞この違いが分からなくて何が原因でしょうか？
省略されているオブジェクトのアクティブシートやアクティブブックなどを
覚えてください

Sheets("Sheet1").Range("A1:C5")　文字通りSheet1のセル範囲ですね

Range(Cells(1, 1), Cells(5, 3))　　は
個々のRangeオブジェクトはアクティブシートのRangeオブジェクトです
ですので・・

Sheets("Sheet1").Range(Sheets("Sheet1").Cells(1, 1), Sheets("Sheet1").Cells(5, 3))
とする必要があります（各Rangeにシートを明示する）

エラーの理由はシートを跨るRange指定はエラーが返ります

（１ブックしか開いていない場合は
Range(Sheets("Sheet1").Cells(1, 1), Sheets("Sheet1").Cells(5, 3))
でもエラーは出なくなると思います）

また、コードが長くなるので変数やwithなどを使う事が多くあります

dim ws1 as worksheet
set ws1=Sheets("Sheet1")
Sheets("Sheet2").Select
With ws1.Range(ws1.Cells(1, 1), ws1.Cells(5, 3)).Validation
.Delete

とか、面倒、変数を１度しか使わない場合などは　Resizeを使う
Sheets("Sheet2").Select
With Sheets("Sheet1").Cells(1, 1)resize(5,3).Validation
.Delete

＞A1形式で書いてあるコードをよく見かけるのですがA1形式でコードを書いた方がいいのでしょうか？
是非は良く解りませんが・・・QAサイトなどでは質問コードに合わせるなどをするのかも・・・変数などを使う場合はCellsの方が使い易いかと

この回答へのお礼

ご回答ありがとうございます。
分かりにくい質問で申し訳ありませんでした。
御指摘通り別シートCell範囲指定は
Sheets("Sheet1").Range(Cells(1, 1), Cells(5, 3))
で別シートセル範囲指定出来てるつもりになっておりました。
コードを修正しCell形式で非アクティブシートへの設定、操作が出来るようになりました。
勉強になります。

お礼日時：2022/08/31 13:12

No.6 回答者： komugi3333 回答日時： 2022/08/28 23:53

オブジェクト変数を使えると

With Sheets("Sheet1").Range(Sheets("Sheet1").Cells(1, 1), Sheets("Sheet1").Cells(5, 3)).Validation

も

Dim WS01 As Worksheet
Set WS01 = Sheets("Sheet1")
With WS01.Range(WS01.Cells(1, 1), WS01.Cells(5, 3)).Validation
～
End With

とか

Dim WS01 As Worksheet
Dim Rng01 As Range
Set WS01 = Sheets("Sheet1")
Set Rng01 = WS01.Range(WS01.Cells(1, 1), WS01.Cells(5, 3))
With Rng01.Validation
～
End With

といった感じに、
（行数は増えますけど、でも）「1行あたりとしては」短く書けるので、
省略をしなくてもよくなり、
エラーが出たときにエラーの原因を探しやすくなります。

上位のオブジェクトや下位のオブジェクトのプロパティやメソッドを流用できるメリットもあります。

この回答へのお礼

ご回答ありがとうございます。
分かりにくい質問で申し訳ありませんでした。
御指摘通り別シートCell範囲指定は
Sheets("Sheet1").Range(Cells(1, 1), Cells(5, 3))
で別シートセル範囲指定出来てるつもりになっておりました。
コードを修正しCell形式で非アクティブシートへの設定、操作が出来るようになりました。
沢山解説頂きありがとうございました。
勉強になります。

お礼日時：2022/08/31 13:14

No.5 回答者： komugi3333 回答日時： 2022/08/28 23:42

VBAには諸悪の根源の「省略」というものが許されていて、

どんな省略があるかは下記を見ればわかります。

https://excel-ubara.com/excelvba4/EXCEL289.html

このページでは省略をしてもいいよ、という内容ですが、
省略をすると
「オブジェクト変数を使うことを学習できずに」
「VBA的にはバカになる」ので、
結果的に
「自力でのエラー解決をできなくさせられる」という
ドツボにハメられてしまいます。

なので、VBAの省略＝諸悪の根源、と覚えておいて、
できるだけ省略をしない書き方を覚えたほうがいいです。

VBAの場合、
「少しでも短くかけるほうが美しいなどと思っているバカな人」が
「結構少なくない」のですが、
そういう人の言うことは聞かないほうがいいと思います。

美しくなくてもいいので、省略しないこと。
省略しないには、エラーを減らすには、
「With」なんかの「メリットほとんどない」「どうでもいい書き方」なんかほうっておいて
その前に、ちゃんと「オブジェクト変数」を使えるようになること、
だと思います。

No.4 回答者： komugi3333 回答日時： 2022/08/28 23:25

何度もすみません。

Sheets("Sheet1").Range("A1:C5").Validation

は

Sheets("Sheet1")の「A1:C5」という意味になるので
エラーにはなりませんが、

With Sheets("Sheet1").Range("A1:C5").Validation

は

先に「Sheets("Sheet2").Select」してしまっているがゆえに、

With Sheets("Sheet1").Range(Sheets("Sheet1").Cells(1, 1), Sheets("Sheet1").Cells(5, 3)).Validation

という意味ではなくて、

With Sheets("Sheet1").Range(Sheets("Sheet2").Cells(1, 1), Sheets("Sheet2").Cells(5, 3)).Validation

という意味になってしまいます。
（※Sheet1とSheet2の違いに着目してください）



With Sheets("Sheet1").Range(Sheets("Sheet1").Cells(1, 1), Sheets("Sheet1").Cells(5, 3)).Validation

では、全部Sheet1で統一されているので問題ないですが、

With Sheets("Sheet1").Range(Sheets("Sheet2").Cells(1, 1), Sheets("Sheet2").Cells(5, 3)).Validation

では、

Sheets("Sheet1")の中にある、Sheet2のセル範囲のCells(1, 1)～Cells(5, 3)の範囲、

と書いてしまっているので、
Sheet1の中に、Sheet2のセルなんかあるわけないやん！
とExcelがエラーを吐いているわけです。



標準モジュールの場合、
Cellsの前に何も書かないと
「Activesheet」のCellsになりますが、つまり、
「Sheets("Sheet2").Select」してしまっているがゆえに
Activesheet＝Sheet2になってしまっているからです。




いっぽう、
標準モジュールではなくて、Sheet1モジュールに直接書いた場合は、
Cellsの前に何も書かないと
「Activesheet」のCellsにはならず、
「Sheet1」のCellsになるので、
「Sheets("Sheet2").Select」してしまっていても
エラーにはならないと思います。
Sheets("Sheet1").Range(Cells(1, 1), Cells(5, 3)).Validation
が
「Sheets("Sheet2").Select」しようが何しようが、
With Sheets("Sheet1").Range(Sheets("Sheet1").Cells(1, 1), Sheets("Sheet1").Cells(5, 3)).Validation
という意味になります。



RangeやCellsの前に何も書かないと
標準モジュールやBookモジュールでは「Activesheetを指定した（＝Activesheetと書いた）」と同じ意味になり
各シートモジュールでは「プログラムを書いたシートを指定した」と同じ意味になり、
少しずつ意味が違ってくるので注意が必要です。



なので、今回のご質問では、
「A1形式で書かなからエラーになった」
わけではなくて
「RangeやCellsの前に何も書かない」ときのルール・・・、
というか、
「RangeやCellsの前に何も書かない」ときは状況によって「意味が異なってくる」
ということを質問者様がご存じなかったので出たエラーです。

なので、
A1形式もCell形式（R1C1形式）も、
「両方必要」なので、「目的によって使い分ける」のが正しい・・・
ということになると思います。

No.3 回答者： komugi3333 回答日時： 2022/08/28 22:56

＞A1形式で書いてあるコードをよく見かけるのですが

＞A1形式でコードを書いた方がいいのでしょうか？

「A1形式→Range(×××）」
「CELL形式→R1C1形式→Cells(×××）」の違い、
ってことですよね？たぶん。
どっちがいい・悪い、とかはなくて、
両方とも性質が違うので、目的によって使い分けることが重要、
と考えたほうがいいと思います。

別のQ&Aサイト（知恵袋）で似たような質問に回答したので、もしご参考になれば幸いです。
↓
https://detail.chiebukuro.yahoo.co.jp/qa/questio …

あと、Rangeオブジェクトを「×××.Rangeプロパティ」や「×××.Cellsプロパティ」で取得（ゲット）する際に知っておきべき基礎知識は以下のような感じです。
↓
https://euc-access-excel-db.com/tips/ct07_se/ct0 …
↑
あまり上手な説明ではないですが、知っておかないといけないことだらけなので、一度お目通ししておくとよいと思います。

No.1 回答者： fujillin 回答日時： 2022/08/28 00:31

こんばんは

A1形式でもCell形式とおっしゃっているのは、Rangeの指定方法のことですよね？
であるなら、どちらでも結局はRangeになるので同じです。
使いやすい方をご利用になれば宜しいかと思います。

＞CELL形式でもシートをアクティブにしなくても設定可能なコードがあるのでしょうか？
可能です。（理由は上記）

＞A1:C5セル範囲など複数セルでの別シートから設定可能な
＞CELL形式のコードがありましたら御教授お願いいたします。
「別のシートから」の意味が不明ですが、「ActiveになっていないシートのRangeを指定する」と言う意味と解釈しました。

対象シートオブジェクトが変数 ws だとするなら
　ws.Range(Cells(1, 1), Cells(5, 3))
とか。
あるいは、
　ws.Cells(1, 1).Resize(5, 3)
などで、実質的に、wsシートのA1:A5のセル範囲を意味することになります。

この回答へのお礼

ご回答ありがとうございます。
分かりにくい質問で申し訳ありませんでした。
勉強になります。

お礼日時：2022/08/31 13:02