SubjectsSubjects(version: 811)
Course, academic year 2017/2018
  
Spreadsheet Applications - N445008
Czech title: Tabulkové aplikace
Guaranteed by: Department of Computing and Control Engineering (445)
Actual: from 2014
Semester: winter
Points: winter s.:4
E-Credits: winter s.:4
Examination process: winter s.:
Hours per week, examination: winter s.:1/2 MC [hours/week]
Capacity: 25 / 25 (unknown)
Min. number of students: unlimited
Language: Czech
Teaching methods: full-time
Level:  
For type:  
Additional information: http://moodle.vscht.cz/course/view.php?id=24
Note: předmět je možno zapsat mimo plán
povolen pro zápis po webu
Guarantor: Finkeová Jana Ing. CSc.
Nachtigalová Iva Ing. Ph.D.
Annotation -
Last update: Finkeová Jana Ing. CSc. (11.07.2013)

The course focuses on creating spreadsheet applications and MS Excel spreadsheet templates that can automatically process the physico-chemical, economic or form data, numerical analysis, etc. The course presents an application development model of "what-if" approach using the advanced capabilities of a spreadsheet (without programming) and then MS Excel VBA programming language (programming language built into MS Office suite), which is necessary for programming applications.
Aim of the course -
Last update: Nachtigalová Iva Ing. Ph.D. (22.07.2013)

Students will be able to:

  • program custom functions, and standard and event macros in VBA
  • design custom dialog boxes in VBA for communication with users
  • edit user interface spreadsheet using RibbonX code
  • create templates and workbooks for automated data processing

Deskriptory -
Last update: Nachtigalová Iva Ing. Ph.D. (22.07.2013)

macros, VBA, document object model, application, spreadsheet, MS Excel

Literature -
Last update: Fialová Jana (30.09.2013)

R: Walkenbach J. Excel 2007 Power Programming with VBA, Wiley 2007, ISBN: 0470044012

R: Walkenbach J. Excel 2010 Power Programming with VBA, Wiley 2010, ISBN: 0470475358

Learning resources -
Last update: Finkeová Jana Ing. CSc. (11.07.2013)

http://spreadsheetpage.com/

Teaching methods -
Last update: Nachtigalová Iva Ing. Ph.D. (22.07.2013)

The 3-hour course starts with one-hour lecture followed by a two-hour thematic exercises in a computer lab where students solve tasks together and gradually step by step create sample applications like templates, custom functions, makros and dialog boxes. Each student works on his own computer at the same time regularly alternates with with other students at the teacher's computer and actively participates in the preparation of the specification in discussion with others. In the exercises, students learn to work with on-line resources as well.

Requirements to the exam - Czech
Last update: Nachtigalová Iva Ing. Ph.D. (12.09.2013)

  • Studenti jsou v rámci ověření studijních výsledků povinni absolvovat souhrnný test. V případě neúčasti na testu v řádném termínu musí studenti absolvovat test v náhradním termínu.
  • Souhrnný test ověřuje znalosti z látky probírané za celý semestr, trvá 50 minut a proběhne v 14. týdnu semestru; náhradní test proběhne ve zkouškovém období v dohodnutém termínu. K vypracování testu nelze využít žádných písemných či elektronických materiálů nebo pomůcek.
  • Klasifikovaný zápočet je udělován na základě bodového hodnocením testu a obdrží ho ti studenti, kteří získají alespoň 50 z maximálních 100 bodů.
Syllabus -
Last update: Finkeová Jana Ing. CSc. (11.07.2013)

1. Templates - types, editing, use, principles of creation.

2. Advanced MS Excel skills used in templates - conditional formatting, formatting own formats, data validation, search, etc.

3. Introduction to VBA - lexical structure, data types, constants, variables, arrays, control commands, VB Editor.

4. Function in VBA - declaration, input parameters, return values, error handling, debugging.

5. Object model in Excel VBA - object hierarchy, container handling, collections, methods, and properties.

6. Macros in VBA - declaration, parameters, calls, communication with users using built-in windows, error handling, debugging.

7. Project 1.

8. Events and VBA - objects and their events, OnTime onkey event.

9. User Interface - modification of ribbon, creating and editing RibbonX code.

10. UserForms in VBA - design, opening, closing, cancellations and other events, ActiveX controls.

11. Forms controls and ActiveX controls - inserting elements, editing features and format elements (in VBA), linking elements with procedures and cells.

12. Development of robust application with its own functions, macros and user interfaces.

13. Cooperation MS Excel with other programs in MS Office VBA, references to object libraries.

14. Project 2.

Entry requirements -
Last update: Finkeová Jana Ing. CSc. (11.07.2013)

Course assumes basic skills in MS Excel spreadsheet (addressing and formatting of cells, working with data, formulas and functions built-in worksheets, creating and formatting graphs) and knowledge of the standard Solver and Data Analysis.

Registration requirements -
Last update: Finkeová Jana Ing. CSc. (11.07.2013)

Applications of Computer Science

Class methods
Activity Credits Hours
Příprava na přednášky, semináře, laboratoře, exkurzi nebo praxi 0,5 14
1 / 4 14 / 112
Evaluation of a student
Form Balance
Aktivní účast na výuce 70
Průběžné a zápočtové testy 30

 
VŠCHT Praha