Vba excel как составить тест

Если ваши ученики освоили в Excel представление и
обработку данных, то можно переходить на
качественно новый уровень работы с Excel –
автоматизировать свою работу. Язык VBA (Visual Basic for
Application) – язык программирования, который
подходит для всех приложений Microsoft Office, это один
из самых простых в изучении и применении языков,
он является версией языка Visual Basic.

Предлагаю задания для создания проектов в виде
тестов в среде MS Excel и VBA.

Задание:

• Откройте Excel.
• Переименуйте Лист1 в Вопросник
  и составьте таблицу.

Для каждого вопроса создайте пользовательские
формы и с помощью их заполните таблицу.

Пользовательские формы могут иметь вид:

UserForm1

UserForm2

• При вызове следующего вопроса закрывается
  текущая форма, записываются результаты на лист Вопросник,
  и открывается форма со следующим вопросом.
• В форме с последним вопросом запрограммировано
  только закрытие формы и запись результатов.
• Спроектируйте на листе Excel Вопросник
  кнопку вызова форм:

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

Темы проектов:

1. Подобрать тест, обработать его, результаты
показать через пользовательскую форму.

2. Вопросник по любому предмету, результаты
которого обработать в Excel и выдать через
пользовательскую форму оценку:

3. Вопросник “МОЯ ВСЕЛЕННАЯ, ПОЗНАЙ СЕБЯ”

• Создать кнопку вызова.
• Создать форму Вопрос и ответы к ним:

Вопросы:

1. Мое любимое занятие…
2. Мой любимый цвет…
3. Мое любимое животное…
4. Мой лучший друг…
5. Мой любимый звук…
6. Мой любимый запах…
7. Моя любимая игра…
8. Моя любимая одежда…
9. Моя любимая музыка…
10. Мое любимое время года…
11. Что я больше всего на свете люблю делать…
12. Место, где я больше всего люблю бывать…
13. Мой любимый певец или группа…
14. Мои любимые герои…
15. Я чувствую в себе способности к …
16. Человек, которым я больше всего восхищаюсь …
17. Лучше всего я умею …
18. Я знаю, что смогу …
19. Я уверен в себе потому, что …

Вопросник “Мне нравится”

1. Мне нравится в школе то, что …
2. Мне не нравится в школе то, что …
3. Я просто в восторге от того, что …
4. Мне не нравится на уроке то, что …
5. Вот было бы здорово, если бы на уроке можно было
   бы …

Смотри Приложение 1, где
можно определить тип темперамента.

Желаю удачи!!!

Время на прочтение
7 мин

Количество просмотров 14K

Проблема

Есть определенная функциональная область приложения: некая экспертная система, анализирующая состояние данных, и выдающая результат — множество рекомендаций на базе набора правил. Компоненты системы покрыты определенным набором юнит-тестов, но основная «магия» заключается в выполнении правил. Набор правил определен заказчиком на стадии проекта, конфигурация выполнена.
Более того, поскольку после первоначальной приемки (это было долго и сложно — потому, что “вручную”) в правила экспертной системы регулярно вносятся изменения по требованию заказчика. При этом, очевидно, неплохо — бы проводить регрессионное тестирование системы, чтобы убедиться, что остальные правила все еще работают корректно и никаких побочных эффектов последние изменения не внесли.

Основная сложность заключается даже не в подготовке сценариев — они есть, а в их выполнении. При выполнении сценариев “вручную”, примерно 99% времени и усилий уходит на подготовку тестовых данных в приложении. Время исполнения правил экспертной системой и последующего анализа выдаваемого результата — незначительно по сравнению с подготовительной частью. Сложность выполнения тестов, как известно, серьезный негативный фактор, порождающий недоверие со стороны заказчика, и влияющий на развитие системы («Изменишь что-то, а потом тестировать еще прийдется… Ну его…»).

Очевидным техническим решением было бы превратить все сценарии в автоматизированные и запускать их регулярно в рамках тестирования релизов или по мере необходимости. Однако, будем ленивыми, и попробуем найти путь, при котором данные для тестовых сценариев готовятся достаточно просто (в идеале — заказчиком), а автоматические тесты — генерируются на их основе, тоже автоматически.

