Thank you for sending your enquiry! One of our team member will contact you shortly.
Thank you for sending your booking! One of our team member will contact you shortly.
Course Outline
Macros
- Recording and editing macros
- Where to store macros.
- Assigning macros to forms, toolbars, keyboard shortcuts
VBA Environment
- Visual Basic Editor and its options
- Keyboard Shortcuts
- Optimizing the environment
Introduction to procedural programming
- Procedures: Function, Sub
- The data types
- The conditional statement If...Then....Elseif....Else....End If
- Instruction Case
- Loop while, until
- Loop for ... next
- Instructions break the loop(exit)
Strings
- Combining strings (concatenation)
- Conversion to other types - implicit and explicit
- Features processing strings
Visual Basic
- Download and upload data to a spreadsheet (Cells, Range)
- Download and upload data to the user (InputBox, MsgBox)
- The declaration of variables
- The extent and lifetime of variables
- Operators and their priorities
- Options modules
- Create your own functions and use them in a sheet
- Objects, classes, methods and properties
- Securing code
- Security code tampering and preview
Debugging
- Processing step
- Locals window
- Immediate window
- Traps - Watches
- Call Stack
Error handling
- Types of errors and ways to avoid
- Capturing and handling run-time errors
- Structures: On Error Resume Next, On Error GoTo label, On Error GoTo 0
Excel Object Model
- The Application object
- Workbook object and a collection of Workbooks
- Worksheet Object and Collection Worksheets
- Objects ThisWorkbook, ActiveWorkbook, ActiveCell ....
- Object Selection
- Collection Range
- Object Cells
- Display data on the statusbar
- Optimization using ScreenUpdating
- The time measurement by the method Timer
The use of external data sources
- Using ADO library
- References to external data sources
- ADO objects:
- Connection
- Command
- Recordset
- Connection string
- Create connections to different databases: Microsoft Access, Oracle, MySQL
Reporting
- Introduction to the SQL language The basic structure of SQL (SELECT, UPDATE, INSERT INTO, DELETE) Calling a Microsoft Access query from Excel Forms to support the use of databases
Requirements
Ability to work with a spreadsheet, basic knowledge (references, ranges, sheets, ...). No knowledge is required to create macros, SQL, or write code in VBA.
Testimonials
a lot of exercise
Małgorzata Jasniecka
Possibilities to practice all the knowledge gained during the training in additional tasks.
Wioleta Kubczak
I generally enjoyed the practical examples.
Marika Agius
I liked the step by step and hands on realistic data examples.
CARL MIFSUD
I mostly liked the hands-on practice and exercises.
Godwin Spiteri
Tamil has an excellent way of explaining things. He speaks clearly and will take the time to explain things individually when necessary.
Theresa Fenech
The trainer was very well prepared and encouraged interaction with the group. The exercises were very interesting and enabled us to utilize the knowledge on the subject in a practical context.
Tomasz Kolbuszewski
Trainer was calm and very patient to explain in a way that everyone understands, even those with basic knowledge of excel.
Sera Farrugia
I appreciated its applicability to work related issues.
Ranier Buhagiar
I appreciate that the training was customized to our company's needs.
Rosanne Tanti
I liked the fact that we were a small group and therefore the trainer was able to offer individual attention to each trainee.
Claire Pace
tempo and help adapted to the participants' needs
Dariusz Leszczak-Phong
Broad knowledge of the lecturer
Michał Jąkalski
A lot of shortcuts and such flavors that improve work.
DNV GL Business Assurance Poland Sp. z o.o.
work on our real files
Katarzyna Grodecka
Very good introduction to VBA, numerous examples, getting to know many previously unknown functionalities
Przemysław Naskręt
What I liked most was the very practical approach, the ability to work on files on which we actually work every day and to adapt the training to our needs, our internal work specifics.
Anna Jarocka
the scope of training to stick to topics; a lot of examples
DNV GL Business Assurance Poland Sp. z o.o.
The coach had time for all participants and made sure that no one was left with mistakes / unresolved task, and yet there was not enough time to implement the planned material
Anna Niestępska
Great!
Shangshu Li - AE Industry GmbH
the content is useful and the trainer gave us information on what to do in the future if we encounter problems and questions
International Trading Fashion & Apparel Supply Limited
Related Courses
Access VBA
21 hours
VBA For Access & Excel
21 hours
Excel Advanced with VBA
21 hours
Analysing Financial Data in Excel
14 hours
Excel For Statistical Data Analysis
14 hours
Excel Advanced
14 hours