Excel VBA Programming - Getting Started | 1 - Introduction

Summary

Join software engineer Boris Passkiver as he introduces you to Excel VBA programming in this comprehensive guide. Designed for beginners, the course covers the basics of Visual Basic for Applications (VBA), an object-oriented language used to automate tasks in Microsoft Office applications like Excel. You'll learn to write macros, create custom functions, react to user events, and more. With VBA, you can enhance your productivity, reduce redundancy, and even grow your technical skills in a familiar work environment. Get ready to dive into VBA and transform your Excel workflow.

Takeaways

• 📘 VBA stands for Visual Basic for Applications, an object-oriented programming language integrated within the Microsoft Office ecosystem.
• 👋 The course is designed for complete beginners with basic Excel knowledge, even those unfamiliar with programming concepts.
• 🛠️ VBA is used to automate tasks in Microsoft Office applications, including Excel, Word, PowerPoint, Access, and Outlook.
• 🔁 VBA excels at handling repetitive tasks, performing the same operations across multiple spreadsheets, workbooks, or rows.
• 💡 With VBA, you can create custom Excel functions that aren't available by default in the application.
• 🔑 VBA can react to user events, such as opening a workbook or entering data into a cell, to trigger actions dynamically.
• 📊 VBA enables the creation of custom user interfaces, known as user forms, to streamline data input and reduce errors.
• 🤝 Although not covered in the course, VBA can facilitate communication between different Office applications.
• 🌐 VBA is a valuable skill with a long lifespan, supported in Windows versions of Excel through 2036.
• 📈 Learning VBA can enhance productivity, provide an introduction to programming, and is beneficial in various industries, including finance.
• 📚 The prerequisites for the course include participation, experimentation, and an intermediate knowledge of Excel, along with basic understanding of data types.

Q & A

What is the full form of VBA?

VBA stands for Visual Basic for Applications, which is an object-oriented programming language used to automate tasks in Microsoft Office applications.

Who is the instructor of the 'Excel VBA Programming the Complete Guide' course?

The instructor of the course is Boris Passkiver, a software engineer based in New York City.

What is the prerequisite for learning VBA according to the script?

The prerequisite for learning VBA is a little bit of basic Excel knowledge, and the course is designed for complete beginners.

What is the purpose of VBA in the context of Microsoft Office applications?

VBA is used to automate tasks in Microsoft Office applications like Excel, Word, PowerPoint, Access, Outlook, and more by using code snippets called procedures or macros.

How can VBA help in reducing the redundancy of an Excel workflow?

VBA can help by performing repetitive sequences of steps automatically, creating custom Excel functions, reacting to user events, and rendering custom user interfaces, thus reducing manual effort and the potential for errors.

What are some of the common Excel functionalities that the script suggests one should be familiar with before starting VBA?

The script suggests familiarity with common functionalities like changing font size, navigating across spreadsheets, opening and saving workbooks, and basic functions such as SUMIF, COUNTIF, and VLOOKUP.

Why is it recommended to have an intermediate knowledge of Excel before learning VBA?

An intermediate knowledge of Excel is recommended because VBA deals with interacting with existing Excel functionalities, and understanding these functionalities will make it easier to write code to manipulate them.

What is the historical connection between VBA and the BASIC programming language?

VBA is a modern dialect of the BASIC language, which dates back to the 1960s. VBA is inspired by the syntax and logic of BASIC but is a completely different language.

What version of Excel is recommended for the course, and why?

Excel 2016 for Windows is recommended because it is the latest version at the time of the course recording, and it offers the least likelihood of running into errors and compatibility issues.

Why is Excel for Mac not supported for this VBA course?

Excel for Mac is not supported because there are major differences between the Windows and Mac versions of Excel, especially in terms of VBA support, which makes it challenging to cover both platforms in the same course.

What is the importance of participation and experimentation when learning VBA from the course?

Participation and experimentation are crucial because writing code oneself is the best way to learn. It involves trying different things, making mistakes, and dealing with bugs, which accelerates the learning process.

Outlines

00:00

📘 Introduction to Excel VBA Programming

