Excel 2019 for Mac での別ブック参照

マスターブックからデータを転記するなどの作業は VBA でよくある作業です。3つの方法を使って Mac版では Windows版と挙動が違うのかを調べてみました。

今回は転記するブックと同じ階層にある xlsm ファイルのA1セルから転記するようにしています。

転記コード

転記コードは3つあります。

１. Workbooks.Open

Sub tenki1()

Dim path As String

path = ThisWorkbook.Worksheets(1).Range("B3").value

Workbooks.Open filename:=path

ThisWorkbook.Worksheets(1).Range("B5").value = Workbooks("FormeCollector2.xlsm").Worksheets(1).Range("A1").value

Workbooks("FormeCollector2.xlsm").Close

End Sub

よくネットで見かけるコードです。私自身も恥ずかしい事にこの方法しか知りませんでした。ブックを開く為、画面変更があります。

２. ExecuteExcel4Macro

Sub tenki2()

Dim dpath As String

dpath = ThisWorkbook.Worksheets(1).Range("B2").value

ThisWorkbook.Worksheets(1).Range("B6").value = ExecuteExcel4Macro("'" & dpath & Application.PathSeparator & "[FormeCollector2.xlsm]Sheet1'!R1C1")

End Sub

VBAと呼ばれる前から存在するマクロコードらしいです。後述しますが、紹介されていたサイトでは「多分誰も使っていない。」と評されていました。ブックを開かずに参照できます。

３. セルに直接入力

Sub tenki3()

Dim dpath As String

dpath = ThisWorkbook.Worksheets(1).Range("B2").value

With ThisWorkbook.Worksheets(1).Range("B7")
    .value = "='" & dpath & Application.PathSeparator & "[FormeCollector2.xlsm]Sheet1'!A1"
    .value = .value
End With

End Sub

これは目から鱗が落ちました。セルに参照式を直接入力し、値に変更しています。ひとつひとつは知っているのに組み合わせて使用するまでは考えが及んでいませんでした。お見事です。しかもファイルを開く必要がありません。

Windows版での挙動

当たり前ですが、問題なく動きます。

Mac版での挙動

全部動きました。ただし、１. Workbooks.Openだけは難がありました。

問題点

まず、ほぼ当たり前になってきていますが初めてアクセスするファイルにはアクセス権の付与作業が必要です。また xlsm ファイルを開く際はマクロセキュリティレベルによって、マクロ許可ダイアログが表示されコードがストップします。Windows版ではマクロ許可バナーになっているので問題がないようです。

参考サイト

２. ExecuteExcel4Macroと３. セルに直接入力は以下のサイトを参考にさせていただきました。

http://officetanaka.net/excel/vba/tips/tips28.htm

ひとつのことを知っているからと言って胡座を書いてはいけないと今回の調査でよくわかりました。転記作業は３. セルに直接入力を使っていこうと思っています。