Ну вот, сделал более-менее законченный вариант, который можно предложить. Ещё раз поясню идею.
Преследуется следующие цели:
1. Упрощение синтаксиса при обращении к данным таблиц переменной длины как из формул экселя так и ВБА.
2. Уход от зависимости местоположения таблиц в книге, их размера (в длину, и в ширину) при обращении к ним из ВБА.
3. Увеличение скорости пересчёта книги, содержащей таблицы с долговычисляемыми формулами.
Для этого предлагаю принять следующие меры: ввести две доп. ячейки, расположение которых будет обозначать левую, правую и верхнюю границы данных таблицы и указывать на расположение строки формул таблицы. Содержимое одной ячейки – будет количество строк таблицы, а другой – расстояние от строки формул до начала данных таблицы.
Строка формул выполняет 2 функции – содержит имена столбцов таблицы и служит для возможности повышения скорости пересчёта книги. Если на листе имеются долгопересчитываемые таблицы данных, то можно заменить все значения таблицы формулами, оставив лишь одну строку с формулами. Предполагается, что эта строка может располагаться либо в качестве первой строки таблицы, либо находиться на несколько строк выше таблицы. Потом эту строку всегда можно скрыть. После этого с таблицей можно делать всё, что угодно (удалять строки (первую, средние, последнюю), добавлять/удалять столбцы на этапе разработке (в т.ч. первый и последний)) и всё это не вызовет никаких проблем при обращении к данным таблицы из экселя и VBA. В дальнейшем, при добавлении строк таблицы либо при необходимости обновления данных таблицы, нужно будет только вызвать одну процедуру, которая распространит строку формул на всю таблицу, пересчитает её заново и опять заменит формулы значениями.
Если необходимости заменять формулы значениями нет – можно этого не делать. В таком случае в качестве строки формул удобно использовать первую строку таблицы.
Упрощение синтаксиса предполагается произвести за счёт использования имён. Каждая таблица должна иметь своё имя. Каждый столбец состоит из имени таблицы, знак "_" и, собственно, имя столбца. (Напр., если Таблица называется "Test", а столбец "Count", то полное имя столбца будет "Test_Count "). Т.о. в каждой таблице может быть свой столбец "Count".
Поскольку длина таблицы динамическая, то имени столбца будет присвоена формула и для того, чтобы "вспомнить" правильное написание имени столбца необходимо открывать "Вставка/Имя/Присвоить..." и там его искать. Для того чтобы далеко не лезть и сразу "вспомнить" имя любого столбца, мы опять обращаемся к строке формул. Каждая её ячейка имеет такое же имя, как и весь столбец, но с дополнительным знаком "_" в конце. Т.о. при написании формул в случае обращения к какому-либо столбцу нужно всего лишь ткнуть мышкой в соотв. ячейку строки формул таблицы и удалить посл. знак "_" !
Во вложении на листах "Пример1" и "Пример2" вы видите пример таблиц и способ обращения к данным их столбцов. Можете открыть "Вставка/Имя/Присвоить..." и просмотреть созданные имена, их диапазоны и формулы их задающие.
Теперь пару слов о процедурах, облегчающих работу с таблицами. В модуле Kernel имеется 2 типа процедур. Одни направлены на облегчение создания таблиц, имён столбцов, их переименования и изменения адреса, другие предназначены для обновления данных таблиц.
Рассмотрим первую группу процедур. При открытии файла "Kernel.xls" появится панель инструментов "Мои таблицы". Первая кнопка – "Создать таблицу". Как я уже говорил, подразумевается, что таблица состоит из строки формул и данных, располагающихся либо с самой строки формул, либо на несколько строк ниже. Для создания таблицы выделите строку формул таблицы (либо пустое место, где вы её планируете разместить), затем удерживая "Ctrl" щёлкните на любую ячейку из той строки, где начинаются (будут начинаться) данные и нажмите "Создать таблицу" на панели инструментов "Мои таблицы" (см. лист "Шаг1" во вложении). После задания имени таблицы и нажатия "Ок" создадутся вспомогательные граничные ячейки и некоторые имена. Однако это ещё не все. Не задано имя счётного столбца (см. лист "Шаг2" вложения").
Для присвоения столбцу таблицы имени необходимо выделить ячейку в строке формул таблицы и нажать кнопку "Создать именованый столбец". Если будет выделена ячейка не в строке формул таблицы, то будет создан простой динамический диапазон. Имя созданного столбца для удобства будет отличаться от его первой ячейки лишь одним символом "_".
Далее на панели инструментов идёт кнопка "Изменить адрес имени". Иногда бывает необходимость изменить адрес псевдонима ячейки. Для этого нужно просто сначала выделить эту ячейку, а затем с контролом выделить ту ячейку, куда вы хотите переместить этот псевдоним. При этом, если перемещаемая ячейка была началом динамического диапазона или заголовком столбца таблицы (в строке формул), то вместе с ней переместиться и весь диапазон (столбец) !
Следующая, довольно полезная кнопка – "Переименовать". Выделяете ячейку, псевдоним которой вы хотите переименовать на нажимаете кнопку "Переименовать". После переименования псевдонима на всём листе в области формул будет произведён поиск старого имени и замена его новым. Затем подобный поиск и замена будет произведена в области формул имён книги
.
Наконец, последняя кнопка – удаление имён. Выделите область и нажмите "Удалить имя". Будут удалены все имена для ячеек, попадающих в выделенную область (имена, адреса которых вычисляются формулами и попадают в выд. область удалены не будут).
Далее, рассмотрим процедуры, предназначенные для облегчения обновления таблиц. Их всего 4: TblClr, TblFillF, TblFillV и TblValues.
TblClr (TblName As String, Optional FromCol As String, Optional ToCol As String) – очистить таблицу, оставив лишь строку формул.
TblName – имя таблицы – то, которое задавалось при нажатии на кнопку "Создать таблицу";
FromCol и ToCol – имена столбцов, с которого по который очищать таблицу. Если опущен один из них или оба – выбирается первый столбец таблицы, последний либо вся таблица соответственно. Суть этих аргументов одинаковая для всех нижеописанных процедур.
TblFillF (TblName As String, Rows As Variant, Optional FromCol As String, Optional ToCol As String) – распространить строку формул на Rows строк вниз начиная со строки начала данных таблицы. Аргумент "Rows" может быть задан как обычным числом, так и именем ячейки, в которой находится число.
TblFillV (TblName As String, Rows As Variant, Optional FromCol As String, Optional ToCol As String) - распространить строку формул на Rows строк вниз начиная со строки начала данных таблицы, а затем заменить формулы значениями (оставив лишь строку формул)
TblValues (TblName As String, Optional FromCol As String, Optional ToCol As String) – заменить значениями все данные таблицы (оставив лишь строку формул). Предполагается, что таблица заполнена и количество строк известно (оно хранится в ячейке "TblName_Rows").
Во вложении имеется лист "Пример1", в коде которого сравнивается синтаксис использования вышеперечисленных процедур и как можно обойтись без них.
На листах "Пример1" и "Пример2" я привёл пару демонстрационных таблиц, которые созданы по вышеизложенной методике. Предлагаю обратить внимание на лаконичность синтаксиса в формулах. Возможно некоторые откроют для себя некоторые интересные моменты. В частности, предлагаю взглянуть на, на мой взгляд, интересную формулу в столбце "Tbl3_Sum" таблицы "Tbl3" на листе "Пример2". Она подсчитывает общую сумму чисел в столбцах Tbl1_Col1:Tbl1_Col2 таблицы "Tbl1" для заданного кода, во всех строках, где он встретился:
{=СУММ((D7=Tbl1_Code)*Tbl1_Col1:Tbl1_Col2)}
Хочу обратить особое внимание на то, что пользоваться данным методом работы с таблицами могут все, даже не владеющие ВБА! Просто, так намного удобнее – формулы становятся более удобочитаемыми, лаконичными и короткими.
У кого родились идеи по модернизации – высказывайте или делайте сами и дополняйте модуль.
Жду ваших отзывов, рекомендаций, советов или просьб, а так же с удовольствием отвечу на все ваши вопросы... Мне будет очень приятно узнать, если мой труд кто-то сочтёт полезным и для себя...
Да, и большое спасибо ув. KL !!! Некоторые моменты, используемые в данном методе работы с таблицами, я позаимствовал именно у него...
