Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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))
like image 524
millsofmn Avatar asked Oct 14 '13 13:10

millsofmn


1 Answers

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.

like image 58
RBarryYoung Avatar answered Sep 29 '22 06:09

RBarryYoung