第２５回 Excelとの連携

Visual Basic 6.0 初級講座

第２５回　Excelとの連携

今回はＶＢからExcel（エクセル）を操作する基本的な方法を説明します。たとえば、Excelのxlsファイルを作ったり、文字を書き込んだり、色をつけたりなどをVBから行う方法を説明します。今回はExcelを使うのでVBのほかにExcelが必要です。

私はこの記事を書くにあたり　Excel 2000 を使いました。Excel 2002やExcel 97でも多分大丈夫だと思いますが、もしかしたら所によりエラーが出るかもしれません。そうだとしてもこれらは基本的な部分は同じなのでちょっとの手直しで済むはずです。

１．Excel と Visual Basic

ほとんどの人が知っているでしょうが、Excelとはマイクロソフト社の表計算ソフトの名前です。現在世界で最も多くの人が使っている表計算ソフトでしょう。Excelは最もはやい段階からVisual Basicを導入しています。つまりExcelにはVisual Basicがついてきているのです！。知らなかった人はExcelの[ツール]メニューから、[マクロ] - [Visual Basic Editor]をクリックしてください。ほら、Visual Basicが起動したでしょう？　

当初このExcelのVisual Basicは、Excleを操作するのが目的だったので機能も絞られていたのですが、現在ではどんどん多機能になってきています。VBのコードをそのままExcelのVisual Basicに貼り付けても動く場合も少なくありません。

このように昔からExcelとVBは相性が良いのでVBを使える人はExcelに関しても詳しくなっていきます。

現在ではExcelの他にもWord、Access、OutLook、PowerPoint、FrontPage、Visio、MapPointに、Visual Basicが装備されていて、これらを本家ＶＢと区別して、VBA（Visual Basic for Applications）と呼びます。

しかし、今回私はVBAの解説はほとんどしません。あくまで、本家VBからExcelを操作することに主眼をおきます。

２．文字を書く

それでは基本中の基本、「Excelのワークシートに文字を書く」プログラムを作ります。

Excel用語１　ワークシート：なんてことはないExcelの表のことです。単に「シート」ということもあります。今回はこのようにちょくちょくExcel用語が出てくるのでその都度、注を入れることにします。

まず、いつもどおりVBを起動してください。これもいつもどおり標準EXEを選択します。そうすると空のフォームが表示されるでしょう。ここまでは他のプログラムを作る時と変わりありません。違いはここからです。

[プロジェクトメニュー]の[参照設定]をクリックしてください。一覧表が出てくるのでその中から、Microsoft Excel 9.0 Object Library を選んでチェックします。（Excelのﾊﾞｰｼﾞｮﾝによって数字の部分は変わります。）

■画像１：参照設定。この一覧の内容は環境によって異なります

チェックしたらＯＫをクリックしてください。これで、あなたのアプリケーションから自由にExcelの機能が呼び出せます。

なお、参照設定の表にはExcelのほかにもいろいろ並んでいましたね。これらの機能をVBにとりこんでいろいろと面白いこともできるのです。それらはまた別の機会にご説明しましょう。

さて、フォームにコマンドボタンを一つ配置したらダブルクリックして次のように記述してください。

Private Sub Command1_Click()

    Dim ExcelApp As New Excel.Application           'エクセルを宣言する
    Dim Book     As WorkBook                        'ワークブックの宣言

    Set Book = ExcelApp.WorkBooks.Add               '新しいワークブックを追加
    Book.Worksheets(1).Range("A1") = "こんにちは！" 'セルA1に「こんにちは！」を入力
    ExcelApp.Visible = True                         'エクセルの表示

End Sub

このプログラムを実行するとセルＡ１に「こんにちは！」と入力されたワークシートが表示されます。

Excel用語２　セル：ワークシート（エクセルの表）のますめのことです。ここのセルを区別するためにＡ列の１行目のセルを　Ａ１　のように表現します。

Excelのセルに書き込むなどの操作をする場合、Excelのセルはワークシートに属していることを意識しなければなりません。さらに、より厳密にはワークシートはワークブックに属しています。

Excel用語３　ワークブック：簡単に言えば xlsファイルのことです。一つのxlsファイルには複数のワークシートを含むことができます。

そのため、プログラムでは、「どのワークブック」の「どのワークシート」の「どのセル」に値を書き込むのか指定する必要があります。

その指定が、Book.Worksheets(1).Range("A1") = "こんにちは！" です。変数Bookにはこのすぐ上の行で新しいワークブックがセットされています。その新しいワークブックのの１つ目のワークシートのセルA1に「こんにちは」を書き込むと言うのがこの行の命令となります。

なお、ワークブックが作成されると自動的にその中にワークシートが３枚作成されますのでワークシートを自分で作る必要はありません。自動的に作成されたワークシートには Book.WorkSheet(1) のようにアクセスすることができます。

最後に ExcelApp.Visible = True を入れないとExcelが表示されないので注意してください。逆にわざとエクセルを非表示にしたまま見えないところで作業するという手もあります。たとえば、このプログラムを次のように変えてみてください。

