Повторное использование кода Spreadhseet

Кто-нибудь знает способ обернуть рабочий лист как функцию UDF?

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

В ответе на этот вопрос есть хак, но он плохо работает.

Использование пользовательской функции в Excel для обновления рабочего листа

В идеале я хотел бы сделать это в Excel, но я принимаю предложения альтернативного программного обеспечения для работы с электронными таблицами, сторонних инструментов Excel или полностью альтернативных платформ.


excel spreadsheet udf
person Tim Galvin    schedule 24.12.2015    source источник
comment
Возможный дубликат Использование UDF в Excel для обновления рабочего листа   -  person Mark Fitzgerald    schedule 24.12.2015
comment
Это не дубликат, поскольку вопрос более открыт, чем тот, на который ссылается.   -  person Tim Galvin    schedule 24.12.2015
comment
Это не ясно. Что означает обернуть рабочий лист как функцию UDF? VBA — полноценный язык программирования. Макросы и UDF, которые вы пишете в одном, могут быть повторно использованы в других, возможно, заключены в Add-ins. Возможно, вы хотите написать надстройку?   -  person John Coleman    schedule 24.12.2015
comment
Это может быть надстройка, но и надстройка, функция которой определяется набором ячеек электронной таблицы.   -  person Tim Galvin    schedule 24.12.2015
comment
Что означает для UDF установка значения определенных входных ячеек? Если UDF устанавливает значение (что на самом деле невозможно с UDF без странных хаков - почему бы не использовать подпрограмму?), то не будут ли это выходные ячейки? Вам нужно объяснить, что вы на самом деле пытаетесь сделать.   -  person John Coleman    schedule 24.12.2015
comment
UDF будет действовать как интерфейс для электронной таблицы A, которая реализует необходимую логику вычислений. Электронная таблица B может затем повторно использовать эти вычисления, вызвав UDF. UDF изменит входные ячейки в электронной таблице B и вернет значение из некоторой выходной ячейки в электронной таблице B в электронную таблицу A.   -  person Tim Galvin    schedule 24.12.2015

Ответы (3)


arrow_upward
1
arrow_downward

Пользовательские функции не предназначены для изменения значения какой-либо ячейки, кроме той, в которой они используются.

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

Если вы хотите изменить более одной ячейки за раз, лучше всего написать Sub. Это дает вам больше контроля, поведение хорошо задокументировано, и в целом ваши расчеты не зависят от неофициальных обходных путей, которые могут сломаться или не сломаться в том или ином патче.

person Vegard    schedule 24.12.2015
comment
Хороший ответ. Возможно, добавьте, что распространенным методом является связывание подпрограмм с событием worksheet change, чтобы имитировать способность UDF мгновенно обновляться на основе изменения значений ячеек. - person John Coleman; 24.12.2015
comment
Я обсуждал это сам с собой, но в конечном итоге удалил эту ссылку, потому что чем больше я думал об этом, тем больше способов взломать ее приходило на ум, и этот список стал слишком большим, чтобы быть полезным для этого ответа. Я думал, что это выходит за рамки, если хотите. - person Vegard; 24.12.2015

arrow_upward
0
arrow_downward

Я нашел ответ на свой вопрос. Похоже, что пользовательские функции не могут изменять значения ячеек в экземпляре excel, из которого они вызываются. Желаемое поведение может быть достигнуто путем создания нового экземпляра Excel и открытия копии текущей книги во втором экземпляре. Затем первый экземпляр может вызвать UDF, который модифицирует второй экземпляр. Таким образом, вычисления в электронной таблице могут быть успешно реализованы в пользовательской функции.

Option Explicit
Public xl As Excel.Application
Public wb As Workbook
Public ws As Worksheet


Function calc(x As Double) As Double

If xl Is Nothing Then


    Set xl = CreateObject("Excel.Application")

    Set wb = xl.Workbooks.Open(ThisWorkbook.FullName)
    xl.Visible = False

    Set ws = wb.Worksheets("CalcluationModule")


End If

ws.Range("i").Value = x
wb.Application.Calculate

calc = ws.Range("PV").Value



End Function
person Tim Galvin    schedule 12.01.2016

arrow_upward
0
arrow_downward

Это правда ' типа... Однако ваша UDF может вызвать функцию, которая активирует таймер Win API, используя ссылку на ячейку, из которой вы ее вызвали. Затем функция обратного вызова может делать с этой ячейкой то, что вы хотите...

person PaulG    schedule 12.01.2016
comment
Я думаю, что основная проблема связана с расчетной зависимостью. Если вы создадите отдельный экземпляр, у вас меньше шансов попасть в бесконечный цикл. - person Tim Galvin; 12.01.2016