560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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!
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
Gets a list of the worksheets in a workbook
Gets details of the last active cell in a worksheet
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|
Some other pages relevant to the above blogs include:
25 Aytoun Street