Restrict usage of Excel 4.0 (XLM) macros

[Bart Duijndam]

Greetings,

It has just been announced that Microsoft will restrict usage of Excel 4.0 (XLM) macros in an upcoming update of Office 365. See for instance:  https://thewindowsupdate.com/2021/07/22/restrict-usage-of-excel-4-0-xlm-macros-with-new-macro-settings-control/

I am using these macro's to create Dialog Boxes accessible from the Ribbon Interface. See: https://github.com/MrBeee/XlDialogBox

My concern is that through Admin Group Policies the XLM macro's will be completely disabled at the company where my Excel-DNA AddIn has been deployed.

If this were true, the only viable alternative I see at the moment would be to split the AddIn into two parts: (a) a core library using Excel-DNA, and (b) a GUI library based on VBA. But this would be messy in view of having to maintain two AddIns that need to stay "in sync".

Are there any viable alternatives that could avoid this situation ?

I'd appreciate any advice to stay away from VBA whilst using dialogs from the Ribbon interface that interact with the active sheet (think of the RefEdit control). 

Cheers !

[Govert van Drimmelen]

Hi Bart,

I should first say that the XlDialogBox helper looks like a wonderful project.

MrBeee/XlDialogBox: C# class to create dialog boxes with Excel-DNA, based on Excel 4.0 XLM macro functionality (github.com)

I have tested the XlDialogBox helpers with the indicated macro settings switched off.

For me it still seems to work - have you tried it and found problems?

I am not 100% sure of this, but my understanding is that the feature Excel is concerned about is the one where Excel 4.0 macros are part of a workbook as a special type of sheet called a macro sheet.

These were still supported but not so easy to create (you can't through the normal UI) and now subject to extra security checks.

See a bit more here: Programming with the C API in Excel | Microsoft Docs

So I do not expect these changes to affect the ability to call the Excel C API and related macro features.

We're using the macro features through the C API and I have no reason to think is in danger of being disabled.

If you have any information to the contrary, I would be very interested.

There is and out-of-date project that explored the interaction between WPF and WinForms with Excel.

Ron-Ldn/DotNetRefEdit: Examples of RefEdit like controls for Excel add-ins using C# and ExcelDna (github.com)

But it needs a bit of attention to get updated and running, if I remember right.

For now if you needs are covered by the XlDialogBox, that looks pretty good to me, if you agree with me that it still works with the new security settings.

Trying to anticipate the Office and Excel team's next moves has never been useful to me.

-Govert

[Bart Duijndam]

Hi Govert,

Thanks for your kind words. 

To get a better grip on the Excel 4.0 (XLM) macro's I managed to buy a second hand book (Greg Harvey's Excel 4.0 for Windows) and as a result, I have made some updates to the XlDialogBox class. I still need to upload these to GitHub, and will do so the coming week(s). For me the sole reason to use an Excel 4.0 macro-sheet is that I can initiate the DIALOG.BOX calls from that sheet. And by giving the macro a shortcut (Ctrl+shift+D) I can open the dialog box under development from anywhere (My end-users don't require this functionality).

This trick comes in handy, as I can now use normal work sheets to develop the layout of a dialog, and can quickly export this as a "dialog_ref " in plain text. It is not completely WYSIWYG, but it comes pretty close.

I'll check with my end-users if they have any trouble opening the dialogs from a Ribbon button.

Best regards,

Bart

[Bart Duijndam]

Hi Govert,

I just learned from my end-users that the dialogs, called from the Ribbon of my Add-in  (and are based on the Excel 4.0 macro call DIALOG.BOX) are no longer functioning correctly in Office-365. I double-checked this on my laptop (that I don't use for code development) and I could replicate this behavior.

To 'fix' this problem, one has to open Trust Center (from File -> Options) and in Macro Settings select: Enable Excel 4.0 macros when VBA macros are enabled.

This make the use of the DIALOG.BOX for dialog boxes still a feasible option, ... but somewhat less attractive, as users are being discouraged in enabling his flag.

Cheers,

Bart

On Sunday, 10 October 2021 at 23:21:16 UTC+2 gov...@icon.co.za wrote:

[Govert van Drimmelen]

Hi Bart,

To be clear, you are just making the macro calls from code in an Excel-DNA add-in - no macro sheets involved?

Can you post the exact version you're seeing this with (the need for the extra checkbox to be on)?

When I tested a while ago with a version that already had the checkbox, it did not seem like a problem.

I also wonder if a native add-in that is already running in the process can change the setting (presumably in the registry) and have the result apply (with or without restarting Excel).

I would be surprised if xlcDialogBox is the only call affected, so I'm curious at the scope of the change they have in mind.

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/6a8e8135-7808-4fa1-8e30-47cd8261a806n%40googlegroups.com.

[Bart Duijndam]

Hi Govert,

To elaborate a bit; 

Yes, the dialog boxes are still shown, so that is fine.

But the dialog box is there for a reason; you first collect user input, and once the user presses OK, you use this input to update cell content.

This is were some things go wrong. I observe the following behavior:

A) I can still use Excel calls to create a new worksheet and edit cells to change their values, formatting or formulas. 

See the next few lines, these all work fine:

            Worksheet SourceSheet = xlApp.ActiveWorkbook.ActiveSheet; 

            Worksheet xlWs = xlWb.Worksheets.Add(Type: XlSheetType.xlWorksheet);

            xlWs.Activate();

            xlWs.Range["C3"].Value = "Layers";

            xlWs.Range["D3"].Value = "Z";

            xlWs.Range["E3"].Value = "dZ";

            xlWs.Range["F3"].Value = "Vp0";

            xlWs.Range["G3"].Value = "Kz";

            xlWs.Range["H3"].Value = "Vp @Top";

            xlWs.Range["I3"].Value = "Vp @Bottom";

            Range Header = xlWs.Range["C3", "I3"];

            Header.Font.Bold = true;

            Header.BorderAround(Type.Missing, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic, Type.Missing);

            // Generally it's faster to write an array to a range

            object [,] formulas = new object[DI_Rows + 1, 7];

            formulas[0, 0] = "Dummy";

            formulas[0, 1] = 0.0;

            // 1st column contains layer names

            for (int i = 0; i < DI_Rows; i++)

                formulas[i + 1, 0] = $"Layer {i + 1}";

B) But when I now try to populate a range with an AddIn function that returns an array, funny things happen:

            string raySegments = $"=GL2.ray.Segments({DI},{Vp},{Pv},{dZ},{SP},{Un})";

            cell1 = xlWs.Cells[row, 3];

            cell2 = xlWs.Cells[row + nLevels + 3, 3 + NrOfRays];

            range = xlWs.get_Range(cell1, cell2);

            // for dynamic arrays, we just need to post the GL2.ray.Segments formula in the right cell

            if (SupportsDynamicArrays())

            {

                cell1.FormulaR1C1 = raySegments;

            }

            else

            // for static CSE arrays, we first need to select the output area for the GL2.ray.Segments formula

            {

                range.Select();

                range.FormulaArray = raySegments;

            }

