Если уже начали создавать надстройки не только для себя, то наверняка сталкивались с такой неприятностью, что если в каком-то файле используется пользовательская функция(UDF) из этой надстройки, то при пересылке такого файла другому человеку, функция не работает должным образом, а файл ругается на наличие связей. При этом если найти ячейку с любой UDF из надстройки, то она будет записана в ячейку вместе с полным путем к надстройке не на текущем компьютере, а на том, в котором файл с UDF был сохранен в последний раз(с которого был прислан файл):
Иными словами - ссылки на надстройку "слетают". Хотя правильнее наоборот - не обновляются.
В таких ситуациях не очень правильно просить пользователей проделывать различные действия вроде: скопировать неправильный путь к UDF -выделить ячейки с UDF -нажать
Дело в том, что 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) сначала запускает все подключенные надстройки, в том числе и нашу. В момент открытия нашей надстройки в ней срабатывает событие
Так же прикладываю файл с кодом и тестовой UDF, чтобы можно было попробовать и поэкспериментировать. Файл сделан не как надстройка, а как обычный, чтобы проще было найти код.
Тестовый файл.xls (55,0 КиБ, 669 скачиваний)