Boris Passkiver, a software engineer, introduces the course on Excel VBA programming, an object-oriented programming language within the Microsoft Office ecosystem. Aimed at beginners, the course covers the basics of programming, the concept of object-oriented programming, and the automation of tasks in Office applications through VBA. It highlights the use of VBA for repetitive tasks, creating custom Excel functions, responding to user events, and custom user interfaces. The potential for VBA to communicate between different Office applications is also mentioned, emphasizing the value of learning VBA for productivity and as a stepping stone into programming.

05:01

🛠 Benefits and History of Learning VBA

The second paragraph discusses the advantages of learning VBA, such as enhancing productivity, introducing programming concepts within a familiar environment, and its value in the job market with a guaranteed presence until 2036. The history of VBA is traced back to its roots in BASIC and its evolution through Visual Basic, with the first version of VBA released in 1994 with Excel 5. The paragraph also emphasizes the importance of hands-on experience and experimentation in learning VBA effectively.

10:03

📝 Course Prerequisites and Technical Requirements

The final paragraph outlines the prerequisites for the VBA course, including the necessity of a working copy of Microsoft Excel, with a recommendation for Excel 2016 due to its features and support for VBA. It advises against using outdated versions or Excel for Mac due to compatibility issues. The paragraph also suggests intermediate knowledge of Excel and familiarity with common functions and features, as well as a basic understanding of data types, to get the most out of the course. The importance of active participation and trying out different coding scenarios is reiterated.

Keywords

💡VBA

VBA, which stands for Visual Basic for Applications, is an object-oriented programming language integrated within the Microsoft Office suite. It is designed to automate tasks and is the central theme of the video. The script introduces VBA as a powerful tool for beginners and experts alike to enhance their Excel experience by automating repetitive tasks, creating custom functions, and responding to events.

💡Object-Oriented Programming

Object-oriented programming is a programming paradigm that uses objects, which can contain data and code, to design applications. In the context of the video, VBA is described as an object-oriented language, meaning it organizes code into objects, making it easier to manage and reuse, which is a key concept for learners new to programming.

💡Macros

Macros in the script refer to the automated sequences of operations that VBA can perform in Excel. They are essentially the procedures or snippets of code that allow users to record and execute repetitive tasks with a single command, demonstrating the practical utility of VBA in simplifying and streamlining workflows.

💡Automation

Automation is a key theme in the video, highlighting how VBA can perform repetitive tasks without manual intervention. The script mentions automating processes such as modifying daily reports across multiple spreadsheets, showcasing the efficiency gains that VBA brings to Microsoft Office applications.

💡Custom Functions

The script discusses how VBA can be used to create custom functions in Excel, beyond the 400+ functions already available in Excel 2016. This feature allows users to tailor Excel to specific needs, such as creating a unique formula for a business analysis, thus personalizing the spreadsheet application to individual requirements.

💡Events

In the context of VBA, events are actions or occurrences that can be captured and responded to by a program. The video script gives examples of user events like opening a workbook or entering data into a cell, which can trigger predefined actions, illustrating the dynamic interaction between the user and the application through VBA.

💡User Forms

User forms in VBA are custom interfaces that can be created to interact with users, similar to web forms. The script describes how these forms can include input fields and buttons, enabling a more controlled data entry process, reducing errors, and enhancing user experience within Excel.

💡Technical Skills

The video emphasizes the enhancement of technical skills as a result of learning VBA. It positions VBA as a valuable tool for increasing productivity, not just for general Excel users but also for business analysts, highlighting the broader professional development opportunities that come with mastering VBA.

💡Programming

Programming, in the video, is introduced as the concept of giving instructions to a computer. VBA serves as an entry point for those new to programming, offering a familiar environment with Excel as the basis for learning new concepts, making the transition into the world of coding more accessible.

💡Data Types

Understanding data types is fundamental in programming, and the script suggests that learners should have a basic knowledge of them, such as the difference between numbers and text, or integers and floating-point numbers. This knowledge facilitates the manipulation and processing of data within VBA.

💡Prerequisites

The script outlines the prerequisites for learning VBA, emphasizing the importance of having a working knowledge of Excel and participation through experimentation. It suggests that hands-on experience with Excel functions and features, as well as a willingness to engage with the programming concepts, are necessary for successfully learning VBA.