Под катом будет рассказано об одном подходе, реализующим данную идею — с использованием MS Excel, XML и XSLT преобразований.

Тест — это прежде всего данные

А где проще всего готовить данные, особенно неподготовленному пользователю? В таблицах. Значит, прежде всего — в MS Excel.

Я, лично, электронные таблицы очень не люблю. Но не как таковые (как правило — это эталон юзабилити), а за то, что они насаждают и культивируют в головах непрофессиональных пользователей концепцию «смешивания данных и представления» (и вот уже программисты должны выковыривать данные из бесконечных многоуровневых «простыней», где значение имеет все — и цвет ячейки и шрифт). Но в данном случае — мы о проблеме знаем, и постараемся ее устранить.

Итак, постановка задачи

• обеспечить подготовку данных в MS Excel. Формат должен быть разумным с точки зрения удобства подготовки данных, простым для дальнейшей обработки, доступным для передачи бизнес пользователям (последнее — это факультативно, для начала — сделаем инструмент для себя);
• принять подготовленные данные и преобразовать их в код теста.

Решение

Пара дополнительных вводных:

• Конкретный формат представления данных в Excel пока не ясен и, видимо, будет немного меняться в поисках оптимального представления;
• Код тестового скрипта может со временем меняться (отладка, исправление дефектов, оптимизация).

Оба пункта приводят к мысли, что исходные данные для теста необходимо предельно оделить и от формата, в котором будет осуществляться ввод, и от процесса обработки и превращения в код автотеста, поскольку обе стороны будут меняться.

Известная технология превращения данных в произвольное текстовое представление — шаблонизаторы, и XSLT преобразования, в частности — гибко, просто, удобно, расширяемо. В качестве дополнительного бонуса, использование преобразований открывает путь как к генерации самих тестов (не важно на каком языке программирования), так и к генерации тестовой документации.

Итак, архитектура решения:

1. Преобразовать данные из Excel в XML определённого формата
2. Преобразовать XML с помощью XSLT в финальный код тестового скрипта на произвольном языке программирования

Конкретная реализация на обеих этапах может быть специфична задаче. Но некоторые общие принципы, которые, как мне кажется, будут полезны в любом случае, приведены ниже:

Этап 1. Ведение данных в Excel

Здесь, честно говоря, я ограничился ведением данных в виде табличных блоков. Фрагмент файла — на картинке.

1. Блок начинается со строки, содержащей название блока (ячейка “A5″). Оно будет использовано в качестве имени xml-элемента, так что содержание должно соответствовать требованиям. В той же строе может присутствовать необязательный “тип” (ячейка “B5″) — он будет использовано в качестве значения атрибута, так что тоже имеет ограничения.
2. Каждая колонка таблицы содержит помимо “официального” названия, представляющего бизнес-термины (строка 8), еще два поля для “типа” (строка 6) и “технического названия” (строка 7). В процессе подготовки данных технические поля можно скрывать, но во время генерации кода использоваться будут именно они.
3. Колонок в таблице может быть сколько угодно. Скрипт завершает обработку колонок как только встретит колонку с пустым значением “тип” (колонка D).
4. Колонки со “типом”, начинающимся с нижнего подчеркивания — пропускаются.
5. Таблица обрабатывается до тех пор, пока не встретиться строка с пустым значением в первой колонке (ячейка “A11”)
6. Скрипт останавливается после 3 пустых строк.

Этап 2. Excel -> XML

Преобразование данных с листов Excel в XML — несложная задача. Преобразование производится с помощью кода на VBA. Тут могут быть варианты, но мне так показалось проще и быстрее всего.

Ниже приведу лишь несколько соображений — как сделать финальный инструмент удобнее в поддержке и использовании.