We all know, Office 365 supports dynamic arrays, overflowing where necessary, but only the Top Left cell is now given a value.

The remainder of the cells in the range all stay empty.

Before the security update, the code above worked fine. It is puzzling me.

The problem can be 'fixed' by enabling Excel 4.0 macros in the Trust Center.

What I can still try is to force Office 365 to use the whole range, as would be done using Office 2016.

I hope this helps in clarifying the problem.

Cheers,

Bart

[Govert van Drimmelen]

Hi Bart,

That sounds like an interesting compatibility issue, but not one that would discourage DIALOG.BOX.

Maybe they're incorporating some compatibility switch for the COM object model with the security feature?

I'll try to explore too when I get a chance, but please keep posting what you find!

-Govert

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/8af4a921-701a-455b-94a6-e36353ed83ecn%40googlegroups.com.

[Bart Duijndam]

Hi Govert,

I just enforced using a CSE fixed array size by changing the code to:

           // for dynamic arrays, we just need to post the GL2.ray.Segments formula in the right cell

            if (false)

            {

                cell1.FormulaR1C1 = raySegments;

            }

            else

            // for static CSE arrays, we first need to select the output area for the GL2.ray.Segments formula

            {

                range.Select();

                range.FormulaArray = raySegments;

            }

Now, on 64-bit Excel 365, everything works as intended. That is:

1. All cells in the array have a text/formula value entered in the whole array
2.  The formula that accomplishes this is shown as follows:
   {=GL2.ray.Segments($D$5:$D$8,$F$5:$G$8,$D$13:$BB$13,$L$7,$L$8,$L$4)}
   

So, the curly brackets are back ;-). Not really what you want with Office 365, but at least the array functionality is working now...

And I just double-checked : In Trust Center the flag "Enable Excel 4.0 macros when VBA macros are enabled" is not checked. 

I'll do one more check to see if I can replicate this behavior, by going back to my old code that checks for SupportsDynamicArrays().

