This post may contain affiliate links. Please read my disclosure for more info.
Advertisement:

Using Excel 4 Macro Functions

Using Excel 4 Macro Functions - Thumb

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


Advertisement:

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


Advertisement:

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


Advertisement:

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.


Advertisement:

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:

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

Leave a Reply

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