Highlights

Introduction to Visual Basic for Applications (VBA) as a powerful tool in the Microsoft Office ecosystem.

VBA is an object-oriented programming language designed for beginners with basic Excel knowledge.

VBA automates tasks in Microsoft Office applications such as Excel, Word, PowerPoint, Access, and Outlook.

Explanation of VBA procedures or macros as sequences of steps to instruct the computer.

VBA's capability to replicate processes across multiple worksheets and workbooks efficiently.

Creating custom Excel functions with VBA to meet specific project or business needs.

VBA's ability to react dynamically to user events like opening a workbook or entering data in a cell.

Utilizing VBA to render custom user interfaces, reducing errors and enhancing data input control.

VBA's potential for cross-application communication, though not covered in the course.

The importance of learning VBA to reduce redundancy in Excel workflows and enhance productivity.

VBA as a gateway to growing technical skills and an introduction to computer programming within a familiar environment.

VBA's value as an introductory programming tool due to pre-existing Excel knowledge.

Microsoft's commitment to supporting VBA in Windows versions of Excel through 2036.

The historical development of VBA from its roots in the BASIC language of the 1960s.

Technical requirements for the course, emphasizing the need for a working copy of Microsoft Excel.

Recommendation for intermediate Excel knowledge and familiarity with common functions for course participation.

The necessity of hands-on participation and experimentation for effective learning of VBA.

Course prerequisites and the importance of trying different things and not being afraid to encounter bugs.

Transcripts

hi there and welcome to excel VBA programming the  complete guide my name is Boris passkiver and I'm  

a software engineer based in New York City and I'm  really excited to introduce you to one of the most  

powerful tools available in the Microsoft Office  ecosystem Visual Basic for applications so what  

is Visual Basic for applications Visual Basic for  applications also known by the popular acronym VBA  

is an object oriented programming language now if  you're unfamiliar with programming if you've never  

written a line of code before if the term object  oriented is unfamiliar to you that's totally fine  

this course is designed for complete beginners all  you need is a little bit of basic Excel knowledge  

with that said we know from popular culture that  programming is the idea of giving instructions to  

the computer a sequence of steps for the computer  to follow in order to execute some kind of task  

when it comes to VBA VBA is used to automate tasks  in Microsoft Office applications like Excel word  

PowerPoint access Outlook and more VBA consists of  Snippets of code that we call procedures or macros  

some people also call them subroutines routines or  programs all essentially different names for the  

same thing but all of these Snippets of code are  just sequences of Steps step one do this step two  

do this step three do this imagine that you have  a real life scenario where you want to explain to  

a co-worker how to perform some kind of process  in Excel like modifying a daily report you might  

call them over and say the first thing I need to  do is to highlight all of the headers in our top  

row and make them bold then change the font to  this font change the font size to this perhaps  

add a border next at the bottom of the worksheet  I want to perform a sum calculation on all of the  

columns perhaps after that I want to add a chart  or a table or a pivot table and then let's say  

they replicate this entire process across multiple  worksheets across multiple workbooks the exact  

same way that you describe this office process to  a co-worker is the exact same way that we're going  

to describe it to VBA we're just going to use a  different syntax a different language in order  

to communicate with the computer but anything that  you can do in Excel in the main interface you can  

tell VBA to do with code so what can VBA help you  do in Excel well VBA is really great at repetition  

it can help you perform the exact same operation  on multiple spreadsheets or multiple workbooks  

or multiple rows whenever you need to execute a  repetitive sequence of steps let's say you run a  

report daily or weekly or monthly or every hour  whenever you find yourself doing the exact same  

process over and over again you can write a  macro that's going to automate the process of  

completing all those steps in order VBA can also  help you to create brand new Excel functions to  

use in the main Excel application by default Excel  2016 includes over 400 functions but perhaps you  

need a custom function for your project or your  business needs or your company you can actually  

use VBA to create brand new Excel functions you  can also use VBA to react dynamically to events  

like user interactions what do I mean by user  events well imagine things like the user opening  

a workbook or creating a brand new worksheet or  even entering or deleting a value in a Cell all  

