Visual Basic for Applications (VBA) in Excel Part 2
Program Overview
This Visual Basic for Applications (VBA) in Excel Part 2 course enhances VBA skills, exploring advanced object and programming techniques. Participants learn to create complex VBA procedures and functions, automate tasks, and interact with other Office applications. The course is delivered in small groups by an industry professional and is beneficial for those seeking to extend Excel's functionality and automate data-intensive tasks.
Program Outline
Visual Basic for Applications (VBA) in Excel Part 2 Short Course
Degree Overview:
This course builds upon the knowledge obtained in Visual Basic for Applications (VBA) in Excel Part 1.
Objectives:
- Enhance your VBA skills in Excel.
- Explore advanced object and programming techniques.
- Learn to create more complex and robust VBA procedures and functions.
Program Description:
- Applications Covered:
- Deep dive into programming constructs
- Dynamically find ranges using Excel range objects
- Utilize the Excel object model beyond standard workbooks, worksheets, and ranges
- Build simple applications using multiple subroutines and functions
- Automate tasks such as creating charts and pivot tables
- Interact with Access, Outlook, PowerPoint, and Word using VBA libraries
- Software Version: Office 2019
Outline:
- Review:
- The Excel API: Workbooks, Worksheets, and Range objects
- Object Browser
- Generic programming concepts from part 1: data variables, If statements, For Each loops, Object variables
- Select Case statement
- Comprehensive review:
- Range objects:
- Dynamically find ranges (especially target ranges for copying data)
- Copy data between sheets and files
- Review of Comments (called Notes in later Excel)
- Debugging errors:
- Step into, step over, step out
- Debug.Print and Debug.Assert
- Breakpoints
- Watch windows
- Immediate and Locals windows
- Validating input, type conversion functions
- Looping structures:
- Review of for-each
- While loops
- For-next loops
- Nested loops
- Performance
- Automating built-in Excel functionality:
- AutoFilter, Go To, WorksheetFunction
- Avoiding loops
- Improving Macro Recorder code
- Arrays:
- Static and dynamic arrays
- Variant arrays
- Interacting with the user:
- MsgBox constants
- Application.InputBox
- GetOpenFilename, GetSaveAsFilename
- Workbooks and filesystem:
- Opening, saving, Dir statement, FSO object
- Workbook and Worksheet events
- Complex applications:
- Structuring programs: separate macros for separate tasks
- Variable scope
- Consistent programming style
- Manipulating charts and pivot tables:
- Automating the creation of multiple charts and pivot tables
- Error-handling:
- On Error Resume Next
- On Error GoTo
- Error-handling template
- External libraries:
- Introduction to automating Office applications: copying data between applications
- Basics of sending Outlook emails
- FileSystemObject (FS0) and dictionaries
- Early and late binding
- Microsoft documentation and review of useful VBA resources
Assessment:
- There is no formal assessment in this course.
Teaching:
- The course is delivered by an industry professional in small groups.
- It follows a practical format with weekly classes held on weekday evenings or Saturday daytime.
Other:
- Attendance of over 70% earns an official City, University of London certificate.
- The course is not formally accredited.
- City Short Courses operate on the academic year schedule:
- Autumn - October
- Spring - January
- Summer - April
Careers:
This course is beneficial if you wish to learn how to extend Excel’s functionality, leverage all its power, and automate it. It enhances your ability to:
- Use VBA for data analysis, visualization, and manipulation
- Build custom tools for data-intensive tasks
- Automate repetitive processes and workflows
- Create user-friendly applications for yourself or others
- Gain a competitive edge in data-driven roles
- Improve productivity and efficiency
- Open doors to opportunities in various industries, including finance, accounting, marketing, and operations.
Note:
This short course follows on from Visual Basic for Applications (VBA) in Excel Part 1. Participants should have completed Part 1 or possess equivalent knowledge.
City University of London
Overview:
City, University of London is a public research university located in London, England. It is known for its focus on business, practice, and the professions, offering a wide range of undergraduate and postgraduate programs. The university is renowned for its strong academic reputation and its commitment to providing students with a high-quality learning experience.
Services Offered:
City University of London provides a comprehensive range of services to its students, including:
Library Services:
Access to extensive library resources, including books, journals, databases, and online resources.Moodle:
An online learning platform for accessing course materials, submitting assignments, and communicating with instructors.Email:
A university-provided email account for official communication.Staff Directory:
A searchable directory of staff members and their contact information.Term Dates:
Information on the academic year and term dates.Room Booking:
A system for booking rooms on campus for meetings, events, and other purposes.Schools and Departments:
Information on the various schools and departments within the university.Student Life and Campus Experience:
City University of London offers a vibrant and diverse student experience, with a strong emphasis on:
London Experience:
The university's location in London provides students with access to a wide range of cultural, social, and professional opportunities.Sports:
A variety of sports clubs and facilities are available for students to participate in, both competitively and non-competitively.Social Activities and Groups:
Numerous student societies and groups cater to diverse interests, providing opportunities for social interaction and personal development.Student Wellbeing:
The university offers a range of support services to ensure students' health and wellbeing, including learning support, personal tutoring, and counseling.Career Development:
The university provides career guidance and support services to help students prepare for their future careers.Key Reasons to Study There:
Strong Academic Reputation:
City University of London is consistently ranked highly in national and international rankings.Focus on Business, Practice, and the Professions:
The university's programs are designed to provide students with the skills and knowledge they need to succeed in their chosen careers.Location in London:
The university's location in the heart of London provides students with access to a wealth of opportunities.Vibrant Student Life:
City University of London offers a diverse and engaging student experience.Excellent Career Support:
The university provides comprehensive career guidance and support services.Academic Programs:
City University of London offers a wide range of academic programs across various disciplines, including:
Business and Management:
Bayes Business School is renowned for its programs in finance, accounting, marketing, and entrepreneurship.Law:
The City Law School is a leading institution for legal education, offering programs in law, international law, and legal practice.Health and Psychological Sciences:
The School of Health & Psychological Sciences offers programs in nursing, midwifery, psychology, and other health-related fields.Science and Technology:
The School of Science & Technology offers programs in computer science, engineering, mathematics, and other STEM fields.Communication and Creativity:
The School of Communication & Creativity offers programs in journalism, media, performing arts, and language studies.Other:
Merger with St George's:
City University of London has merged with St George's, University of London, forming a new institution called City St George's, University of London.Awards and Accreditations:
The university has received numerous awards and accreditations for its teaching, research, and commitment to equality and diversity.Global City:
City University of London is a global institution with a strong international presence.University of London:
City University of London is a member of the University of London, a federation of 18 independent colleges and institutes.Entry Requirements:
Part 1: You must have attended Visual Basic for Applications (VBA) in Excel Part 1, and completed it with good grades. Part 2: You also need a good understanding of basic Excel and VBA Part 1 as they will be expanded on in Part 2. If you have not completed Part 1 or have poor grades in Part 1 you may take a test that determines if Part 2 is suitable for your knowledge level. If it is not determined satisfactory you may still take the course but you are not eligible to receive the City University of London certificate.
