前のシートの所定の位置のデータを参照するマクロと関数 Excel / LibreOffice Calc ~How to get prev sheet's cell value on Microsoft Excel and LibreOffice Calc

最近はLibreOfficeの関数・Basicを勉強しています。というのも、次にパソコンを買い替えるとしたら、中華薄型ノートか4K出力のMiniPCかと思っているので、数万円かけてMicrosoft Officeを買うのはありえないなと。

先ずは家計簿（複式・月毎シート）のExcelからLibreOfficeへの移行を始めました。これから試行錯誤の足跡を残しつつ、いずれ企業のLibre化コンサルで一儲けしようかと（オイオイ…笑）

今回は、前月の資産を参照するための「前のシートのあるセルの値を参照する」マクロと関数を、それぞれのアプリで作ってみました。

Excel VBAの場合【Application.Caller】がミソ

Excel VBAは、Publicで宣言したFunctionをワークシート関数として使うことが出来ます。以下の関数では、自分のセルの行・列・シートインデックス（左から１、２・・・）を取得し、ひとつ前のシートのオフセットしたセルの値を参照します。複式で家計簿を書くと、前月資産をある場所に書き写す必要がありますが、この関数では、シート名を明記する必要が無く、全シート同じ関数の入力で済みます。

Application.Callerは（呼ばれた＝関数が入力されたセル）を取得できるので、アドレスや親シートも参照できるのです。

Public Function getPrevData(offsetRow, offsetColumn)
    
    currentRow = Application.Caller.Row
    currentColumn = Application.Caller.Column
    currentSheetIndex = Application.Caller.Parent.Index
    
    If currentSheetIndex > 1 Then
        With ThisWorkbook.Worksheets(currentSheetIndex - 1)
            getPrevData = .Cells(currentRow + offsetRow, currentColumn + offsetColumn).Value
        End With
    Else
        getPrevData = "first_sheet_error"
    End If
    
End Function

【使い方例】
３月のC2セルに =getPrevData(0, -2) と入力すると、２月のA2セルの値が表示されます。
１月はエラー表示となりますが、前年度の別ファイルから手動コピーするので問題ないと判断しました。

LibreOffice Calcの場合はCallerが無いので、関数を組み合わせる（INDIRECT、SHEET）

LibreOffice Basicで上記と同様のことを行おうとすると…

Dim currentSheet As Object
Dim currentIndex As Integer
currentSheet = ThisComponent.getCurrentController().getActiveSheet()
currentIndex =  currentSheet.RangeAddress.Sheet

これ、Activeなシートは参照できるのですが、Excel VBAのCallerに相当するプロパティが無いので、自分のセルは参照できないのです。

ちなみに、引数のセル範囲をRangeオブジェクトとして受け取ることもできないので、文字列をパースするしかないそうです。つまりLibreOffice Basicのユーザー定義関数は、算術計算くらいにしか使えない仕様。

海外の掲示板を見ても、「Application.Callerを実装してくれよ」という声を見かけるので、皆さん困っているんですね。

そこで、仕方なく機能を絞ってワークシート関数で考えました。

=INDIRECT(SHEET($A$1)-1& ".F2")

※".F2"の部分はセルごとに指定が必要です
※シート名を1,2,3...とする必要があります（１月、Januaryは不可）

SHEET($A$1)で入力したセルのシート名を取得できます（$A$1はどこでも構いません）。「3」なら3-1=2で、「2.F2」セルを参照します（Excelだと「2!F2」、シート参照は、びっくりマークとピリオドの違いがある）。入力は面倒ですが、月毎のシートでは不変なので、ひとつシートを作ればコピーだけで済みます。

【使い方例】
３月のC2セルに =INDIRECT(SHEET($A$1)-1& ".A2") と入力すると、２月のA2セルの値が表示されます。

ExcelとLibreOfficeは、マクロに関してはほぼ互換性ゼロなので、移行は苦労しますね。ただ、覚えればこれからOfficeにお金を使う必要が無くなること、JavaScriptやPythonでの開発もできることがメリットなので、頑張っていきます。