Re: ExcelAsyncUtil.QueueAsMacro lock up excel wile workgin

[Govert van Drimmelen]

Hi Dmitry,

Excel is (essentially) a single-threaded application. So while the
macro is running, Excel disables the user interface.

You can speed up setting the data by making an ExcelReference that
covers a large range (new ExcelReference(rowFirst, rowLast,
columnFirst, columnLast)) and the using SetValue with an object[,]
array of the right size. This should be much faster. A million cells
or so take less that a second.

If you have some slow calculation or a database lookup to do before
your set the data in the sheet, you should wait until the work is
complete and then call the macro to do the work via
ExcelAsyncUtil.QueueAsMacro. You can make this call from any thread.

Regards,
Govert


* * * * * * * * * * * * * * * * * * * * * * * * * * * *

Ensure that the Excel-DNA project continues by
making your donation - http://excel-dna.net/support/

* * * * * * * * * * * * * * * * * * * * * * * * * * * *


On May 24, 2:46 pm, Dmitry <dmitryp...@gmail.com> wrote:
> Hi All,
> I'm using ExcelAsyncUtil.QueueAsMacro for filling up a big range, 10k rows
> and several columns, like so:
> ....
>                ExcelAsyncUtil.QueueAsMacro(delegate
>                {
>                     for (int i = 1; i < 10000; i++)
>                     {
>                         for (int j = 1; j < 5; j++)
>                         {
>                             ExcelReference cell = new ExcelReference(i, j);
>                             Random random = new Random();
>                             int randomNumber = random.Next(0, 100);
>                             cell.SetValue(randomNumber);
>                         }
>                     }
>                  });
> ....
>
> While loop is filling cells excel is locked - it means user can't select,
> edit cells or so.
>  random.Next(0, 100) here just for example but It could be an database
> request, then the whole loop will
> take a much more time, and excel will be locked for this period.
>
> How can I change this behavior?
>
> And the second question, could you recommend faster\better way for filling
> cells in a loop, because the above method
> creates ExcelReference for each cell and I suppose it is not the best
> practice.
>
> Thx!

[Govert van Drimmelen]

Hi Dmitry,

Yes, ExcelAsyncUtil.QueueAsMacro is similar to Application.OnTime, but
works more reliably.

It's asynchronous in the sense that you can call it from any thread,
or from a calculation context, and it will be queued to run when Excel
is able to run a macro.
It also shared some of the underlying implementation with the RTD-
based async functions, so I've exposed it from the ExcelAsyncUtil
class. "QueueAsMacro" is the right way to think about it.

Regards,
Govert

* * * * * * * * * * * * * * * * * * * * * * * * * * * *

Ensure that the Excel-DNA project continues by
making your donation - http://excel-dna.net/support/

* * * * * * * * * * * * * * * * * * * * * * * * * * * *

On May 24, 4:05 pm, Dmitry <dmitryp...@gmail.com> wrote:
> Thx for reply!
>
> Another words ExcelAsyncUtil.QueueAsMacro acts like delay macro call in vba
>  application.OnTime ?
> If It is,  why it is asynchronous? you still need to wait while macro has
> end to get result and access to excel interface.
>
> About bulk data insert through object[,]  - super, it's really speed up the
> process.