Private Sub Command1_Click()

    Dim ExcelApp As New Excel.Application           'エクセルを宣言する
    Dim Book     As WorkBook                        'ワークブックの宣言

    Set Book = ExcelApp.WorkBooks.Add               '新しいワークブックを追加
    Book.Worksheets(1).Range("A1") = "こんにちは！" 'セルA1に「こんにちは！」を入力
    Book.SaveAs "C:\VBExcel.xls"
    Book.Close
    ExcelApp.Quit

End Sub

後半の部分を変えただけですが、Excel.Visible = True がないので、画面にはエクセルは一切表示されません。

画面に表示されないのでエクセルを終了させるのもプログラムから行いましょう。終了し忘れるとVBでの処理が終了しても目に見えないエクセルだけが残ってしまいます。もし、目に見えないエクセルが残ってしまった場合は、[Ctrl] + [Alt] + [Del]で表示される強制終了ダイアログから　Excel を選んで強制的に終了させるしかなくなります。

しかも、エクセルは同時にいくつでも起動できるのでこの手のプログラムを試しに実行しながら作っていると気が付いたら目に見えないエクセルが５、６個起動していたということもありました。

３．こった処理もこんなに簡単！

文字を書き込むことさえできればあとは簡単です。といわれてもすぐには信じられないしょう。というのも、WorkBookとかRangeとか普段VBでは使わないキーワードがたくさん出てくるから少なくともこれらを覚えたり、その説明をみたりしなくてはいけないと感じていると思うからです。

しかし、このような心配はほとんどあたりません。私たちはエクセルの大変優れた「マクロ」機能を使うことができるからです。エクセルのマクロ機能は、エクセルの操作をVBで表現するとどうなるか教えてくれます。

では、例題とともに説明しましょう。VBでエクセルに棒グラフを作成して表示します。エクセルの知識があまりなくてもこのプログラムが驚くほど簡単にできることを実感していただけると思います。

例題：ひろし君の残業時間は次のように変化しています。

１月１２時間

２月８時間

３月４５時間

４月５１時間

５月２４時間

これをエクセルで表にして棒グラフも作りなさい。

なお、表は月の部分は太字で、背景はベージュ色にしなさい。

こんなこといきなり言われてもやり方がわからない・・・・・とか思うでしょう？でも、まぁ本当に簡単だから、私の言うようにやってみてください。

１．Excelを起動します。

２．[ツール]メニューから[マクロ][新しいマクロの記録]をクリックします。

マクロ名のところに「テスト」と入力し、「ＯＫ」をクリックします。

■画像２：マクロの記録

３．以下の画像のように月別残業時間を書き込みます。

■画像３

・セル A1 から B6 の背景色はベージュにします。

・セル A1 から A6 の文字は「太字」にします。

　

４．セル B1 から B6 をマウスで選択して、グラフウィザードを起動します。

■画像４：グラフウィザードのアイコン。このアイコンが表示されていない場合は、[挿入]メニューから[グラフ]をクリックするとグラフウィザードが起動します。

５．グラフウィザードが起動したらすぐ「完了」をクリックします。

６．[ツール]メニューから[マクロ][記録終了]をクリックします。

・・・とまぁエクセルの使い方をグダグダと説明してしまいましたが、以上の操作で自動的にVBのプログラムが作成されています。そのしかけが途中に出てきたマクロの記録です。早速今の操作で生成されたＶＢのコードを見てみましょう。

[ツール]メニューから[マクロ][マクロ...]をクリックして、出てくる一覧の中から「テスト」を選択して、「編集」をクリックしてください。画面が切り替わり、見事にVBのコードが表示されたでしょう。

つまり、あなたはエクセルの操作方法を知っていればいいだけで、細かいエクセルのVisual Basicの知識は必ずしも必要ないのです。あなたの操作をエクセルがVBに変換してくれるからです。

では、このようにして自動的に生成されたＶＢのコードをあなたのプログラムで動作させるにはどうした良いでしょうか。

答えは簡単です。コピーして貼り付けです。信じられないかもしれませんがたったこれだけであなたのVBのプログラムは完成します。実際に例題の作業を行うＶＢのプログラムは次のようになります。

このプログラムは先ほどのプログラムに１行追加してから、Book.Worksheets(1).Range("A1") = "こんにちは！"の部分を、エクセルが生成したコードに置き換えただけです。

追加した行は５行目の Book.Worksheets(1).Activate の行です。この命令は処理の対象となるワークシートを指定するものです。エクセルが自動生成したコードには「どのワークシート」に対して命令するかはっきりとした記述がないので、この部分だけ自分で追加します。

Private Sub Command1_Click()

