Top Menu

Tag Archives | automation

Excel Data Validation and Case-Sensitivity

I was working with a client recently on some VBA programming and the client was trying to limit data entries in a column of cells to one of four specific values. Now those of you who are familiar with Excel will immediately shout out “set up data validation”, and you would be correct. However, not entirely, as most people would create the set of entries in a range of four cells somewhere and set the Excel data validation to that range of cells.

While using data validation this way would work after a fashion it would not enforce the case of the entries. Those of you staying in Excel would probably not worry. I was working using VBA (Visual Basic for Applications) and the VBA code was case sensitive. Not only that but the client wanted to force the text entered by users to have the first letter of each entered word to be upper case.

So how do you force data validation to enforce the case of entered values. The answer is to enter the values literally in the Source box of the Data Validation dialog, separating each value with a comma, and not set the source to be a range reference. So if I was restricting my entries to the three major cities of England then I would type in the Source box:

London, Birmingham, Manchester

In this way London would be acceptable but london or lonDON would be rejected.

Of course, if you are working with a dynamic list then you have to stick with range references and use some other method (as I would in my VBA code) to enforce the case of entries, but the above is a simple solution for short, static lists.

What is Office Automation?

So you use Microsoft Office programs a fair bit. You probably use Excel to analyse data. You might extract that data from the company database or just another spreadsheet file. Really advanced users might even link the Excel data to a Word document so they can publish a report. In any of these scenarios, if you find yourself carrying out repetitive actions, or a lot of long-winded copying and pasting, formatting, checking for correct values, etc then automating these actions through macros and programming could relieve you of the unnecessary tedium if the actions need to be carried out on a regular basis – freeing you to concentrate on other important work.

What is a macro?

A macro can be regarded as a shortcut to a task that you do repeatedly.  A macro is really a series of tasks that you record in your Office program that can be replayed time-and-time again. When you record a macro, your program stores information about each step you take as you perform a series of commands. You then run the macro to repeat, or play back, the commands.

For example, if you need to perform the same action on, say, 12 different Excel workbooks, you can record a macro while you perform the task on the first workbook and then let the macro repeat your action on the other workbooks.

The downside of this recording process is if you make a mistake when you record the macro, corrections you make are also recorded. Macros also are not that clever. If you record a macro to process ten rows of data in Excel then it won’t know how to handle 100 rows next time. The macro will not be able to make decisions, and if you need to interact with the user then macros will not be able to help.

How can programming help?

All Microsoft Office macros are actually saved using a programming language called Visual Basic for Applications, or VBA. VBA is really a very powerful programming language, and it can be edited and added to to make what might be a very basic macro into an extremely useful extension to the Office program that not only can perform repetitive tasks, but can make intelligent decisions, interact with the user to ask for their input, work on data that changes from one month to the next or changes in data size, and even control other Microsoft Office programs.

For example, in almost any office, you can find lots of people who don’t really understand how to use computers. Using VBA, you can make it easy for these inexperienced users to perform some useful work. For example, you can set up a foolproof data-entry template so you don’t have to waste your time doing mundane work.

Assume you are a sales manager and need to prepare a month-end sales report to keep your boss happy. If the task is straightforward, you could develop a VBA program to do it for you.

Although Excel includes numerous built-in functions (such as SUM and AVERAGE), you can create custom worksheet functions that can greatly simplify your formulas. You’ll be surprised by how easy this is. Even better, the Insert Function dialog box displays your custom functions, making them appear built-in.

If you have a need for automation, you can either learn how to do it – I have 3-day Office training courses to teach you – or I could do it for you. Give me a call for a no-obligation chat!