Setステートメントをまとめて記述する方法 （エクセル2000VBA）

質問

お世話になります。

Setステートメントで以下のように書いて、シート名を省略して使っています。
　Set a = ThisWorkbook.Worksheets("い")
　Set b = ThisWorkbook.Worksheets("ろ")
　Set c = ThisWorkbook.Worksheets("は")

これをプロシージャ毎に書くとコードが長くなるので、先頭かどこかに1回書くだけで、全てのプロシージャで使えるようにしたいのですがどうしたら良いでしょうか？

このようなプロシージャを実行したいのですが、
Private Sub CommandButton1_Click()
　a.Range("A2").Value = "データ1"
　b.Range("B4").Value = "データ2"
　c.Range("C9").Value = "データ3"
End Sub
（他にもコマンドボタンやチェックボックス用のプロシージャがあります）

Setステートメントだけを先頭に書くと、
「プロシージャの外では無効です」というエラーが出ましたので、
Public Sub hensuu()
　Set a = ThisWorkbook.Worksheets("い")
　Set b = ThisWorkbook.Worksheets("ろ")
　Set c = ThisWorkbook.Worksheets("は")
End Sub
のようにしたら、「実行時エラー"424"：オブジェクトが必要です」というエラーが出てしまいました。

どのようにしたらエラーが出ず正しく動くようになりますでしょうか？よろしくお願いします。

回答No.4

めとろいと（@naktak） · ベストアンサー

> ただ、シート数や順番、シート名など変更になる場合が多いので、1ヶ所変更したら置換などしなくても全てのコードが変更されるようにしたかったので

シート数が変更になった時点で、別のコーディングが入ると思います。
シート名、シート数が変更になっただけであれば、
以下の方法で対処可能です。
No.3のコーディングを例にします。

【Sheet（い）】
Private Sub CommandButton1_Click()
With ThisWorkbook.Sheets(SHEET1_NAME)
.Range("A2").Value = "データ1"
.Range("B4").Value = "データ2"
.Range("C9").Value = "データ3"
End With

With ThisWorkbook.Sheets(SHEET2_NAME)
.Range("A2").Value = "データ1"
.Range("B4").Value = "データ2"
.Range("C9").Value = "データ3"
End With

With ThisWorkbook.Sheets(SHEET3_NAME)
.Range("A2").Value = "データ1"
.Range("B4").Value = "データ2"
.Range("C9").Value = "データ3"
End With

End Sub

【Module1】
Public Const SHEET1_NAME As String = "い"
Public Const SHEET2_NAME As String = "ろ"
Public Const SHEET3_NAME As String = "は"

シート数が増えた場合にはModule1の定数を追加して下さい。

とりあえず、瞬間的に考えたらこれしか思いつきませんでした＾＾；

お礼

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

シート名をModule1にまとめて書いておいたらいいんですね。これで変更箇所がひとつで済み、楽になりました！

個々のプロシージャにも、1行だけ
Set W = Thisworkbook.Worksheets
を追加し、

With W(SHEET1_NAME)
.Range("A2").Value = "データ1"
.Range("B4").Value = "データ2"
.Range("C9").Value = "データ3"
End With

としてみました。書き忘れや変更もれがなくなりそうです。ありがとうございます。

回答No.6

Wendy02

追伸です。Wendy02ですが、タイトルのご質問そのものは、変数をCollection にすれば、1つの変数で、Objectを複数個、保持できますが、それは、ひじょうに特殊な方法です。

私自身は、「シート数や順番、シート名など変更になる場合」を想定して作りますが、それは、隠し技のようなものです。

それよりも、ユーザー側が、ブック変更の禁止の方法をさせる方法を考えたほうがよいと思います。変更するのは、製作する側だけのものにします。

実際に、私自身は、そんな多くはありませんが、#5で示したようなローカル配列変数を使っています。Publicにする場合は配列変数では受け渡しはしません。1つのプロシージャの中で配列変数を作ります。Collectionの場合も同じです。ローカルの中で処理するようにします。手間のようですが、経験的に、そのほうがエラーが避けられます。

お礼

ご回答ありがとうございます。
Collectionについてヘルプなどを読んでみましたが、いまいちピンときませんでした…。まだまだ知識が足りないので、これからゆっくりでも理解していきたいと思います。

Wendy02さん、naktakさん、たくさんのご回答・アドバイスありがとうございました。

回答No.5

Wendy02

こんばんは。

現実的な使い方でないと、どうしようもないので、後は、こんな使い方がありますね。

> ただ、シート数や順番、シート名など変更になる場合が多いので、1ヶ所変更したら置換などしなくても全てのコードが変更されるようにしたかったので

標準モジュール
Public Const myWshName As String = "い,ろ,は"

シートモジュール
'----------------------------------
Private Sub CommandButton1_Click()
Dim Wsh() As String
　Wsh() = Split(myWshName, ",")
　With ThisWorkbook
　.Worksheets(Wsh(0)).Range("A2").Value = "データ1"
　.Worksheets(Wsh(1)).Range("B4").Value = "データ2"
　.Worksheets(Wsh(2)).Range("C9").Value = "データ3"
　End With
End Sub

ただ、単に、置き換えただけに過ぎません。配列変数を置かない理由は、実際にやってみるとわかりますが、失敗する時があります。

お礼

ご回答どうもありがとうございます。
標準モジュールで定義した変数を、関数で配列のようにしているのですね。初めて見るコードや関数が多くてとても勉強になります。すごく便利そうですね。
また機会がありましたら使わせていただきます。

回答No.3

めとろいと（@naktak）

> Setでなく、文字列を省略するようなコードというのはありますでしょうか？

With句で出来ます。

Private Sub CommandButton1_Click()
With ThisWorkbook
.Sheets("い").Range("A2").Value = "データ1"
.Sheets("ろ").Range("B4").Value = "データ2"
.Sheets("は").Range("C9").Value = "データ3"
End With

End Sub

１シートに複数処理をしたい場合は以下のようにします。

Private Sub CommandButton1_Click()
With ThisWorkbook.Sheets("い")
.Range("A2").Value = "データ1"
.Range("B4").Value = "データ2"
.Range("C9").Value = "データ3"
End With

With ThisWorkbook.Sheets("ろ")
.Range("A2").Value = "データ1"
.Range("B4").Value = "データ2"
.Range("C9").Value = "データ3"
End With

With ThisWorkbook.Sheets("は")
.Range("A2").Value = "データ1"
.Range("B4").Value = "データ2"
.Range("C9").Value = "データ3"
End With

End Sub

ただこれは変数として保持は出来ないので、
複数プロシージャで処理する場合には
何度も記述する必要があります。
ですが、インスタンスを生成しない為、メモリの消費は抑えられます。
１つのオブジェクトなどに対して複数処理を行う際に
コーディングの手間を省く事が出来ます。

お礼

ご回答どうもありがとうございます。
With句は、セルの書式設定をする時などによく使っていますが、こういう使い方もあるのですね。とても勉強になります。

でもこの場合ですと、「ThisWorkbook.Sheets("い")」という文字を沢山のプロシージャに何回も書かなければいけないので、できればそこを省略できるようにしたいのです…。

With句を使うとメモリの節約になるというのも初めて知りました。ありがとうございます！

回答No.2

Wendy02

こんにちは。

私には、インスタンスを作る以外には、実際にそのようなオブジェクトを、ずっと置いておくということはしませんね。本来は、オブジェクトコードネームを使うという方法もありますが、開発する時は、そのシートの目的や主旨を表したオブジェクトコードネームに、名称につけます。省略するためではありません。

もしするなら、標準モジュールにこのようにおいたらいかがですか？

戻し忘れをしない限りは、一旦、シートオブジェクトを入れれば、できますね。あくまでも、これも参考です。

Public a As Worksheet
Public b As Worksheet
Public c As Worksheet

お礼

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

用語がほとんど分かりませんので、調べつつ拝見してるのですが、Setステートメントは入力を簡略化するものではないのですね…。字数を少なくするとかコードを見やすくするものだと思っていました。

Setでなく、文字列を省略するようなコードというのはありますでしょうか？
よろしくお願いします。

回答No.1

めとろいと（@naktak）

あくまで参考例です。

【ThisWorkbook】
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set xlWS = Nothing

End Sub

Private Sub Workbook_Open()
Set xlWS = ThisWorkbook.Worksheets

End Sub

【Sheet1（い）】
Private Sub CommandButton1_Click()
xlWS("い").Range("A2").Value = "データ1"
xlWS("ろ").Range("B4").Value = "データ2"
xlWS("は").Range("C9").Value = "データ3"

End Sub

【Module1】
Public xlWS As Sheets

こんな事でしょうか？
動作確認済み。

お礼

ご回答どうもありがとうございます。
思っていた通りの動作になりました！
是非使わせていただきますね。

ただ、シート数や順番、シート名など変更になる場合が多いので、1ヶ所変更したら置換などしなくても全てのコードが変更されるようにしたかったので、シート名までも含めて、定義できないのは残念です…。
そういう設定はエクセルには不可能ということなのでしょうか…。