Data randonmly showing up in Excel Cells...

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

Data randonmly showing up in Excel Cells...
Difficult to track down bug... 
George Spears




Posts: 70
Joined: 2010-05-06
Newbie problem... at least I hope...
Delphi XE6, 32 bit, running against Excel 2013

Scenario... Using Ribbon Tab, I have added a series of button...
Each button does a similar task. One button allows user to remove spaces, one button removes HTML formatting, etc.

I have 2 different forms, one for each process, and they inherit from a common form. The common form, called BaseEdit sets up some default information; it shows a ComboBox, which holds the column headers, and has a PROCESS and Cancel button.

The user clicks my Ribbon button, and the descendant form shows MODALLY. OnShow, the Combo Box will get loaded with Column names. 99% of the time, this works great. RANDOMLY - at least it seems - There will be NO data in my combobox, but whatever CELL the user was on PRIOR to hitting my button gets additional text added. (Normally, something in the form of "Sheet1!B..."

At the point, NO code has been executed in my descendant form, only in the ancestor. Here are the applicable code snippets.

When the ribbon button is clicked...

var
  aws: ExcelWorksheet;
  myForm: TRemoveSPACEForm;
begin

  ExcelApp.Connect;
  aws := Self.ExcelApp.ActiveSheet as ExcelWorksheet;

  myForm := TRemoveSPACEForm.Create(nil);

  try
    myForm.aws := aws;// Pass active Sheet to form variable
    myForm.ShowModal;
  finally
    myForm.Free;
  end;


When my descendant shows (OnShow event), the ancestor code runs...
it Basically loops through all the columns, and populates the ComboBox for the user to select the column of interest.


...
for i := 1 to LastCol do
    begin
      CellAddr := ExcelColIntToStr(i) + '1'; // returns A1, B1, C1, etc.
      HeaderText := Trim(aws.Range[CellAddr, EmptyParam].Value); // Get the data from that cell
      ThisCol := '(' + string(ExcelColIntToStr(i)) + ' ) ' + HeaderText; // Build a string of Column address + Content
      CB.Items.Add(ThisCol);  // Add this to the ComboBox
     
    end;



This issue happens very infrequently. I cannot force it to do so. It happens on multiple descendant forms, prior to ANY user interaction on the descendant forms. I cannot find any pattern, other than the fact that when in occurs, my combo box is empty, and there is "corruption" in the Excel spreadsheet, which is really odd, because NOTHING should have been written to the spreadsheet yet. The OnShow event in my ancestor form does NO writing to the spreadsheet, and there is no code being executed in the descendant form at this point.

Any ideas?
Posted 16 Sep, 2014 17:25:48 Top
Andrei Smolin


Add-in Express team


Posts: 14094
Joined: 2006-05-11
Hello George,

Do you access the Excel object model in a background thread? If so, you must do this on the main thread only as all Office object models are not thread-safe.

Do you have any third-party components on that form?

Make sure that you have the XLAutomationAddin property of the add-in module set to false.

You can find what causes this by adding OutputDebugString calls to your code. At run time you collect the messages sent by these calls using the DebugView utility, see http://technet.microsoft.com/en-us/sysinternals/bb896647.aspx.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 17 Sep, 2014 09:15:56 Top
George Spears




Posts: 70
Joined: 2010-05-06
I have tracked down the bug (or at least one of them...ha) but I don't know quite how to fix it. The issue has to do with determining the number of rows and columns in a spreadsheet. The flow of my program is...
User clicks on ribbon button...
I create, but not show a new window
I determine the last row and colomn to pass to that new window
...

Determining the last row and column is my problem. I use the following code.

 try
{$IFDEF Excel2000}
      BoundRec.LastRow := XLSheet.Cells.Find('*', EmptyParam, EmptyParam, EmptyParam, xlByRows, xlPrevious, EmptyParam,
        EmptyParam).row;
      BoundRec.LastCol := XLSheet.Cells.Find('*', EmptyParam, EmptyParam, EmptyParam, xlByColumns, xlPrevious,
        EmptyParam, EmptyParam).Column;
{$ELSE}
      BoundRec.LastRow := IWks.Cells.Find('*', EmptyParam, EmptyParam, EmptyParam, xlByRows, xlPrevious, EmptyParam,
        EmptyParam).row;
      BoundRec.LastCol := IWks.Cells.Find('*', EmptyParam, EmptyParam, EmptyParam, xlByColumns, xlPrevious, EmptyParam,
        EmptyParam).Column;
{$ENDIF}
    except
      on E: Exception do
      begin

        BoundRec.LastRow := 0;
        BoundRec.LastCol := 0;
      end;
    end;


Generally, this works fine, with one exception. Assume I had data in row 1, Columns A, B, and C. If I load this spreadsheet, Add data to Column D, but DO NOT CLICK ON ANOTHER CELL, then click on my ribbon button, when the above code is executed, it returns last row and last col to be 1, which is NOT accurate.

How do I get last row/last col when the user may have added data but not left the cell to update the internal mechanisms which track last row/last column?
Posted 25 Sep, 2014 06:45:22 Top
Andrei Smolin


Add-in Express team


Posts: 14094
Joined: 2006-05-11
Hello George,

You can't decide whether the cell is the last one while it is still being edited: the user may press Esc.

More generally, it isn't a good idea to check such things while the user is editing the cell. I would disable the Ribbon button in this case. Please see How to check programmatically if the user is editing an Excel cell.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 25 Sep, 2014 07:29:09 Top
George Spears




Posts: 70
Joined: 2010-05-06
That did it. Thanks! If anyone is interested, here is my code to determine if any cell is being edited...

// Is any cell in edit mode?  If so, return true, else return false
function TAddInModule.InEditMode : Boolean;
begin
  if ExcelApp.Interactive[LOCALE_USER_DEFAULT] = False then
  begin
    Result := False;
    Exit;
  end;

  try
    ExcelApp.Interactive[LOCALE_USER_DEFAULT] := False;
    ExcelApp.Interactive[LOCALE_USER_DEFAULT] := True;
  except
    result := True;
    Exit;
  end;

 Result := False;
end;
Posted 25 Sep, 2014 12:25:24 Top
Andrei Smolin


Add-in Express team


Posts: 14094
Joined: 2006-05-11
Many thanks for posting this!

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 26 Sep, 2014 02:36:34 Top