VBA – Why/Why Not?

For those special individuals with proper skills, there is a massive amount of Hidden Power lurking in the depths of Excel. VBA – Visual Basic for Applications is the programming language of Excel with which you can create the sometimes famous, sometimes infamous Macros. There are, after all, good reasons to use VBA, and some good reasons not to use it.

Why to Use VBA

·       By implementing User Forms, you can make your Excel worksheets very easy to manipulate for nearly any user.

·       It allows you to conduct more sophisticated tasks and permanently automate them, potentially saving a ton of your valuable time.

·       With good coding, you can produce creations that can solve business conundrums that are difficult to approach with built-in Excel tools.

·       VBA is truly amazing, and once you build your skills, it will transform you to unquestioned Guru status!

Why to Not Use VBA

·       First, a scary one: There is No Undo button (yes, I thought that might get your interest…).

·       Testing and the inevitability of debugging your code can take a lot of time.

·       If you find it difficult to maintain clear notes in your code, it may cause dreadful problems in the future (rebuilding your work is not cool…).

·       Most importantly, most users do not have VBA skills, and probably do not know anyone who does. If at some point they do not have you for a reference, your beautiful creation may turn out to be useless.

VBA. A fabulous tool or a disaster waiting to happen. You will need to make your own decision…

Transcend VLOOKUP!

You want to look up information in a table or database, and you wonder, “Which is better, VLOOKUP or the combination of INDEX and MATCH”?

Without a doubt, VLOOKUP is the most frequently used function in this instance, primarily because it is the more familiar formula for most users, but also because most Excel users simply aren’t aware of the ease and benefits of the INDEX/MATCH combination.

The major drawback of VLOOKUP, of course, is it requires a static reference in the form of the first column. (Who needs that?!?!) INDEX/MATCH on the other hand, is more Flexible, allowing you use whichever column you choose for your reference. Rock on!

For example, look at the illustration of using the INDEX/MATCH combo below:

Let’s say you want to create a Code Identifier in cell F2.  MATCH returns the Row Number of the location in an array of value you specify. Using the example above, the number “4” is returned by the formula:

=MATCH("Tampa", $A$2:$A$8,0)

INDEX, on the other hand, returns the Value that you identify by row number in an array. Using the example above, “Tampa” is retuned by the formula:

=INDEX($A$2:$A$6,4)

Combining the INDEX and MATCH functions is where the Magic comes in. Let’s say we want the Code for San Diego. We can set up a Code Retriever as shown in cells E1:F2, by inserting the following formula in cell F2 (This will return "141"):

=INDEX($C$2:$C$8, MATCH(E2, $A$2:$A$8, 0)) 

Using the MATCH and INDEX functions together is truly a Powerful and Versatile way of extracting data. Transcend VLOOKUP! There are better tools in town!

Double-Click Magic Tricks

All of us are familiar with the use of Double-Click shortcuts that save you time. Since some can be overlooked or forgotten about over the years, it is occasionally worthwhile to review some of these useful pieces of “Excel Magic”.

1. Perfectly Adjust Column Widths

This is an oldie, but still a goodie. Simply select Multiple Columns and Double-Click on the separators. Works equally well for adjusting row heights.

2. Collapse Ribbon to Get More Space

One of my favorites. Merely Double-Click on ribbon Menu Names and Poof – More Excel real estate!

3. Lock Format Painter

This one surprises a lot of users. Save a ton of time by Double-Clicking on the Format Painter icon, making it Reusable. (Who knew?!)

4. Jump to Last Row / Column in Table

Another old favorite: Just select a cell, and Double-Click on the cell-border in the direction you want to go. Bamm! You’re there!

5.  Double-Click in the Corner to Create a Split (2010 & 2013)

Not for the Excel 365 users, but valuable for the 2010-2013 crowd. You can very quickly insert Splits, (highly useful when you want to see multiple areas at one time), by clicking on either the Little Bar Shape next to Horizontal Scroll-Bar near bottom right corner of the Excel window or directly above the Vertical Scroll-Bar. Once done, you can drag the bars wherever you wish.

Double-Click Tricks can save you time and make you Look Good in the process!  (And Looking Good is always, well, good...)

Spinners are Tops!

Okay, okay, I know that the title of this week’s blog is corny, but Hey, Spinners are nonetheless Very Cool!

Spinners are Form Controls which you can place into an Excel Worksheet to give you Interactive Functionality with your data. By adding a spinner button control to a worksheet and linking it to a cell, you can return a numeric value from the position of the control, and go on to use that value in conjunction with other functions (e.g. Offset, Index, etc) to return values from lists.

What is so Cool about Spinner buttons is that they provide an intuitive approach to controlling the data you enter into your spreadsheets, and make you look Brilliant while doing it (that is, after all, the most important thing…). 

To find where they are located, (in Excel versions 2010 or later), do the following: 

1. Click on File in the upper-left and select Options 
2. Choose Customize Ribbon and then put a checkmark next to Developer

3. Click Okay 

After you have completed the above, the Developer ribbon will be available on your toolbar. Along with a great many exciting resources (you can spend days exploring them), the Developer ribbon supplies all the Form Controls. Hover your mouse over the selections until you see Spinner button, give it a click, and draw the button on your worksheet. 

Now is where the Fun begins! Right-click on the spinner, and then click Format Control. In the initial test of this cool control, enter the following in the boxes on the Control tab dialogue box:

1. Current value: 1 
2. Minimum value: 1 
3. Maximum value: 30 
4. Incremental value 1 
5. Cell link: $B$2 (Note: You can choose any value…) 

Now when you click the Spinner control, cell B2 is be updated according to the numbers you choose. You can then use that number to do Wondrous Things (or at least pretty cool stuff…) in Excel!

Use Spinner Buttons in your Excel worksheets to make it look like someone spent hours of programming time designing them. In just 5 minutes you can make amazing additions to your worksheets that are just too good to miss. If you have never tried the Form Controls, give them a Spin! It may open a new world for you…