of these things are called events they shoot off  at a given moment in time when something occurs we  

can actually use VBA to dynamically react to those  events to do something when they occur for example  

we can trigger a workbook to save whenever the  user enters a value into a cell we can also use  

VBA to render custom user interfaces we call these  things user forms and they resemble very much like  

a real life web form where we have inputs we may  have radio buttons or buttons to click or check  

boxes to check and after the user completes the  user form we can take the data that they provided  

and use it to modify the worksheet somehow  it's a really great approach for reducing the  

total amount of errors that the user can commit  and asking them to submit their data in a much  

more controlled environment finally although it's  not covered in the course VBA can also be used to  

communicate between different office applications  so for example if you want to use Outlook the  

email app to send an email to every single email  that's listed let's say in a spreadsheet you can  

do that and although this course doesn't cover a  cross app communication as soon as you understand  

the basics of how VBA Works in Excel it's a lot  easier to apply this concept to other office apps  

and especially make them communicate with each  other so why learn VBA well as I mentioned VBA  

reduces the redundancy of your Excel workflow  just about everybody uses Excel from time to  

time whether it's for professional reasons or for  personal reasons like calculating your personal  

finances or even calculating Sports statistics  Microsoft Office is installed on over 750 million  

computers worldwide so Excel is a really really  popular spreadsheet application the most popular  

one in the world and it's highly likely you're  going to be working with it from time to time so  

why not pick up a skill that's going to help make  you more productive and more automated within it  

VBA will grow your technical skills as a general  Excel user and as a business analyst if you've  

reached a threshold in your knowledge of excel if  you're wondering what's the next best thing that  

I can pick up within this application to make me  a more productive employee VBA is undoubtedly the  

number one answer there is no other tool available  within any Office application that will give you  

as much power as VBA VBA also offers an excellent  introduction to computer computer programming in  

a familiar work environment now I mentioned  earlier that you don't have to know anything  

about programming to start this course if you are  interested in learning programming for the first  

time and you picked up another language outside of  EBA you usually have to learn a lot of things to  

get started you have to learn the context in which  the language operates you might have to pick up a  

brand new text editor it's a lot harder to get  started with something from scratch the great  

thing about VBA is that most users have at least  some basic knowledge of Excel how we enter values  

how we enter functions the general purpose of a  spreadsheet and because you have that pre-existing  

knowledge you have a really strong foundation  and a lot of the complicated things that you'd  

have to learn in other programming languages you  don't have to learn here you can just focus on  

the brand new Concepts within the context of  something that is already very familiar to you  

finally VBA remains a valuable skill in today's  job market Microsoft itself has said that VBA will  

be around in Windows versions of excel through  2036 that's 20 years from now so this isn't going  

anywhere it's here to stay and it's used a lot  across many different Industries including uh  

Finance so it's an incredibly valuable skill  but even if you don't work with excel in your  

day-to-day job it's an incredibly amazing thing  that you can pick up as a general Excel user  

I want to take a brief moment here to discuss  the history of VBA VBA is a modern dialect of  

a language called basic that goes back all  the way to the 1960s the two are completely  

different languages VBA is just inspired by the  syntax and logic of basic that's what we mean by  

modern dialect in 1991 Microsoft released the  visual basic programming language for Windows  

that's also a completely separate language that's  used to build Standalone desktop applications on  

windows so the important thing that I want to  emphasize here is that basic Visual Basic and  

Visual Basic for applications are three completely  different things in 1994 Microsoft released the  

first version of VBA Visual Basic for applications  with Excel 5. so again VBA was inspired by Visual  

Basic which itself was inspired by basic but VBA  can only be used within the context of existing  

office applications it can't be used to develop  something outside of an off Office application so  

key takeaway both VB and VBA are inspired by basic  but they are completely different languages I also  

want to provide a really cool throwback here this  is actually what Excel looked like back in 1995  

the very first version in which VBA was introduced  as I look at this I'm kind of surprised because as  

much as it looks a little bit dated aesthetically  some of the things are not as stylish as they are  

in today's version of excel what I'm really most  surprised by is how much things haven't changed I  

