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.
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
Enter your email address below to download the PDF.
A PDF version of the official Microsoft help file for Excel 4 macros.
A complete reference for all Excel 4 macros including syntax and examples.
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.
Great job, thank you for the document.
And some correctioins may be updated: Functions from E to G are duplicated.
Thanks. I’ve updated the PDF and removed the duplicates.
regards
Phil
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.
You’re welcome Jeff, glad it was helpful.
Regards
Phil
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
Hi Alan,
MATCH is a modern function so I’d use the modern version rather than any old XL4 implementation.
Regards
Phil
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
Hi Alan,
You could consider using Power Query to get data from closed workbooks.
Regards
Phil
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
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
I love the exercise. Great insight and will make me more productive
Thanks, glad it was helpful
ExecuteExcel4Macro (“GET.WORKSPACE(10)”) how does this work in VBA macro?
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
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
Thanks Jerry, interesting t hear that perhaps finally the old XL4 macros aren’t supported.
Very useful. Thank you.
Thx Juan
Perfect thanks
no worries
Great, this is what I really need. Thanks.
No worries
thanks
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.
Thanks Janusz, glad to help 🙂
Great!
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
You’re welcome Jean.
Regards
Phil
Cool! I found something I was looking for an hour!
Amazing.
The huge amount of time invested in this comprehensive, easy-to-use manual should be highly appreciated.
Well done, Philip.
Thanks a lot.
Thanks Meni.
Thank you Phil for providing us such a document!
Best regards,
Chuck
You’re welcome.