VBA не добавляет элементы в модель Солвера
Ранее у меня была проблема с решателем, не добавляющим бинарные ограничения в модель через VBA... Я (в основном) понял это. Но теперь у меня новая проблема. Во-первых, позвольте мне опубликовать соответствующий раздел обидного кода. Я должен отметить, что это работает в Excel 2007.
'build string of ByChange cells and set up cascading constraints
by_change_string = ""
For i = 1 To j - 1
If Len(by_change_string) > 0 Then 'there are already some elements in the string, so we might start with a comma
If Not (Right(by_change_string, 1) = ",") Then 'make sure the last character isn't already a comma
by_change_string = by_change_string & ","
End If
End If
current_status = Sheets("Buyback Risk Area").Range("C1").Offset(i).Value
Select Case current_status
Case "Y" 'risk area is currently yellow, so green transition is available
by_change_string = by_change_string & "$E$" & i + 1
solverok setcell:="$B$" & j + 4, MaxMinVal:=2, ValueOf:="0", ByChange:=by_change_string
'add binary constraints
solveradd cellref:="$E$" & i + 1, relation:=5, formulatext:="binary"
Case "O" 'risk area is currently orange, so green and yellow transitions are available
by_change_string = by_change_string & "$E$" & i + 1 & ",$G$" & i + 1
solverok setcell:="$B$" & j + 4, MaxMinVal:=2, ValueOf:="0", ByChange:=by_change_string
'add cascading constraints
solveradd cellref:="$E$" & i + 1, relation:=1, formulatext:="$G$" & i + 1 'says E <= G, which means you can't select green unless you've already selected yellow
'add binary constraints
solveradd cellref:="$E$" & i + 1, relation:=5, formulatext:="binary"' solveradd cellref:="$G$" & i + 1, relation:=5, formulatext:="binary"
Case "R" 'risk area is currently red, so green, yellow, and orange transitions are available
by_change_string = by_change_string & "$E$" & i + 1 & ",$G$" & i + 1 & ",$I$" & i + 1
'solverok setcell:="$B$" & j + 4, MaxMinVal:=2, ValueOf:="0", ByChange:=by_change_string
'add cascading constraints
solveradd cellref:="$E$" & i + 1, relation:=1, formulatext:="$G$" & i + 1 'says E <= G, which means you can't select green unless you've already selected yellow
solveradd cellref:="$G$" & i + 1, relation:=1, formulatext:="$I$" & i + 1 'says G <= I, which means you can't select yellow unless you've already selected orange
'add binary constraints
solveradd cellref:="$E$" & i + 1, relation:=5, formulatext:="binary"
solveradd cellref:="$G$" & i + 1, relation:=5, formulatext:="binary"
solveradd cellref:="$I$" & i + 1, relation:=5, formulatext:="binary"
Case "B" 'risk area is black, so green, yellow, orange and red transitions are avaailable
by_change_string = by_change_string & "$E$" & i + 1 & ",$G$" & i + 1 & ",$I$" & i + 1 & ",$K$" & i + 1
solverok setcell:="$B$" & j + 4, MaxMinVal:=2, ValueOf:="0", ByChange:=by_change_string
'add cascading constraints
solveradd cellref:="$E$" & i + 1, relation:=1, formulatext:="$G$" & i + 1 'says E <= G, which means you can't select green unless you've already selected yellow
solveradd cellref:="$G$" & i + 1, relation:=1, formulatext:="$I$" & i + 1 'says G <= I, which means you can't select yellow unless you've already selected orange
solveradd cellref:="$I$" & i + 1, relation:=1, formulatext:="$K$" & i + 1 'says K <= M, which means you can't select orange unless you've already selected red
'add binary constraints
solveradd cellref:="$E$" & i + 1, relation:=5, formulatext:="binary"
solveradd cellref:="$G$" & i + 1, relation:=5, formulatext:="binary"
solveradd cellref:="$I$" & i + 1, relation:=5, formulatext:="binary"
solveradd cellref:="$K$" & i + 1, relation:=5, formulatext:="binary"
End Select
Next i
'buyback amount constraint
solveradd cellref:="$O$" & j + 1, relation:=1, formulatext:="$B$" & j + 2
'set target cell
solverok setcell:="$B$" & j + 4, MaxMinVal:=2, ValueOf:="0", ByChange:=by_change_string
'set binary constraints
'binary_array = Split(by_change_string, ",")
'For i = 0 To UBound(binary_array)
' solveradd cellref:=Range(binary_array(i)), relation:=5, formulatext:="binary"
'Next i
Application.ScreenUpdating = True
SolverSolve userFinish:=False
Основная идея здесь состоит в том, чтобы пройтись по листу и посмотреть на данные (которые будут меняться от исполнения к выполнению) и построить соответствующую модель.
Строка by_change_string содержит адреса ячеек всех переменных, которые являются двоичными. Поэтому, когда код проверяет каждую строку таблицы, он определяет, какие ячейки в этой строке доступны для рассмотрения моделью, и добавляет их в строку.
Многократные вызовы solverok с растущей строкой by_change_string являются попыткой решить более раннюю проблему, когда двоичные ограничения не были добавлены в модель. Все ограничения <= были, но не двоичные. Кажется, вы не можете ограничить переменную быть бинарной, пока она не будет добавлена в модель. Раньше после завершения цикла, который конструировал by_change_string, был только один оператор solverok, но когда я сделал это таким образом, я получил все ограничения <= и ни одного из двоичных.
Когда этот код выполняется на моем тестовом листе, полученная модель должна иметь 136 переменных решения (путем изменения ячеек). Я проверил, и by_change_string действительно имеет 136 адресов. Но когда я смотрю в диалоговом окне решателя, есть только первые 41. Я прошел весь процесс в режиме отладки и видел, что он вызывает resolrok после каждой итерации, но по какой-то причине там только первые 41 ячейки изменений., И нет ничего особенного в строке данных, где что-то идет не так... это не похоже на то, что это первый экземпляр одного из выбранных случаев или чего-то в этом роде...
Когда решатель работает на этой сокращенной модели, он возвращает решение для мусора, которое, по его словам, является оптимальным. По сути, он сохраняет все то же самое, так что конечное значение целевой функции совпадает с начальным.
Тогда я попробовал что-то еще... вы заметите закомментированный блок кода внизу:
'set binary constraints
'binary_array = Split(by_change_string, ",")
'For i = 0 To UBound(binary_array)
' solveradd cellref:=Range(binary_array(i)), relation:=5, formulatext:="binary"
'Next i
Идея здесь состояла в том, чтобы сделать вещи намного более эффективными, извлекая все эти решающие задачи и решающие задачи из избранных случаев. Дождавшись окончания после последнего оператора solverok, все переменные теперь находятся в модели, поэтому вы должны просто пройти по by_change_string и сделать каждую из них двоичной переменной. Таким образом, вам нужен только один оператор решения проблемы, и вы можете избавиться от всех solveradd в выбранных случаях. Таким образом, я закомментировал все эти решетки и solveradd в выбранных случаях и снова запустил макрос. Я не получаю вывод от решателя. Когда я смотрю на диалоговое окно решателя после запуска, поля как целевой ячейки, так и поля путем изменения ячеек остаются пустыми. Это как если бы это последнее утверждение решателя никогда не выполнялось.
Итак, я попытался раскомментировать все решающие задачи, но оставил зашифрованные значения для двоичных переменных закомментированными. Я возвращаюсь к получению только первых 41 переменных решения. Цикл для установки бинарных ограничений сделал свое дело, но solveradd не получает переменные в модель.
Я в значительной степени озадачен в этом пункте. Есть идеи?
3 ответа
Как я и предполагал:
Solver допускает использование только 255 символов в параметре By Changing Cells. Если вы мне не верите, попробуйте добавить нужные ячейки вручную. Вы не сможете сделать это.
Максимальное число несмежных диапазонов, которые вы можете передать решателю, составляет 51 (при условии, что каждая группа имеет только 1 ячейку с таким форматом, как $A$1, с 1 буквой 1 цифра 2 знака доллара и 1 запятая). Если вы попытаетесь добавить больше вручную, он удалит все предыдущие варианты. Предположительно, делая это с кодом, вы просто игнорируете все, как только вы встретили ограничение длины.
Честно говоря, я никогда не использовал решатель и понятия не имел, как получить к нему доступ через код. Независимо от этого, вы пытаетесь заставить его делать то, на что он не способен.
Теперь вместо того, чтобы просто сказать вам, что это не сработает, здесь возможен (но, на мой взгляд, неприятный) обходной путь. Это то, что я бы попробовал, если бы мне пришлось делать то, что ты делаешь. Скопируйте значения, которые имеют значение, в другое место в вашей рабочей книге и установите их рядом друг с другом, чтобы вы могли передавать их как непрерывный диапазон. Как только решатель запустится, верните свои значения туда, где они должны быть расположены.
Редактировать: Эта ограниченная документация для Солвера может быть немного устаревшей, но в ней говорится следующее:
Переменные решения модели вводятся в поле редактирования By Changing Cells. Excel позволяет вводить так называемый множественный выбор, который состоит из 16 диапазонов (прямоугольников, строк или столбцов или отдельных ячеек), разделенных запятыми.
Это означает, что вы можете иметь только 16 несмежных диапазонов для Солвера и ожидать, что он сработает.
Даниэль Кук, кажется, прав насчет причины проблемы. Я бы предложил другой обходной путь, хотя. Создайте именованный диапазон, который включает все несмежные диапазоны, и установите by_change_string в строку, представляющую этот именованный диапазон.
Я хотел бы добавить к этим ответам:
- Переход к максимальному значению с помощью кода оставляет пустую область Bychange в модели.
- Наименование диапазона не решит проблему, так как Солвер немедленно переведет его на свой обычный язык и удалит последние диапазоны, которые не помещаются в окне.
Источник: я только что попробовал эти решения с Excel 2013