This post may contain affiliate links. Please read my disclosure for more info.
Power Query Course

Using Excel 4 Macro Functions

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

Excel 4 Macro - PDF Cover

Download Excel 4 Macros 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.

Where to download the 653 page PDF

Click the image below to download the PDF.

Excel 4 Macro - PDF Cover

Download Excel 4 Macros PDF

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:

10 thoughts on “Using Excel 4 Macro Functions

    • Excel Off The Grid says:

      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.

  1. Bill Benson says:

    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.

    • Excel Off The Grid says:

      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.

  2. Derek Jackson says:

    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

    • Craig says:

      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.

  3. Marek says:

    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

Leave a Reply

Your email address will not be published. Required fields are marked *