Dim ExcelApp As New Excel.Application
Dim Book As Workbook

    Set Book = ExcelApp.Workbooks.Add
    Book.Worksheets(1).Activate

    Range("A1").Select
    ActiveCell.FormulaR1C1 = "月"
    ActiveCell.Characters(1, 1).PhoneticCharacters = "ツキ"

    Range("B1").Select
    ActiveCell.FormulaR1C1 = "残業時間"
    ActiveCell.Characters(1, 2).PhoneticCharacters = "ザンギョウ"
    ActiveCell.Characters(3, 2).PhoneticCharacters = "ジカン"

    Range("A2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "12"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "8"
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "3"
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "45"
    Range("A5").Select
    ActiveCell.FormulaR1C1 = "4"
    Range("B5").Select
    ActiveCell.FormulaR1C1 = "51"
    Range("A6").Select
    ActiveCell.FormulaR1C1 = "5"
    Range("B6").Select
    ActiveCell.FormulaR1C1 = "24"
    Range("A1:B6").Select

    With Selection.Interior
        .ColorIndex = 40
        .Pattern = xlSolid
    End With

Range("A1:A6").Select
Selection.Font.Bold = True

Range("B1:B6").Select
Charts.Add

    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B1:B6")
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"

ExcelApp.Visible = True

End Sub

なお、エクセルが自動的に生成したコードはエクセルのバージョンによって異なります。多少違うコードが生成されてもあまり気にする必要はありません。

４．それでも自分でわかりたい

以上の解説であなたの操作をエクセルにＶＢ化させて、ＶＢから自動的に実行させる方法がわかっていただけたと思います。この一連の作業ではあなたはVBからエクセルを操作することについて最低限の知識だけしか必要ないということも理解していただいたでしょう。

それでも、わざわざVBからエクセルをあやつるのだからもっといろいろやりたいという要望もあることと思います。たとえば、If文を使った条件判断、ファイルやデータベースから値を読み込んでエクセルに書き込む処理などなど。

このようなエクセルのマクロ機能ではVB化できないような処理を記述するにはやはりエクセルVBAの機能をもっと知る必要があります。

たとえば、先ほどの例題では背景をベージュにするという部分がありましたが、コードで言うとそれがどの部分にあたるかわかりますか？

答えを言ってしまえば、

With Selection.Interior

.ColorIndex = 40
.Pattern = xlSolid

End With

の部分がそれにあたりますが、では青に変更したいと思った場合どういじればいいかということはわかりませんね。

といって私がこまごま説明していてはこのViausl Basic中学校がExcel中学校になってしまいます。そこで、みなさんにはこれらのExcel VBAについての説明が載っている良いＷＥＢページを紹介して私からの説明は割愛いたします。

１．MSDNライブラリ

http://www.microsoft.com/japan/msdn/library/?frame=true

左に表示される目次から、

[MSDN ライブラリアーカイブ] - [Office ソリューション開発] - [Microsoft Office 2000] - [VBA ランゲージリファレンス] - [Microsoft Excel 2000 リファレンス]をたどっていくとExcel VBAに関する説明書が読めます。

たとえば、先ほどのベージュ色に関して、InteriorオブジェクトのColorIndexプロパティを調べるにはさらに、-「プロパティ] - [C] - [ColorIndex プロパティ]を選択します。

説明を見れば、青色にするにはColorIndexプロパティを5や32などにすればよいことがすぐにわかりますね。

MSDNライブラリのCDやDVDを持っていればインターネットにアクセスしなくてもこれらの情報を見ることができます。ただし、MSDNライブラリのバージョンにより目次の構造がかなり変わっていますので注意してください。

MSDNライブラリはVisual BasicやOffice 2000 Developer を買うと多分箱の中に入っています。MSDNの会員には常に最新版が配布されます。

２．Microsoft Office Developer Center

http://www.microsoft.com/japan/msdn/office/

このページはOffice開発者向けのメインページと言う位置づけです。Office開発に関するいろいろな情報や記事が参照できます。

３．その他

インターネットで検索すると結構いろいろ出てきます。知りたい情報をずばり検索してみましょう。

５．最後に

今回の説明では本当に必要な処理の記述などは説明していません。しかし、VBからエクセルを操作する方法はわかっていただけたでしょう。私の解説がみなさんのエクセル進出への第一歩となっていただければ幸いです。

それから、途中にも書きましたがExcelを操作するときはExcelのプロセスが残らないようにきちんと終了しているか常に確認してください。もし、VBでの処理が終了しているのにExcelのプロセルが残ってしまって困っている場合はテクニック１５．消えないExcelのプロセスを参照してください。

最後に最近(2005年)の動向にもふれておきます。目下マイクロソフトはOffice製品を.NET Frameworkから制御できる新しい仕組みを開発中です。既にExcel 2003とWord 2003に関してはVB.NETやC#などの.NET言語を用いて今回解説した内容と同じことが可能になっています。それが、「Visual Studio Tools for the Microsoft Office System」です。

今後、他のOffice製品も.NET Frameworkから制御できるようにするとのことですが大分先の話になるようです。たとえば、AccessをVB.NETでプログラムできる日はかなり未来です。

１月	１２時間
２月	８時間
３月	４５時間
４月	５１時間
５月	２４時間