Хитрости »
Основные понятия (23)
Сводные таблицы и анализ данных (9)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (14)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (38)
Баги и глюки Excel (2)

Сбивается путь к UDF из надстройки

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

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

как это исправить
А исправить это можно при помощи не такого уж сложного кода. Правда, код должен быть прописан особым образом, т.к. должен срабатывать при открытии любой книги, чтобы изменить в ней ссылки. Этого можно добиться, если создать специальное подключение к объекту 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 KiB, 56 скачиваний)


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Outlook Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Надстройки Настройка Печать Поиск данных Политика Конфиденциальности Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx анализ данных баги и глюки в Excel ссылки
Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2018 Excel для всех   Войти