1. Автоматический запуск Программы VBA.

  Теперь, когда появился законченный проект программы VBA, возникает вопрос как запускать программу минуя редактор  VBA? Нужно сделать вызов нашей программы в момент открытия книги электронной таблицы, т.е. другими словами когда для объекта "Workbook"  происходит событие "Workbook_Open"  Нужно выполнить код (Рис.1):

Privite Sub Workbook_Open()

           UserForm1.Show                   'Вызов программы

End Sub

Рис. 1. Код вызова программы.

    Добавляем код в нашу программу "RRasch23.xls"(Primer4.rar)как показано на Рис.1. Сохраняем изменения, закрываем всё и открыв файл "RRasch23.xls" смотрим на результат. Как это работает поясним на рис. 2:

Рис. 2 Схема автозапуска VBA приложения.

        При открытии книги Excel выполняется автозапуск приложения VBA, которое открывается поверх окна  Excel. При закрытии приложения VBA управление передаётся в Excel и электронную таблицу можно закрыть.

 Нас интересует только панель нашей программы и книгу Excel таблицы можно и не показывать. Нет проблем. Делаем нашу книгу Excel таблицы невидимой, а при закрытии программы нужно закрывать и  Excel. Как это сделать? Рассмотрим рис.3:

Рис. 2 Схема автозапуска VBA приложения с переводом Excel в фоновый режим.

В алгоритм работы нашей программы добавим команды : cкрыть Excel, cделать Excel видимым, закрыть Excel. При открытии приложения VBA Excel переводится в фоновый режим(делается невидимым). Когда нужно закрыть программу, то сначала Excel делаем видимым, а затем закрываем Excel(при выполнении данной команды закрывается и наша программа).  

    В программу внесём следующий код:

- для  процедуры Private Sub UserForm_Initialize() добавим код

'******** ПЕРЕВОДИМ Excel в фоновый режим********
Application.Visible = False
'************************************************

- добавить процедуру для выхода из программы вместе с Excel 

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'******** Сделать Excel видимым ********
Application.Visible = True
Application.Quit
'***************************************
End Sub

             2. Взаимодействие приложения VBA с ячейками Excel.

Программирование на VBA - это управления объектами приложения. Ячейки Вот и рассмотрим упрощённую структуру приложение Excel:

Application

WorkbookS

................... Workbook

WorksheetS

........................... Worksheet

Cell

..........

Excel приложение является объектом для VBA-программирования с именем- Application. В данный объект могут входить несколько книг -Workbook, объединённые в коллекцию Workbooks. Данный объект содержит все открытые объекты Workbook, а в книге находятся листы Worksheet, объединённые в коллекцию Worksheets. Коллекция Worksheets содержит по одному объекту Worksheet для каждого листа текущей книги. Листы разбиты на ячейки (Cell). При работе активными могут быть только одна книга и один лист.

Коллекции (WorkbookS, WorksheetS) имеют  два важных метода и два свойства :

                Add         - добавить элемент в коллекцию;

                Remove   - удаляет элемент из коллекции;

                Count      - возвращает количество элементов в коллекции;

                Item         - возвращает элемент коллекции.

И так первым делом испытаем свойства Count и Item . Создаём Excel файл c именем "Test.xls" и открыв данный файл переходим в редактор VBA (alt+F11). Создаём форму. На форму добавим два элемента Label  и установим свойства :

Label1

Lb1Kol1

Для события инициализации формы  напишем код :

Private Sub UserForm_Initialize()

 Me.Width = 250             'Ширина формы

 Me.Height = 100            'Высота формы

 Me.Caption = "Test v.5.1." 'Заголовок формы

 kol = Application.Workbooks.Count 'Определяем кол-во открытых книг

 lblKol1.Caption = kol      'Вывод значения Kol на форму

End Sub

Самое главное  в двух последних строчках - определяем количество открытых книг.  Если перед запуском программы не было открытых книг, то при  запуске должно быть одна открытая книга(см. рис.4). Для внесения ясности можно поэкспериментировать:

- Открыть какую ни будь книгу excel и запустить программу;

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

Рис. 4. Результат тестовой программы - открыта одна книга. 

                3. Взаимодействие приложения VBA с ячейками Excel.

    Программирование на VBA - это управления объектами приложения. Ячейки Вот и рассмотрим упрощённую структуру приложение Excel:

                  Application

                  WorkbookS

                  ................... Workbook

                  WorksheetS

                  ........................... Worksheet

                 Cell

                 ..........

    Excel приложение является объектом для VBA-программирования с именем- Application. В данный объект могут входить несколько книг -Workbook, объединённые в коллекцию Workbooks. Данный объект содержит все открытые объекты Workbook, а в книге находятся листы Worksheet, объединённые в коллекцию Worksheets. Коллекция Worksheets содержит по одному объекту Worksheet для каждого листа текущей книги. Листы разбиты на ячейки (Cell). При работе активными могут быть только одна книга и один лист.

