Использование UDF в Excel для обновления рабочего листа

На самом деле это не вопрос, но сообщение об этом для комментариев, потому что я не помню, как раньше видел этот подход. Я отвечал на комментарий к предыдущему ответу и пытался что-то, чего я раньше не пытался: результат был интересным, поэтому я бы опубликовал его как отдельный вопрос, а также свой собственный ответ.

На SO (и на многих других форумах) было много вопросов по принципу «что не так с моей пользовательской функцией», где ответ был «вы не можете обновить листок из UDF» – это ограничение, описанное здесь :

Описание ограничений пользовательских функций в Excel

Существует несколько способов, которые были описаны для преодоления этого, например, см. Здесь ( https://sites.google.com/site/e90e50/excel-formula-to-change-the-value-of-another-cell ), но я не думаю, что мой точный подход среди них.

См. Также: изменение комментариев ячейки из UDF

Отправка ответа, чтобы я мог отметить свой «вопрос» как ответ.

Я видел другие обходные пути, но это кажется более простым, и я удивлен, что он работает вообще.

Sub ChangeIt(c1 As Range, c2 As Range) c1.Value = c2.Value c1.Interior.Color = IIf(c1.Value > 10, vbRed, vbYellow) End Sub '######## run as a UDF, this actually changes the sheet ############## ' changing value in c2 updates c1... Function SetIt(src, dest) dest.Parent.Evaluate "Changeit(" & dest.Address(False, False) & "," _ & src.Address(False, False) & ")" SetIt = "Changed sheet!" 'or whatever return value is useful... End Function 

Пожалуйста, напишите дополнительные ответы, если у вас есть интересные приложения для этого, которые вы хотели бы поделиться.

Примечание. Неподтвержденное в каком-либо реальном приложении «производство».

Ошибка MSDN KB .

В нем говорится:

Пользовательская функция, вызываемая формулой в ячейке рабочего листа, не может изменить среду Microsoft Excel. Это означает, что такая функция не может выполнять одно из следующих действий:

  1. Вставлять, удалять или форматировать ячейки в электронной таблице.
  2. Измените значение другой ячейки .
  3. Перемещение, переименование, удаление или добавление листов в книгу.
  4. Измените любые параметры среды, такие как режим расчета или виды экрана.
  5. Добавить имена в книгу .
  6. Установите свойства или выполните большинство методов.

В приведенном ниже коде вы можете легко увидеть точки 1, 2,4 и 5.

 Function SetIt(RefCell) RefCell.Parent.Evaluate "SetColor(" & RefCell.Address(False, False) & ")" RefCell.Parent.Evaluate "SetValue(" & RefCell.Address(False, False) & ")" RefCell.Parent.Evaluate "AddName(" & RefCell.Address(False, False) & ")" MsgBox Application.EnableEvents RefCell.Parent.Evaluate "ChangeEvents(" & RefCell.Address(False, False) & ")" MsgBox Application.EnableEvents SetIt = "" End Function '~~> Format cells on the spreadsheet. Sub SetColor(RefCell As Range) RefCell.Interior.ColorIndex = 3 '<~~ Change color to red End Sub '~~> Change another cell's value. Sub SetValue(RefCell As Range) RefCell.Offset(, 1).Value = "Sid" End Sub '~~> Add names to a workbook. Sub AddName(RefCell As Range) RefCell.Name = "Sid" End Sub '~~> Change events Sub ChangeEvents(RefCell As Range) Application.EnableEvents = False End Sub 

! [введите описание изображения здесь

  • Как вводить только уникальные значения в столбце Excel 2007
  • Проверка флажка на основе листа.
  • Excel VBA для ответа на приглашение загрузки Internet Explorer 11 в Windows 10?
  • Считать уникальные значения в Excel
  • Как скопировать строку в excel с использованием определенного слова и вставить в другой лист Excel?
  • Управление IE11 «Вы хотите открыть / сохранить» диалоговые windows в VBA
  • Excel vba: копировать строки, если данные соответствуют значениям в столбце на другом листе
  • Использование шаблона для открытия книги Excel
  • Как скопировать письмо Outlook Outlook в Excel с помощью VBA или макросов
  • Могу ли я запустить этот макрос быстрее?
  • Продолжить цикл
  • Interesting Posts

    Каково значение ключа / admin в файле mstsc.exe?

    Как сохранить жесткое вращение / предотвратить spindown?

    Подробное сообщение об ошибке 500, ASP + IIS 7.5

    Как конвертировать «Mon Jun 18 00:00:00 IST 2012» до 18/06/2012?

    Как работает оператор побитового дополнения (~ тильда)?

    Как испускать и выполнять байт-код Java во время выполнения?

    Есть ли команда в Linux знать номер процессора, в котором загружается процесс?

    Автоматически переименовывать файл, если он уже существует в Windows

    Ошибка проверки данных, скрытая от окон замораживания?

    Как установить стиль пули по умолчанию в Microsoft Word 2010?

    Как подключиться к беспроводной сети, используя только командную строку в Linux?

    Singleton через экземпляр JVM или приложения или экземпляр Tomcat

    Как я могу получить значок из исполняемого файла только с экземпляром его процесса в C #

    Как TransactionScope откатывает транзакции?

    Ошибка регистрации зарегистрированного получателя?

    Давайте будем гением компьютера.