Play by Play is a series in which top technologists work through a problem in real time, unrehearsed, and unscripted. In this course, Play by Play: Obliterate the Tedium - Practical VBA for the Excel Practitioner, Ryan Hinkle and Don Robins demonstrate that with less than 50 lines of code you can create powerful programs for any highly repetitive process to automate tasks, including moving among sheets, rows, and columns to retrieve and output data in exactly the format you seek. Learn the basics of configuring the Developer Environment in Excel, various programming elements required to script simple yet powerful macros, and how to design and construct pseudo code. By the end of this course, you’ll have a clear understanding of just what you can accomplish with VBA macros in Excel, and you’ll come away with some tools and samples to apply to your own transformation tasks, leveraging the powerful capabilities of the embedded programming environment.
Don Robins is a well known Salesforce MVP, instructor, author, and speaker.
A custom business application developer for more decades than he cares to
admit, he focuses on Salesforce technical instruction and knowledge
Ryan is a Managing Director at Insight Venture Partners, a leading software investor. Ryan led Insight's investment in Pluralsight and has served on the board of directors since 2012.
Section Introduction Transcripts
Section Introduction Transcripts
Course Overview Welcome to this Play by Play with Pluralsight. Play by Play is a series where we sit down with experts to work through solutions and discuss challenges faced everyday by users and technologist across various technologies. In this course, we challenge Ryan Hinkle, partner and Excel practitioner at Insight Venture Partners, to explain how every day Excel data analysts can use the power of Visual Basic for Applications, for VBA, to make their data transformation tasks easier and less prone to error, while saving lots of time and reducing frustration. Ryan walks us through the basics of configuring the developer environment in Excel and then demonstrates how to build and edit a simple VBA macro. He explains the various programming elements required to script simple yet powerful macros and shows how to design and construct pseudo code, the logic used as a blueprint to create any executable VBA macro. Along the way, you'll learn about variables, sub routines, objects, string manipulation, conditional branching, loops, basic coding best practices, and more. By the time we're done, you'll have a clear understanding of just what you can accomplish with VBA macros in Excel, and you'll come away with some tools and samples to apply to your own transformation tasks, leveraging the powerful capabilities of the embedded programming environment. So please join us for Obliterate the Tedium - Practical VBA for the Excel Practitioner. We hope you enjoy it.
Understanding Psuedo Code Okay, we are at the last piece before we can actually put this all together. We've done the environment and how to use the VBA environment. We've gone through a lot of the commands, the Legos as we used, that we need to build what we're trying to build. The last piece is this notion of pseudo coding, and while it's not strictly necessary, it's often very difficult to go from what I want to do in plain English all the way to code. The idea is, if I can describe this intermediate step, which is code-like, it's getting the logic structure, right, but it still refers to English understandable language. It's a great intermediate step to help us prep for the actual act of writing code. I view this as the hardest part of the entire process because if you don't know how to pseudo code, it's really hard to actually code. So what we're going to do is two very, very simple examples of pseudo coding, just to give you the familiarity with it and then in the following two modules when we put it all together, we're going to the entirety of our whole task with pseudo code and immediately translate that into code. But this is an important final step for us. So let me make sure I understand. So pseudo code is English, right? It is English. It's not programming language, but what you're doing is you're sort of breaking out the task, you're breaking down the tasks that are going to have to be, each task is going to correlate to something that actually will happen in code. Yes. So you have to understand what has to happen, what can happen in code in order to do proper pseudo coding. But the pseudo coding is English. That's right. When I write pseudo coding, and this is stylistic, this is not rule, remember all of this is to help make it more understandable or a little bit easier to write. Generally in this course, there's a one for one correspondence of a line of pseudo code will become a line of code. It might become two or three, but there's a pretty close correlation between the two.