1. Код представлен в виде Excel add-in (.xlam) — для упрощения поддержки кода, когда количество файлов с тестовыми данными более 1 и эти файлы создаются/поддерживаются более чем одним человеком. Кроме того — это соответствует подходу разделения кода и данных;
2. XSLT шаблоны размещаются в одном каталоге с файлом add-in — для упрощения поддержки;
3. Генерируемые файлы: промежуточный XML и результирующий файл с кодом, — желательно помещать в тот же каталог, что и файл Excel с исходными данными. Людям создающим тестовые скрипты будет удобнее и быстрее работать с результатами;
4. Excel файл может содержать несколько листов с данными для тестов — они используются для организации вариативности данных для теста (например, если тестируется процесс, в котором необходимо проверить реакцию системы на каждом шаге): откопировал лист, поменял часть входных данных и ожидаемых результатов — готово. Все в одном файле;
5. Поскольку все листы в рабочей книге Excel должны иметь уникальное имя — эту уникальность можно использовать в качестве части имени тестового скрипта. Такой подход дает гарантированную уникальность имен различных подсценариев в рамках сценария. А если включать в имя тестового скрипта название файла, то достичь уникальности названий скриптов становится еще проще — что особенно важно в случае если тестовые данные готовят несколько человек независимо. Кроме того, стандартный подход к именованию поможет в дальнейшем при анализе результатов теста — от результатов исполнения к исходным данным будет добраться очень просто;
6. Данные из всех листов книги сохраняются в один XML файл. Для нас это показалось целесообразным в случае генерации тестовой документации, и некоторых случаях генерации тестовых сценариев;
7. При генерации файла с данными для теста удобно оказалось иметь возможность не включать в генерацию отдельные листы с исходными данными (по разным причинам; например, данные для одного из пяти сценариев ещё не готовы — а тесты прогонять пора). Для этого мы используем соглашение: листы, где название начинается с символа нижнего подчёркивания — исключаются из генерации;
8. В файле удобно держать лист с деталями сценария по которому создаются тестовые данные («Documentation») — туда можно копировать информацию от заказчика, вносить комментарии, держать базовые данные и константы, на которые ссылаются остальные листы с данными, и так далее. Разумеется, данный лист в генерации не участвует;
9. Чтобы иметь возможность влиять на некоторые аспекты генерации финального кода тестовых скриптов, оказалось удобным включать в финальный XML дополнительную информацию «опции генерации», которые не являются тестовыми данными, но могут использоваться шаблоном для включения или исключения участков кода (по аналогии с pragma, define, итп.) Для этого мы используем именованные ячейки, размещённые на негенерируемом листе «Options»;
10. Каждая строка тестовых данных должна иметь уникальный идентификатор на уровне XML — это здорово поможет при генерации кода и при обработке кросс-ссылок между строками тестовых данных, которые при этом необходимо формулировать в терминах как раз этих уникальных идентификаторов.

<MasterRecord type="Type1">
<columns>
<column>
<type>Field</type>
<name>TechName1</name>
<caption>Business Name 1</caption>
</column>
<column>
<type>Field</type>
<name>TechName2</name>
<caption>Business Name 2</caption>
</column>
<column>
<type>Field</type>
<name>TechName3</name>
<caption>Business Name 3</caption>
</column>
</columns>
<row id="Type1_1">
<Field name="TechName1">A</Field>
<Field name="TechName2">123</Field>
<Field name="TechName3">2016-01-01</Field>
</row>
<row id="Type1_2">
<Field name="TechName1">B</Field>
<Field name="TechName2">456</Field>
<Field name="TechName3">2016-01-01</Field>
</row>
</MasterRecord>

Этап 3. XML -> Code

Эта часть предельно специфична задачам которые решаются, поэтому ограничусь общими замечаниями.

