At the start of this year, I wrote a post entitled Turn a string into a formula with EVALUATE. This post used an Excel 4 Macro as a possible option for turning a string into a formula. These types of Macros were superseded when VBA was introduced in Excel version 5, hence why any macros before that are referred to as Excel 4 Macros.
Excel 4 Macros still work today, but there is little information about them. The help file documentation is still available, however it no longer compatible with current versions of Windows. There are some sites with examples of Excel 4 Macros, but I couldn’t find anything complete.
In November 2017 Phil Treacy from www.myonlinetraininghub.com issued a 653 page PDF (no, that’s not a typo, it really is 653 pages long) with a comprehensive list of Excel 4 macros. Phil has kindly agreed that I can share the PDF with the readers of Excel Off The Grid. Click the image below to download the PDF.
Why use Excel 4 Macros?
You may be wondering why you would want to use Excel 4 Macros. The main benefit is performing actions without knowing VBA code which is not available using normal spreadsheet functionality.
If you already know how to use VBA, this question becomes harder to answer. These Macros are triggered using standard spreadsheet functionality, so may be a better option in some circumstances, especially when considering the final users of a spreadsheet.
Much of the functionality is likely to be well past its usefulness and probably only relevant to the versions of Excel in which it is supported. But there is plenty of scope to explore and have some fun with Excel.
How to use an Excel 4 Macro?
There are two ways to use Excel 4 Macros:
- Named Range – when information can be returned to a cell as a value
- Macro worksheet – when a procedure/process is to be performed
The Excel 4 Macros which return values can be applied within a Named Range.
Let’s work this through as an example.
We will use the GET.CELL function (page 339 of the PDF download) to return the formula used within a cell.
Here is the data and scenario:
Columns A and B contain a list of the top 10 grossing movies of all time. Cell E2 shows the name of a movie we want to lookup from that list. Cell E4 displays the result, but which formula did we use in this cell to retrieve the result? (I know I could just click on the Cell and look, or use the FORMULATEXT function but remember, this is just an example of how Excel 4 Macros work).
Click on Cell F4 (we will be creating a relative named range, so the selected cell is relevant). From the Formulas Ribbon select Name Manager.
Click New to create a new Named Range.
Looking at the screenshot above, the key points to notice are:
The name used to apply Excel 4 Macro.
Usually the Refers to box contains a range, but in this circumstance it contains a formula.
- Get.Cell is the Macro Function to be applied.
- 6 represents the Type_num reference, as noted in the PDF manual. 6 relates to the formula, 7 to the number format 18 to the font etc. Check out page 339 for more options.
- !E4 references the cell one to the left of the selected cell.
Click OK, then close the Name Manger to return the worksheet.
To apply the Excel 4 Macro click on Cell F4 type:
The formula bar shows the text entered into F4 is =GetFormula. The value returned in Cell F4 is the formula used in cell E4. F4 shows the INDEX MATCH function was used. The Excel 4 Macro has worked.
It is not essential for the Type_num to be hard-coded into the Named Range, it can be based on a cell reference. See the screenshots below, as an example.
Here are examples of what can be achieved with Excel 4 Macros within a named range.
- Turning a string into a function (EVALUATE function)
- Listing all the files within a folder (FILES function)
- Reading cell attributes (GET.CELL function)
- Calculating using precision as displayed (GET.CELL function)
- List all the Named Ranges within a workbook (NAMES and GET.NAMES function)
- Get name of currently selected worksheet (GET.WORKBOOK function)
- Display Excel’s settings e.g. version number, name of user, organisation, default currency formats (GET.WORKSPACE function).
As I’ve been playing around with this I’ve find functions which returne the attributes of cells, workbooks or named ranges easier to use and apply.
If you’re used Excel 4 Macros and remember some functions which might be useful today, please share them in the comments below.
Excel 4 Macros can be run from a Macro Worksheet.
Right click on an existing worksheet tab and select Insert… from the menu.
From the Insert window select MS Excel 4.0 Macro, then click OK.
A new worksheet will appear which is probably labelled Macro1, if you’ve not already got a Macro Worksheet.
The Excel 4 Macros listed in the PDF can be entered directly into the Macro worksheet.
From the screenshot below, I have used PROTECT.DOCUMENT (to protect the worksheet) FULL.SCREEN (to enter full screen mode) and HALT (to end the macro).
When ready to run the Macro, right click on a cell in the Macro worksheet and select Run.
From the Macro window ensure the cell reference displayed is the first line of the Macro (A1 in the screenshot above), then click Run.
Ta Dah – like magic, the worksheet is protected and the window is in fullscreen mode.
From my brief usage of these it appears the Macro must end with a HALT() function.
Generate accurate VBA code in seconds with AutoMacro
AutoMacro is a powerful VBA code generator that comes loaded with an extensive code library and many other time-saving tools and utilities.
Whether you’re an experienced coder looking to save time, or a newbie just trying to get things to work, AutoMacro is the tool for you.
Notes for using Excel 4 Macros
- When using a relative named range, the cell distance between the subject of the function and the result must be the same, else it may calculate an incorrect result.
- Any file with an Excel 4 Macro must be saved as a macro-enabled workbook (.xlsm), trying to save as standard Excel file will trigger the following error message
- Excel’s calculation chain recalculates cells only when preceding cell values have changed. When working with cell attributes or other non-cell value elements (such as cell color or font) it may be necessary to include a volatile function, such as NOW() to the force the recalculation of the cell.
Example for value-based results: =GET.CELL(6,!E4)+(Now()*0)
Example for text-based results: =GET.CELL(18,!E4)&LEFT(NOW(),0)
- Any function which contains an Array, such as GET.WORKSPACE(37) or NAMES() should be wrapped within the INDEX function.
In the example above A1 contains the number from the array which should be retrieved, e.g. the if A1 contains the value 2, it will return the 2nd item from the GET.WORKSPACE(37) array.
- When using a Macro Worksheet the worksheet is set to display the formula, not the result of the formula. Use Ctrl + | to toggle between the formula view and the result view.
Don’t rely on Excel 4 Macros
Microsoft replaced Excel 4 Macros with VBA in 1993, so it is amazing they still work in Excel 2016 (released 23 years later). The lack of support and documentation should be taken as a clear sign that Microsoft could remove this functionality at any point. Even if you find an awesome little feature within these Macros, it would be unwise to make it a key part of any process.
Where to download the 653 page PDF
Click the image below to download the PDF.
I am a long way from being proficient with using Excel 4.0 macros. The first version I used was Excel 97, and I certainly was not an advanced user in anyway. May readers will have used these Macros before, so feel free to share your learnings in the comments below.
There is a lot of learning, fun and experimentation to be had with Excel 4 macros, just remember not to rely on them for any key processes, as Microsoft could withdraw support for them at any point.
Other useful resources:
- XL4 Macro Functions In Names
- Show Formula Formula – Excel 4 Macro Function GET.CELL
- List Files in a Folder Using FILES XL Macro (XLM)
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
But, if you’re still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: