Worksheet Object - Excel 97 - vba - 表計算゛ (Hyo-Kei-Zan)

はじめに

今回のテーマは、Worksheet Object の操作です。シートを自在に操れれば、データの入出力、計算領域、印刷用、ユーザーの操作面等、目的ごとに複数のシートを切り替えていけます。このような手法は、見通しの効いた VBA プログラミングには欠かせません。

この先、Object と Collection という言葉が、何度も出てきます。この概念に慣れていない人には、予め整理をしておいた方がよいでしょう。VBA から見ると、Excel は、巨大な Object の塊になっています。Excel 自身がひとつの Object であり、また、その配下に、多数の Object が存在します。VBA で Excel を使いこなすための重要な手がかりは、Excel の持つ Object を理解して、いかに VBA から操るか、にあります。また、同じタイプの Object を複数まとめたものを Collection と呼びます。今回のテーマになっている Worksheet は、1枚では Object ですが、たいがいの Excel Book には、複数の Worksheet が入っています。これらをひっくるめて、Worksheets Collection と呼びます。Object をスマートに扱うためには、Collection を上手に利用することが大切です。

実例

Worksheet の追加と削除

新規ワークシートを追加するには、Workbook の Sheets Collection に対して、Add を指示します。このメソッドは、新規追加された Worksheet Object を返します。
```
Sub Worksheet1()
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Sheets.Add
    MsgBox ws.Name, vbOKOnly, ws.CodeName
End Sub
```
Add メソッドで、複数の新規ワークシートを追加することができます。引数 Count に追加したい枚数(例題では 3枚)を指定します。この場合には、最後に追加された Worksheet Object を返します。
```
Sub Worksheet2()
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Sheets.Add(Count:=3)
    MsgBox ws.Name, vbOKOnly, ws.CodeName
End Sub
```
ワークシートを削除するには、Worksheet Object に対して、Delete を指示します。このメソッドは、必ず削除確認ダイアログを出すので、それが嫌な場合には、DisplayAlerts = False を一時的に設定しておきます。
```
Sub Worksheet3()
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Application.DisplayAlerts = True
End Sub
```
Active でない Worksheet も同じように削除できます。 Next プロパティの示す、「次のワークシート」を削除する例です。
```
Sub Worksheet4()
    Application.DisplayAlerts = False
    ActiveSheet.Next.Delete
    Application.DisplayAlerts = True
End Sub
```
Worksheet をまとめて一気に削除しましょう。ただし、Excel には最低でも 1 枚のシートが必要なので、全部消そうとするとエラーになります。Active な Worksheet を残して、他の Worksheet をすべて消してみましょう。Workbook の Worksheets Collection に対して、For Each でループをかけます。For Each は、Collection と相性のいい手法で、Collection に含まれるすべての Object に対して、ループ内の処理を順次、実行できます。Worksheet の CodeName プロパティを使って、削除対象かどうかを判定してから、Delete メソッドを使用します。
```
Sub Worksheet5()
    Dim MyName As String
    Dim ws As Worksheet
    MyName = ActiveSheet.CodeName
    Application.DisplayAlerts = False
    For Each ws In ActiveWorkbook.Worksheets
        If ws.CodeName <> MyName Then
            ws.Delete
        End If
    Next ws
    Application.DisplayAlerts = True
End Sub
```

Worksheet の名称

Worksheet には、2 つの名称があります。Excel を操作する人から見えるシート名称は、 Name プロパティが持っています。これと別に、 VBA から扱いやすいように作られた CodeName プロパティにもシート名称を持っています。この 2 つの違いを理解するために、次の例題を実行して、Excel のシート名称と、Visual Basic Editor から見えるシート名称が、どう変化するかを見てみましょう。これは、すべての Worksheet の Name プロパティだけを変更します。
```
Sub Worksheet6()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        ws.Name = "Sheet200" & ws.Index
    Next ws
End Sub
```
2つの異なる名称は、時としてプログラミングの際に思わぬ混乱をもたらします。次の例題では、Name プロパティの持つシート名称を CodeName プロパティと同じにします。もともと、Name プロパティは、ユーザーがいつでも変更できるもので、表示用の名称という色の濃いものです。これに対して、CodeName は、VBA から不変の名称で扱えるよう、自動的に割り振られるものです。Name はいつでも変更できますが、CodeName は変更できません。
```
Sub Worksheet7()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        ws.Name = ws.CodeName
    Next ws
End Sub
```

Worksheet のコピーと移動

Active な Worksheet の複製を、同じ Book 内に作成します。Worksheet Object の Copy メソッドを使用しますが、引数 Before または After に、コピー場所を明示しなければ、新しい Book にコピーされてしまいます。
```
Sub Worksheet8()
    ActiveSheet.Copy Before:=ActiveSheet
End Sub
```
Active な Worksheet を、新しい Book としてコピーします。Worksheet Object の Copy メソッドは、引数を省略した場合、新規 Book を作成します。
```
Sub Worksheet9()
    ActiveSheet.Copy
End Sub
```
Workbook 内のすべての Worksheet を、新しい Book としてコピーします。Worksheets Collection の Copy メソッドを使用します。
```
Sub Worksheet10()
    ActiveWorkbook.Worksheets.Copy
End Sub
```
Active な Worksheet を、新しい Book に移動します。Worksheet Object の Move メソッドは、引数を省略した場合、新規 Book を作成します。
```
Sub Worksheet11()
    ActiveSheet.Move
End Sub
```

