【VBA】他ブックを参照している関数式を更新(再計算)する方法

仕事で他ブックのデータを参照しているセルの式を更新(再計算)させようとしたら、うまくいかなくて嵌りました；；。

再計算処理でよく使われる「Calculate」や「CalculateFull」を使用しても更新することができませんでした。

上記は関数式を再計算する関数なので、「なんで計算されないんだよ！」と長時間考えてしまいました。

一応解決することができたので、その方法について記載をしてみます。

他ブックを参照している関数式とは

解消方法を記載する前に、今回の問題がどのようなことを指しているかを記載します。

下記のように、マスタのようなシートに商品の値段を管理していたとします。

そして、そのマスタの値段を他のブックから関数式で参照したとします。

=VLOOKUP(A2,[マスタ.xlsx]Sheet1!$A:$B,2,FALSE)

この関数式は、参照元のブックを開いていない限り更新(再計算)されないという特徴があるので、ユーザーが参照元のブックを開いていないと値が古い可能性があります。

そこで、今回ブックを再計算してあげるマクロを組んでブックオープン時などに最新の情報にしようとしました。

↓試してダメだったソース（単にcalculateするだけ）

Private Sub Workbook_Open()
    Dim sh As Worksheet
    
    Set sh = ThisWorkbook.Worksheets("Sheet1")
    sh.Calculate
End Sub

しかし全く最新の情報に更新されませんでした。

解決する方法

以下の2つの方法で、「VBAで他ブックを参照している関数式を更新する」を実現することができました。

• calculateを実行する前に、参照元のブックを開く
• 関数式を再代入する

それぞれの方法を詳細に記載します。

calculateを実行する前に、参照元のブックを開く

参照元になるExcelブックを開いた状態であれば、caluculateで関数式の結果が正常に更新されたので、予め開いてあげればよいです。

参照元になるブックが「C:\マスタ.xlsx」の場合

Sub test()
    Dim sh As Worksheet
    Dim openFlg As Boolean
    Dim wb As Workbook
    
    
    '参照元ブックが開いているか確認する
    openFlg = isOpenWorkBook("C:\Users\namek\Desktop\test\マスタ.xlsx")
    
    '参照元ブックが開かれていなければ開く
    If Not openFlg Then
        Set wb = Workbooks.Open("C:\Users\namek\Desktop\test\マスタ.xlsx")
    End If
    
    'シートの計算を実行
    Set sh = ThisWorkbook.Worksheets("Sheet1")
    sh.Calculate
    
    '参照元ブックを閉じる
    If Not openFlg Then
        wb.Close
    End If
End Sub

Private Function isOpenWorkBook(path As String) As Boolean
    On Error Resume Next
    'ブックを追記モードで開く
    Open path For Append As #1
    Close #1
    If Err.Number > 0 Then
        'エラーがあれば開かれている
        isOpenWorkBook = True
    Else
        'エラーがなければ開かれていない
        isOpenWorkBook = False
    End If
End Function

上記のような感じで、まず処理のはじめに参照されているブックを開いてあげれば計算をすることができます。

この方法の欠点は、予め参照元のブックが何であるかをVBAで定義しなければいけないことですかね。

どのブックが参照されているのかをExcelから動的に取ることも可能だとは思いますが、かなり大掛かりになりそうです。

セルをループさせながらFormulaの値を見て判断する形で実装することになるかなと思います。

関数式を再代入する

参照元のブックが開いていない状態でも、セルに新しく式を代入すると強制的に再計算されるます。

その動きを利用して、現在設定されている式を再設定することで値が最新になります。

Sub test()
    Dim sh As Worksheet
    Dim i As Integer
    
    Set sh = ThisWorkbook.Worksheets("Sheet1")
    
    For i = 1 To sh.Cells(Rows.Count, 1).End(xlUp).Row
        If sh.Cells(i, 2).HasFormula Then
            'セルに値を入れ直して強制的に再計算させる
            sh.Cells(i, 2).Formula = sh.Cells(i, 2).Formula
        End If
    Next
End Sub

この方法ですと、参照元のブック名をVBA側に持たなくてもいいというメリットがあります。

一方で、どのセルが他ブックを参照しているかを予め持たせて置かなければいけません。

もちろん全セルをチェックして更新処理をしてもいいのですが、巨大な表などがある場合は、処理時間が酷いことになりそうです。

そもそも他ブックの値を参照しないほうがいい

そもそも論ですがExcel関数式で他のブックの値は参照しないほうがいいですね。

他人にそのExcelを渡した時に、全く同じ階層に参照元のブックを置かないとエラーになってしまいます。

また、参照元になっているブックのファイル名やシート名を変更してもエラーになってしまうので、極めて使い勝手が良くないです。

他のブックの値を参照したいときは、関数式はやめてVBAで取得してくる方法がベターだと思います。