VBA - Change IF Statements for Reference Table

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 RANDOM SELECTED).



Doesn't this look nasty? Yes, we just tried with a couple of fruit and this went out of control. Imagine having a full list with hundreds of fruits and subconditions for each fruit that you need to update every month. CRAZY! In cases like this, you need to avoid if statements and use a reference table instead. Check how easy and organized is the following schema table:





And use the following Code to filter out and return Type from the Table:



I put some comments in the code for you to better understand the procedure but the main point I want to give here it's to use Reference Table to substitute big, nested if statements. It's easy to maintain, more legible and readable and more escalable.


That's all folks!
Cheers,

Comments

Post a Comment

Popular posts from this blog

VBA - Using JSON in VBA

Image
A very important skill you will need to know in VBA if you want to write professional applications is to handle JSON text and convert it into VBA objects. JSON is a very popular text-based file format used to transmit data objects. It's very common right now for APIs to return requests using JSON text. If you learn how to handle it, you can make some good use out of it. To put in an example (very trending right now by the way), imagine you have a Cryptocurrency portfolio you built in Excel where you store all your transactions and the number of Bitcoins you have. To know how much your Bitcoins are worth, you need to go and check the web for the current Bitcoin price. Or, instead, you can have Excel to automatically call an API to request this information. Parse out into an Object in VBA and display the price in the Worksheet to compare how much you Bitcoins are worth right now. Another example: let's say you want to know the list of the Best Sellers according to...
Read more

VBA - Copy Sheet from One Workbook to Another

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!
Read more