It is recommended that participants have advanced spreadsheet skills although previous programming experience is not required.
Visual Basic for Applications (VBA) is a programming language which extends the functionality of the Microsoft Excel spreadsheet. As our skill with the spreadsheet reaches some critical level of mastery, the next logical step to improving workplace productivity is through VBA. Having built an efficient spreadsheet model to solve a business problem, it is often necessary to replicate such models using VBA.
This introductory course extends the functionality of Excel by developing the foundation for modular programming using VBA. The ability to create simple sub-procedures provides the important first step in automating routine tasks in Excel. With practice and experience it is then possible to develop more complex procedures that will ultimately lead to dramatic productivity improvements.
Upon completing this seminar, participants will be able to:
Understand the basics of VBA and how it adds functionality to Excel
Understand the struPrerequisites:
It is recommended that course participants have advanced spreadsheet skills and have completed the Level One course (introduction to VBA).
This workshop builds on the concepts introduce in the Level One course. Automating tasks in Excel is a logical next step for those with a high degree of proficiency with spreadsheets. This is achieved to some degree using the ‘macro recorder’. Using the ‘recorder’ in a range of different scenarios will provide important insights about the process of automation and serve as the basis in understanding the code it generates. For those skilled in this macro language, enormous productivity and efficiency gains may be achieved. Routine reports may be produced with the click of a button and many common tasks automated.
Each Microsoft Office application has a unique set of coding instructions referred to as the object model - this workshop focuses on the Excel object model. The code generated by the macro recorder is the programming language VBA (Visual Basic for Applications). A great approach to follow in learning this language is to employ the macro recorder while completing simple Excel tasks, such as creating charts and Pivot Table, and then examine the code generated.
As might be expected with any formal language, a solid grounding in the structure of the language will provide significant benefits over time. The power of VBA becomes more apparent as one’s knowledge of the language increases and with the experience gained from using it to automate progressively more complex tasks. A commitment over time is therefore necessary to acquire a mastery of the language.
Key topics covered:
Generate code by using the Macro Recorder for specific Excel tasks
Understand various types of Excel objects
Understand and use the code generated by the macro recorder
Learn about naming and code writing conventions
Use the macro recorder to generate and understand the code required for the creation of Pivot Tables and Charts
Use VBA and MS Outlook to email Excel files and add data to Word documents
Participants will receive detailed notes and a set of example files to take away with them, thus enabling them to perfect their technique after the session.
The following should attend:
those who have advanced spreadsheet skills
those who need to improve their efficiency in using Excel
those who desire to develop their skills in Visual Basic for Applications
Dr Vince Bruno
Dr Vince Bruno is a lecturer in the School of Business IT & Logistics at RMIT University. He has worked in IT industry and academia.Academically he has focused on the Information Systems area for teaching & learning and research activities.
Vince has extensive experience teaching IT related courses from database, programming and the Microsoft range of products. In the research area he has predominantly focused on qualitative research methods having taught and supervised PhD students. He has a Bachelors and Masters in Computer Science and completed a PhD in Information Systems that examined the improvement of usability activities in IT projects.