EXCEl:ユーザー定義関数をLANで共有する方法

質問

LANに接続しているユーザーでVBAを共有する仕組みを以下のように構築しています。

== サーバー環境 ==
VBAはアドイン（Addin.xla）としてサーバー（\\SvA）に保存。
サーバーがもう１つあり（\\SvB）、Addin.xlaは、\\SvAから \\SvBに定期的にコピー。
\\SvAと \\SvBは、物理的に距離が離れているため、１つにできません。

== クライアント環境 ==
クライアントPCの XLSTARTフォルダには、Addin.xlaのショートカットを置く。
\\SvAのユーザーであれば、\\SvA\Addin.xlaへのショートカット、
\\SvBのユーザーであれば、\\SvB\Addin.xlaへのショートカットです。

Addin.xlaに変更があった場合に \\SvA\Addin.xlaを更新するだけなので、この方法を採用し、うまく運用できているのですが、ユーザー定義関数をワークシートで使用したいということになって、ハマってしまいました。

\\SvAのユーザーがユーザー定義関数 User1()をワークシートに =User1()と書き込んで他のユーザーにブックを配布しました。
\\SvAのユーザー間では問題なくブックを使用できますが、\\SvBのユーザーがそのブックを開くと
「このブックには、ほかのデータソースへのリンクが含まれています。」というメッセージが表示されます。
ここで、更新を選択すると、
「このブックには更新できないリンクが１つ以上含まれています。」というメッセージが表示されます。
ここで、継続を選択すると、
ユーザー定義関数が入力されたセルは #NAME? エラーが表示されます。\\SvBのユーザーのワークシートでは、ユーザー定義関数が ='\\SvA\Addin.xla'!User1()となっています。

この問題を解決する方法がないでしょうか。よろしくお願いいたします。

回答No.1

end-u · ベストアンサー

ひとつの方法としては、[ネットワークドライブの割り当て]を使って
同じドライブ文字を使うようにする事で対処できるかもしれません。
例）
\\SvA\folder\Addin.xla
\\SvB\folder\Addin.xla
という配置だとして、
SvAユーザーは\\SvA\folder をドライブ J: に割り当て
SvBユーザーは\\SvB\folder をドライブ J: に割り当て

またはAddinファイルに、強制的にChangeLinkさせるようにするコードを仕込むとか。
'ThisWorkbook Module
Option Explicit

Private WithEvents xApp As Application

Private Sub Workbook_BeforeClose(Cancel As Boolean)
　　Set xApp = Nothing
End Sub

Private Sub Workbook_Open()
　　Set xApp = Application
End Sub

Private Sub xApp_WorkbookOpen(ByVal Wb As Workbook)
　　Const svA = "\\SvA\folder\Addin.xla"
　　Const svB = "\\SvB\folder\Addin.xla"
　　Dim oldN As String
　　Dim newN As String
　　Dim vLinks, v

　　With Wb
　　　　vLinks = .LinkSources(Type:=xlLinkTypeExcelLinks)
　　　　If Not IsEmpty(vLinks) Then
　　　　　　newN = ThisWorkbook.FullName
　　　　　　If newN = svA Then
　　　　　　　　oldN = svB
　　　　　　Else
　　　　　　　　oldN = svA
　　　　　　End If
　　　　　　For Each v In vLinks
　　　　　　　　If v = oldN Then
　　　　　　　　　　.ChangeLink Name:=oldN, NewName:=newN, Type:=xlExcelLinks
　　　　　　　　End If
　　　　　　Next
　　　　End If
　　End With
End Sub

ただ、この方法の欠点としては、強制的にChangeLinkさせてますから、
[更新しない]を選択しても更新されてしまう事になります。（多分

お礼

回答ありがとうございました。
ChangeLinkを使う方法は、運用が難しいようです。
結局、個々のＰＣのCドライブに同じ名前のアドイン保存用のフォルダを作成し、スタートアップ時にサーバーからこのフォルダに自動でコピーする方法を採用しました。