If the 'old error' then comes back, it would be good to know if there is a registry setting for "Enable Excel 4.0 macros when VBA macros are enabled".

If that were to be the case, I can expand the if-then-else check honoring the setting of this security feature.

Although I really would like to avoid any CSE solution when 'modern' Excel dynamic arrays are available....

Cheers,

Bart

[Govert van Drimmelen]

Should you not be using Range.Formula2 under dynamic-arrays Excel?

-Govert

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/a85e2a7c-c96f-467b-84fd-42dd14fa8ccbn%40googlegroups.com.

[Bart Duijndam]

Hi Govert,

That's a good catch; because after recompiling I now get the following result in the top left cell of the array:

=@GL2.ray.Segments($D$5:$D$8,$F$5:$G$8,$D$13:$BB$13,$L$7,$L$8,$L$4)

I don't really understand why it now behaves differently, and after removing '@' things are fine, but '@' should not be there in the first place .

In the mean time I found out some relevant security settings for Excel that may be useful i this context:

            int VBAMacroWarning = (int) Microsoft.Win32.Registry.GetValue("HKEY_CURRENT_USER\\SOFTWARE\\Microsoft\\Office\\16.0\\Excel\\Security", "VBAWarnings", -1);

            int XL4MacroWarning = (int) Microsoft.Win32.Registry.GetValue("HKEY_CURRENT_USER\\SOFTWARE\\Microsoft\\Office\\16.0\\Excel\\Security", "XL4MacroWarningFollowVBA", -1);

            int RequireAddinSig = (int) Microsoft.Win32.Registry.GetValue("HKEY_CURRENT_USER\\SOFTWARE\\Microsoft\\Office\\16.0\\Excel\\Security", "RequireAddinSig", -1);

