Прикручивание Solver'а в VBA

Последнее сообщение
Михаил Н. 135 11
Ноя 15

Всем привет!

Пробовал ли кто-либо прикрутить встроенный экселевский поиск решения (Solver) в VBA без указания адресов ячеек?

Поясню: по умолчанию солвер записывается так:

    SolverOk SetCell:="$B$53", MaxMinVal:=3, ValueOf:=0, ByChange:=  "$B$29,$B$36,$B$12", Engine:=1, EngineDesc:="GRG Nonlinear"

Требуется, чтобы макрос обращался к переменной, а не к ячейке с записанными датами. 

Возможно, кто-нибудь сможет предложить иной простой минимизатор взамен встроенного экселевского?

Гоша 1197 14
Ноя 15 #1

А что мешает записать значение переменной в ячейку, и обращаться все таки к ячейке?

Если переменная  - вектор, можно поискать употребление именованных диапазонов в макросах и, в частности, солвере - что-то вроде "excel solver named ranges"... Стек-оверфлоу что-то не очень качественное выдает (http://stackoverflow.com/questions/30417923/excel-solver-named-ranges-in-vba)

voron4m 380 12
Ноя 15 #2

Google -> excel vba solver macro
Первые пять ссылок дают развернутый ответ.

Михаил Н. 135 11
Ноя 15 #3

voron4m пишет:
Google -> excel vba solver macro Первые пять ссылок дают развернутый ответ.

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

VIT 1124 14
Ноя 15 #4

Думаю можно только через именованные ячейки. Для VBA они в каком-то смысле выступают переменными так что зачем изобретать велосипед (не понимаю в чем для вас проблема подхода через ячейки). Второй вариант если solver имеет паблик API через библиотеки dll/xll по типу Crystall Ball где можно вызовы делать напрямую (скорей всего такая опция есть даже если она не документирована).

*Сами подумайте как solver будет знать какие и где переменые вы объявили в своем макросе который к тому же скорее всего зацеплен на одну workbook, а solver внешний ко всем workbook-ам. Т.е. чтобы солверу напрямую обращаться к вашим VBA переменным это надо очень много jumps through hoops ему сделать.

Михаил Н. 135 11
Ноя 15 #5

VIT пишет:

не понимаю в чем для вас проблема подхода через ячейки

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

 

VIT пишет:

*Сами подумайте как solver будет знать какие и где переменые вы объявили в своем макросе который к тому же скорее всего зацеплен на одну workbook, а solver внешний ко всем workbook-ам. Т.е. чтобы солверу напрямую обращаться к вашим VBA переменным это надо очень много jumps through hoops ему сделать.

С этим согласен. Вижу выход только в отказе от встроенного солвера.

 

Гоша 1197 14
Ноя 15 #6

Михаил Н. пишет:

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

Тогда, может быть, решать задачу функциональным программированием - средства R или C# вам в помощь. Хотя даже и в простом C можно указатель на функцию использовать в аргументах.

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

 

VIT 1124 14
Ноя 15 #7

Михаил Н. пишет:

VIT пишет:

не понимаю в чем для вас проблема подхода через ячейки

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

Все равно не понимаю в чем проблема. Допустим у вас имеется функция y=f(x) которая может быть y=z(x) и т.д. Сделайте функцию wraper и поставте ее в ячейку '=mywrapfunction(input_cell)'. Эта mywrapfunction будет прописана в VBA и может ссылаться на что угодно, например:

function mywrapfunction(v as double) as double

result=f(v) or result=z(v) и т.д.

end function

Den_Z 16 7
Ноя 15 #8

Как кто-то выше уже упомянул, эта проблема решается использованием ячейки в качестве переменной. В конце концов, переменная – это просто контейнер определенного типа. Вы так же можете под нее выделить определенную ячейку и, вместо того чтобы присваивать значение переменной в коде, присваивать значение ячейке. Если у вас, скажем, 30 шагов по времени и на каждом шаге вам независимо нужно оптемизировать 10 коэффициентов (оптимизация дебитов скважин, к примеру), то вы можете выделить 10 ячеек для солвера + 1 ячейку для оптимизируемого значения и в коде связывать эти ячейки с ячейками нужной строки таблицы, которая оптимизируется. В данном случае солвер будет тупо менять значения в этих ячейках, а они будут отражаться в таблице и отрабатываться  как нужно (лукапы, формулы, и т.д.). Закончили с одной строкой, перекинули ссылки и заново повторили.

kealon 120 12
Ноя 15 #9

Михаил Н. в эксель можно дописать любую свою функцию, погуглите

http://www.firststeps.ru/vba/excel/r.php?72

в новых версиях нужно сохранить книгу в специальном формате (расширении), посмотрите при сохранении что то вроде сохранить с поддержкой функций

Eugene 554 14
Фев 16 #10

Коллеги, хочу вернуться к вопросу прикручивания солвера.

Необходимо вызвать процедуру в процессе решения Solver, чтобы значение в оптимизируемой ячейке посчитать.

При вызове Solver в VBA есть в опциях SolverOptions ключ StepThru. Он позволяет в итоге вызывать макрос для обработки событий, где вроде как можно прописать свою функцию.

В инете ищутся некоторые варианты решений, но не все у меня работает как надо. В 2010 экселе, Solver останавливается (по требованию пользователя), что вроде пишут как баг этой версии.

Есть у кого опыт решения подобной задачи?

MaksimN 22 7
Фев 16 #11

Михвил, добрый день!

 

На сколько я понимаю, вы пытаетесь сделать адрес ячеек (на которые ссылается Solver) динамическим. Ваша проблема решается следующим образом,

a,c-строки, b,d-колонки

SolverOk SetCell:=Cells(a,b), MaxMinVal:=3, ValueOf:=0, ByChange:= Cells(c,d), Engine:=1, EngineDesc:="GRG Nonlinear"

Go to top