Коллекции (WorkbookS, WorksheetS) имеют  два важных метода и два свойства :

                Add         - добавить элемент в коллекцию;

                Remove   - удаляет элемент из коллекции;

                Count      - возвращает количество элементов в коллекции;

                Item         - возвращает элемент коллекции.

    И так первым делом испытаем свойства Count и Item . Создаём Excel файл c именем "Test.xls" и открыв данный файл переходим в редактор VBA (alt+F11). Создаём форму. На форму добавим два элемента Label  и установим свойства :

    Label1

    Lb1Kol1

Для события инициализации формы  напишем код :

Private Sub UserForm_Initialize()

 Me.Width = 250             'Ширина формы

 Me.Height = 100            'Высота формы

 Me.Caption = "Test v.5.1." 'Заголовок формы

 kol = Application.Workbooks.Count 'Определяем кол-во открытых книг

 lblKol1.Caption = kol      'Вывод значения Kol на форму

End Sub

Самое главное  в двух последних строчках - определяем количество открытых книг.  Если перед запуском программы не было открытых книг, то при  запуске должно быть одна открытая книга(см. рис.4). Для внесения ясности можно поэкспериментировать:

- Открыть какую ни будь книгу excel и запустить программу;

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

Чтобы понять как работают методы добавить и удалить книгу добавим надпись  для вывода названия текущей активной книги и  пояснительную надпить (как это было сделано для кол-во открытых книг). Для добавления и удаления книг добавим соответствующие кнопки. И так программа должна работать так :

-нажал кнопку "Добавить книгу" и должна быть добавлена книга, а в пояснительной надписи выведено имя текущей активной книги;

-нажал кнопку "Удалить книгу" и должна быть удалена текущая активная книга, если она непоследняя.

Добавим новые объекты на форму и установим для них следующие свойства:

    Label2

    Lb1N

cmdAdd

cmdYdl

Допишем в программу следующий код 

(жирным выделено что нужно добавить):

Private Sub cmdAdd_Click()

With Application.Workbooks

                .Add

          kol = .Count 'Определяем кол-во открытых книг

End With

 lblKol1.Caption = kol      'Вывод значения Kol на форму

NameBook = Application.ActiveWorkbook.Name

lblN.Caption = NameBook

End Sub

Private Sub cmdYdl_Click()

kol = Application.Workbooks.Count 'Определяем кол-во открытых книг

If kol > 1 Then

    With Application.Workbooks(2)

                .Close

    End With

Else

    MsgBox ("Эту книгу удалять нельзя!!!")

End If

lblKol1.Caption = kol      'Вывод значения Kol на форму

NameBook = Application.ActiveWorkbook.Name

lblN.Caption = NameBook

End Sub

Private Sub UserForm_Initialize()

 Me.Width = 250             'Ширина формы

 Me.Height = 100            'Высота формы

 Me.Caption = "Test v.5.1." 'Заголовок формы

 kol = Application.Workbooks.Count 'Определяем кол-во открытых книг

 lblKol1.Caption = kol      'Вывод значения Kol на форму

 NameBook = Application.ActiveWorkbook.Name

 lblN.Caption = NameBook

End Sub

Из всего написанного следует отметить оператор With...End With, который применяется для сокращения кода. Чем меньше обращений к методам и свойствам объектов через точку тем программный код выполняется быстрее. Об этом нужно помнить, особенно когда такие коды выполняются в цикле.

Запустим данный пример и нажмём кнопку "Добавить книгу" и получим результат как на рис.5:

Рис.5. Пример работы методов ADD и Close для коллекции Workbooks .

На рисунке показано, что при запуске программы открывается книга"test.xls" (1). Если нажать кнопку "Добавить книгу" появиться книга "Книга1.xls" и станет активной(2). Если нажать кнопку "Удалить книгу", то книга, активная на данный момент будет удалена. Если открыта одна книга закрываться книга не будет.(Это сделано чтобы наша программа не повисла.)

Следует отметить, что для получения имени активной книги был применён код:

Application.ActiveWorkbook.Name

Для работы с листами книги используется коллекция Sheets. Нужно сначала указать книгу, а затем коллекцию Sheets :    Application.Workbook("test2.xls").Sheets.Count

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

Рис.6. Добавление и удаление листов в книгу "test2.xls" .

Так как возможно при решении каких-то задач нужно будет  проводить манипуляции с листами, то рассмотрим добавление листов подробне. Для добавления листов в книгу в коллекции листов также применяется метод Add . Этот метод можно применять с четырьмя необязательными параметрами Add [Before, After, Count, Type]. 