(You get -1 when the key isn't present; normally 0 or positive values are used for registry keys)

Of course, the above registry calls won't work anymore when Office is bumped up to version 17.0.  One of the joys of working with Microsoft products.

In any case, I'll update to Range.Formula2 and let you know the outcome...

Thanks very much for your help !

Cheers,

Bart

[Bart Duijndam]

Govert - Still working on this, but I seem to be lacking Range.Formula2 in package(s) I'm using.

Available under Range.cs are the following formula-related functions:

        // Summary:

        //     Returns or sets the object's formula in A1-style notation and in the language

        //     of the macro.

        [DispId(261)]

        dynamic Formula { get; set; }

        //

        // Summary:

        //     Returns or sets the array formula of a range.

        [DispId(586)]

        dynamic FormulaArray { get; set; }

        //

        // Summary:

        //     Returns or sets the formula label type for the specified range.

        [DispId(1380)]

        XlFormulaLabel FormulaLabel { get; set; }

        //

        // Summary:

        //     Determines if the formula will be hidden when the worksheet is protected.

        [DispId(262)]

        dynamic FormulaHidden { get; set; }

        //

        // Summary:

        //     Returns or sets the formula for the object, using A1-style references in the

        //     language of the user.

        [DispId(263)]

        dynamic FormulaLocal { get; set; }

        //

        // Summary:

        //     Returns or sets the formula for the object, using R1C1-style notation in the

        //     language of the macro.

        [DispId(264)]

        dynamic FormulaR1C1 { get; set; }

        //

        // Summary:

        //     Returns or sets the formula for the object, using R1C1-style notation in the

        //     language of the user.

        [DispId(265)]

        dynamic FormulaR1C1Local { get; set; }

        //

        // Summary:

        //     Determines if the specified cell is part of an array formula.

        [DispId(266)]

        dynamic HasArray { get; }

        //

        // Summary:

        //     Determines if all cells in the range contain formulas.

        [DispId(267)]

        dynamic HasFormula { get; }

I'm hesitant to update the packages as the new ones aren't all compatible with ExcelDnaDoc....

So I'll check first if if Formula or FormulaArray (instead of FormulaR1C1) solve my problem here...

Cheers,

Bart

[Govert van Drimmelen]

Just call it late-bound.

 

If ‘range’ refers to the one top-left cell where you want to anchor the dynamic array.

Then call:

 

dynamic rangeDyn = range;

rangeDyn.Formula2 = raySegments;

 

Range.Formula2 is probably not in any ‘package’ you’re looking at, but it is a method on the Range COM class in versions of Excel that support Dynamic Arrays.

And you have to use it to get the non-@ behaviour you want for setting dynamic array values into a cell.

 

-Govert

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/c280bd62-55ae-44db-8318-c0770e4d8994n%40googlegroups.com.

[Bart Duijndam]

Hi Govert,

I have no clue how this works, or why it works, but it does work !

And no need to enable the Excel 4.0 macro settings in the Trust Center either...

Maybe I should familiarize myself with the dynamic keyword ?

In any case, thanks very much for your help.

To give back to the community, I've just updated  my Excel dialog project:

See : https://github.com/MrBeee/XlDialogBox 

It now supports Icons, and I have expanded the spreadsheet with several example dialogs.

I hope this may be of use.

Kind regards,

Bart

[Govert van Drimmelen]

I guess the things that is disabled by the new setting is the macro sheet you use for your example.

That macro sheet then has a cell with =DIALOG.BOX(F9:L19).

But when called as xlcDialogBox through the C API it still works fine.

 

Do you think I have it right?

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/4a16d869-c7b3-4719-bc36-f0649374d283n%40googlegroups.com.

[Bart Duijndam]

Hi Govert,

With hindsight, the new setting fo Excel 4.0 macros did not affect running a call to DIALOG.BOX( ... ) from a ribbon command.

There must have been other changes in Excel that now necessitate using  Formula2 to get good results with dynamic arrays.
(after all, it did work before)

With respect to the macrosheet in the examples spreadsheet DialogBox.xlsb, I use it to quickly test various dialog layouts.

You select 0 ... 4 in cell C2 of the macros sheet, then press Ctrl+Shift+D, and a dialog is being launched.

This makes it easy to quickly edit / update the layout of a dialog, without having to compile it in VS2019.

Once again, thanks for pointing out how Formula2 should be used.

If you would have more articles explaining how this mysterious COM interface works, that would be great.

Cheers,

Bart

[Govert van Drimmelen]

Some Dynamic Array related links, including Formula vs Formula2 here:  Dynamic Arrays · Excel-DNA/ExcelDna Wiki (github.com)

-Govert

[Andy Sprague]

I'm currently getting the error "NetOffice.ExcelApi.Range" does not contain a definition for 'Formula2', even in the late-bind version:

dynamic rangeDyn = range;

rangeDyn.Formula2 = raySegments;

Do you have a suggestion on how to fix this? I'm using packages Excel-Dna 1.5.1 and NetOfficeFw.Excel 1.8.1

Thanks, Andy

[Govert van Drimmelen]

Hi Andy,

 

NetOffice wraps the COM object model in a .NET object model.

So even if the COM object exposes the Formula2 property, the NetOffice object model might not.

You probably don’t need to use NetOffice anymore, since .NET 4 supports the “Embed Interop Types” option.

But even with a reference to the Excel PIA assembly, I’m not sure whether the Formual2  is exposed for early-binding, so you might still need the dynamic in that case.

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/8bee4be1-a632-42fc-882f-09ff63006702n%40googlegroups.com.

[Bart Duijndam]

Hi Andy,

I trust you followed the complete discussion around Formula2 in this thread ?

The way I have implemented use of Formula2 is as follows:

            // for dynamic arrays, we just need to post the GL2.ray.Segments formula in the right cell

            if (Lib.SupportsDynamicArrays())
            {
                dynamic rangeDyn = cell1;

                rangeDyn.Formula2 = raySegments;

                // cell1.Formula = raySegments;
            }
            else
            // for static arrays, we first need to select the output area for the GL2.ray.Segments formula
            {
                range.Select();
                range.FormulaArray = raySegments;
            }

So things work for both the 'old' and 'new' versions of Excel.

Cheers,

Bart

[Andy Sprague]

Thanks both, your comments have lead me to my solution: "So even if the COM object exposes the Formula2 property, the NetOffice object model might not." seems true.

I have resolved by changing to the excel COM object model (just for the assignment to Formula2, I have a productionized app using NetOfficeFw.Excel, so not in a position to move away from that yet)

This is my extension function, anything to comment on? Either way leaving here may be useful for someone else searching the same thing

public static void SetFormula2(this NetOffice.ExcelApi.Range cell, string formula)
{
        try
        {
                cell.Worksheet.Activate();
                var comXlApp = (Microsoft.Office.Interop.Excel.Application)ExcelDnaUtil.Application;
                dynamic range = comXlApp.Range[cell.Address];
                range.Formula2 = formula;
        }
        catch
        {
                //Probably older version of excel
                cell.Select();
                cell.FormulaArray = formula;
        }
}

Bart - I have left the catch in to fallback to FormulaArray for older versions of excel - It wasn't clear in my original question but expect Formula2 to work as I (and all my users) are on Office 365.

Thanks,

Andy