A Cheat Sheet to get more out of Microsoft Excel
Ever wanted just the Cliff Notes version of Excel? We've got you covered with the Excel Power Users Quick Guide - and it's free.
Rick: What’s going on with the VBA Pro Course?
Jon: My VBA Pro Course takes you from a beginner to a pro level.
Jordan: Is this on your own learning or is it guided learning?
Jon: It is based on my own experience with VBA trying to use real-world scenarios and applications versus just talking about theory and concepts. We go through different practices and build out an add-in file which replicates a simpler version of my Tab Hound add-in and others. We build it out starting from scratch to putting buttons on the ribbon. We also build spreadsheet based applications which are more like tables using the spreadsheet as an interface. I have a financial analyst and accounting background so a lot of my course is based on that experience and automating processes in that kind of world; budgeting, forecasting, reporting and general data cleansing and data analysis.
Rick: Do you start the course with the macro or do you script the course from the beginning.
Jon: I don’t start with the macro recorder although I think it’s an important tool. I think it’s more important to understand what you’re doing with that code that the macro recorder produces. The macro recorder is producing VBA code but you cannot interpret half of what it spits out. And some of it is not usable tracking stuff that you don’t need. So I start at square one and explain the object model, how it works, and how you reference common objects like worksheets and cells. We talk about the macro recorder, but we start by writing macros. Macro recorder is not going to take you to automation.
Jordan: What challenges do you see in teaching people VBA the first time? The main challenge I see is that a lot of people don’t know why they’re doing things. They’re trying to write this code, but it doesn’t make any sense in their head. What’s the end product? So I try to show them what it’s going to be and then backing into the elements of the code. Szilvia what about you?
Szilvia: On the teaching side, a lot of people who learn VBA are new to VBA and computer programming. Finding the right balance and shortcuts is important. I start with the macro recorder and move into figuring out the object model later as I explained in my book. Macro recorder is the gateway drug into writing code from scratch!
Rick: We have questions from fans and bloggers. Here you go. Question one is from Chris Macro of Spreadsheetguru.com. Chris asks, “You have made a lot of creative Excel Add-ins and tools over the year. Where do you get your inspiration?”
Jon: My Add-ins have come from trying to find a solution that Excel does not have built into it. I love VBA because it allows you to get rid of repetitive and boring tasks. Add-ins like my pivot table field search is to solve search problems that don’t exist in Excel. A lot of my inspiration comes from banging my head against the table.
Rick: So you’re solving the question, “I hate it when…,” as all great entrepreneurs say when looking to create products.
Jon: So true. “I hate it when…” When you sign up for my website, I send you an email asking questions. The VBA Pro Course is for people who want to learn but don’t know where to start.
Rick: Question two is from Ryan Wilson. What is the one thing that you’ve learned in your later years of VBA programming that you really wish you knew when you first started using it?
Jon: Awesome question. The first is the object model. I didn’t know what I was doing in VBA for a long time and I wish I had a better idea of how the model object worked and properties and methods within the model. And, second, loops. Loops are a huge way to save time in the VBA. You can write a loop to handle all the repetitive actions. It changes how you approach Excel.
Jordan: I want to add that it isn’t just a VBA thing. The ‘design time error’ is annoying. I wish I knew earlier that you can turn it off under the tools in the auto syntax check. That was a game changer for my personality. I became happy.
Szilvia: Jordan has a thing about Hungarian notation. Szilvia, as a Hungarian, is partial to the Hungarian notation. Where do you stand on the great Hungarian notation debate?
Jon: I don’t want to start any fights. For people who don’t know what Hungarian notation is, if you have a variable to name, say a phone number. I would call it ‘phone number.’ But someone with Hungarian notation would call it lowercase lng.
Jon: I do use Hungarian notation. But, you can use the control spacebar auto fill keyword shortcut. Jordan agrees there is an advantage there, but he doesn’t use it.
Szilvia: I just need my variable names to please myself, so I stopped using Hungarian notation.
Jordan: Bob Phillips doesn’t use Hungarian notation either.
Rick: Question three is from Chris Newman. Option explicit. Two camps. The Jordan Goldmeier camp and the Bill Jelen camp who says it is stupid. Are you Team Bill or Team Jordan?
Jon: I’m Team Jordan. I teach in the course a strong recommendation for option explicit. I think Bill secretly uses it too.
So leave your below
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.
Get the FREE Excel Power User Guide