Hiding column via Excel C-Api - Excel 2007

Question

Hi,

I'm trying to hide a column via the C-Api using xlcColumnWidth.

For this I created a wrapper function which gets a bool and if it is true hides its column, if false un-hides.

According to the macro documentation (quoted below) xlcColumnWidth with every parameter set to missing except for "type_num" set to 1 or 2 (depending on whether we want to hide or not) should do the trick.

Since the xlcColumnWidth is "macro sheet only", I registered my wrapper with the following parameter signature "UU#" (Excel 2007), if the # is missing it won't find the function number and return an error.

The problem is: my code runs fine and doesn't give any errors, BUT returns false and doesn't do anything.

I tried calling the function via Excel12v and Excel12 and it is always the same result - when I call other functions, for instance xlfSum everything works fine. Its just this one function that doesn't work.

Any help would be greatly appreciated!

Thanks in advance

--Frieder

EDIT: just to clarify this, after the call to Excel12, the variable return_value (integer) will be xlretSuccess and the variable return_val (XLOPEr12) will be of type xltypeBool with value set to FALSE. 

Here's the code that is inside the wrapper function (C++):

typedef XLOPERX XLOPER12;

// Create an array of XLOPER12s
XLOPERX *xOpArray = (XLOPERX *)malloc(size * sizeof(XLOPERX));
	
// Initialise and populate the array of XLOPER12s
// and set up the pointers in the pointer array
XLOPERX xOpMulti;
xOpMulti.xltype = xltypeMulti;
xOpMulti.val.array.lparray = xOpArray;
xOpMulti.val.array.columns = 1;
xOpMulti.val.array.rows = size;
for(int i = 0; i < size; i++)
{
	if (i == 3)
	{
		xOpArray[i].xltype = xltypeNum;
		xOpArray[i].val.num = hide ? 1.0 : 2.0;
	}else{
		xOpArray[i].xltype = xltypeMissing;
	}
}

XLOPERX return_val;

int return_value = Excel12( xlcColumnWidth, &return_val, 1, xOpMulti);

if( return_value != xlretSuccess || return_val.xltype == xltypeErr) {
        free(xOpArray);
        throw std::exception();
}

Quoted macro documentation:

--------------------------------------------------------------

Macro Sheets Only
Equivalent to choosing the Width command on the Column submenu of the Format menu. Changes the width of the columns in the specified reference.

Syntax

COLUMN.WIDTH(width_num, reference, standard, type_num, standard_num)
COLUMN.WIDTH?(width_num, reference, standard, type_num, standard_num)
Width_num    specifies how wide you want the columns to be in units of one character of the font corresponding to the Normal cell style. Width_num is ignored if standard is TRUE or if type_num is provided.
Reference    specifies the columns for which you want to change the width.

If reference is specified, it must be either an external reference to the active worksheet, such as !$A:$C or !Database, or an R1C1-style reference in the form of text, such as "C1:C3", "C[-4]:C[-2]", or "Database".
If reference is a relative R1C1-style reference in the form of text, it is assumed to be relative to the active cell.
If reference is omitted, it is assumed to be the current selection.

Standard    is a logical value corresponding to the Standard Width command from the Column submenu on the Format menu.

If standard is TRUE, Microsoft Excel sets the column width to the currently defined standard (default) width and ignores width_num.
If standard is FALSE or omitted, Microsoft Excel sets the width according to width_num or type_num.

Type_num    is a number from 1 to 3 corresponding to the Hide, Unhide, or AutoFit Selection commands, respectively, on the Column submenu of the Format menu.

Type_num Action taken

1 Hides the column selection by setting the column width to 0
2 Unhides the column selection by setting the column width to the value set before the selection was hidden
3 Sets the column selection to a best-fit width, which varies from column to column depending on the length of the longest data string in each column

Standard_num    specifies how wide the standard width is, and is measured in points. If standard_num is omitted, the standard width setting remains unchanged.

Remarks

