Eight Bug Reduction Tips - VBA

You cannot completely eliminate bugs in your programs, but there are a few tips that will help you keep them to a minimum

Use an Option Explicit at the beginning of your module - Doing so will require that you define the data type for every variable that you use. It's a bit more work, but you'll avoid the common error of misspelling a variable name.. And there's a nice side benefit: Your routines will often run faster.

Format your code with indentation - Using indentation to delineate code segments is quite helpful. If you have several nested For...Next loops, for example, consistent indentation will make it much easier to keep track of them all.

Be careful with On Error Resume Next - This statement causes Excel to ignore any errors and continue. In some cases, using this statement will cause Excel to ignore errors that shouldn't be ignored. Your may have bugs and not even realize it.

Use lots of comments - Nothing is more frustrating than revisiting code that you wrote six months ago - and not having a clue as to how it works. Adding a few comments to describe your logic can save you lots of time down the road.

Keep your subroutines and functions simple - Writing your code in smaller modules, each of which has a single, well-defined purpose, makes it much easier to debug them.

Use the macro recorder to help you identify properties and methods - If I can't remember the name or syntax of a property or method, it's often quicker to simply record a macro and look at the recorded code.

Consider a different approach - If you're having trouble getting a particular routine to work correctly, you might want to scrap the idea and try something completely different. In most cases, Excel offers several alternative methods of accomplishing the same thing.

Understand Excel's debugger - Although it can be a bit daunting at first, you'll find that Excel's debugger is an excellent tool. Invest some time and get to know it.

Finding cells by format

We often search cells by values or contents in the cells by using find functionality in Excel. But do you know we can search a cell by format.

Let’s see how?  Assume I have a big client database in Excel and I have highlighted a customer number with different cell color and with different font format (Bold and Italic). now from this database if I have to find this different customer, I will use Excel find functionality with finding format instead of the content of the cell.

To find the customer number, by format press Ctrl+F to get Find and Replace pop-up. click on the options button, to get advanced find options.

When you click on the Options buttons, the Find and Replace pop-up gets expanded and shows all advanced options. 

Click on Format button, and give the kind of format you want to search in the range. 

  

Excel shows the preview of the format you are going to search in the range. Press Find Next or Find All and Excel finds all the cells with the same format you have given in the condition.

Similarly you can give colors in the filter criteria and filter the range by color.

Important: Clear the format once you are done with Find operation as Excel may retain the format and search the same format every time you are using Find functionality. 

Wildcard Characters in Excel

We will discuss wildcard characters in this blog. People who have worked on any SQL, must be knowing about the wildcard characters and their uses. People who have not worked on SQL here is a brief about the wildcard characters we have in Excel.

Wildcard characters * (asterisk), ? (question mark), ~(tilde) followed by a * or ? or ~.

Usage of asterisk (*) in Excel:

 Asterisk is used when we want to search a string which starts/ends with a specific word or pattern.

Asterisk is also used to search a pattern if the pattern is appearing in middle of the word.

Example and usage of  Asterisk:

<!--[if !vml]--><!--[endif]-->

We have a few string values in a range and we are passing few conditions with the use of wildcard characters:

"*ex" in the filter condition

"*ex*" in the filter condition

"ex*"  in the filter condition

          

Question mark (?) can be used when you want to search a string with one variable character. For example: you have strings ABC and ACC, if you give “A?C” in filter condition, excel will show both the strings.

So where do we use wildcard character tilde (~)?

The answer is when you want to search a wildcard character in the sheet, you have to use tilde before the character to search it. Let’s take the same example we have used above. I have appended an asterisk to at the end of the list.

Now if I want to filter only the character * so that filter returns the last row from the list. If I write * in the filter condition, the filter will display the entire list instead of just the last row. So what do I do to get just the last row?

I need to use the tilde wildcard character. To search a wildcard character from the list/sheet, write tilde just before character in the filter condition:

<!--[if !vml]--><!--[endif]-->

You will find the required result.

<!--[if !vml]--><!--[endif]-->

Important

Wildcard characters can be used in Find functionality and formulas in Excel.

To know more about usage of wildcard characters in the formula and Find functionality contact me on : support@excelvba.org