Excel and MS Access store dates as numeric fields, allowing for each calculations in VBA.

Users will make mistakes, and some of those will impact your VBA code. Here are three ways you can handle that.

Use VBA to check for nearly identical data in your Excel worksheet.

In recent posts, I’ve been talking about validating your Excel data using VBA. I talked about creating a validation harness, which you can leverage to validate any Excel data. Then I covered some phone number validations you might add to that harness. Today I’m going to talk about using that harness to automatically replace text that a user may enter. I consider this part of data validation, because you can use it to head off data issues. Here’s an example: Converting "K" to

Last week, I wrote a blog post about creating a validation harness in Excel VBA. I laid out a structure you can add to your Excel file that allows you to add custom validation as users are entering data. Now that we’ve laid that framework, we can work on adding some useful validation. Phone Numbers and Extensions Today, we’re going to look at phone numbers and extensions. When setting up an Excel file, it’s common for people to create a single column that is used for both, as

Adding a Visual Basic structure to your Excel file for validating data.

I wrote a post several years ago about adding line numbers to your VBA code. To this day, it remains my post popular post ever. Why? It’s because they can be a useful tool in your debugging arsenal. Taking a walk down memory lane, old-school computer programmers will remember that line numbers were crucial back in the day. Whenever there was a problem with your program, you were always referred to a particular line number, identifying the command that was failing, which gave

If you’ve ever written VBA code in Excel, Access, or any of the other products in the Microsoft Office suite, you may already be familiar with Option Explicit. If you’re not, it'll be worth a few minutes of your time to learn about it. It’s a useful tool that will save you a lot of headaches. In order to understand the Option Explicit statement, you need to start by understanding that Visual Basic allows implicit variable declaration by default. This means that you are not re

How to add line numbers to VBA

The other day I had a client ask me to create a reminder in an Excel document that would pop up several times a day. It had to run at specific times, and the interval wasn’t always the same. For example, it might need to be displayed at 8:00 am, 9:00 am, 10:30 am, and 2:45 pm. The times were listed on a worksheet and were subject to change. They only needed to be displayed when one particular document was opened. At first I told the client that this would be an easy job, but

Recording macros in Excel is an easy way to automate repetitive tasks. Maybe you receive a new file every month with your sales figures in it, for example. You may need to sort the data, move it from one worksheet to another, or format it. If you repeat the same steps on the file month after month, this is a scenario where a macro will save you time and effort. The problem comes when the file that you receive isn’t quite the same each month. It’s typical in this scenario for

If you've ever thought about creating macros in Excel, you've probably heard the term "VBA". In this post, I'm going to give you a little background on VBA so that you can decide if it's something you want to tackle. Excel is a great tool for analyzing and presenting your data. But if you're like most people, you spend more time than you'd like performing repetitive tasks. Maybe you need to import data from your database once a month, sort it, and format it, for example. Macr