Add Your Own Function to the Status Bar in Excel

When a range of cells are selected, Excel automatically displays formula results on the status bar. This can be an incredibly useful feature of Excel, especially when combined with filtering data.

Excel can display the sum, average, max, min, count of cells containing values and count of cells that are not blank in the status bar.

Excel displaying formulas in status bar

This blog post looks at how you can get Excel to display your own function on the status bar. For example, you may need to know the median or mode value, or count the number of blanks in the selected range.

In this post we will get Excel to display the number of blank cells in the selected range on the status bar.

Continue reading

Animate a Chart in Excel

Animate a chart in Excel to create a cool effect for your charts. You will see the chart build itself in front of your very eyes.

The chart can be created in the usual way and then VBA is used to create the animation effect.

In this example a combo box is used to provide a way for the user to select the chart they want to see. The chart then gradually appears one data point at a time.

Continue reading

Create a Hangman Game in Excel

I created a hangman game using Excel VBA for fun, and thought I would post it here for all budding Excel VBA learners to look at.

I find creating games, quizzes and sports spreadsheets an enjoyable way of keeping my Excel skills fresh, and to develop them further.

The game asks you to guess a letter to complete the phrase. A hint is provided for a little help, and the phrase will be related to music, sports, people, film or geography.

The spreadsheet is unprotected so you can check out the code and play around it with it further.

The VBA used to create this hangman game includes;

  • Dynamic arrays.
  • Creating a userform.
  • Module scoped variables.
  • Lots of text work including functions such as Left and StrConv.
  • Lots of For Loops, IF statements and a Select Case statement.

Continue reading

Automatically Create a Table of Contents in Excel

If a workbook contains many sheets you can create a table of contents to make navigating to the sheets easier. This is a fantastic idea when producing a final version of a report in Excel for a customer.

Excel does not yet contain a feature that produces a table of contents, but you can create a macro to get the job done.

Continue reading

Macro in Excel – Loop Through all Files in a Folder using VBA

One of the main reasons to create a macro in Excel is to make a repetitive task easier and quicker. Such a task may be to loop through all files in a folder and perform an action on each one.

The code below will display the folder picker dialog box to allow the user to specify the folder (directory) that they want to use. It will then loop through all the files within that folder. It will open the workbook, perform an action and then close it saving the changes made.

Comments have been used to explain different areas of the code.

Continue reading

Spell Check a Protected Excel Worksheet

Worksheet protection is used in Excel to protect cells containing formulas, hide sensitive data and much more.

Unfortunately one of the problems that arises from protecting a worksheet is the inability to spell check a worksheet.

To be able to spell check a protected worksheet you need a macro to unprotect the sheet, perform the spell check, and then protect the sheet again. Continue reading

Excel Macros Tutorial – Remove Blank Rows Macro

Blank rows in your Excel spreadsheet are undesirable and can make it difficult to work with. Because of this, Excel provides many ways that we can locate and then delete the blank rows.

These techniques can include sorting the list to group the blank rows together, filtering for blanks and using Go To Special to locate blank cells for deleting.

However these can be time consuming and may not be the best approach for you. For example, sorting the list will change the order of the list. You may not want this.

By creating a macro to delete the blank rows of a spreadsheet this process will be effortless, and can also be performed by anyone when the macro is made available with a button. In this Excel Macros tutorial we show you how.

Continue reading

Create a Splash Screen in Excel

A splash screen is a screen which loads when you open a program. A splash screen can be used to display a company logo, advertise and provide information to the user.

To create a splash screen in Excel, you need to create a userform and then use some simple VBA to show the userform when opening a workbook, and make it disappear after a certain amount of time. Continue reading

Use the Offset Property in VBA

Excel VBA offers many different ways to refer to ranges in a worksheet. One very popular way is to use the Offset property of the Range object.

The Offset property allows you to move around a worksheet with ease using ranges relative to your current position, or to a specific range.

The Offset property takes two arguments. These relate to the upper left hand corner of the specified Range object. The first argument is the number of rows to offset, and the second is the number of columns. Continue reading