Excel Add-In set range value strange behavior

Add-in Express™ Support Service
That's what is more important than anything else

Excel Add-In set range value strange behavior
Excel Add-In set range value strange behavior if hidden columns and filtered columns 
Ervin Gegprifti




Posts: 2
 Joined: 2015-02-17
Using Add-in Express version 9.5.4661
Using Excel 365

My data is as follow:
A B C
1 ID Name City
2 1 Carl Vienna
3 2 Marie Paris
4 3 Aina Helsinki
5 4 Martine Brussels
6 5 Lorenzo Rome

I am getting different results when trying to change the range value as: 
range = worksheet.Range["$A$4:$C$4"];
object[] newValue = { 3, "Aira_New", "Helsinki" };
range.Value2 = newValue;


CASE 1. Data is just plain cells:
1.1 No hidden columns + no filters -> change as expected.
1.2 Column A hidden + no filters -> change as expected.
1.3 No hidden columns + filter on column C (omitting Rome) -> change as expected.
1.4 Column A hidden + filter on column C (omitting Rome) -> PROBLEM new values are shifted on the right by one.

CASE 2. Data is converted to table
2.1 A cell inside the table is selected
2.1.1 No hidden columns + no filters -> change as expected.
2.1.2 Column A hidden + no filters -> change as expected.
2.1.3 No hidden columns + filter on column C (omitting Rome) -> change as expected.
2.1.4 Column A hidden + filter on column C (omitting Rome) -> PROBLEM new values are shifted on the right by one.
2.2 A cell outside the table is selected
2.2.1 No hidden columns + no filters -> change as expected.
2.2.2 Column A hidden + no filters -> change as expected.
2.2.3 No hidden columns + filter on column C (omitting Rome) -> change as expected.
2.2.4 Column A hidden + filter on column C (omitting Rome) -> change as expected.

Full code below: 
private void adxRibbonButton1_OnClick(object sender, IRibbonControl control, bool pressed)
{
  Excel.Workbook workbook = null;
  Excel.Worksheet worksheet = null;
  Excel.Range range = null;
  try
  {
    workbook = ExcelApp.ActiveWorkbook;
    worksheet = workbook.ActiveSheet as Excel.Worksheet;
    range = worksheet.Range["$A$4:$C$4"];
    object[] newValue = { 3, "Aira_New", "Helsinki" };
    range.Value2 = newValue;
  }
  finally
  {
    if (range != null) Marshal.ReleaseComObject(range);
    if (worksheet != null) Marshal.ReleaseComObject(worksheet);
    if (workbook != null) Marshal.ReleaseComObject(workbook);
  }
}
Posted 07 Jun, 2021 14:28:14 Top
Andrei Smolin


Add-in Express team


Posts: 18219
 Joined: 2006-05-11
Hello Ervin,

We've studied the issue: it belongs to Excel. Our guys suggest this workaround: before assigning an array of values, collect and store filters applied, clear the filters (see https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.showalldata), then set values and restore the filter.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 08 Jun, 2021 06:33:13 Top
Ervin Gegprifti




Posts: 2
 Joined: 2015-02-17
Thank you, Andrei,

I will try that.

Also, what seems to always work is:
- Split the main range into subranges referencing one cell only
- Then set the corresponding value from the array per each subrange
But that seems to slooww… things down.

Best regards,
Ervin
Posted 08 Jun, 2021 07:13:29 Top
Andrei Smolin


Add-in Express team


Posts: 18219
 Joined: 2006-05-11
Hello Ervin,

Yes, setting cell by cell is the slowest possible method; see my research at https://www.add-in-express.com/creating-addins-blog/2011/09/29/excel-read-update-cells/.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 08 Jun, 2021 08:54:34 Top