1. Семейство WorkSheets.
2. Объект WorkSheet
3. Пример

 

Объект WorkSheet и семейство WorkSheets.

В иерархии Excel объект WorkSheet  идет сразу после объекта Workbook и  представляет рабочий лист.

MS Excel позволяет оперировать с несколькими типами листов:

•          Рабочие листы, содержащие ячейки. Эти листы представляются объектом WorkSheet.

•          Листы диаграмм, содержащие диаграммы. Такие листы представляются объектом Chart.

•          Свойства объекта Worksheets

•          Application- Когда используется без объектного спецификатора, это свойство возвращает объект Application , который представляет приложение Excel Microsoft.

•          Когда используется с объектным спецификатором, это свойство возвращает объект Application, который представляет создателя указанного объекта (Вы можете использовать это свойство с объектом Automation OLE, чтобы возвратить приложение того объекта).

•          Только для чтения.

•           Count- Возвращает значение Long , которое представляет число объектов в коллекции (т.е. число листов и диаграмм).

•          Creator- Возвращает 32-разрядное целое число, которое указывает приложение, в котором был создан этот объект. Только для чтения Long.

•          Worksheets (1).Creator

•          Комментарии: Если объект был создан в Excel Microsoft, это свойство возвращает строковый XCEL, который эквивалентен шестнадцатеричному числу 5843454C. Свойство Creator проектировано, чтобы использоваться в Excel Microsoft для Macintosh, где у каждого приложения есть четырехсимвольный код ассоциированного приложения. Например, у Excel Microsoft есть код ассоциированного приложения XCEL.

•          HPageBreaks- Возвращает коллекцию HPageBreaks, которая представляет горизонтальные разрывы страниц на листе. Только для чтения.

•          Worksheets (1). HPageBreaks

•           

•          Item- Возвращает единственный объект из коллекции

•          Worksheets (1).Item (Index)

•          Параметры: Index — Название или индекс объекта.

•          Пример: Следующие две строки программы эквивалентны

•          ActiveWorkbook.Worksheets.Item (1)

•          ActiveWorkbook.Worksheets (1)

 

VPageBreaks- Возвращает коллекцию VPageBreaks , которая представляет вертикальные разрывы страниц на листе. Только для чтения.

Worksheets (1). VPageBreaks

Этот пример отображает общее количество полноэкранных и область печати вертикальные разрывы страниц.

For Each pb in Worksheets (1).VPageBreaks

   If pb.Extent = xlPageBreakFull Then

       cFull = cFull + 1

   Else

       cPartial = cPartial + 1

   End If

Next

MsgBox cFull & “ полноэкранные разрывы страниц, “ & cPartial & _

   » разрывы страниц области печати «

Visible- Определяет видимость объекта

Возможные варианты: Valse и True

 Worksheets (1).Visible = True

Parent- Возвращает родительский объект для указанного объекта. Только для чтения

Методы семейства WorkSheets.

Add - создает новый рабочий лист.

Синтаксис:

objWorkSheets.Add(Before, After, Count, Type)

Параметр Before – указывает лист, перед которым будет размещен новый рабочий лист.

Параметр After — указывает лист, после которого будет размещен новый рабочий. Если аргументы Before и After опущены, то новый лист размещается перед активным листом.

Параметр Count — число добавляемых листов, по умолчанию имеет значение 1

Параметр Туре — указывает тип добавляемого листа. Допустимые значения: xlWorksheet(по умолчанию),xlExcel4MacroSheet и хlЕхсеl4IntlMacroSheet.

Например: вставим новый лист перед активным листом активной рабочей книги.

ActiveWorkbook.Worksheets.Add

Delete – удаляет рабочий лист.

Copy- Копирует лист в другое местоположение в рабочей книге.

Worksheets (1).Copy (Before, After)

Параметры:

Before — Лист, перед которым будет помещен скопированный лист.

After — Лист, после которого будет помещен скопированный лист.

Комментарии:

