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