This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

The hidden door to extra functions in Excel Part two of a six-part series of blogs
Did you know that you could combine range names and legacy Excel 4 functions to list out the worksheets or range names in your workbook - wthout using macros? I didn't either - here's how to do this, and much more! The hidden door to extra functions in Excel A little background (this blog) Using Excel 4 functions with range names Example - Listing Worksheets Other ideas for the use of Excel 4 functions References for this blog

The hidden door to extra functions in Excel

Part two of a six-part series of blogs

Did you know that you could combine range names and legacy Excel 4 functions to list out the worksheets or range names in your workbook - wthout using macros? I didn't either - here's how to do this, and much more!

The hidden door to extra functions in Excel
A little background (this blog)
Using Excel 4 functions with range names
Example - Listing Worksheets
Other ideas for the use of Excel 4 functions
References for this blog

Posted by Andy Brown on 15 September 2022

A little background

When I first started teaching programming in Excel, VBA didn't exist as a programming language. Instead, macro commands were written into cells - like this in fact:

From memory, this macro would work, but I offer no guarantees!

You can still write macros like this:

Just right-click on a worksheet tab and choose this option to go back in time to the early 1990s!

However, you would be foolish to do so - why not use the much better VBA language incorporated in Excel?

A VBA macro to do the same thing.

Excel 4 macros were introduced into Excel in 1992 (the year in which John Major's Conservatives won a General Election, and in which Wise Owl was founded). They were superseded by the introduction of VBA the following year, and that should have been that.

For reasons of backward compatibility, Excel 4 macros still work in Excel to this day, and will continue to do so until Microsoft decide to disable them. The reason this is useful is that you can reference Excel 4 functions in range names - for example:

Excel 4 function	Example of something it does
Get.Workbook	Gets a list of the worksheets in a workbook
Get.Document	Gets details of the last active cell in a worksheet
Get.Name	Gets information about a range name

It's this little-known (well, little-known by me until now) fact which allows us to extend the range of functionality in Excel.

Parts of this blog
The hidden door to extra functions in Excel A little background (this blog) Using Excel 4 functions with range names Example - Listing Worksheets Other ideas for the use of Excel 4 functions References for this blog