How can I reverse engineer a (deprecated) Excel V4 macro?

Question

I have been tasked with figuring out that this does. I haven't seen this type of language/formulas before and when I browse the web for these formulas I can't seem to find any concrete definitions as to what each function does. There are 3 sheets to this workbook: Query Sheet, Data Sheet and Macro1. These are showing up on Macro1.

It would be nice to find an API, help or something to figure this out.

Cmd ExtractData (a)
    =DEFINE.NAME("Criteria",'Query Sheet'!P1:W2)
    =DEFINE.NAME("Extract",'Query Sheet'!$A$7:$N$7)
    =DEFINE.NAME("Database",'Data Sheet'!Database)
    =EXTRACT(FALSE)
    =IF(ISBLANK(!A8))
    =  SELECT("r8c1")
    =  FORMULA("No Match Found")
    =ELSE()
    =  SELECT("r7c1")
    =  SELECT.END(4)
    =  ROW(ACTIVE.CELL())
    =  FORMULA.FILL("=Serr_(RC[-9],RC[-8],RC[-7],RC[-6],RC[-3],RC[-2],RC[-1])","R8C14:R"&TEXT(B13,0)&"C14")
    =  SELECT("R8C1:R"&TEXT(B13,0)&"C14")
    =  IF(mSTB="U")
    =    SORT(1,"R8C9",2,"R8C14",1)
    =  ELSE.IF(mSTB="S")
    =    SORT(1,"R8C9",1,"R8C14",1)
    =  ELSE()
    =    SORT(1,"R8C14",1)
    =  END.IF()
    =  SELECT("R8C1")
    =END.IF()
    =RETURN()
    
Fctn    SErr_
    =RESULT(1)
    =ARGUMENT("Acet",1)
    =ARGUMENT("Agar",1)
    =ARGUMENT("PH9",1)
    =ARGUMENT("PH6",1)
    =ARGUMENT("IEF",1)
    =ARGUMENT("Short",1)
    =ARGUMENT("Long",1)
    =VarM_(!mAcet,Acet)
    =VarM_(!mAgar,Agar)
    =VarM_(!mPH9,PH9)
    =VarM_(!mPH6,PH6)
    =VarM_(!mIEF,IEF)
    =VarM_(!mShort,Short)
    =VarM_(!mLong,Long)
    =RETURN(SUMSQ(B36:B42))
    
    
Fctn    VarM_
    =RESULT(1)
    =ARGUMENT("mNT",3)
    =ARGUMENT("sN",1)
    =IF(OR(ISTEXT(mNT),sN= -99),RETURN(0), RETURN(mNT-sN))

I believe that this is called a "V4 Macro" in Excel. They're from ***way*** back in Excel V4 (Excel 2010 is V14) and they're deprecated but still runnable (I think). — RBarryYoung, Oct 14 '13 at 14:05
@RBarryYoung To quote Neo, [whoa](http://www.picturenose.com/wp-content/uploads/Neo_Whoa.jpg)! — JMK, Oct 14 '13 at 14:22

score 5 · Accepted Answer · edited Jul 19 '21 at 12:29

This is called a "V4 Macro" in Excel. They're from way back in Excel V4 (Excel 2010 is V14) and they're deprecated but still runnable.

These V4 macros have an old help file here: http://support.microsoft.com/kb/128185

You can download and install that, but the result is a WinHelp file, which is also archaic and does not work out of the box in Windows anymore. If you can get a WinHelp reader to work you should be able to use this to figure out what's going on here. MS says that they have WinHelp readers available for download up through Windows 8, but I cannot get the windows 7 version to work for me.

That said, it seems pretty clear to me what most of the commands used here are:

DEFINE.NAME(name,range)    Defined a Named Range
EXTRACT(False)             Probably writes FALSE to the "Extract" named range
IF(..), ELSE()             Implements an "If..Then..Else.."
SELECT(range)              Selects the cell or range indicated
SELECT.END(4)              Selects to the end of something (row, column or sheet?)
FORMULA(string)            Write the string to the selection as it's formula
FORMULA.FILL("<<formula>>",range)
                           Autofills the formula to the range.  Uses R1C1 addressing.
SORT(..)                   Probably invokes the Excel Data Sort
RETURN()                   Ends the Command and returns
RETURN(expression)         Ends the function and returns the value of the expression

ARGUMENT("name",number)    Assigns names to the functions parameters in order.
                           (no idea what the number does here)

Almost everything else are Excel formula functions, so you can still look them up.

`EXTRACT(unique)` - "Equivalent to choosing the Extract command from the Data menu in Microsoft Excel version 4.0. Finds database records that match the criteria defined in the criteria range and copies them into a separate extract range. Unique is a logical value corresponding to the Unique Records Only check box in the Extract dialog box. If unique is TRUE, Microsoft Excel selects the check box and excludes duplicate records from the extract list. If unique is FALSE or omitted, Microsoft Excel clears the check box and extracts all records matching the criteria." — barrowc, Oct 15 '13 at 01:54
`SELECT.END(4)` - "Selects the cell at the edge of the range in the direction specified. Equivalent to pressing CTRL+ARROW in Microsoft Excel for Windows" 1 = Left, 2 = Right, 3 = Up, 4 = Down — barrowc, Oct 15 '13 at 01:58

How can I reverse engineer a (deprecated) Excel V4 macro?

1 Answers1