Forum Discussion

yushang's avatar
yushang
Brass Contributor
Mar 22, 2023

Is possible to create compound data in traditional formula?

Hi guys,

I found Excel linked data type is convenient and from the following figure, I found its type is compound data (formula in A2 is TYPE(A1)). So my question is, how to create such value in my user-defined function? There is an example of compound data, but it uses Office-js. Many thanks!

 

Excel
  • lori_m's avatar
    lori_m
    Steel Contributor
    Mar 22, 2023

    yushang 

    One method might be to create a lambdas function by nesting SWITCH functions for dictionaries and CHOOSE functions for lists. Translating the linked example gives the definition below.  Enter '=image' in a cell to test...

    getArticle
=LAMBDA(a, [b], [c],
    SWITCH(
        a,
        "type", "Entity",
        "text", "Create a Personal Macro Workbook",
        "properties", SWITCH(
            b,
            "publishDate", SWITCH(
                c,
                "type", "FormattedNumber",
                "basicValue", 44505,
                "numberFormat", "m/d/yyyy"
            ),
            "author", SWITCH(
                c,
                "type", "string",
                "basicValue", "Bill Jelen"
            ),
            "image", SWITCH(
                c,
                "type", "Webimage",
                "address", "https://www.mrexcel.com/img/excel-tips/2021/11/create-a-personal-macro-workbook-sm.jpg"
            )
        )
    )
);

image
=IMAGE(getArticle("properties","image","address"))

     

Resources