пятница, 3 июня 2011 г.

Мои статьи по Excel 2010


Экранные формы в Excel 2010
Программист Excel имеет возможность создавать пользовательские диалоговые окна для своих приложений VBA. Эти диалоговые окна, называемые экранными формами (user form), могут включать любые элементы, которые обычно встречаются в диалоговых окнах программы Excel и других приложений Windows. Экранные формы представляют собой очень мощный инструмент программирования, поскольку позволяют создать для приложений Excel визуальный пользовательский интерфейс любого требуемого вида.

Знакомство с экранными формами
Экранная форма состоит из трех непосредственно связанных друг с другом компонентов:
1. Форма как таковая, представляет собой окно со строкой заголовка и другими компонентами, типичными для других окон операционной системы Windows. Эта форма представлена объектом UserForm.
2. Элементы управления, размещенные в окне формы, составляют ее визуальный и функциональный интерфейс. Каждый тип элементов управления представлен своим собственным классом.
3. Программа VBA является частью экранной формы. Экранная форма не обязательно должна содержать программу, однако в большинстве случаев формы снабжаются своими программами.
Экранная форма является обычным окном, однако для нее не преду-смотрены некоторые характеристики, которые могут иметь окна многих программ. В частности:
пользователь не может вручную изменить размер формы (хотя это может быть сделано в программе VBA;
в окне формы отсутствуют кнопки Свернуть/Развернуть;
формы не имеют меню управления.
Однако пользователь может переместить форму по экрану, перетащив строку заголовка.
Объект UserForm, как и все размещенные в окне формы элементы управления, обладает свойствами и методами, определяющими их внешний вид и функциональные характеристики. Многие объекты могут также реагировать на собы¬тия, а в большинстве случаев — на действия пользователя (например, на щелчок кнопкой мыши). Одновременно используя все эти элементы — свойства, методы и события — в программе VBA можно определить внешний вид и функциональ¬ные возможности экранной формы, которые будут точно соответствовать требованиями создаваемого приложения.
Создать экранную форму в редакторе VBA несложно. Редактор форм позволя¬ет проектировать форму визуально, по принципу WYSIWYG (What You See Is What You Get — что видишь, то и получаешь). 
Создание экранной формы
1. Сохраните файл в формате Книга Excel с поддержкой макросов. 
2. Перейдите в редактор VBA, нажав клавиши Alt + F11.
3. В редакторе VBA выберите команду Insert UserForm (Вставить-Экранную форму), чтобы создать для текущего проекта новую экранную форму.
4. В окне Properties свойству Name данной формы присвойте значение Start, а свойству Caption — Прогностик.
5. Щелкните на форме, чтобы активизировать ее. Добавьте к форме 6 элементов управления надписи Label  и присвойте их свойствам Caption следующие значения: Исходная информация для прогнозирования бюджета:, Прогнозируемый год:, Предшествующий период в годах:, Число статей доходной части:, Число статей расходной части:, Единицы измерения статей бюджета.
6. Установите Шрифт (Font) для первой надписи (заголовка) 16 пт, а для 5-ти последующих 14 пт. Расположите все надписи в столбик и увеличьте их размер и формы, чтобы они были видны полностью.
7. Добавьте к форме 4 элемента управления TextBox разместите их в столбик справа от 4-х последующих за заголовком надписей соответственно.
8. Добавьте к форме элемент управления ComboBox  и разместите его в столбик справа от 6-той надписи Единицы измерения статей бюджета.
9. После этого щелкните на значке Command Button (Командная кнопка) панели инструментов.
10. Расположите на форме командную кнопку, перетащив курсор над требуемой позицией в нижнем левом углу формы.
11. В окне Properties присвойте свойству Name этой кнопки значение CommandButton1, а свойству Caption — Отменить.
12. Расположите на форме командную кнопку, перетащив курсор над требуемой позицией в нижнем левом углу формы.
13. В окне Properties присвойте свойству Name этой кнопки значение CommandButton2, а свойству Caption — Готово.
14. Щелкните на кнопке Save панели инструментов, чтобы сохранить проект.
15. На этом этап разработки дизайна формы можно считать завершенным, хотя к ней еще требуется добавить некоторые коды.
Добавление кодов
Следующий этап заключается в написании для формы требуемых кодов. Эти коды помещаются в процедуры обработки событий и выполняются автоматически, когда пользователь предпринимает определенные действия (в данном случае щелкает на командных кнопках). 
1. Выделив экранную форму, щелкните на кнопке View Code (Показать код) окна Project. В результате на экране появится окно редактирования кодов данной формы. 
Обратите внимание, что в верхней части окна расположено два раскры-вающихся списка. Эти списки используются следующим образом:
2. В списке слева содержится перечень всех элементов управления, распо-ложенных в окне формы, в том числе и название самой экранной формы. Кроме того, в нем находится пункт (General). Чтобы перейти к редактированию кодов одного из элементов управления или самой формы, выберите требуемый пункт из этого списка.
3. В списке справа содержится перечень всех процедур обработки событий, доступных для выбранного в левом списке элемента управления. Укажите требуемое событие, и редактор автоматически добавит шаблон, соответствующий процедуре обработки этого события. Выберите в списке пункт (Declarations), чтобы набрать/отредактировать код, не принадлежащий ни одной из процедур.
4. Вы сможете добавить код события к создаваемому проекту, если в левом списке в окне редактирования выберите пункт CommandButton1. 
5. В списке справа автоматически будет выбрано событие Click (щелчок), по¬скольку именно оно наиболее часто используется для элемента управления Command Button. В окне появится шаблон процедуры этого события. В теле процедуры между открывающей и закрывающей строками наберите Me.Hide.
6. Щелкните на кнопке Save, чтобы сохранить проект.
Написание кода инициализации формы
Код инициализации формы выполняет только одно действие: загружает список ComboBox с единицами измерения статей бюджета. Далее описана процедура добавления кода инициализации.
1. Щелкните на кнопке View Code окна Project, чтобы открыть окно редакто¬ра кодов для экранной формы.
2. Из списка в левой верхней части окна выберите вариант UserForm.
3. Из списка в правой верхней части окна выберите вариант Initialize.
4. Наберите код листинга в теле процедуры события Initialize. Код инициализации экранной формы, с помощью которого в список ComboBox1 загружаются единицы измерения приведен ниже:
Private Sub UserForm_Initialize()
ComboBox1.AddItem "р."
ComboBox1.AddItem "тыс.р."
ComboBox1.AddItem "млн.р."
ComboBox1.AddItem "млрд.р."
End  Sub
Проверка корректности ввода данных
Создаваемый проект можно усовершенствовать, добавив ограничение на ввод в поля только тех значений, которые состоят из цифр. Подобное ограничение можно рассматривать как метод проверки корректности вводимых данных. Чтобы не проверять данные после их ввода пользователем, целесообразнее изначально запретить вводить некорректных значений.
Чтобы оценить вводимые с клавиатуры данные прежде, чем они достигнут элемента управления, воспользуйтесь событием KeyDown. Как вы уже знаете, это событие получает значение аргумента, соответствующее нажатой клавише. Если нажата допустимая клавиша, информация передается далее к элементу управления; в противном случае нажатие клавиши игнорируется.
В справочной системе VBA представлена таблица значений KeyCode, из кото¬рой следует, что клавишам 0-9 соответствуют коды 48-57. Следовательно, если процедура обработки события KeyDown получает для аргумента KeyCode значе¬ние, находящееся в диапазоне 48-57, это свидетельствует о нажатии цифровой клавиши. Такую информацию можно передать далее к элементу управления. Нажатие всех остальных клавиш должно игнорироваться.
Вы можете установить ограничение на ввод в текстовом поле TextBox1 не более 4-х символов, а TextBox2, TextBox3, TextBox4 - не более 2-х символов,  присвоив его свойству MaxLength соответствующее значение.
Чтобы добавить код, выполняющий проверку на корректность вводимых значений, откройте для формы окно редактора кодов, после чего добавьте для элемента управления TextBox процедуру обработки события KeyDown. В теле процедуры наберите код, показанный в листинге. Обратите внимание на использование оператора Веер, благодаря которому при нажатии неправильной клавиши система издает звуковой сигнал. Процедура обработки события KeyDown позволяет вводить в текстовом поле TextBox только цифровые значения:
Private  Sub  TextBox.KeyDown(ByVal   KeyCode As MSForms.Returnlnteger, ByVal  Shift As   Integer)
If  KeyCode   <   48   Or  KeyCode   >   57  Then 
KeyCode  =   0
Beep
End  If
End  Sub
Как видно из листинга, в текстовом поле могут быть введены только цифры, набранные с основных клавиш, но не с боковой цифровой клавиатуры. Попро¬буйте в качестве упражнения изменить программу так, чтобы можно было набирать и с цифровой клавиатуры, перемещать и корректировать данные.
Коды, выполняющие проверку корректности данных, набраны в теле функции (а не процедуры), благодаря этому вызывающей программе может быть возвращено требуемое значение. В нашем случае при условии успешного завершения проверки функция возвращает значение True; если же набранные пользователем данные некорректны — значение False.
Код функции, выполняющей проверку корректности набранных пользовате¬лем данных. Обратите внимание, что в первой строке кодов этой функции добавлен определитель As Boolean, указывающий, значе¬ние какого типа будет возвращаться функцией. Добавьте к своей программе код, представленный в листинге для функции проверки корректности набранных данных:
Public  Function ValidateData()   As Boolean
‘ Возвращает True,   если набранные  пользователем данные  корректны 
‘ В  противном случае  возвращает  False и сообщает  о  проблеме
If TextBox1.Value <> "" Then
If TextBox1.TextLength <>  4 Then
MsgBox  "Год должен состоять из 4-х цифр"
ValidateData = False
Exit Function 
End If
If TextBox2.TextLength <  2 Then
MsgBox  " Нельзя устанавливать меньше 2 периодов"
ValidateData = False
Exit Function 
End If
If TextBox3.TextLength <  1 Then
MsgBox  " Нельзя устанавливать меньше 1 статьи"
ValidateData = False
Exit Function 
End If
If TextBox4.TextLength <  1 Then
MsgBox  " Нельзя устанавливать меньше 1 статьи"
ValidateData = False
Exit Function 
End If
End If
ValidateData = True
End Function
Окончание проекта
Для завершения этого проекта остается только написать процедуру обработки событий Click для элемента управления CommandButton2. 
После щелчка на кнопке Готово программа проверяет корректность набранных данных. Если данные некорректны, они продолжают отображаться в полях формы, с тем чтобы пользователь мог их исправить. Если набранные пользователем данные корректны, форма после ввода данных в ячейки рабочего листа закрывается.
Свойство Cells используется для получения доступа к отдельным ячейкам диапазона и сохранения в них набранных пользователем значений. Добавьте эти коды к кодам экранной формы, и проект будет готов к работе:
Private Sub CommandButton2_Click()
If ValidateData = True Then
If TextBox1.Value <> "" Then Worksheets("ПрогнозДоход")._ Range("B3").Cells(l).Value = TextBox1.Value
If TextBox2.Value <> "" Then Worksheets("ПрогнозДоход")._ Range("G3").Cells(l).Value = TextBox2.Value
If TextBox3.Value <> "" Then Worksheets("ПрогнозДоход")._ Range("K3").Cells(l).Value = TextBox3.Value
If TextBox4.Value <> "" Then Worksheets("ПрогнозРасход")._ Range("K3").Cells(l).Value = TextBox4.Value
If ComboBox1.Value <> "" Then Worksheets("ПрогнозДоход ")._ Range("E18").Cells(l).Value = ComboBox1.ValueMe.Hide
End If
End Sub 
Тестирование проекта
Если экранная форма открыта в редакторе VBA, то для тестирования создан¬ного проекта следует нажать клавишу <F5>. Можно также написать макрос, ко¬торый будет отображать форму на экране путем вызова метода Show:
Sub Prognos()
' Сочетание клавиш: Ctrl+Shift+D
Dim frm As New Start
frm.Show
End Sub 
1. В конце панели быстрого доступа щелкните на кнопке Настройка панели быстрого доступа, а затем щелкните на Другие команды. Появится страница Настройка диалогового окна Параметры Ехсеl. 
2. Щелкните на стрелке поля Выбрать команды из, а затем щелкните на категории Час¬то используемые команды, чтобы отобразить команды этой категории. 
3. Щелкните на стрелке поля Выбрать команды из, а затем щелкните на категории Мак¬росы. Появится список доступных макросов.
4. В списке макросов щелкните на Prognos.
5. Щелкните на кнопке Добавить. Макрос Prognos появится в списке ко-манд панели быстрого доступа.
6. В списке команд панели быстрого доступа щелкните на Prognos.
7. Щелкните на кнопке Изменить. Появится диалоговое окно Изменение кнопки.
8. Щелкните на кнопке с красным восклицательным знаком (шестая в пер-вом ряду).
9. Дважды щелкните на кнопке ОК, чтобы закрыть диа¬логовые окна Изменение кнопки и Па¬раметры Ехсеl. Диалоговые окна закроются, а на панели быстрого дос¬тупа появятся кнопка Prognos. 
10. Щелкните на кнопке Макросы на панели быстрого доступа. В диалоговом окне Макрос выберите Prognos и нажмите кнопку Выполнить. Ехсеl 2010 выполнит макрос Prognos.
11. На панели быстрого доступа щелкните на кнопке Сохранить.
Поработав с Prognos, вы обнаружите, что экранная форма существенно облегчает ввод данных, а это гораздо удобнее, чем вручную вводить значения непосредственно в ячейки рабочей книги.
В статье рассказывается о способах защиты данных в Excel. Сразу оговорюсь, что в ней не рассматривается защита с помощью IRM (ограничения прав). В Excel 2010 предусмотрено несколько уровней защиты, позволяющих управлять доступом к данным...
В статье рассказывается о работе с макросами. Поработав с документами Ехсеl 2010, вы можете обнаружить, что часто выполняете одни и те же задачи. Некоторые из них (такие как сохранение и закрытие файлов) выполняются достаточно...

Многие задачи, решаемые с помощью табличного процесса, требуют анализа данных для прогнозирования и принятия решений, в частности ...
В Microsoft Office Excel 2010 значительно расширены и усовершенствованы приемы по визуализации данных ... 





Литература
Черняков М.К. Информатика. Организация и средства пользовательского интерфейса в приложении Microsoft Office / Учебное пособие. — Новосибирск: СибУПК, 2010.- 112 с.

13 комментариев:

  1. Примите мои поздравления! Нужно было приложить немало труда, чтобы добиться успеха!

    ОтветитьУдалить
  2. Очень хорошо получилось! я горжусь твоими достижениями!!!!

    ОтветитьУдалить
  3. Серьезное начало блога, Михаил!
    Сразу - серьезно-тематический, да еще и на основе имеющихся достижений. Здорово!!!
    Так держать!

    ОтветитьУдалить
  4. Валентина4 июня 2011 г., 14:22

    Потрясающе! Поздравляю!

    ОтветитьУдалить
  5. Суханова Татьяна4 июня 2011 г., 17:08

    Миша, ты молодец! Горжусь одноклассником. Ты, кстати, помнишь, что в этом году сорокалетие нашего окончания школы?

    ОтветитьУдалить
  6. Михаил,примите мои поздравления!
    Диплом -это замечательно!
    И статьи в Блоге ,лично для меня ,полезные.

    ОтветитьУдалить
  7. Храмова Валентина5 июня 2011 г., 7:59

    Здорово, Михаил! Глобальная работа! Радует, что не останавливаешься на достигнутом. Это ведь как езда на велосипеде: либо ты едешь вперед, либо падаешь. Успехов тебе впереди! Так держать!

    ОтветитьУдалить
  8. Валерий Маринин5 июня 2011 г., 10:54

    Очень толково и полезно! Спасибо!Здорово,что ты занимаешься этим полезным делом. Этим можно гордиться!

    ОтветитьУдалить
  9. Татьяна Камнева7 июня 2011 г., 8:47

    Рада твоим успехам!!!
    Молодец!!!

    ОтветитьУдалить
  10. Михаил Константинович, очень ценные статьи!Почерпнула для себя много полезного!

    ОтветитьУдалить
  11. Здравствуйте, Михаил!
    С сегодняшнего дня я становлюсь Вашим постоянным читателем.
    У Вас есть чему поучиться.
    Спасибо за полезную и доступную информацию!

    ОтветитьУдалить
  12. Очень интересная статья, да и блог Ваш, хоть и молодой, но уже вполне серьёзно о себе заявляющий! Так держать!

    Мой сайт http://vremyairazum.ru/

    ОтветитьУдалить
  13. Женщины в восторге. Обращаются на ты. Я также восхищён, пока только мужчиной, а книгу собираюсь смотреть

    ОтветитьУдалить