グラフ作成用プロシージャの作成
グラフ作成用プロシージャCreateGraphは、ワークシートに転送されたデータのセル範囲を自動的に算出し、グラフを生成します。これには、転送されたデータのあるセル範囲を取得し、グラフ用のセル範囲に作り変えるプロシージャGetRangeを作成して、この戻り値を元にグラフを作成します。
グラフ用のセル範囲の取得
プロシージャGetRangeは、メソッドとして公開しない、内部処理用のプロシージャとして使用しますので、Friendキーワードを付けたFunctionプロシージャにします。
Friend Function GetRange() As String End Function
WorksheetオブジェクトのUsedRangeプロパティで、ワークシート内で使用されたセル範囲を取得します。このプロパティは、セル範囲をRangeオブジェクトで返してきますので、RangeオブジェクトのAddressプロパティと組み合わせてセル番地を取得し、変数InDataに格納します。
InData = QryBook.Worksheets("Sheet1").UsedRange.Address
CreateObject関数は、複数のExcelのインスタンスを作成することができますから、どのブックのワークシートなのかを明示的に指定しないと、マクロが正常に動作しません。今回使用するクエリ「商品区分別売上高」は、列1には区分コード、列2に区分名があります。グラフに使用するのは列3、列4のデータなので、この部分をセル範囲として作成します。
Addressプロパティが返してくるアドレスは、「$A$1:$G$34」という「$」記号が付いた書式になっています。この文字列から、最下行の行番号を取り出します。
まず、Len関数でこのアドレスの文字数を数えます。そして、Mid関数で文字列の最後尾から1文字ずつ取り出し、変数UseRowに代入します。例えば、「$A1:$G34」であれば、「4」「3」「G」「$」と取り出し、1つの文字列にします。
Cnt = Len(InData) While Num <> "$" Num = Mid(InData, Cnt, 1) Cnt = Cnt - 1 If Num <> "$" Then UseRow = UseRow + Num End If Wend
取り出す文字が「$」記号に出会った時点でループは終わり、行番号だけが逆の文字列で変数に格納されます。例えば、「$A$1:$G$34」であれば「43」が格納されます。これを、StrReverce関数で逆の順序に入れ替えます。これで、この例であれば「34」という行番号が取り出せるわけです。
UseRow = StrReverse(UseRow)
この番号を元に、グラフに使用する列名と結合してセル範囲を作成し、プロシージャの戻り値になるようセットして出来上がりです。
GetRange = "C2" & ":D" & UseRow
グラフ作成プロシージャを作る
グラフの元になるセル範囲ができたら、グラフ作成を行う内部処理用のSubプロシージャCreateGraphを、キーワードFriendを付けて作成します。
Friend Sub CreateGraph() End Sub
まず初めに、グラフの元になるセル範囲を取得し、変数InDataに格納します。Friendキーワードを付けたプロシージャを呼び出すには、キーワードMeを使用します。Meのあとに「.」をつけると、自動的に使用できるメソッドの一覧が表示されます。ここから、セル範囲を取得するプロシージャGetRangeを選択します。
InData = Me.GetRange
このMEキーワードを付けないと、Friendキーワードを設定したプロシージャを呼び出すことができませんので注意してください。
次に、ChartオブジェクトのAddメソッドを使ってグラフを組み込みます。この時も、対象のオブジェクトに作成したブックを指定しておきます。メソッドを実行しグラフが作成されるとオブジェクトへの参照を返してきますから、これをオブジェクト変数に格納します。
Set Grp = QryBook.Charts.Add
そして、ChartオブジェクトのChartTypeプロパティとSetSourceDataメソッドで、グラフの種類と対象セル範囲を指定します。また、Locationプロパティで、ワークシート内のグラフの位置を指定します。
Grp.ChartType = xlColumnClustered
Grp.SetSourceData _
Source:=QryBook.Sheets("Sheet1").Range(InData), _
PlotBy:=xlColumns
Grp.Location Where:=xlLocationAsObject, _
Name:="Sheet1"
これで出来上がりです。最後にセルA1を選択して、グラフの選択状態を解除します。
QryBook.Sheets("Sheet1").Range("A1").Select
作成したクラスを利用するプロシージャの作成
作成したクラスのメソッドRecord_Transを使ったプロシージャを作成していきましょう。標準モジュールにプロシージャ「レコードを選択して転送」を作成します。
Sub レコードを選択して転送() End Sub
はじめに、DimステートメントにNewキーワードを付けて、クラスからオブジェクトを作成します。
Dim Qex As New clsTrans
そして、プロパティQryNameに、クエリ「1997年 商品区分別売上高 」をセットします。
With Qex .QryName = "1997年 商品区分別売上高 "
そして、メソッドRecord_Transを実行します。これで、クエリの結果セットから15項目をワークシートに転送し、このデータを元にグラフを作成します。
.Record_Trans
End With
最後に、使用したオブジェクト変数を開放して完成です。
Set Qex = Nothing
なお、作成したクラスは、Excelの場合と同様、プロジェクトエクスプローラでエクスポート・インポートが行えるので、1つクラスを作成してそれを複数のプロジェクトで使うことができます。
まとめ
これで、VBAでもクラスを作成して使えることがお分かりいただけたと思います。クラスの大きな目的は、複雑な処理を隠蔽しパッケージ化して、簡素なインターフェイスで誰もが使用できる形態にできることでしょう。サンプルの説明でもお分かりのように、クラスの作成には手間と時間がかかりますが、使うときは実に簡単です。プロパティを設定してメソッドを実行するだけです。
今回は入門ということで、駆け足で説明しましたが、本稿を参考に他の記事と併せて、便利なクラスを作ってみてはいかがでしょう。
