Excel 2013/2016 VBA Programming for Beginners
Learning how to use the VBA (Visual Basic for Applications) programming language in Excel will give you the ability to create actual data applications in Microsoft Excel. By combining the two, you wield the power of Excel and you can use VBA to automate its functionality. Let’s take a look at some of the things that you might learn if you take a class on Programming Excel with VBA.
Working with Procedures and Functions
These small building blocks of code allow you to encapsulate sets of instructions that can be executed multiple times and called from multiple locations. Think of them as useful tools that can be used whenever you need them. They prevent you from needing to write the same code over and over since they can be written in one block and called from anywhere.
They not only cut down on redundant code but reduce errors as well. A bug can be fixed in one function and everywhere that it is called can reap the benefit instead of having to fix it in multiple locations.
Understanding Objects
Objects are used to define things or entities in code. Objects consist of properties, methods and events. The properties help to describe the object. A real-world example of an object could be a boy. Some of the boy’s properties or attributes could be hair color, eye color, height, weight, etc. Those things describe what the boy looks like. The methods describe what the boy can do.
For example, the boy can run, jump and play. In code, these methods would be procedures and functions with instructions on how the boy completes these tasks. Finally, events are instructions on what to do when a specific event occurs. An example of this would be if someone were to throw a ball to the boy. If a ball is thrown, then the event would contain instructions on how to catch the ball.
Using Expressions, Variables and Intrinsic Functions
These structures are intended to help make your life easier. You can build expressions by combining numbers and Excel’s built-in operators to perform any calculation that you can think of. You can also use variables that can be set by expressions and use them throughout your code. Variables can also be passed into functions as parameters.
Functions and procedures are larger and more complex combinations of smaller expressions and variables. Functions are like macros in that they do the same thing over and over without you having to program the same thing each time you want to execute the same functionality.
Controlling Program Execution
VB code can be inserted into Excel to control the execution of your programs. This code can be used to fire when events occur, such as when the text in a text box changes or a button is clicked. You can also pop up message boxes and other dialogues to ask the user questions or provide cues on inputs or validations to help guide the user through your functionality more gracefully.
Working with Forms Controls
Forms and controls can be constructed to make for a more robust user interface. Simple controls, such as text boxes, labels and buttons can be organized on a form to help create screens to allow users to input the desired data to execute your functionality. This is much nicer than just entering values into boxes on a spreadsheet and provides for a richer user experience.
Working with the Pivot Table Object
Pivot tables are very powerful data organization structures. They are also very popular in Excel and can be created easily with the included wizard. The Pivot Table Object is just like the objects we discussed above except that it has already been written by Microsoft to include the most popular functionality that is performed on pivot tables. You get the properties, methods and events most used to manipulate pivot tables without having to write it yourself. It’s basically canned functionality that is provided for you.
Debugging Code
If you find that your code isn’t exactly behaving as planned, VBA provides you with a debugger. The debugger gives you the ability to analyze exactly what is happening in your code and find where it is breaking. It also allows you to momentarily stop the execution of your code and step through it to figure where the error is occurring. You can see the values of your variables at their current state and even set breakpoints to automatically stop execution at specific points in your code. This way you can determine exactly what is happening at that place and in the particular context that the code is being used.
Handling Errors
VBA has built in statements to make catching and handling errors easy. The “On Error GoTo” statement tells the program where to go if an error occurs. The usual practice is to send it to an error handling routine that will trap it and log it or display it. Then the error handling code can dispose of the error gracefully so that the program can continue execution.
The “Resume” and “Resume Next” statements tell the program what to do after the error has been appropriately handled. Error handling should prevent the program from crashing and provide the programmer with information they can use to eliminate the scenario altogether. At the very least, errors should be handled gracefully and not prevent further program execution.
Knowing how to use VBA and the structures outlined above will give you many advantages over competition in the eyes of potential employers. If you can unlock the capabilities of Excel and automate its powerful functionality, then you will truly be a valuable asset to any company.
Anyone can learn to use the basics of Excel, but not everyone can pair it with VBA programming to produce a data application. Learn how to write VBA in Excel today by attending an instructor-led class at ONLC. We can train you to be an expert VBA programmer in no time in one of our 300 convenient nationwide classrooms. Contact us today to find out more!