1. Начальная итерация начинается по элементам, представляющим листы (различные тестовые сценарии). Здесь можно размещать блоки setup / teardown, утилит;
2. Итерация по элементам данных внутри элемента сценария должна начинаться с элементов ожидаемых результатов. Так можно логично организовать сгенерированные тесты по принципу «один тест — одна проверка»;
3. Желательно явно разделить на уровне шаблонов области, где генерируются данные, выполняется проверяемое действие, и контролируется полученный результат. Это возможно путём использования шаблонов с режимами (mode). Такая структура шаблона позволит в дальнейшем делать другие варианты генерации — просто импортируя этот шаблон и перекрывая в новом шаблоне необходимую область;
4. Наряду с кодом, в тот же файл будет удобно включить справку по запуску тестов;
5. Очень удобным является выделение кода генерации данных в отдельно вызываемый блок (процедуру) — так чтобы его можно было использовать как в рамках теста, так и независимо, для отладки или просто создания набора тестовых данных.

Финальный комментарий

Через какое-то время файлов с тестовыми данными станет много, а отладка и «полировка» шаблонов генерации тестовых скриптов будет все продолжаться. Поэтому, прийдется предусмотреть возможность «массовой» генерации автотестов из набора исходных Excel файлов.

Заключение

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

В нашем проекте удалось довольно быстро создать набор тестовых сценариев для интеграционного тестирования сложной функциональной области — всего на данный момент около 60 файлов, генерируемых примерно в 180 тестовых классов tSQLt (фреймворк для тестирования логики на стороне MS SQL Server). В планах — использовать подход для расширения тестирования этой и других функциональных областей проекта.

Формат пользовательского ввода остается как и раньше, а генерация финальных автотестов можно менять по потребностям.

Код VBA для преобразования Excel файлов в XML и запуска преобразования (вместе с примером Excel и XML) можно взять на GitHub github.com/serhit/TestDataGenerator.

Преобразование XSLT не включено в репозиторий, поскольку оно генерит код для конкретной задачи — у вас все равно будет свой. Буду рад комментариям и pull request’ам.

Happy testing!

1. Overview

Welcome to the world of Excel VBA unit testing! In this tutorial, I am going to introduce you to Rubberduck VBA, and its unit testing capabilities.

What you’ll learn

• How to add test modules
• How to add unit tests
• How to run unit tests
• How to interpret test results

What you’ll need

• A Windows computer with Excel 2007, or newer
• Basic knowledge of Function and Sub procedures

All the commands that are listed below are executed on a Windows 10 machine with Excel 2016.

2. Get started

This step is only needed if you have not set up your development environment. You need to install Rubberduck VBA to your Excel. You can follow these instructions to install it.

Download Rubberduck installer from this link. Once download is finished, start the installer and follow the steps in the setup wizard.

Kicking off Excel

After setup has finished installing, it’s a good idea to launch Excel, open VBA Editor, and verify Rubberduck has been properly installed:

The options we’ll use in this tutorial are located unter the Rubberduck > Unit Tests menu:

3. Create a function to test

In the previous step, we started Excel and opened Visual Basic Editor. In this step, you are going to insert a new code module, and add a new Function to it.

I’ve already prepared a function that we will unit test later, so just paste it into Module1:

Here, you defined a new function, called ValidatePasswordLength(). It takes a single parameter of value string, and returns a boolean.

The purpose of the function is to assure that the password length is valid. It needs to be greater or equal to 8, and less or equal to 16 characters.

In next steps, we are going to create several unit tests that will test this function.

4. Insert a Test Module

Before you can start adding unit tests, you need to first insert a test module. A test module is a container for unit tests. Each test module can have zero or more unit tests. You can have any number of test modules in a project, depending on the complexity of your requirements. In this tutorial, you are just going to use a single Test Module.

To insert a new Test Module, you need to display the Test Explorer window. You do that with the Rubberduck > Unit Tests > Test Explorer menu command:

Next, select the  Test Explorer >  Add > Test Module command. This inserts a new Test Module called TestModule1:

Every time you insert a new test module, it contains these procedures: ModuleInitialize(), ModuleCleanup(), TestInitialize() and TestCleanup(). They are used to perform any initialization before running the tests, as well as cleaning up after tests have finished running.

Step 5: Add unit tests

If you try running the tests now, you won’t get any output. That’s because there are no unit test methods yet. So let’s start to add some tests. Usually it’s best to add the tests at the very bottom of this module, below ModuleInitialize(), ModuleCleanup(), TestInitialize() and TestCleanup() functions.