Вы не можете определить Before , если Вы определяете After и на оборот. Если Вы не определяете или Before , чем или After, Excel Microsoft создаст новую рабочую книгу, которая содержит скопированный лист

Этот пример копирует Sheet1, помещая копию после Sheet3.

Worksheets ( «Sheet1»).Copy After:=Worksheets ( «Sheet3»)

FillAcrossSheets- Копирует выбранный диапазон в ту же самую область на всех других рабочих листах в коллекции.

Worksheets ( «Sheet1»).FillAcrossSheets (Range, Type)

Параметры:

Range — Диапазон, который необходимо скопировать на рабочие листы в коллекции. Диапазон должен быть от рабочего листа в пределах коллекции.

Type — Определяет, как скопировать диапазон.

Этот пример заполняет диапазон A1:C5 на Sheet1, Sheet5, и Sheet7 с информационными наполнениями того же самого диапазона на Sheet1.

x = Array ( «Sheet1», «Sheet5», «Sheet7»)

Sheets (x).FillAcrossSheets _   

      Worksheets ( «Sheet1»).Range ( «A1:C5»)

Move- Перемещает лист в другое местоположение в рабочей книге.

Worksheets ( «Sheet1»).Move (Before, After)

Параметры:

Before-Лист, перед которым будет помещен перемещенный лист.

After — Лист, после которого будет помещен перемещенный лист.

Комментарии:

Вы не можете определить Before, если Вы определяете After и наоборот. Если Вы не определяете ни Before, ни After , Excel Microsoft создаст новую рабочую книгу, которая содержит перемещенный лист.

Этот пример перемещает Sheet1 после Sheet3 в активной рабочей книге.

Worksheets ( «Sheet1»).Move  after:=Worksheets ( «Sheet3»)

PrintOut- Печатает объект.

Worksheets ( «Sheet1»).PrintOut (From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName, IgnorePrintAreas)

Параметры:

From — Число страницы, чтобы запустить печатать. Если этот параметр опущен, печатается с первой страницы.

To — Число последней страницы, которая напечатает. Если этот параметр опущен, печатается до последней страницы.

Copies — Число печатаемых копий. Если этот параметр опущен, печатается одна копия.

Preview — True, чтобы вызвать предварительный просмотр информации, выводимой на печать прежде, чем напечатать объект. False (или опущенный), чтобы напечатать объект немедленно.

ActivePrinter — Определяет имя активного принтера.

PrintToFile — True, чтобы напечатать в файл. Если PrToFileName не определен, Excel Microsoft запрашивает пользователя имя выходного файла.

Collate — True, чтобы сопоставить множественные копии.

PrToFileName — Если PrintToFile установлен в True, этот параметр определяет название файла, в который Вы хотите печатать.

IgnorePrintAreas — True, чтобы проигнорировать области печати и напечатать весь объект.

Этот пример печатает активный лист.

ActiveSheet.PrintOut

PrintPreview- Показывает предварительный просмотр объекта, как это смотрелось бы когда печатается.

Worksheets ( «Sheet1»).PrintPreview (EnableChanges)

Параметры:

EnableChanges — Передайте Boolean значение, чтобы определить, может ли пользователь изменить края и другие опции Параметров станицы, доступные в предварительном просмотре информации, выводимой на печать

Этот пример отображает Sheet1 в предварительном просмотре информации, выводимой на печать.

Worksheets ( «Sheet1»).PrintPreview

Select- Выбирает объект.

Worksheets ( «Sheet1“).Select (Replace)

Параметры:

Replace — (используемый только с листами). True , чтобы заменить текущий выбор указанным объектом. False , чтобы расширить текущий выбор, чтобы включать любые ранее выбранные объекты и указанный объект (выбор нескольких объектов).

Свойства объекта WorkSheet.

Name – возвращает имя рабочего листа.

Пример: первому листу рабочей книги присваивается имя “урок1”

ActiveWorkbook.Worksheets(1).Name = "урок1"

