Become an Excel Programmer and You Can Finally Put Your Work On Autopilot!
Even with all of Excel's great features, and built-in tools, you might find yourself doing repetitive tasks, that take longer than they should.
Or the day will come when you say, "Oh, I wish there were a better way to do this!"
The good news is that Excel, and other Microsoft Office applications, comes with Visual Basic for Applications (VBA), a programming language that will extend the functionality of your Office applications.
In This Course You Get:
This is a VBA beginner's course that will get you on the road to being in that elite group of VBA programmers.
Learning to program can seem daunting at first, but I'm going to make it as easy as possible.
With that said, VBA is a huge topic, and yes, there are a lot of lectures in this course.
But don't stress out.
What I do go over will get you from, "I don't even like looking at VBA", to, "I can do this!"
NOTE: There are multiple ways to get the same result in VBA.
So the way I write a procedure or two might be slightly different than your co-workers code, or yours, and that's O.K.
If you're ready to cut your workload, automate your tasks, and learn a desirable skill while your at it, click the Take This Course button on the top right, and I will help start programming your Excel workbooks with VBA.
Some people write VBA, some record macros, and some do both.
Learn about Object Oriented Programming, your key to VBA.
Learn about Container objects in VBA
Before you can write VBA, you need to know how VBA refers to objects.
Get familiar with the different security settings that that dictate when your code will run.
Key Points for Part One: Introduction to Visual Basic for Applications
Now that you have recorded a macro, let's make sure it works, and take a look at the VBA code behind it.
There are two different modes that you can record you macros in: Absolute and Relative.
Now that you know about Relative mode, let's see it in action.
Learn how to create custom keyboard shortcuts for your Excel macros
Assign your macro to a button on the Quick Access Toolbar.
Assign your macro to a button on the Ribbon.
Assign your macro to a shape or to an image.
Assign your macro to a button in the Developer toolbox.
A macro project for quickly navigating through your Excel workbook
Key Points for Part Two: Understanding Macros
Time to write code. Time to get familiar with the Visual Basic Editor.
Learn about other important windows in the VBE
Windows in the VBE can float, or be docked.
Modules are where you will write your VBA. Learn how to add more modules, or remove the modules you know longer need.
Knowing how to Export and Import your modules is an important step to maintaining your VBA code.
Change the default module name into something more meaningful.
To be comfortable writing VBA, you need to be comfortable in the VBE.
Learn how to customize the VBE to meet your needs.
Browse through your project's available objects, and see their properties, methods, and events.
If you want to lock down your code, here is how you do it.
Key Points for Part Three: Getting to Know the Visual Basic Editor
An Introduction to Writing VBA
A couple of tips that will help with the speed and accuracy of writing your VBA.
Oh oh! Your code has an error in it, and a yellow arrow has appeared - what can you do to keep following along with the exercises?
Taking a look at the structure of SubProcedures
Using one SubProcedure inside of another.
Using one Function inside of another.
How can you call one procedure with the same name as another?
Comments can do more than just add notes to your code. Learn another import feature of COmments.
Using Objects in Excel VBA
Understanding and using Methods in VBA.
Understanding and using Events in VBA
Key Points for Section: Getting Started with Programming VBA
Learn about Variables, and how to declare them.
More than likely you are going to be declaring multiple Variables in a procedure, learn how.
Learn about Object Variables in Excel VBA and how it can help you when writing your code.
What are data types, and how can they be used in VBA?
You can also declare variable's data types with Identifier Type Characters
Learn how to use Constants
A refresher on Arithmetic operators.
An exercise using Byte
A description and example of Strings
A description and example of Currency Variables
A description and example of Date variables
A refresher on Logical operators.
Learn how to use the Colon operator in VBA
Bring multiple strings together with String Contatenation
When you want to wrap your VBA code around to multiple lines, use the Line Continuation Operator
When you want to wrap the text output to multiple lines, use the CRLF.
Using the With statement is a real time saver, find out why.
Scope is an important concept to understand. Learn about the three different kinds of Scope.
Key Points for Section: Getting to Know Variables, Data Types, and Operators
Creating an Excel workbook using VBA is easy, but there are a few things you should know.
Learn the simple lines of VBA code that will open workbooks, close current workbooks, or other workbooks.
Find out the difference between Worksheets and Sheets.
Represents the active sheet in the active workbook, or specified workbook.
Learn to add worksheets to your workbooks.
Learn to copy your worksheets.
Learn to use the Move method to organize your Excel workbooks.
Learn to use the Delete method you remove worksheets from your Excel workbooks.
Learn how to refer to cells in your workbooks
Follow along with this exercise on using operator and the cells object.
ActiveCell is an important property to know. Learn how you can use it in Excel VBA.
Learn how to use the Rows property to select areas of your Excel workbook.
Learn how to insert columns and rows into workbooks
Automatically adjust the width of the columns, or the height of the rows for the best fit.
Range is one of the most important, and most commonly used Excel VBA objects.
If you have named ranges in your Excel workbook, you can use VBA to change their properties.
The Select method is used to select a part of a worksheet.
CurrentRegion is a property of the ActiveCell object.
Use the End Property to select the cell at the end of the region that contains data.
Learn how to copy information on your worksheet to the clipboard, or to a specified locaion
Learn the different ways to use the Pate method in VBA
Learn to Paste the data that you have copied.
Font Object Members and examples of their use.
Learn how to clear the contents of your cells or ranges.
Represents the name of the object using it.
Represents the data stored in the object, such as a worksheet cell.
In this exercise you will change the worksheet tab’s name to reflect the value in cell A1, when the value in the cell changes.
Learn how to Sort the records in your workbooks.
Learn how to Filter the records in your workbooks.
Learn the simple line of code that allows you to remove duplicate records.
Use the PrintPreview method in Excel VBA to display your worksheets in Preview mode.
Use the PrintOut method in VBA to send your worksheets to your printer.
Key Points for Part Four: Writing VBA Code
Test your skills with this PDF exercise and Excel workbook.
Learn how to use Named Format expression to format your values.
Learn to use the Format Function to create Custom Number Formats
Learn to use the Format Function to create Custom Date/Time Formats
Learn to use the Format Function to create Custom String Formats
Format Currency values with the FormatCurrency function.
The FormatNumber format works similarly to FormatCurrency, the difference being no currency symbol is returned.
Learn how to use the FormatDateTime function
The FormatPercent format works similarly to FormatCurrency, and FormatNumber.
The difference being that the number is formatted as a percentage with a trailing % character.
An Introduction to Control Structures
The If…Then statement is a simple control that tests whether a condition is true or false.
With an If…Then…Else statement, the control can take a secondary course of action. If the condition is true, then an action occurs. If the condition is false, take an alternate action.
When you have a large number of conditions to examine, the If…Then…Else statement will go through each one of them. Visual Basic offers the option of jumping to the statement that applies to the state of a condition.
Key Points for Part Five: Control Structures
What do you think of the course so far?
Is it helpful?
Please leave a Review, or a Star Rating, letting other students know about the course.
A loop is a sequence of instructions that is continually repeated until a certain condition is reached.
If you know with certainty how many times you want to execute a statement, you can use the For...Next expression.
You will create a simple procedure that executes a For…Next loop that populates data into the first six rows of a worksheet.
If you want to control how the incrementing processes, you can set your own, using the Step option.
Answers to the questions in the previous lecture.
For Each loops are typically used with collections and arrays
In this example you want to remove the hyphens from each cell in the A column.
A continuation of the previous For Each lecture
You can also exit a For loop. To do this, in the section where you want to stop, type Exit For.
The Do Until loop repeatedly executes a section of code until a specified condition evaluates to True.
An example of using a Do Until Loop
The Do While loop repeatedly executes a section of code while a specified condition continues to evaluate to True.
An example of using a Do While Loop
You can also exit a Do loop. To do this, in the section where you want to stop, type Exit Do.
Key Points for Part Six: Loops
Test your Looping skills with this PDF exercise and Excel workbook.
Inspiring and Empowering People to Achieve Their Goals through Learning.
Sharing the strategies, and tips you need to regain control at work, and optimize productivity.
IsaBel has been serving students since 1999. Successfully self-employed, she is a trusted consultant for businesses large and small. She is a publisher, and author of several highly-rated eBooks.
IsaBel has been certified many times over as a Database Administrator, IT Professional, Developer, Network Administrator, and even Certified Ethical Hacker. Even with a technical background, she is a down to earth half-marathoner, who enjoys her family, good food, and sports.
IsaBel grew up around teachers, so education was always important to her.
All her life she has seen how gaining even a little more knowledge can make such a big difference in everything from the way you speak, to the way you cook, the way you parent, to the way you fix a leaky faucet, the speed in which you perfect your spreadsheets, to the upgrade your resume can get by learning SQL.
She was taught the importance of always learning, and works hard daily to help others, like you, meet your goals.
Enroll Now, then ▶Subscribe to My YouTube Channel, because Change Matters, Growth Matters.