Before- Перед каким листом и After- после какого листа добавлять новый. 

Count- Кол-во добавляемых листов.

 Type- Тип добавляемого листа (например xlWorkSheet - для расчетного листа, xlChart- для диаграммы ).

Для добавления листа в конец книги код может быть следующим:

' в общем разделе нужно декларировать константу NBook

Const NBook="test2.xls"

Private Sub cmdAdd_Click()

With Application.Workbooks(NBook).Sheets 

                .Add , Sheets(kol)

          kol = .Count 'Определяем кол-во листов

End With

NameBook = Application.Workbooks(NBook).ActiveSheet.Name

 lblKol1.Caption = kol      'Вывод значения Kol на форму

lblN.Caption = NameBook

End Sub

 .Add , Sheets(kol) - запятая перед параметром говорит, что первый параметр отсутствует. Как работает данный метод с данным параметром хорошо видно на рисунке рис.6 .

Пример "test2.xls" можно скачать здесь: test2.rar

     Для работы с ячейками листа хорошо подходит свойство Range. На примере покажу как добавить число в ячейку и как занести значение ячейки в переменную:

With Application.Workbooks(NBook)

    Worksheets(3).Activate

    Range("A5")=10

    A=Range("A5")+15

    Range("B5")=A

End With 

Вставьте данный фрагмент в "test2.xls" в процедуру Private Sub UserForm_Initialise() и после запуска программы в ячейки A5 будет число10, а в ячейки B5 число 25.

    Попробуем использовать данные знания для очередной модернизации нашей программы для расчёта сопротивления провода. Можно один из листов использовать для сохранения различных настроек. Например: будем сохранять последнее значение выбранного материала провода в ячейки листа "Config" при закрытии программы и устанавливать значение материала, прочитав из данной ячейки, в момент запуска программы.

    Сделаем копию предыдущего примера и переименуем в книгу "RRasch31.xls". Теперь переименуем один из листов книги в "Config", а ячейку "B1" переименуем в  "Material". (См. Рис. 7.)

Рис.7. Переименовываем ячейку B1 и "Material". 

    В программу добавляем следующий код:

--Общая область--------------------------------------------------------------------------------

............................

Dim kf(0 To 9) As Single

Dim nkf(0 To 9) As String

Dim PathProecta As String

Const Nbook = "RRasch31.xls"

---------------------------------------------------------------------------------------------------

Фрагмент процедуры инициализации:

..............................

For I = 0 To 9            'Заполняем

        cmbMat.AddItem nkf(I)   'список

Next I                    'в элементе "поле со списком"

    cmbMat.BoundColumn = 0

    With Application.Workbooks.Item(Nbook)

        .Worksheets("Сonfig").Activate

        NMat = Range("Material").Value

    End With

    cmbMat.ListIndex = NMat

      txtR.Value = 0

      txtD.Value = 0

      txtR.TabIndex = 0

      txtD.TabIndex = 1

    cmdR.TabIndex = 2

 ...........................

-----------------------------------------------------------------------------------------------------------------------------------

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

 NMat = cmbMat.Value                'определяем текущий номер материала

    With Application.Workbooks.Item("RRasch31.xls")

        .Worksheets("Сonfig").Activate 'выбираем лист "Config"

       Range("Material").Value = NMat  'запоминаем номер материала

    End With

'//**********************************************

 Application.Visible = True 'Сделать Excel видимым

'Application.Quit           'Закрыть Excel !!!!!!!

'//**********************************************

End Sub

Если сделать все правильно, то программа будет помнить предыдущею установку вида материала.

Пример данной программы здесь RRasch31.rar

Для того, чтобы  распечатать какую либо область листа нужно выделить определённую область листа и отправить на печать:

Private Sub cmdPRN_Click()

NameBook = Application.Workbooks(NBook).ActiveSheet.Name

lblN.Caption = NameBook

Worksheets(3).Range("A1:H10").Select

Worksheets(3).PrintOut

End Sub

 Сначала не допишем  код " Worksheets(3).PrintOut " и запустим программу, чтобы посмотреть как на листе будет выделена область "А1: Н10". Заполним данную область каким либо текстом и добавив код выдачи на печать испытаем программу.

Пример данной программы здесь test3.rar

  Горшков Ю.В. , г. Сальск

Свои вопросы можно присылать на :mailto:vbadevir@narod.ru

  --------------------------------------------------------------------------------------------------------------

Советую посетить :

--------------------------------------------------------------------------------------------------------------------------------------

  Руководства и документации - каталог ссылок на документы.

FullNet.ru - Каталог сайтов.

Последнее изменение :