■ ワークシートに作成した表の合計を計算する (2000,2002,2003,2007)
図 ワークシートに作成した表の列行の合計を計算する
ワークシートに作成した表の列合計と行合計を計算します。Accessのフォームを開いたら、[ワークシートの列行の合計を計算する]のボタンをクリックします。Excelが起動されて、表の列合計(B4:D4)と行合計(D2:D4)が表示されます。このサンプルは、ExcelのSUM()関数を使用して列合計と行合計を計算しています。
1. 新規データベース作成
Accessを起動したら、ツールバーから新規作成のアイコン
をクリックして、作業フォルダworkにW180.mdbを作成します。
2. フォーム、モジュールをインポート
Windowsのエクスプローラから、ダウンロードした180.mdbをダブルクリックして開きます。データベースウィンドウのオブジェクトから[フォーム]をクリックしたら、フォーム(frmRowColumnTotals)をドラッグしてW180.mdbのデータベースウィンドウにドロップします。同様の手順で、モジュール(basExcelAutomation)をドラッグ＆ドロップします。フォーム、モジュールをインポートしたら、180.mdbを閉じます。
3. ライブラリを追加
データベースウィンドウのオブジェクトから[モジュール]をクリックしたら、[basExcelAutomation]をダブルクリックして、デザインモードで開きます。VBEにモジュールが表示されたら、メニューバーから[ツール]→[参照設定]を選択します。｢参照設定｣のダイアログが表示されたら、参照可能なライブラリファイルから[Microsoft Excel X.X Object Library]をクリックしてチェックマークを付けます。[OK]ボタンをクリックしてダイアログを閉じます。
4. フォームを開く
VBEのツールバーから表示(Access)のアイコン
をクリックして、Accessのウィンドウに切り替えます。データベースウィンドウのオブジェクトから[フォーム]を選択したら、[frmRowColumnTotals]をダブルクリックして開きます。フォームが開いたら、[ワークシートの列行の合計を計算する]のボタンをクリックします。
Excelが起動されて、ワークシートに作成した表(B1:C3)の列合計(B4:C4)と行合計(D2:D4)を計算します。
図 ワークシートに作成した表の列合計と行合計を計算する
●解説
ワークシートに作成した表(B2:C3)の列合計と行合計を計算するには、SUM関数を埋め込みます。列合計を計算するには、列合計を表示する範囲(B4:C4)を求めてSUM関数を埋め込みます。SUM関数に指定するセルのアドレスは、Addressプロパティから取得します。
‘ 表の範囲(B2:C3)を求める
Set rngData = .ActiveSheet.UsedRange
‘ 列合計の範囲(B4:C4)を求める
Set rngColumnTotal = rngData.Offset(rngData.Rows.Count).Rows(1)
‘ 列合計の範囲にSUM()関数を埋め込む
rngColumnTotal.Formula = "=Sum(" & rngData.Columns(1).Address(False, False) & ")"
行合計には、列合計の行も含まれますので表の範囲をB2:C4に拡張します。次に、行合計を表示する範囲(D2:D4)を求めて、SUM関数を埋め込みます。SUM関数に指定するセルのアドレスは、Addressプロパティから取得します。
'
表の範囲を拡張する(B2:C3)→(B2:C4)
Set rngData2 = rngData.Offset(0, 0).Resize(rngData.Rows.Count + 1, rngData.Columns.Count)
‘ 行合計の範囲(D2:D4)を求める
Set rngRowTotal = rngData2.Offset(0, rngData2.Columns.Count).Columns(1)
‘ 行合計にSUM()関数を埋め込む
rngRowTotal.Formula = "=Sum(" & rngData2.Rows(1).Address(False, False) & ")"
‘ ワークシートの列合計/行合計を計算する
Public Sub CalculateRowColmunTotals()
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Workbooks.Add
‘ B2:C3の範囲に表を作成する
.Range("B2") = "1"
.Range("C2") = "2"
.Range("B3") = "3"
.Range("C3") = "4"
‘ 表の範囲(B2:C3)を求める
Set rngData = .ActiveSheet.UsedRange
‘ 列合計の範囲(B4:C4)を求める
Set rngColumnTotal = _
rngData.Offset(rngData.Rows.Count).Rows(1)
‘ 列合計の範囲にSUM()関数を埋め込む
rngColumnTotal.Formula = "=Sum(" _
& rngData.Columns(1).Address(False, False) & ")"
'
表の範囲を拡張する(B2:C3)→(B2:C4)
Set rngData2 = _
rngData.Offset(0, 0).Resize(rngData.Rows.Count + 1, _
rngData.Columns.Count)
‘ 行合計の範囲(D2:D4)を求める
Set rngRowTotal = _
rngData2.Offset(0, rngData2.Columns.Count).Columns(1)
‘ 行合計にSUM()関数を埋め込む
rngRowTotal.Formula = "=Sum(" _
& rngData2.Rows(1).Address(False, False) & ")"
.Visible = True ‘ Excelを可視状態にする
End With
End Sub