To insert a unit tests:

1. First, make sure you’re located in TestModule1, just like in the previous screen shot.
2. In Test Explorer, click Add > Test Method.

Test Explorer inserts a new test method:

What’s happening here?

Looking at the generated code, we see the Arrange, Act and Assert comments. These help guide us in writing better tests. For the purpose of this tutorial, we can delete all three of these comments.

Notice the @TestMethod attribute right in front of the function declaration? This attribute is automatically added to every test method. It’s there so that the method can get picked up by the test runner, as well as Test Explorer. Without it, test methods wouldn’t show up in Test Explorer.

To illustrate this concept, let’s click on the Refresh button in Test Explorer, and observe how the newly added test method now shows up in the Test Explorer window.

It’s a good idea to use the refresh button when you make changes to your methods, such as renaming them, deleting, or adding new ones.

Note: When you get more comfortable with writing unit tests, you can omit using the wizard — and code your test methods directly in the editor. You can do this by adding the @TestMethod attribute right in front of your test method.

Step 6: Customize the Unit Test

So far, this test method doesn’t do anything — it doesn’t test any code. So let’s customize it, by giving it a more appropriate name, and let’s add some code to it.

Let’s rename the test method from TestMethod1() to TestPasswordLenthIsTooSmall(), and delete the three “Arrange:”, “Act:” and “Assert:” comments.

Next, let’s modify the TestPasswordLenthIsTooSmall() test method, by having it invoke the ValidatePasswordLength() function we created in Step 3, by passing it the parameter “bob”.

You can do this by replacing the line:

Assert.Inconclusive

with:

Assert.IsFalse (ValidatePasswordLength("bob"))

The purpose of the ValidatePasswordLength() function, is to verify whether the password is valid, by comparing it’s length. If it’s within the range of 8-16 characters, the password is valid, and the function will return true. If it’s outside of that range, it will return false. Because we’re passing the parameter “bob”, we’re expecting to see a false result. That’s why we use Assert.IsFalse().

When you’re done, your code should look like this:

Assert.IsTrue and Assert.IsFalse above are methods from Rubberduck’s Assert class. Conveniently, it also provides a few other methods that we can use:

Believe it or not, that’s it! We’re done with creating our first test case.

Step 7: Run the test

Now starts the real fun: let’s run it. In order to run the test:

• Inside Test Explorer, click Run > All Tests.

In Test Explorer, we can see a few things:

• Test result: failed, passed, and not run tests
• The name of the test module
• Name of the test method
• How long it took to execute the test, in ms

That’s it! As you can see, our unit test passes. If you’d like to add the remaining test cases, you may do so. When you’re finished adding them, you just need to refresh the Test Explorer, and re-run the tests. The Test Explorer will run all of them, and show you the summary of results for each.

Once all your tests pass, you’re good to go!

Step 8: Add more test cases (optional)

So far, we’ve added one single test method, and ran it to make sure it passed.

We can use many different values to ValidatePasswordLength()‘s parameters. For example:

• “bob” –> should return FALSE
• “ASecretPassword” –> should return TRUE
• “” –> should return FALSE
• “123456” –> should return FALSE
• “IAmAVeryLongLongLongLongPassword#!{}$#.” –> should return FALSE

If we imagine running ValidatePasswordLength() with the inputs above, we should get results as indicated in the bullet points above.

Just for fun, I’ve added a few of the test cases above, and this is what I get after refreshing and running the tests:

All the tests pass. Pretty neat!

Summary

In this tutorial, I’ve shown you how to get started with Rubberduck VBA to create and run your very first unit test. Congratulate yourself on your success!

Obviously, a real-world scenario doesn’t include just one single test. There can be many unit tests organized in many test modules, depending on the complexity of your solution. The great thing about using Rubberduck VBA is that it provides a Test Explorer which allows you to navigate your test cases.

If your company is considering implementing unit tests, or require tailor-made solutions for complex scenarios, please don’t hesitate to get in touch.