VBA Professional Development

When developing VBA applications for a considerable high amount of users, you should be able to control every user code from a centralized module. Think about a company with 500 users using an Excel application you built. The customers use Excel as a database to store monthly data. Every day they run an application that organizes new fields to the respective sheet. One day, you notice a bug that affects every user. Grabbing a fresh copy is not an option, every user will lose months of work. Your only option is to go computer by computer doing the manual update in their specific workbook or, creating an application that goes to everyone's computer, finds the workbook, open it, and modify the code. This last option is very, very risky and I would not recommend it at all. You could encounter a lot of problems while doing it. On the contrary, if everyone's Excel application could point to a centralized workbook where the code to run the procedures and functions is located, you w...

If Statements are a crucial part of the daily VBA Development process. You can't live without conditional statements since they are, in core, the most fundamental decision derivative expression of programming. However, in Excel, where you have to deal with a lot of conditions, it might get tricky to use them. Imagine having 7 different conditions and for each condition 2 sub-conditions where 3 of them don't result in final "ELSE" condition. Hard to see, right? How many lines do you need to code to accomplish this? How about if in the future, you need to add more conditions, will you know where to place them? A lot of If Statements can be replaced for a table that drives all possible conditions. This will help with readability and will also help you to avoid modifying or adding more code in the future. Check this example where you have to return a fruit type based on the arguments passed to a function. (DO NOT PAY ATTENTION TO DESCRIPTIONS, THEY ARE VERY R...

Sometimes we need to insert a Sheet from one Workbook into another one. The solution is really easy. Follow bellow code: That was really simple. Just a few comments: Always make sure to define the workbooks that are going to be used (Source & Target). This will make the code more legible and it will make things easy to understand in the future. You should apply this rule everywhere in your coding experience. Variables are not only valuable when you have no other choice, they can be used to make your code clearer. That being said, note in the code how we are inserting the copied Sheet after the last Sheet in the Workbook Target. That's all folks... Cheers!

It's a common mistake when we start using VBA to filter data by using long loops and conditions. This is probably the most logical way to interpret what we want to do. When we use loops and conditions we are taking to the code our natural process of filtering data. LOOK LINE BY LINE, AND IF A CONDITION IS MET, TAKE ACTION. Well yes, that works but it's not the optimal solution. Loops are great, I love them, but they are evil! They can slow down your process a lot when your data is considered big. Fortunately, we have a better "not that naturally logical" way to do it. FILTER! Like water and oil you can separate values and once you have them visible, take the desired action. Let's see it on the code: Ok, this wasn't that complicated. Let's see what we are doing here: 1- I'm selecting the Data that I want to filter. 2- I'm applying a filter to the 5th column of my Data, based on the Criteria "Bikes". 3- My Range show...