Макросы занятие 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