Excel optional parameters not working

Using (or providing) components based on the "Win32" framework

Excel optional parameters not working

Postby zaibot.w on Mon Jul 11, 2022 10:53 am

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!
zaibot.w
 
Posts: 6
Joined: Mon Jun 29, 2020 4:09 pm

Re: Excel optional parameters not working

Postby JohnD|Dyalog on Tue Jul 12, 2022 10:22 am

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
User avatar
JohnD|Dyalog
 
Posts: 74
Joined: Wed Oct 01, 2008 9:35 am


Return to Windows: GUI, COM/OLE/ActiveX

Who is online

Users browsing this forum: No registered users and 1 guest