feel like if I had to dive back into this version  of Excel and use it for my day-to-day spreadsheet  

needs I don't think it would be that difficult to  just go into this one and use it as well so it's  

really cool to observe how 20 years have Excel  development have led us here about how a lot  

of those common elements remain the same even in  this piece of software that is almost 20 years old  

all right so let's dive into the prerequisites  for the course before I dive into the technical  

requirements I want to really emphasize the  importance of participation and experimentation  

no matter how many tutorials you watch there  really is no substitute for writing code yourself  

you have to be willing to try out different  things yourself you have to be willing to make  

mistakes you have to be willing to deal with the  frustration of running into a bug and having to  

Google it and reading responses on stack Overflow  and the like no matter how effective a course is  

no matter how many video tutorials or quizzes  or assignments there are the single best way to  

learn is to try things out yourself so I strongly  recommend regardless of your Excel knowledge or  

your programming knowledge whenever you watch a  video in this course copy the code alongside in  

your own version of excel make sure you're not  just watching make sure you're participating  

and then after the video is done try out something  different try an alternate syntax don't be afraid  

to fail don't be afraid to run into bugs that's  the quickest way to learn now as far as the  

technical requirements you do need a working copy  of Microsoft Excel the good news is that VBA is  

pre-installed with Excel so if Excel is working  on your computer there's nothing more that you  

need to install or to purchase or to configure  we're all good to go for this course I strongly  

recommend Excel 2016 for Windows as of right now  at the end of 2017 and the beginning of 2018 when  

this course is being recorded this is still the  latest version of excel backwards compatibility  

issues are always possible this won't be an issue  with different versions of windows but may be an  

issue with different versions of excel so if you  have a more dated version of excel especially  

something really old like 2003 or 2007 versions  versions that are more than a decade old I do  

recommend investing a little bit and getting the  most recent version it is worth the upgrade there  

are a lot of brand new features introduced and  that will ensure the least likelihood for running  

into errors and backwards compatibility issues as  you proceed throughout the course I want to point  

out that Excel for Mac is not supported as much  as Microsoft has tried the there's still a lot of  

major staggering differences between the windows  version and the Mac version the Mac version really  

isn't great for VBA it has a slightly different  interface and it's just too much of a challenge  

to have to do deal with two different videos to do  the exact same thing on two different Platforms in  

addition Microsoft's also office offers Excel on  things like iPad and iPhone and an office web app  

in your browser but none of those versions support  VBA so they're also not covered by the course  

for this course I also recommend an intermediate  knowledge of excel I don't want to put any strict  

constraints on you I don't want to say things like  you need to know this function or this number of  

functions or this functionality but essentially  all this means is this won't be your first time  

using Excel you should have some familiarity with  this application with what it's used for with some  

of its most common functionalities you should know  how to change basic things like the font size how  

to navigate across different spreadsheets how to  open workbooks and save them those basic common  

beginner operations should all be very comfortable  to you I do recommend a general knowledge of  

really common functions like sumif countif and  vlookup this will come really handy and of course  

a little bit of experimentation with things like  pivot tables charts and graphs you don't have to  

know every detail you don't have to know how to  create you know two axes on a graph or all of the  

Nitty Gritty details of the pivot table but just  a little bit of familiarity with these things if  

you're if you've never played around with any of  these Excel features I do recommend spending some  

time with a general Excel course before diving  into VBA VBA deals with interacting with a lot  

of these existing Excel functionalities so if  you don't know them it's going to be hard to  

write code to deal with them finally I recommend  a basic knowledge of data types so you should be  

able to know the difference between a number and  text or even as a more complicated example between  

an integer versus a floating Point number the more  data types you know for example dates or booleans  

or in programming terms things like arrays  the easier the course will be to pick up for  

you but again as I mentioned you don't need any  concrete knowledge of programming to get started  

that's all there is to cover in this lesson I'm  really excited to teach you how to use VBA it's  

one of the most powerful things you can learn  within an office application to help you automate  

your workflow I really hope you're excited  as well and in the next lesson we'll begin  

our journey into VBA by enabling the developer  tab within Excel which will allow us to actually  

interact with visual basics for applications  hope you're excited and I'll see you there