Макросы занятие 6. Объекты и их свойства.

Работа с ячейками эл.таблицы при произвольном количестве данных.

В макросе прописанном макрорекодером по вставке формул на лист рейсы, есть недостаток – жестко закрепленный диапазон области вставки (от 2 до 50).

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

Среди прочих бросается в глаза свойство Address. Если присвоить переменной значение ActiveCell.Address, например tx= ActiveCell.Address, поставить точку останова на команде следующей за данной инструкцией то, наведя курсор на tx после запуска макроса увидим “$A$1” (выделена ячейка А1). Поскольку мы уже освоили оператор Mid, вырезать текстовое значение номера строки не представляет труда.

Но есть другая проблема – определить, где заканчивается список значений в таблице. Здесь ход рассуждений тоже вполне естественен. Использование служебных клавиш Shift, Ctrl, Alt приводит к исполнению каких либо функций. Даже не обращаясь к справочной системе можем попробовать их действие. Основная клавиша Ctrl. Нажатие < Ctrl> +< End> приводит курсор в конечную область данных вниз+вправо. Нажатие < Ctrl> +< ¯ > в последнюю непустую ячейку данной колонки. У первой клав. комбинации есть недостаток – перемещение может произойти в область прежде редактируемых, но ныне пустых ячеек. Вывод – будем определять диапазон значений по самой заполненной колонке (в каждой ячейке – значение), используя < Ctrl> +< ¯ > для перемещения на последнюю ячейку и определение ее адреса. После записи действий получим «волшебную» комбинацию:

Range(“A1”).Select

Selection.End(xlDown).Select

Окончательный вариант будет таким:

Sub formuls()

Sheets(" Рейсы").Select

Range(“A1”).Select

Selection.End(xlDown).Select

tx= ActiveCell.Address

tx=Mid(tx, 4)

Range(" G2").Select

ActiveCell.FormulaR1C1 = " =RC[-2]*RC[-1]"

Selection.Copy

Range(" G3: G" +tx).Select

ActiveSheet.Paste

Application.CutCopyMode = False

Range(" C2").Select

ActiveCell.FormulaR1C1 = " =MONTH(RC[1])"

Selection.AutoFill Destination: =Range(" C2: C" +tx), Type: =xlFillDefault

Range(" C2: C" +tx).Select

Range(" A1").Select

End Sub

При использовании свойства Value (содержимое) объекта Range можно сократить текст макроса до нескольких строк. Дело в том, что макрорекодер не оптимизирует текст программы, а просто фиксирует программными кодами все ваши действия.

Результатом осмысленного использования упомянутых свойств известного объекта будет лаконичный программный код.

Sub formuls()

Sheets(" Рейсы").Select

Range(“A1”).Select

Selection.End(xlDown).Select

tx= ActiveCell.Address

tx=Mid(tx, 4)

Range(" G2: G" +tx).Value=" =RC[-2]*RC[-1]"

Range(" C2: C" +tx).Value = " =MONTH(RC[1])"

Range(" A1").Select

End Sub

Для закрепления использования данного открытия освоим еще одну задачу. Попутно разберемся с еще одним свойством активной ячейки. Зачем нам в текстовом виде номер последней заполненной строки, если можно поискать соответствующее свойство активной ячейки. Это свойства Row и Column. Они соответственно возвращают номер строки и колонки в виде целых чисел. Это удобнее в большинстве случаев. Кроме того, для обращения к конкретной ячейке удобнее воспользоваться объектом Cells(ном_строки, ном_колонки).

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

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

Sub чет_нечет()

Range(“A1”).Select

Selection.End(xlDown).Select

proiz=1

nomst= ActiveCell.Row

for i=1 to nomst step 2

proiz=proiz*Cells(i, 1)

ssum=ssum+ Cells(i+1, 1)

Next I

Cells(1, 2)= proiz

Cells(1, 3)= ssum

End Sub