Если уже начали создавать надстройки не только для себя, то наверняка сталкивались с такой неприятностью, что если в каком-то файле используется пользовательская функция(UDF) из этой надстройки, то при пересылке такого файла другому человеку, функция не работает должным образом, а файл ругается на наличие связей. При этом если найти ячейку с любой UDF из надстройки, то она будет записана в ячейку вместе с полным путем к надстройке не на текущем компьютере, а на том, в котором файл с UDF был сохранен в последний раз(с которого был прислан файл):
Ссылка на UDF другого ПК
Иными словами - ссылки на надстройку "слетают". Хотя правильнее наоборот - не обновляются.
В таких ситуациях не очень правильно просить пользователей проделывать различные действия вроде: скопировать неправильный путь к UDF -выделить ячейки с UDF -нажать Ctrl+H и заменить скопированный путь на пусто. Правильнее делать так, чтобы пользователь вообще не узнал о подобных нюансах работы надстроек.

почему это происходит
Дело в том, что UDF является частью книги-надстройки и Excel автоматом связывает эту книгу-надстройку(в которой непосредственно код UDF) с книгой, в которой применяется такая UDF. Делается для того, чтобы можно было безошибочно отнести UDF именно к нужной книге. Все было бы отлично, но когда мы закрываем файл, в ячейках которого записана UDF, в нем сохраняется информация об абсолютном пути к файлу с самой UDF, т.к. относительные пути Excel сохранять не умеет в качестве связей. И когда впоследствии файл открывается на другом ПК, пусть даже на нем есть такая же надстройка - Excel извлекает путь к надстройке(с кодом UDF) на основании информации при последнем сохранении. И даже не пытается определить наличие надстройки на текущем ПК.

как это исправить
А исправить это можно при помощи не такого уж сложного кода. Правда, код должен быть прописан особым образом, т.к. должен срабатывать при открытии любой книги, чтобы изменить в ней ссылки. Этого можно добиться, если создать специальное подключение к объекту Application. Подробно об этом я писал в этой статье: Как отследить событие(например выделение ячеек) в любой книге?.
Ниже приведен весь код для модуля ЭтаКнига(ThisWorkbook), который сделает "правильное обновление" ссылок:

'переменная с возможностью отслеживать события текущего Excel
Private WithEvents App As Application
'подключаемся к текущему экземпляру Excel
Private Sub Workbook_Open()
    Set App = Application
End Sub
'надстройка закрывается - очищаем подлючение к Excel
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Set App = Nothing
End Sub
'обрабатываем событие открытия любой книги
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    'этот код "обновляет" все связи открываемой книги
    '   на книгу, в которой записан код
    Dim sMulTEx As String, sLnk, wsSh As Worksheet
    sMulTEx = UCase(ThisWorkbook.Name)
    On Error GoTo exit_
    With Wb
        'отбираем только связи на книги Excel
        For Each sLnk In .LinkSources(Type:=xlExcelLinks)
            'если связь ссылается на на нашу книгу
            If UCase(sLnk) Like "*" & sMulTEx Then
                'но связь к книге по другому пути
                If UCase(sLnk) <> sMulTEx Then
                    'меняем связь на текущую книгу(надстройку)
                    .ChangeLink Name:=sLnk, NewName:=sMulTEx
                    'цикл по всем листам открытой книги
                    For Each wsSh In .Worksheets
                        'пересчитываем лист, чтобы функции пересчитались
                        wsSh.Calculate
                    Next
                End If
                Exit For
            End If
        Next
    End With
exit_:
End Sub

Теперь, если у пользователя подключена надстройка с таким кодом, то функции из этой надстройки будут работать правильно в любом файле, в котором эти функции используются.
Как это работает
Как уже упоминалось, код выше прописывается в модуль ЭтаКнига(ThisWorkbook) нашей надстройки(именно надстройки, а не файла, в котором UDF не работает!). Когда мы открываем Excel, он(excel) сначала запускает все подключенные надстройки, в том числе и нашу. В момент открытия нашей надстройки в ней срабатывает событие Private Sub Workbook_Open, в котором мы "подключаемся" к нашему Excel(Set App = Application). После этого у нас появляется возможность отслеживать некоторые события, которые происходят в Excel. Нам важно отследить событие открытия любой книги, для чего применяем Private Sub App_WorkbookOpen. И внутри прописываем код, которой будет искать в открываемой книге все "левые" ссылки на нашу надстройку и исправлять их так, чтобы они ссылались именно на надстройку на том ПК, на котором файл открыт.

Так же прикладываю файл с кодом и тестовой UDF, чтобы можно было попробовать и поэкспериментировать. Файл сделан не как надстройка, а как обычный, чтобы проще было найти код.
Скачать файл:

  Тестовый файл.xls (55,0 КиБ, 669 скачиваний)

Loading

Добавить комментарий

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.