Using Excel 4 Macro Functions

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 button below to download the PDF.

Excel 4 Macro - PDF Cover

Download the example file: Join the free Insiders Program and gain access to the example file used for this post.

File name: 0169 Excel 4.0 Macro Functions Reference.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

Named Range

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:

Excel 4 Macros Example Data

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.

Formulas Name Manager

Click New to create a new Named Range.

GET.CELL Named Range

Looking at the screenshot above, the key points to notice are:

Name:
The name used to apply Excel 4 Macro.

Refers to:
Usually the Refers to box contains a range, but in this circumstance it contains a formula.

=GET.CELL(6,!E4)
  • 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:

=GetFormula
Applying the Excel 4 Macro

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.

GET.CELL Named Range Example 2

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.

Macro Worksheet

Excel 4 Macros can be run from a Macro Worksheet.

Right click on an existing worksheet tab and select Insert… from the menu.

Macro 4 Insert Worksheet

From the Insert window select MS Excel 4.0 Macro, then click OK.

Macro 4 Insert Options

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.

XLM 4 Run Macro

From the Macro window ensure the cell reference displayed is the first line of the Macro (A1 in the screenshot above), then click Run.

XLM4 Run Macro Cell A1

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 messageMust Save Excel 4 Macros as xlsm
  • 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.
    Example: =INDEX(GET.WORKSPACE(37),!A1)
    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.

Conclusion

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:


Discover how you can automate your work with our Excel courses and tools.

Excel Academy

Excel Academy
The complete program for saving time by automating Excel.

Excel Automation Secrets

Excel Automation Secrets
Discover the 7-step framework for automating Excel.

Office Scripts Course

Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.

23 thoughts on “Using Excel 4 Macro Functions”

    • 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.

      Reply
  1. 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
    = ColVisible
    where ColVisible is a defined name that looks like this when in cell H17
    =IF(GET.CELL(44,!H17)-GET.CELL(42,!H17)=0,0,1)+0*NOW()
    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.

    Reply
    • Hi Bill,

      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.

      Reply
  2. I have been experimenting with creating a Dialog Box as Below-
    (See end of macro after HALT() )
    Enter records
    =DIALOG.BOX(B16:H32)
    =RETURN()

    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

    =HALT()
    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

    Reply
    • 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.

      Reply
  3. Hi

    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 ?

    Regards Marek

    Reply
  4. 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.

    Brian

    Reply
  5. 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.

    Reply
  6. 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.?

    Reply
    • 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.

      Reply
  7. 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.

    Reply
  8. Hello,

    I know this is a rather old thread, but I am interested in using some old Excel4 macros and can not get them working on my old Windows 10 laptop with Office 2007.

    When I create a name in the Excel Name Manager it is accepted without any error message, but when I try to use the name in a cell in my macro-enabled workbook it is not accepted.

    I have created the name “CellHeight” in Name Manager with the reference =GET.CELL(17)

    When I type the following in cell A1 it gives me a #NAME? error.
    =CellHeight

    I have also tried to insert an Excel4 Macro-sheet to execute some simple code such as =ALERT(“Hello world”) followed by =HALT(), but that also does not work. It too gives me an Excel4 Macro error message;

    Macro error at cell:
    [Excel4Macro.xlsm]Macro1!A1

    Any ideas how I can get this to work..?

    Reply

Leave a Comment