Visible- true (рабочий лист выводится на экран), false (рабочий лист не видим, т.е. скрыт, но его можно отобразить на экране с помощью команды Формат®лист® отобразить (Format, Sheet, Show) и  xlVeryHidden(рабочий лист скрыт, и его можно отобразить на экране только программно).

StandardHeight – возвращает стандартную высоту всех строк рабочего листа.

Пример: устанавливает стандартную высоту 3строки.

Worksheets(1).Rows(3).RowHeight = Worksheets(1).StandardHeight

ActiveCell - возвращает активную ячейку активного рабочего листа.

Intersect – возвращает диапазон, являющийся пересечением нескольких диапазонов.

Синтаксис:

Intersect (range1, rabge2, …)

Пример: выбирается пересечение диапазонов A1:D3 и C3:D4, т.е. диапазон C3:D3.

Intersect(Range("A1:D3"), Range("C3:D4")).Select

Union – возвращает диапазон, являющийся объединением нескольких диапазонов.

Синтаксис:

Union(rangeI, range2, ...)

Пример: выбирается объединение двух диапазонов A1:B2 и C3:D4.

Union(Range("A1:B2"), Range("C3:D4")).Select

Методы объекта WorkSheet.

Activate – активизирует рабочий лист.

Пример: Worksheets(1).Activate

Protect – защищает рабочий лист от внесенных в него изменений.

Синтаксис:

Protect (Password, DrawingObjects, Contents,Scenarios, UserInterfaceOnly)

Password -Строка используется в качестве пароля для защиты листа

DrawingObjects -Допустимые значения :true(графические объекты защищены) и false(графические объекты не защищены).По умолчанию используется значение false.

Contents - Допустимые значения : true(ячейки защищены) и false(ячейки не защищены). По умолчанию используется значение true.

Scenarios - Допустимые значения :  true(сценарии защищены) и false(сценарии не защищены).По умолчанию используется значение true.

UserInterfaceOnly - Допустимые значения: true(лист защищен от изменений со стороны пользователя, но не подпрограммы VBA) и false(лист защищен от изменений со стороны как пользователя , так и подпрограммы VBA). По умолчанию используется значение false.

В примере устанавливается полная защита активного рабочего листа от любых изщменений со стороны пользователя:

ActiveSheet.Protect Password:=”Секрет”, DrawingObjects:=true, Contents:= true, Scenarios:=true

Unprotect – снятие защиты с рабочего листа.

Синтактис:

Unprotect(Password)

Password - Строка используемая в качестве пароля для защиты листа

Снятие защиты с активного рабочего листа

ActiveSheet.unProtect Password:=”Секрет”

Copy –копирование рабочего листа в другое место рабочей книги.

Синтаксис:

Copy (Before, After)

Пример:

Worksheets("урок1").Copy after:=Worksheets(3)

Move – перемещение рабочего листа в другое место рабочей книги.

Move (Before, After)

Before- лист рабочей книги, перед которым вставляется данный

 After – лист, после которого вставляется данный

Одновременно допустимо использование только одного из аргументов.

В примере Лист1 активной рабочей киги перемещается перед Лист3 той же рабочейкниги:

Worksheets(“Лист1”).Move Before:= Worksheets(“Лист3”)

Evaluate - преобразует выражение в объект или значение. Используется при вводе формул и ячеек из диалоговых окон.

В следующем примере в поле ввода первого появившегося диалогового окна вводится ссылка на ячейку, например А1, процедура считывает значение из этой ячейки и отображает его в диалоговом окне.

Sub eva()

Ячейка = InputBox( "Введите имя ячейки")         ' Ввод адреса ячейки

Значение = Evaluate (Ячейка) .Value ' Считывание значения из ячейки с указанным адресом 1

MsgBox CStr (Значение) ‘ Вывод считанного значения в диалоговое окно

End Sub

 После этого в поле ввода следующего диалогового окна надо ввести какое-нибудь арифметическое выражение, например A3*A4. Процедура вычислит значение этого выражения и отобразит в диалоговом окне.

Sub eva()

функция = InputBox("введите имя ячейки")

значение = Evaluate(функция)

MsgBox CStr(значение)

End Sub

События объекта WorkSheet.

Чтобы создать процедуру обработки события конкретного листа, следует выбрать этот лист в окне проекта и затем в списке Общая область выбрать WorkSheet. После этого перейдите в список Описания и пометьте событие, процедуру которого необходимо создать.

Calculate происходит после того, как будут произведены вычисления для всех ячеек рабочего листа.

Change – происходит после изменения пользователем содержания ячейки рабочего листа.Процедура обработки события Change содержит один параметр Target , котрый представляет собой объект Range , содержащий измененные ячейки.

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

SelectionChange – происходит каждый раз при очередной смене выделения на рабочем листе. Процедура обработки события Change содержит один параметр Target , котрый представляет собой объект Range , содержащий выделенные ячейки.

BeforeClose        при закрытии рабочей книги

BeforePrint         перед печатью рабочейкниги

BeforeSave         перед сохранением рабочей книги

Deactivate          когда рабочая книга теряет фокус

NewSheet           придобавлении нового листа

Open                   при открытии рабочейкниги

SheetActivate     при активации любого рабочего листа

SheetDeactivate  когдарабочий лист реряет фокус

Задача: есть таблица, надо пройти по ячейкам и если они равны некому эталону, то всю строку скопировать на другой лист.

Этот скрипт пробегает по всем ячейкам таблицы размером 10х10, в ячейке (1, 2) есть некоторое значение, с которым сравнивается содержимое каждой проверяемой ячейки, если оно совпадает, то всю строку, в которой находится эта ячейка, надо перекопировать на новый лист Result. Таким образом, на новом листе создастся новая таблица, в которой будут только необходимые нам строки.

Sub macros1() 

  Dim i As Integer 

  Dim j As Integer 

  Dim k As Integer  

 On Error Resume Next   

Set NewSheet = Worksheets.Add  

 With Worksheets("Sheet1") 

      k = 1    

   For i = 1 To 10        

   For j = 1 To 10          

     If .Cells(i, j) = .Cells(1, 2) Then     

Rows(i).Copy NewSheet.Rows(k)        

           k = k + 1           

        Exit For           

    End If    

       Next j  

     Next i   

End With  

 NewSheet.Name = "Result"  

 Worksheets("Result").Activate

End Sub

Обмен данными между листами Excel  и переменными (массивами) VBA

 Dim X(), Y(), N As Variant ' объявление переменных уровня модуля

 ‘ фрагмент программы под кнопку «ЧТЕНИЕ С ЛИСТА»

Private Sub CommandButton1_Click()

N = Worksheets(1).Range("B14").Value ' чтение ячейки B14 листа 1 в переменную N

ReDim X(N), Y(N)

' цикл считывания N ячеек первого и второго столбца листа 1 в массивы X и Y

For i = 1 To N

X(i) = Worksheets(1).Cells(i + 1, 1).Value

Y(i) = Worksheets(1).Cells(i + 1, 2).Value

Next i

End Sub

‘ фрагмент программы под кнопку «ЗАПИСЬ НА ЛИСТ»

Private Sub CommandButton2_Click()

' Запись на лист 2 содержимого переменных N (в ячейку D14), X и Y ( 3 и 4 столбцы)

Worksheets(2).Range("D14").Value = N

For i = 1 To N

Worksheets(2).Cells(i + 1, 3).Value = X(i)

Worksheets(2).Cells(i + 1, 4).Value = Y(i)

Next i

End Sub

‘ фрагмент программы под кнопку «ОЧИСТКА»

Private Sub CommandButton3_Click()

' Очистка на 2 листе ячейки D14, 3 и 4 столбца

Worksheets(2).Range("D14").ClearContents

For i = 1 To N

Worksheets(2).Cells(i + 1, 3).Value = " "

Worksheets(2).Cells(i + 1, 4).Value = " "

Next i

End Sub

Самостоятельно: На лист1 заполнить матрицу чисел размером 3х3.Написать макрос чтения этихчисел в массив ,нахождения максимального и сохранения результата в ячейку Excel.

Задача: Написать приложение, решающее задачу расчета амортизации в зависимости от выбранного метода: стандартного или А;-кратного учета амортизации. На примере данного приложения вы узнаете, что такое:

•          Финансовые функции расчета амортизации

•          Управление видимостью отдельных элементов управления в окне диалога

•          Программный вывод объектов WordArt на рабочий лист

•          Теория

•          Под амортизацией подразумевается уменьшение стоимости имущества в процессе эксплуатации. Обычно оценивают величину этого уменьшения на единицу времени.
Функция рабочего листа AMP (SLN) возвращает величину амортизации имущества за один период времени, используя метод равномерной амортизации.

•          Синтаксис:
АМР(стоимость; остаток; время_эксплуатации)
Аргументы:

•               Стоимость- Начальная стоимость имущества

•                Остаток - Остаточная стоимость в конце периода амортизации (иногда называется ликвидной стоимостью имущества)
Время_эксплуатации - Количество периодов, за которые собственность амортизируется (иногда называется периодом амортизации)

•          Предположим, вы купили за 6 000 руб. компьютер, который после 5 лет эксплуатации будет оцениваться в 1 000 руб. Снижение стоимости для каждого года эксплуатации вычисляется формулой =дмр(6000; 1000; 5), которая определяет 1 000р.

•          Функция АМГД (SYD) возвращает годовую амортизацию имущества для указанного периода.

•          Синтаксис:

•          АМГД(стоимость; остаток; время_эксплуатации; период)

•          Аргументы:

•             Стоимость - Начальная стоимость имущества
Остаток - Остаточная стоимость в конце периода амортизации (иногда называется ликвидной стоимостью имущества)
Время эксплуатации - Количество периодов, за которые собственность амортизируется (иногда называется периодом амортизации)
Период- Номер периода для вычисления амортизации (должен измеряться в тех же единицах, что и время полной амортизации)

•          Практика

•          Для решения задачи нахождения вычисления амортизации оборудования по стандартному методу или методу к-кратного учета с помощью редактора пользовательских форм создадим диалоговое окно Расчет амортизации (рис. У4.1).

Рис. У4.1. Диалоговое окно Расчет амортизации

Обсудим, как приведенная ниже программа решает перечисленные задачи и что происходит в программе.

UserForm Initialize

1. Активизирует диалоговое окно.
2. Запрещает ввод данных пользователем в поле Кратность метода.
3. Назначает клавише <Esc> функцию кнопки Отмена, а клавише <Enter> -- Вычислить.
4. Назначает кнопке вычислить сочетание клавиш <Alt>+<B>, а кнопке Отмена — <Alt>+<O>.
5. В группе тип амортизации при инициализации диалогового окна назначает выбор переключателя Стандартный метод. За счет выбора этого переключателя при инициализации диалогового окна не отображаются надпись кратность метода и соответствующие ей поле и счетчик (рис. У4.2).
6. Устанавливает для счетчика минимальное значение, равное 2, и шаг изменения значений счетчика, также равный 2, для убыстрения прокрутки ; счетчика. Промежуточные значения вводятся в i поле Кратность метода не с помощью счетчика, i а посредством клавиатуры.
7. Нажатие кнопки вычислить запускает на выполнение процедуру
8. CoramandButton1_Click
   Проверяет согласованность вводимых данных. В случае их несогласованности отображает соответствующее сообщение (рис. У4.3, а и У4.3, б).
9. Используя финансовую функцию рабочего листа SYD (АМГД) и DDB (ДДОБ) вычисляет величину амортизации выбранным методом.
10. Удаляет с рабочего листа все ранее созданные графические объекты и внедряет объект WordArt. Подготавливает рабочий лист для вывода результатов вычислений. Выводит полученные данные на рабочий лист и в диалоговое окно (рис. У 4. 4).