My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

You are here: Home / Excel VBA / Excel 4.0 Macro Functions
excel 4 macro functions
by

Before VBA there were XLM macros. VBA was introduced in Excel version 5, so these Excel macros are also known as Excel 4 macros.

Even though these XLM macros are quite old they still work in Excel 2016. To use them you call them like a regular function e.g. =GET.CELL(64,A1) but entering this into a cell will give you an error.

What you need to do is combine them with defined names and then you can do some cool stuff.

You can do things that might normally require writing VBA, so if you don't fancy doing any coding check these out. Listing files in a folder, highlighting cells containing formulas, or getting the background color (ColorIndex) of a cell. Once you know a cell's background color, you could then do math with cells of the same color like sum, average etc.

Note: the last link to Jan Karel Pieterse site has other examples too.

The trouble I had was that I couldn't easily find any reference to the Excel 4 macros. Because they were written for such an old version of Excel, the help file for the macros is no longer a supported format as of Windows 10.

Even if you did have a Windows 7 or 8 PC you'd have to download an executable file from Microsoft, install this, then update Windows to allow you to open the help file. I know because I built a Windows 7 virtual machine on Azure and did all of this. It was just too hard.

So I decided to put together this 653 page reference eBook. It basically contains the official Microsoft Excel 4 macro functions help file, but in a useful PDF format.

Free eBook - Excel 4 Macro Functions Reference

Excel 4 macro functions reference ebook

A complete reference for all Excel 4 macros including syntax and examples.

Enter your email address below to download this free PDF.

By submitting your email address you agree that we can email you our Excel newsletter.

You may not find all of these macros will be useful, but some certainly are.

This is provided for reference and if you are curious to see what you can do.

Don't go building mission critical models with these, support for them could be withdrawn in the future.

excel 4 macro functions

More Excel VBA Posts

Display All Matches from Search in Userform ListBox
Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts
Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms
Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables
PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel
Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list
Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms
Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports
Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform
Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba
Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.
Category:

Reader Interactions

Comments

  1. R

    Great job, thank you for the document.

    And some correctioins may be updated: Functions from E to G are duplicated.

    Reply

  2. Jeff

    Thank you so much for the refence PDF to the 4.0 macro language!
    I have one of the original paperback manuals, but when I’m away from my office, I can now use your Excel 4.0 Macro Functions Reference.pdf.

    We have a massive formatting macro that still uses that syntax & having that reference book is very helpful when having to go back and adjust syntax.

    Reply

  3. Alan Elston

    Hi
    I have just heard about these Excel 4 Macro things. It came about after using a VBA code line of the form, pseudo
    x = ExecuteExcel4Macro(“MATCH(something,FullClosedWorkbookReferrence,0)”)
    This was very useful for getting the row number in a closed workbook for something. ( That code line returns me in x , the position along the range given by FullClosedWorkbookReferrence of where it finds something

    I am having difficulty relating that to what you are talking about. I can’t even find the MATCH function in your Excel 4 Macro Functions Reference.

    Alan

    Reply

    • Philip Treacy

      Hi Alan,

      MATCH is a modern function so I’d use the modern version rather than any old XL4 implementation.

      Regards

      Phil

      Reply

      • Alan Elston

        I have been using simple formulas, such as Match with closed workbook references in them as an efficient way to get information out of closed workbooks for a few years. Sometime the performance is much better than the most advanced data retrieval techniques. Because in VBA the Evaluate function does not work with closed workbook references, automating this technique with VBA means putting a formula into a cell, then converting it to a value , then taking the value in. Its still quite good. Match is one formula which can be used like this
        Stumbling on a code line like I mentioned was quite exciting, since it comes close to working as Evaluate would if it could work with closed workbook references.
        So I wanted to get a bit more clued up on Excel 4.0 Macro Functions to see what other jems are to find.
        I can’t follow so easily yet any of the Blogs on using them .
        I will keep looking.
        Thanks for the reply, Phil, stay safe and healthy.
        Alan

        Reply

          • Alan Elston

            Hi Phil
            Sql / ADO and co. things work as well. Good on Text files and .xls Excel files. Slow on .xlsm .xlsx and .xlsb files
            But all too slow for me
            Power Query is on my list of things to get around to learn. But I need a solution that can be incorporated into other VBA coding. So I doubt it would be any good, but that’s just a guess. – Cant imagine it could come close to the performance I have.

            I think there could be some potential in this ExecuteExcel4Macro( with MATCH ) and maybe who knows what else. ***

            Along with a lot of other things, I compared some time ago the well known ExecuteExcel4Macro( with closed workbook path ) way of doing it. But that was strangely orders of magnitude slower than the way I do it for a larger range.

            ***I did just try both ExecuteExcel4Macro( with INDEX ) and ExecuteExcel4Macro( with INDEX with MATCH ) and its working on closed workbooks. This is exciting. I must find time to investigate fully and compare with my other ways…

            Happy Holidays, stay safe.
            Alan

          • Philip Treacy

            Hi Alan,

            PowerShell may offer you some tools to do what you want. I know you can manipulate Excel files with it but I have never used it.

            Regards

            Phil

    • Philip Treacy

      Hi,

      Not sure what you mean by ‘how does it work in VBA macro’?

      To use XL4 macros define a name -as explained in this article.

      What GET.WORKSPACE does is to return information about the workspace. WORKSPACE(10) gives you a number indicating special modes of Excel.

      Phil

      Reply

  6. Jerry Harper

    FYI,

    For years, starting about 2006, I have been using CellHasFormula to shade all cells with formula and all blank cells in a large workbook. I am running on a Win10 1809 latest version PC. November 21, 2018 I switched to from MS Office 2016 Pro Plus to MS Office 2019 Pro Plus. No issues with existing workbooks. On 2/1/2019 this was upgraded to Office 2019 to Version 1901 (build 11231.20130 Click-to-run). About that time I started having problem with a large XLSM workbook. It would not open unless I used Open-repair every time. I repaired ExceI, reloaded Win 10, version 17763.292, killed all excel add-ins … I tried everything and nothing helped, but open- repair. Today I believe I finally found a solution. Apparently Excel 2019 does not like Excel 04 functions. As a last resort today I tried to convert the XLSM file to XLSX file and got the error message about having an Excel 04 as a named function. The first helpful error message in nearly 2 weeks. After deleting the CellHasFormula =Get:CELL(48,INDIRECT(“rc”,FALSE)) named function with name manager I was able to save the file as XLSL. Now Excel appears to be working correctly. I have converted 20+ worksheets in the workbook to use conditional formatting with =isformula, and =isblank, to identify cells with formula and blank cells. Hopefully this is the fix. Time will tell.

    Jerry Harper

    Reply

  11. Janusz Dorożyński

    Phil, I amso happy that in our world there are such wonderful guys like you who do not hesitate to share their very valuable works that require great effort and time. Many thanks.

    Reply

  13. MONVILLE

    I was looking for such a document for eons…
    Actually it’s a bit late but as I have still Excel 4 programs, I am happy to have it
    Thank you very much.
    Jean MONVILLE

    Reply

  15. Meni Porat

    Amazing.
    The huge amount of time invested in this comprehensive, easy-to-use manual should be highly appreciated.

    Well done, Philip.

    Thanks a lot.

    Reply

Leave a Reply

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

Current ye@r *