出直し!! ドミノ塾: Notes - Excel 連携：#24）式の設定

今回はセルに計算式を設定する方法についてまとめます。

式の設定

セルに式を設定する方法は、Range オブジェクトの Formula プロパティを使用します。

次の図のように E10 のセルに E6 ～ E9 までの合計を計算させる場合を考えます。

LotusScript のコードとしては、次のようになります。なお、 oSheet にはこれまでの流れで WorkSheet オブジェクトがセットされている前提です。

oSheet.Cells(10, 5).Formula = "=SUM(E6:E9)"

単一のセルのアクセスは Range オブジェクトを使用するより、Cells プロパティを使用して行と列を数値で指定（RC 形式）できるので便利です。ただ、設定する式では、列を文字であらわす形式（正式名称を知らないので”A1形式”と呼びます）の方がなじみがあります。

行列を A1 形式に変換

ループや加算減算などを考えるとプログラム内では数値で行列で指定する方が断然向いています。そこで、行と列を A1 形式の文字列に変換する関数を作成します。

まず、数値を文字列に変換する関数です。この関数では、1 を A、26 を Z、27 を AA に変換します（２桁までと単純化しています）。

Function x9ToA(ByVal viColNumber As Integer) As String
   Dim i As Integer
   Dim i1 As Integer
   Dim i2 As Integer
   Dim s As String

   i = viColNumber - 1
   i1 = (i Mod 26) + 1
   i2 = Int(i / 26)

   If i2 > 0 Then s = Chr(64 + i2)
   s = s & Chr(64 + i1)

   x9ToA = s
End Function

Mod は割り算の余りを求める演算子です。これを利用して、i1 変数には１桁目の数値を求めています。i2 は２桁目で Int を使って商の整数部分を求めて算出しています。

これに 64 を足して文字に変換し A - Z の文字列を取得しています（A の文字コードは 65）。

続いて、行と列を A1 形式に変換する関数を作成します。

Function xRCToA1(ByVal viRow As Integer, ByVal viCol As Integer) As String
xRCToA1 = x9ToA(viCol) & CStr(viRow)
End Function

行単位の計算式

作成した関数を利用して、行単位の計算式を設定します。白黒とカラーの金額（枚数×単価）と合計金額の３か所を計算します。

ヘッダ行の次の行から、明細の行数（viDoc）分だけ順に式を設定します。

Function xCalcRow(voSheet As Variant, ByVal viDoc As Integer)
   Dim iRow As Integer
Dim i As Integer
Dim oRange As Variant

iRow = xciHeaderRows
   For i = 1 To viDoc
iRow = iRow + 1

      '白黒
      Set oRange = voSheet.Cells(iRow, 5)
      oRange.Formula = "=" & xRCToA1(iRow, 3) & "*" & xRCToA1(iRow, 4)
      'カラー
      Set oRange = voSheet.Cells(iRow, 8)
      oRange.Formula = "=" & xRCToA1(iRow, 6) & "*" & xRCToA1(iRow, 7)
      '合計
      Set oRange = voSheet.Cells(iRow, 9)
      oRange.Formula = _
"=Sum(" & xRCToA1(iRow, 5) & "," & xRCToA1(iRow, 8) & ")"
   Next
End Function

列単位の計算式

続いて、列単位の計算を行います。

次の関数は、指定した列（viCol）の合計を最終の明細行（xciHeaderRows + viDoc）の次の行に SUM 関数をセットします。

Function xCalcSum(voSheet As Variant, ByVal viCol As Integer, ByVal viDoc As Integer)
   Dim oRange As Variant
   Dim iMin As Integer
   Dim iMax As Integer

   iMin = xciHeaderRows + 1
   iMax = xciHeaderRows + viDoc

   Set oRange = voSheet.Cells(iMax + 1, viCol)
   oRange.Formula = _
"=Sum(" & xRCToA1(iMin, viCol) & ":" & xRCToA1(iMax, viCol) & ")"
End Function

列単位の合計は、枚数と金額の計 5 列にセットします。

Function xCalcCol(voSheet As Variant, ByVal viDoc As Integer)
   Call xCalcSum(voSheet, 3, viDoc)
   Call xCalcSum(voSheet, 5, viDoc)
   Call xCalcSum(voSheet, 6, viDoc)
   Call xCalcSum(voSheet, 8, viDoc)
   Call xCalcSum(voSheet, 9, viDoc)
End Function

メインルーチンの修正

今回作成した関数を実行するためメインルーチンに以下の行を追加します。

Sub Initialize
      ・・・
   '使用量出力（年月度指定）
iDoc = xPrintUsages(oSheet, 2023, 10)

'計算式のセット
Call xCalcRow(oSheet, iDoc)
Call xCalcCol(oSheet, iDoc)

'Excel を UI に表示
   oXls.Visible = True
End Sub

ここまでのプログラムを実行すると、下図のように合計が表示されます。