Automated Testing Excel
•
1 like•617 views
Felix Zumstein gave a presentation on automated testing of Excel workbooks at an xlwings Meetup in NYC. The presentation covered writing unit tests for VBA functions and cell formulas using xlwings in Python, running integration and logic tests on workbooks, comparing implementations to tested libraries, and setting up continuous integration to run the tests automatically after code changes. Examples were given of different types of tests like VBA unit tests, cell formula tests, and integration tests involving macros. Unittest and other test frameworks were also discussed for running the tests.
Recommended
More Related Content
What's hot
What's hot (14)
Similar to Automated Testing Excel
Similar to Automated Testing Excel (20)
More from xlwings
More from xlwings (15)
Recently uploaded
Recently uploaded (20)
Automated Testing Excel
- 1. Automated Testing of Excel Workbooks Felix Zumstein, Oct 3rd, 2019 xlwings Meetup NYC From unit tests to continuous integration
- 2. Agenda 2 1. Meetup organisation 2. xlwings intro 3. Automated testing by Felix Zumstein 4. Teaching machine learning via xlwings by Daniel Guetta, Senior Lecturer at Columbia Business School
- 3. About us
- 4. Innovative Solutions for Microsoft Excel A small team with a big mission 4
- 5. xlwings 5 • Python for Excel • Scripts, UDFs, Macros, REST API • Windows & Mac • Free & open-source • New: Professional support plans
- 6. xltrail 6 • Version control for Excel • Tracks formulas and VBA code
- 7. • Local Git extension that makes git diff and git merge work with VBA code (only) • Free & open-source Git XL 7
- 8. xlwings CI 8 Continuous integration for Excel files • Write unit tests for Excel files with xlwings • Run them automatically on the server after pushing changes to your Git repo Example: Gitlab Pipeline
- 9. xlwings-reports (coming soon) 9 Template Report create_report( ”template.xlsx”, performance=df )
- 11. Meetup organisation 11 • Community event • About any innovative solution for Excel, not just those based on xlwings • Contact me if you want to give a talk (short or long) in one of the next meetups
- 12. 3. xlwings intro
- 13. xlwings 13 1) Automation 2)Macros 3)UDFs (Windows only) 4)REST API
- 15. What is a unit test (Python sample)? 15 Python function that we want to test Unit test: OK or FAIL
- 16. Map VBA functions to Python 16 VBA function my_sum (Python) MySum (VBA) args result
- 17. VBA unit test boilerplate 17 run at the beginning and end of the test suite, respectively actual test
- 18. What can be tested in Excel? 18 1) Unit test: E.g. a cell formula, a VBA function 2) Integration test: E.g. running a macro or testing the effect of a cell change on the whole workbook 3) Cell logic test: Test dynamic tables 4)Alternative implementation test: Comparison with a function from (a tested) 3rd party lib
- 19. 1a) VBA unit test 19
- 20. 1a) Cell formula unit test 20
- 21. 2) VBA integration test 21
- 22. 3) Cell logic test 22
- 23. 4) Alternative implementation test 23
- 24. Run the tests 24 To run a single test: python -m unittest test_mybook.TestMyBook.test_vba_unittest
- 25. Unittest runners/frameworks 25 1) unittest: built-in 2) pytest 3) nose/nose2
- 26. Continuous integration (1/2) 26 • Write unit tests for Excel files with xlwings • Run them automatically on the server after pushing changes to your Git repo Example: Gitlab Pipeline