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.
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)
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match 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:
22 thoughts on “Using Excel 4 Macro Functions”
Good post Mark. Lots of useful little nuggets amongst the old XL4 macros 🙂
Thanks Phil. I really appreciate you letting me share your PDF.
Hopefully some newer Excel users will enjoy playing with the Excel 4 Macros too.
This is still an area of learning for me. Let me know if you achieve anything really good with it.
I just have to speak up on this topic. I have a love hate relationship to Excel 4 Macros right about now. They were a delight when I could use this formula to return 0 when a cell is in a hidden column and a 1 when the cell is in a visible column
where ColVisible is a defined name that looks like this when in cell H17
That got my app away from using a user defined function, which kept interrupting me while I was single stepping through my code, because every recalculation would fire off all these UDFs that achieved the same aim.
But then the client asked for an Export to XLSX feature. And at that point, everything broke down. I had to convert every formula that relied on the necessary 0 or 1 in lengthier formula, to values, which was very processor intensive for the VBA routine that was “dumbing down” the application. Furthermore, all the business logic went out the window, because while many formulas were retained (did not use those banned defined names), they were left virtual orphans.
I had been using range containing cells with that ColVisible defined name as one of the range arguments in a SUMPRODUCT formula so as to immitate Excel’s SUBTOTAL(109, RowRange) function, only for columns – all because Excel doesn’t support suppression of hidden columns using SUBTOTAL(109,…) which I think is a shame. Worked great when the workbook was macro enabled, but the workbook is largely crippled when saved to XLSX. Probably should have come up with another approach, but off the top of my head I cannot even think of one.
I guess macro workbooks are like any other program. Can’t expect something to function as a program once you take out the code; and same goes for a workbook laden with Excel 4 Macros – my client is asking for the impossible to expect a “working” XLSX equivalent of something that only worked because it had been an XLSM file.
I can understand your frustration. Any solution for a 3rd party which is relying on Excel 4 Macro sounds quite risky to me. Microsoft stopped developing them in 1993, so it’s amazing they still work at all.
Why does your client want an XLSX file rather than and XLSM file? Would they be happy with an XLSB or old XLS file?
Based on what you’re described I don’t think you can achieve it with a standards XLSX file. Unless you are allowed to reshape the data in some way, or maybe re-design the entire user-interface.
I have been experimenting with creating a Dialog Box as Below-
(See end of macro after HALT() )
item x y width height text int/result
blank 12 750 300 552 177 alto sax
text 5 24 14 50 18 &Name: bari sax
text edit 6 74 12 170 18 baritone
text 5 24 44 90 18 &Dues Paid: bass clarinet
number edit 8 114 42 130 18 50 bassoon
check box 13 24 72 220 18 &Available during summer: TRUE clarinet
group box 14 24 96 220 72 Student Status conductor
radio group 11 24 114 180 45 1 flute
radio button 12 32 114 172 15 &High School Student french horn
radio button 12 32 132 164 15 &College Student malletts
radio button 12 32 150 164 15 Non-&student oboe
text 5 268 108 88 12 &Instrument percussion
text edit 6 268 126 120 18 tenor sax
linked list box 16 268 12 120 90 I16:I31 #N/A trombone
default ok button 1 412 12 120 21 Enter record trumpet
cancel button 2 412 39 120 21 Cancel tuba
ok button 3 412 66 120 21 Done entering
However the “Linked List Box” item 16 will not show the items in cells I16:I31 in the dialog box.
Could anyone throw any light onto the problem please
Kind Regards Derek
Define a name, like “instruments”, for the items in cells I16:I31 then enter that name in the text field of the linked list box item. The init/result field of the linked list box item will contain the index into the I16:I31 array.
I’m looking for an idea, how to make macro for making list of folders (subfolders) without VBA. A long time ago there was a function called =direstories (” “), but now I can’t find it.
Coul’d you tell me how to solve this problem ?
Sorry, I would always use a VBA solution for this: https://exceloffthegrid.com/vba-code-loop-files-folder-sub-folders/
Has anybody else got any ideas?
You can use PowerQuery and use ‘From Folder’ as a data source. That will give you all the files and folders recursively in a directory.
Here is my post about how to do that: https://exceloffthegrid.com/power-query-file-attributes/
Very Useful article, Thanks For Sharing With Us
I got issue with add.bar() function in Excel 2016. Do you know if these functions ( add.menu() too ) are compliant ?
Good content Mark. really helpful…Thanks for sharing!
I’m asking of folks who arrive here and thus have an interest in preserving knowledge of and crowdsourcing potential for Excel 4.0 macros whether we could get a forum or subforum going for this language? In all the excel forums I can get extensive help with cell programming that uses virtually the same language but as soon as I move to using the language in macros all i get are VBA solutions.
I understand this doesn’t guarantee miscrosoft’s support but the language essentially lost that 25 years ago and its still going strong. The computers and software i’m running are 25 years old in some cases and it runs great. I don’t need support in the classic sense of the developer’s support although I do need crowdsourcing help in writing these macros (e.g. I am using a macro to get around the nested IF limit in cell programming and i’m running a loop on 26,000 records and I had tried to write this to kick out of the IF’s once a true value is encountered just to save the next 20 checks since each cell only returns true on one of the IF statements. So I put a NEXT() in the true conditional but this throws an error vis-a-vis the NEXT() at the end of all the statements even when IF doesn’t return TRUE–which I don’t understand because if the true conditional is not met it would only see the last NEXT, I thought.)
And I’m still working on whether I could use INDEX and MATCH functions to do the whole thing as a cell function which would be more straightforward. Got this working with an array function with values but not text at the moment. But think this very possibilty outlines the critical point about Excel 4.0 macros which you walk right up to without completely articulating in this post: that they are syntactically and logically derivative of the skill of cell programming.
the macro sheets help to arrange multiple such functions in what would otherwise be so complicated and bracketed a single cell formula as to be all but unworkable (or disallowed as in nested IF limit).
Having a lot of sunk intellectual capital in XLM, i was totally uninterested in learning VBA to do essentially the same thing. It may have more ‘power’ or capability in theory but for my money they should never have sidelined 4.0 macros. A very few macros that I couldn’t figure out in XLM I would record with the macro recorder and then I could make small changes by inferring the syntax and approach from the recorded VBA.
I really wish I could get a version of Excel 5.0 running because that allowed recording in both languages so you could at least help build your translation skills.
Support in later versions of Excel is a sword of damocles although cell programming is not going to go away so a lot of these cell functions that are the basis of excel 4.0 macro functions need to be supported. I think maybe the biggest choke point will be saving as macro sheets. Hopefully we can find some workaround if microsoft tries to pull that plug.
But you gotta crawl before you can walk and a forum that could gather dinosaurs who never left 4.0 or new devotees who find it a compelling language can only help with solutions for its continuing relevance and who are happy to run old versions of excel would be a great start.
The Article seemed very informative which covers most part.
Can anyone here tell me if there is a converter from XL4 macros to VBA. I have loads of XL4 macros which i hardly understand.
In case auto converter is isnt present, can this be converted to any other language?
I appreciate the help.
I came across this page while involved in extensive search for way to use or convert Excel 4 macros in my latest upgrade to Excel 2019 for Mac on new Mac Air M1. I used and built comprehensive pile of macros in a macro sheet that I run successful quoting-costing-invoicing-database for my business since my Mac Plus back in ’80’s constantly tweaking and runs smoothly. Last incarnation was Mac Excel 2011 on older Mac Air with Mojave and stored on Dropbox. Works like a charm for quoting, job jacket creation, invoicing and database of jobs invoices, debtors and creditors. BUT now as always trying to keep up to date installed Excel 2019 on the Mac Air M1 running Big Sur cannot for the life of me get the macros to work. Searched and searched for answers as the thought of learning and rewriting the macros in VBA turns me off completely. So for moment keeping to old Excel 2011 in Mojave on old Mac Air. Defeating purpose of buying latest technology in one computer. Any one got any clues to resolve.?
Hey Alan – given that Excel 4 macros were replaced 25+ years ago, then it’s only a matter of time until they stop working. I hope you do find a solution though.
I put together an HTML version of the Excel 4 Macro Function Reference:
I have been using excel4 macros since 2001 for statistical data. Its done by somebody else but I myself out of interest learned. There are lots of features to be learnt, But some menu editor functionalities just work in older version only but I want to know how it worked in previous versions. Why that functionalities were dropped.