Worksheet をファイルに保存する

Worksheet をファイルに保存するには、Worksheet Object の SaveAs メソッドを利用します。保存するファイル形式を指定しない場合には、Excel Book として、他のシートと共に保存されます。引数 ReadOnlyRecommended に True を指定すれば、読み取り推奨保存されます。
```
Sub Worksheet13()
    ActiveSheet.SaveAs FileName:="C:\tmp\sheet13.xls", 
                       ReadOnlyRecommended:=True
End Sub
```
Worksheet を単独で保存するには、新しい Book にコピーしてから、保存します。例題の中に ActiveSheet が 2 回出てきますが、1つ目はオリジナル、2つ目はコピーした後のシートを指し示します。
```
Sub Worksheet14()
    ActiveSheet.Copy
    ActiveSheet.SaveAs FileName:="C:\tmp\sheet13.xls"
End Sub
```
Worksheet を CSV テキストとして保存するには、SaveAs メソッドの引数 FileFormat に xlCSV (6) を指定します。
```
Sub Worksheet15()
    ActiveSheet.SaveAs FileName:="C:\tmp\sheet15.csv", 
                       FileFormat:=xlCSV
End Sub
```
Worksheet をスペース区切りテキストとして保存するには、SaveAs メソッドの引数 FileFormat に xlTextPrinter (36) を指定します。
```
Sub Worksheet16()
    ActiveSheet.SaveAs FileName:="C:\tmp\sheet16.txt", FileFormat:=xlTextPrinter
End Sub
```

Worksheet に関する、その他の操作

Worksheet Object の Activate メソッドは、対象となる Worksheet を Active に切り替えます。この例題は、一定間隔を置きながら、Active な Worksheet を順次切り替えていきます。
```
Sub Worksheet12()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        Application.Wait Now() + 1 / 24 / 3600
    Next ws
End Sub
```
Worksheet Object の Visible プロパティを使って、シートを非表示にできます。定数 xlSheetHidden は 0 です。
```
Sub Worksheet17()
    ActiveSheet.Visible = xlSheetHidden
End Sub
```

この例題は、すべての Worksheet を可視にします。定数 xlSheetVisible は -1 (&HFFFFFFFF) です。

Sub Worksheet18()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next ws
End Sub

小噺

Worksheets Collection と Sheets Collection

Worksheets と Sheets は、非常に似通った Collection です。これらのメソッドやプロパティは同じです。Worksheet Object は、これらのいずれにも含まれています。Sheets Collection は Worksheets Collection を包含する、より大きな Collection で、Worksheet Object だけでなく、以下の Object も含んでいます。

Object	説明	Type の定数値
Worksheet	ワークシート	Const xlWorksheet = -4167 (&HFFFFEFB9)
Chart	チャートシート	Const xlChart = -4109 (&HFFFFEFF3)
Dialog	ダイアログシート	Const xlDialogSheet = -4116 (&HFFFFEFEC)
-	Excel 4 マクロシート	Const xlExcel4MacroSheet = 3
-	Excel 4 マクロシート(国際版)	Const xlExcel4IntlMacroSheet = 4

Sheets Collection の Add メソッドで、引数 Type に上記の定数値を指定すれば、各々のシートを作成できます。これら大部分は、古いバージョンとの互換性のために残されているものですが、現時点ではチャートシートの存在が、2つの Collection の大きな違いだと言えます。

Worksheet を特定する、いくつかの方法

数多くの Worksheet Object の中から、目的の Worksheet を選び出す方法は、いくつかあります。

用例	解説
ActiveSheet	Active になっている Worksheet Object を特定する。
ActiveWorkbook.Worksheets(1)	Worksheets Collection の item 順位を利用する。1から始まる連番を指定する。
ActiveWorkbook.Worksheets.Item(1)	Worksheets Collection の item 順位を利用する。上の方法の丁寧な表現。1 2
ActiveWorkbook.Worksheets("Sheet3")	Worksheets Collection に対して、Worksheet Object の Name プロパティを与える。
Sheet1	Worksheet Object の CodeName を直接用いる。
ActiveSheet.Next	Worksheet Object の Next プロパティで、次のシートを示す。最後のシートで実行するとエラーになります。
ActiveSheet.Previous	Worksheet Object の Previous プロパティで、前のシートを示す。最初のシートで実行するとエラーになります。

Worksheet Object の Index プロパティは、Sheets Collection の Item プロパティに対応するもので、Worksheets Collection に対応する数値ではありません。
Worksheets Collection の Item 数は、Count プロパティで取得できます。

上位 Object

Worksheet Object の上位 Object は、Workbook Object です。次のように、Parent プロパティを使って、Worksheet Object が含まれる Workbook Object を示すことができます。

ActiveSheet.Parent

上の表現は、ActiveWorkbook と同じものを返します。

Download a Sample Code

上記の解説で用いたサンプルコードは、以下のリンクからダウンロードできます。テキストファイルとして用意しているので、保存して Excel の Visual Basic Editor にインポートしてください。

Save a program source (sample-vbaxlws1-1.bas) on your local disk.
Start Microsoft Excel with a blank(new) book.
Open the Visual Basic Editor. (Tool/Macro/Visual Basic Editor)
Import the downloaded program source. (File/Import Files)

History

2002/3/24: URL移転
2001/12/5: object ってなんだろう？へのリンクを追加
2001/2/11: sample-vbaxlws1-1.bas put
2001/2/11: 初版