Variant Type Nothing

[xcal]

I agree with you. I only meant it tongue-in-cheek.

[Locodarwin]

I haven't seen any big issues from Locodarwin, big_daddy or DaleHohm and they've used COM

extensively

Excel's COM dispatch weirdness notwithstanding, I've been impressed with AutoIt's COM-interfacing implementation overall. In most cases it works quite well and I certainly have no room to complain about it.

I've been watching this thread with interest, though, because I, too, would benefit from a solution to the issue being discussed here.

I would love to see the proposed solution to this issue implemented:

It is treated as a VARIANT of VT_EMPTY unconditionally. According to the VT_EMPTY documentation,

that's the wrong thing to do when the optional argument is an interface. In that case it needs to be

VT_ERROR and the value DISP_E_PARAMNOTFOUND.

There have been a few scattered occasions where I need to skip a parameter that normally holds an interface or requires "Null." That's all fine - I'm not in a hurry. I'm sure the developers have plenty of other things to do. Just add my name to the list of people who would benefit from a fix.

This brings up a situation that has been bothering me for a while now. I'm having trouble with a couple of different Excel methods that don't seem to be behaving like I expect. I'm having an issue with the Excel method .Move(), for example, which is a member of .Sheets. Here's the calling syntax in VB:

Sub Move( _
    <InAttribute()> Optional ByVal Before As Object, _
    <InAttribute()> Optional ByVal After As Object _
)

Okay, so that looks fine and dandy. Two optional objects - A "before" sheet, and an "after" sheet.

The problem is, the documentation for this method (http://msdn2.microsoft.com/en-US/library/microsoft.office.interop.excel.sheets.move.aspx) says this:

Parameters:

Before - Optional Object. The sheet before which the moved sheet will be placed. You cannot specify Before if you specify After.

After - Optional Object. The sheet after which the moved sheet will be placed. You cannot specify After if you specify Before.

So...you can't specify "before" if you've specied "after." Well, trying to put "Default" in place of "before" when what I'm trying to specify is an "after" fails. The following code running on a freshly-created 3-sheet Excel workbook object ($oFoo) causes a COM error:

$oFoo.Sheets("Sheet1").Move(Default, "Sheet3")

The error number is the ol' familiar 80020006 and the WinDescription is "Unknown Name."

If I just specify one parameter, Excel interprets it as the first one in all cases, meaning it puts the referenced sheet "before" the sheet specified in .Sheets().

Isn't this, in theory, supposed to work with two parameters? The C++ syntax for calling this method is:

public: Void Move(

Object^ Before,

Object^ After

);

...yet MS documentation says you can't specify both? How would you call it, then, to make my example work? Null the first parameter?

-S

Edited October 26, 2006 by Locodarwin

[Valik]

Sounds like the same thing. It's expecting an object but currently we aren't specifying the correct thing for a "default/empty/optional" argument so when the function sees the malformed first variable, it doesn't recognize it as being the "default" and bombs. There's a good chance that fixing the OP's issue will fix this. And there's a good chance I can use this function to test the OP's issue.

[Locodarwin]

Great. Shall I submit this issue more formally (e.g. minus the hubub) in the correct forum?

-S

[Valik]

Write me a full example that works. Open a new Excel instance and move "Sheet2" before "Sheet1" or something trivial like that. I just tried something but I'm getting a totally different error than what you reported - even when I try to use only one parameter. And don't use your Excel library, just plain code, two or three lines or whatever it takes to produce the working code.

[Locodarwin]

10-4.

-S

[Locodarwin]

Here's code that works:

$oFoo = ObjCreate("Excel.Application")
$oFoo.Visible = 1
$oFoo.WorkBooks.Add
$oFoo.Sheets("Sheet1").Move($oFoo.Sheets("Sheet3"))oÝ÷ Ø̦h'¬{ayìZ^"Þ¾*.²©¢Ë^t¬yÖ¬¶¸§)駡ö§¡¸ÞrÜ!zzÞ}êÞȧØ^²¶)íã(½é¶N­Á«#ºËZê뢺'*Z®Ó2Âäx¡×¢Ø^P1i®h±©n±ë-ëë2Ö±#fºÈ§«y8^rëyËB8Ç«®âØb±Æ¬z+

-S

Edited October 27, 2006 by Locodarwin

[Valik]

I just tried your code, Locodarwin, and my proposed fix. Surprise, surprise, it worked. I just did a very quick hack and forced the Default keyword to create a VARIANT of type VT_ERROR with an scode of DISP_E_PARAMNOTFOUND like the documentation said should be done for objects. And it of course worked. Now to see if it's possible to determine which way needs used at run-time or if it's going to require new keywords.

[Valik]

It looks like we were doing it wrong from the very beginning. The fact that it worked at all was an unfortunate fluke. The Default keyword should work correctly for skipping parameters in the next release.