Changing the value of standard_num changes the width of all columns except those that have been set to a custom value.
If any of the argument settings conflict, such as when standard is TRUE and type_num is 3, Microsoft Excel uses the type_num argument and ignores any arguments that conflict with type_num.
If you are recording a macro while using a mouse and you change column widths by dragging the column border, Microsoft Excel records the references of the columns using R1C1-style references in the form of text.

--------------------------------------------------------------

---

Frieder Hofmann

Answer 1

Hi Freder,

Thanks for posting in the MSDN forum.

would you please tell me which kind of C++ your program is. It's based on my experience that we can handle Excel via Use OLE Automation or use CLR C++ invoke Excel's PIA (Primary Interop Assembly). I feel you snippet is too shorter to do more trouble shooting would you please clarify it more clearly?

Have a good day,

Tom

---

Tom Xu [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Hello Tom,

thank you for answering.

This is an umanaged C/C++ program that gets compiled into a *.xll that we then load into excel.

This is the interface we use (the XLL SDK, C-API):

http://msdn.microsoft.com/en-us/library/office/bb687835(v=office.12).aspx

so in this case we don't use PIAs or OLE, but the XLL.

Do you need more information?

Thank you

--frieder

---

Frieder Hofmann

Answer 3

Hi f_hofmann,

I will involve some experts into your issue to see whether they can help you. There might be some time delay, thanks for your patience.

Have a good day,

Tom

---

Tom Xu [MSFT]
MSDN Community Support | Feedback to us

Answer 4

Hello Frieder,

Could you please share the XLL sample with me so that I can troubleshoot the issue? You could upload the file to your skydrive account (if any), and share the link with me.

Thanks,

Sreerenj G Nair

Answer 5

Hello Sreerenj G Nair,

is it enough to provide the binaries or do you need sources as well?

Thanks,

Frieder

---

Frieder Hofmann

Answer 6

Hello Frieder,

It would be better if you could share the source.

Thanks,

Sreerenj G Nair

Answer 7

okay I uploaded a minimal sample.

There's a Readme.txt in there with a step by step explanation on how to reproduce the problem.

As I said, everything works fine, there are no errors, but nothing happens - with the sample workbook, the column "A" should be hidden.

Download Sample

Thanks for looking into it - if there are any questions about it, feel free to ask!

---

Frieder Hofmann

Answer 8

Hello Frieder,

I tested the sample and I saw that the cell displays FALSE and the column was not getting hidden. I did some research on this but couldn't find the cause of the issue.

This would require a more in-depth level of support which falls into paid support category. If the support engineer determines that the issue is the result of a bug the service request will be a no-charge case and you won't be charged. Please visit the below link to see the various paid support options that are available to better meet your needs. http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone

Thanks,

Sreerenj G Nair

Answer 9

Hello and thanks again for your help!

I poked around a bit more and finally found the problem - the code was fine, its just not possible to do it like that. 

The Excel Commands (all callback functions starting with xlc - like for instance xlcColumnWidth) can not be called during recalculation. Thats why there was no error, but the returned value was "false" and nothing happened.

The solution is as follows:

• When the function to hide the column is called, instead of actually hiding it, I just remember the Column that needs to be hidden.
• In our Addin we have a "recalc_handler" that is registered via a call to "xlcOnRecalc" and in there - after the normal calculation is done - I can finally call the xlcColumnWidth function for all cells that !

 

So, this one is solved! 

Here is a snippet from the Microsoft Excel 2010 XLL SDK help file:

---------------------------------------------------------------------------------------------

Commands

In Excel, commands have the following characteristics:

• They perform actions in the same way that users do.

• They can do anything a user can do (subject to the limits of the interface used), such as altering Excel settings, opening, closing, and editing documents, initiating recalculations, and so on.

• They can be set up to be called when certain trapped events occur.

• They can display dialog boxes and interact with the user.

• They can be linked to control objects so that they are called when some action is taken on that object, such as left-clicking.

• They are never called by Excel during a recalculation.

• They cannot be called by functions during a recalculation.

---------------------------------------------------------------------------------------------

---

Frieder Hofmann