На этом шаге мы рассмотрим структуру этого блока.

На практике может сложиться ситуация, при которой названия НП введены в список НП на листе Города, а расстояние между ними не введено в список на лист База. В таком случае формулы в диапазоне ячеек I2:I8 возвращают значение ошибки #Н/Д.

Рис.1. Элементы управления для определения НП, между которыми указано расстояние

Для просмотра названий НП, с которыми введены расстояния на листе База, создайте еще один элемент управления Поле со списком, который раскрывает список по заданному диапазону ячеек В2:В200 на листе Города и помещает результат в ячейку R10 (порядковый номер выбранного НП в раскрывающемся списке). На рабочем листе Ввод этот элемент управления расположен в правом верхнем углу и ему назначен макрос ПоискСвязанныхГородов (рисунок 2).

Рис.2. Подпрограмма ПоискСвязанныхГородов

Эта подпрограмма формирует в столбце Р листа База список НП, с которыми у искомого (выбранного) НП введено расстояние между ними на листе База (рисунок 1).

Просмотр сформированного списка названий НП макросом ПоискСвязанныхГородов, осуществляется с помощью следующего элемента управления Список. Для этого после его создания, вызвав диалоговое окно Формат элемента управления, задайте ссылку на диапазон ячеек Р2:Р200 в поле Формировать список по диапазону вкладки Элемент Управления.

Создание подпрограммы для определения названий НП, с которыми указаны расстояния между ними

Алгоритм подпрограммы формирования списка названий НП, расстояние между которыми и искомым НП введено на рабочем листе База, довольно похож на подпрограмму поиска повторяющихся данных (рисунок 3).

Рис.3. Подпрограмма ПроверкаПовторения

Последовательность процедуры формирования списка следующая:

при обращении к элементу управления Поле со списком и нажатии на кнопку, раскрывается список, в котором представлены все названия НП, введенные в диапазон ячеек В2:В200 рабочего листа Города;
при выборе названия НП элемент управления помещает порядковый номер этого НП в ячейку R10;
далее производится ввод в ячейку Р1 формулы:
```
  =ВПР(R10;Города!$A$2:$B$200;2;ЛОЖЬ)
```
по которой происходит поиск названия НП на листе Города, выбранного в элементе управления Поле со списком по порядковому номеру, помещенному в ячейку R10.
осуществляется ввод в ячейку Р2 формулы:
```
  =ЕСЛИ(База!A2=$P$1;База!B2;"")
```
которая сравнивает в первом аргументе функции ЕСЛИ содержимое ячейки Р1 на листе Ввод с ячейкой А2 на листе База. Если названия НП совпадают, то возвращается значение, введенное в соседнюю ячейку справа этой строки (В2) на листе База - название НП, до которого введено расстояние. Ссылка в формуле на ячейку Р1 абсолютная, поэтому при копировании и вставке этой формулы в диапазон ячеек Р2:Р5000 ссылка на ячейку Р1 не изменяется, а изменяются только номера строк ссылок на ячейки листа База;
далее подпрограмма заменит формулы вычисленными значениями и произведет сортировку списка по убыванию.

Обратите внимание на код VBA строки сортировки. Последнему аргументу Header метода сортировки присвоено значение xlYes (первую строку выделенного диапазона не сортировать). За счет этого при сортировке диапазона выбранное название НП (в ячейке Р1) не перемещается.

Причина создания справочного блока

Необходимость создания справочного блока объяснит следующий пример. В списке НП на листе Города введены название городов Москва и Киев. В списке расстояний на листе База введено и расстояние между ними. Необходимо произвести расчет времени пути по маршруту Москва - Санкт-Петербург - Киев. Название НП Санкт-Петербург отсутствует в обоих списках.

При вводе названия НП - Москва в ячейку F2 и НП - Санкт-Петербург в ячейку G2 листа Ввод, формула в ячейке I2 возвратит значение ошибки #Н/Д (расстояние между этими НП не введено в лист База). Перейдите на лист Города и введите в ячейки D2, E2 и G2 названия НП и расстояние между ними, после чего нажмите на кнопки ввода названия НП Санкт-Петербург и расстояний между ними в списки данных на листах База и Города.

Перейдите на лист Ввод. В ячейке I2 появилось вместо значения ошибки #Н/Д значение введенного расстояния. Но при заполнении второй строки маршрута между Санкт-Петербургом и Киевом, несмотря на то, что оба названия этих НП присутствуют в списке элементов Поле со списком, формула в ячейке I3 возвратит то же значение ошибки.

Обратитесь к элементу Поле со списком проверки - с какими НП введены расстояния и при выборе НП Санкт-Петербург, второй элемент Список возвратит только одно название НП - Москва. Следовательно, необходимо вернуться на лист Города и ввести расстояние между НП Санкт-Петербург и Киев.

Если системных ресурсов недостаточно

После создания приложения и при попытке "прокладки" маршрута с помощью элементов управления Поле со списком через некоторое время при выборе населенного пункта, независимо от мощности компьютера, может появиться предупреждающее диалоговое окно (рисунок 4) о том, что у компьютера не хватает системных ресурсов.

Рис.4. Предупреждающее диалоговое окно о нехватке системных ресурсов

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

Один из путей устранения появления такой ошибки может заключаться в очистке ячеек, содержащих формулы "прокладки" маршрута, а затем их восстановление для проведения вычислений. Для выполнения первой задачи создайте макрос ОчисткаФормул, запись которого заключается в выделении ячеек с формулами и последующем нажатии на клавишу Delete. Для одновременного выделения несмежных ячеек выделите первый диапазон ячеек с формулами, а затем удерживая клавишу Ctrl, с помощью мыши выделите остальные. Макрос назначьте элементу управления Флажок, активизация которого будет указывать на отсутствие формул на рабочем листе и при этом элемент управления будет вводить в ячейку R11 значение ИСТИНА.

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

Полный текст этого приложения можно взять здесь.

Со следующего шага мы начнем рассматривать модель расчета максимальной загрузки контейнера.

Предыдущий шаг Содержание Следующий шаг