Excel optional parameters not working

[zaibot.w]

Here's what I did and the resulting error:

Code: Select all

  xls←⎕new 'OleClient' (⊂'ClassName' 'Excel.Application')
  xls.Workbooks.Add ⍬
  wb ←xls.Workbooks[1]
  ⍝ wb.Protect (⊂'abc') ⍝ This works
  wb.Protect 'abc' 1 ⍝ Doesn't work, although it should
DOMAIN ERROR: DISP_E_EXCEPTION (E_INVALIDARG)
  wb.Protect 'abc' 1 1 ⍝ Doesn't work, although it should
DOMAIN ERROR: DISP_E_EXCEPTION (E_INVALIDARG)

Here's the Microsoft reference:
https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.protect

Does anybody know what's going on here? Am I doing something wrong?

Thanks!

[JohnD|Dyalog]

Hi,

The issue here is with the types of the "Structure" and "Windows" parameters. They are *declared* as being Variant, which the interpreter takes as meaning that multiple types are acceptable. The interpreter passes the 1s and 0s as 32 bit integer values. However the *documentation* states that they must be Boolean values, and so the 32 bit integer values are rejected by Excel, and an error is raised.

Fortunately the interpreter provides a mechanism to view and *change* the declared parameter types:

Code: Select all

⎕←info←wb.GetMethodInfo 'Protect' ⍝ see that the last two parameters are of type VT_VARIANT:
┌──────────┬───────────────────────┬────────────────────────┬──────────────────────┐
│┌┬───────┐│┌──────────┬──────────┐│┌───────────┬──────────┐│┌─────────┬──────────┐│
│││VT_VOID│││[Password]│VT_VARIANT│││[Structure]│VT_VARIANT│││[Windows]│VT_VARIANT││
│└┴───────┘│└──────────┴──────────┘│└───────────┴──────────┘│└─────────┴──────────┘│
└──────────┴───────────────────────┴────────────────────────┴──────────────────────┘
  (2⊃¨¯2↑info)←⊂'VT_BOOL' ⍝ change to a boolean type:
⎕←info
┌──────────┬───────────────────────┬─────────────────────┬───────────────────┐
│┌┬───────┐│┌──────────┬──────────┐│┌───────────┬───────┐│┌─────────┬───────┐│
│││VT_VOID│││[Password]│VT_VARIANT│││[Structure]│VT_BOOL│││[Windows]│VT_BOOL││
│└┴───────┘│└──────────┴──────────┘│└───────────┴───────┘│└─────────┴───────┘│
└──────────┴───────────────────────┴─────────────────────┴───────────────────┘
    wb.SetMethodInfo 'Protect' info ⍝ update the information in the function.

And now:

Code: Select all

    wb.Protect'abc' 1 0 ⍝ no Error
    wb.Protect'abc' 1   ⍝ no Error