Function Wizard (2)

[terahertz]

Following Holger's post regarding Function Wizard. I have following
code in c#:

string formulaR1C1 = "=MyUDF()";
Xl.Worksheet sheet = (Xl.Worksheet)xlApp.Sheets["Sheet1"];
Xl.Range range = sheet.get_Range(cellName, System.Type.Missing);
range.FormulaR1C1 = formulaR1C1;
range.FunctionWizard();

It turns out that a default function wizard will pop up but shows no
formula. That is, the function wizard seems cannot recognize the
formula existence.

To further test the case, I write a VBA macro and tested it on
Excel2003/2007/2010 with Automatic/Manual on and off.

Sub test()
Dim R As Range
Set R = Range("A1")
R.formulaR1C1 = "=MyUDF()"
R.Select
R.FunctionWizard
End Sub

Same thing happenes here. It is interesting to point out that, if you
run it the first time, the function wizard is in default status
without showing a formula; the second time, if range("A1") is selected/
highlighted, the function wizard pops up the correct way with formula
displayed, no matter the function "MyUDF()" exists or not. But if the
second time the range("A1") not selected, the function wizard will not
recognize "MyUDF()", same thing will happen as if the first time.

Further more, The code below works as desired, if you manually run
test() then test2().

Sub test()
Dim R As Range
Set R = Range("B1")
R.formulaR1C1 = "=MyUDF()"
R.Select
End Sub

Sub test2()
Range("B1").FunctionWizard
End Sub

Thanks for any suggestions.

Terahertz

[Govert van Drimmelen]

Hi Terahertz,

It looks like Excel needs to do some processing before it will accept the formula for display in the function wizard.

What about running the function wizard display macro (test2) in a call to Application.OnTime(Now) , at the end of test1 ?

-Govert

> --
> You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
> To post to this group, send email to exce...@googlegroups.com.
> To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/exceldna?hl=en.
>
>
>
>

[terahertz]

Thank you Govert. that's also what I guess. I think Application.OnTime
could work -- I will give it a try shortly. But I think one issue is
that this is a menu button click triggering event, the function wizard
popup depends on selected cell -- any click before Application.OnTime
eariest time will mess up the behaviour.

Then comes out a following question: Is there any other event trigger
methods that I can use? Say I can send a event trigger to some other
thread/listener -- something like ".OnAction", and another macro will
take over and then popup the wizard?

Thanks in advance,
Terahertz.

> > For more options, visit this group athttp://groups.google.com/group/exceldna?hl=en.- Hide quoted text -
>
> - Show quoted text -

[Govert van Drimmelen]

I'd guess Application.OnTime with Now is as close as you're going to
get to running code when Excel is done processing your selection
changes.

You might try using the C API instead of the COM interface to see if
that behaves differently.

-Govert

> > > For more options, visit this group athttp://groups.google.com/group/exceldna?hl=en.-Hide quoted text -

[terahertz]

Thanks for your reply Govert. I agree application.onTime could be as
close as enough. But for a product release we 'd better solve the
problem in a way that avoid those potential issues. Here I'd like to
share a piece of code our technique guy recently provided, which seems
to solve the issue with my satisfactory.

Hope this is helpful.

class functionwizardThread
{
private Xl.Range range_ = null;

public functionwizardThread(Xl.Range range)
{
range_ = range;
}

public void functionargumentsPopup()
{
range_.FunctionWizard();
}
}

public static void Function_Click()
{
String fname = "=MyUDF()";

// defined range here...

range.FormulaR1C1 = fname;
range.Select();

functionwizardThread othread = new
functionwizardThread(range);
Thread thread = new Thread(new
ThreadStart(othread.functionargumentsPopup));
thread.Start();

}


On Jan 5, 10:26 am, Govert van Drimmelen <gov...@icon.co.za> wrote:
> I'd guess Application.OnTime with Now is as close as you're going to
> get to running code when Excel is done processing your selection
> changes.
>
> You might try using the C API instead of the COM interface to see if
> that behaves differently.
>
> -Govert
>
> On Jan 4, 5:20 pm, terahertz <kl2...@gmail.com> wrote:
>
>
>
> > Thank you Govert. that's also what I guess. I think Application.OnTime
> > could work -- I will give it a try shortly. But I think one issue is
> > that this is a menu button click triggering event, thefunctionwizard
> > popup depends on selected cell -- any click before Application.OnTime
> > eariest time will mess up the behaviour.
>
> > Then comes out a following question: Is there any other event trigger
> > methods that I can use? Say I can send a event trigger to some other
> > thread/listener -- something like ".OnAction", and another macro will
> > take over and then popup thewizard?
>
> > Thanks in advance,
> > Terahertz.
>
> > On Jan 4, 3:03 am, Govert van Drimmelen <gov...@icon.co.za> wrote:
>
> > > Hi Terahertz,
>
> > > It looks like Excel needs to do some processing before it will accept the formula for display in thefunctionwizard.
>

> > > What about running thefunctionwizard display macro (test2) in a call to Application.OnTime(Now) , at the end of test1 ?

>
> > > -Govert
>
> > > On 04 Jan 2012, at 2:44, "terahertz" <kl2...@gmail.com> wrote:
>

> > > > Following Holger's post regardingFunctionWizard. I have following

> > > > code in c#:
>
> > > > string formulaR1C1 = "=MyUDF()";
> > > > Xl.Worksheet sheet = (Xl.Worksheet)xlApp.Sheets["Sheet1"];
> > > > Xl.Range range = sheet.get_Range(cellName, System.Type.Missing);
> > > > range.FormulaR1C1 = formulaR1C1;
> > > > range.FunctionWizard();
>

> > > > It turns out that a defaultfunctionwizardwill pop up but shows no
> > > > formula. That is, thefunctionwizardseems cannot recognize the

> > > > formula existence.
>
> > > > To further test the case, I write a VBA macro and tested it on
> > > > Excel2003/2007/2010 with Automatic/Manual on and off.
>
> > > > Sub test()
> > > >    Dim R As Range
> > > >    Set R = Range("A1")
> > > >    R.formulaR1C1 = "=MyUDF()"
> > > >    R.Select
> > > >    R.FunctionWizard
> > > > End Sub
>
> > > > Same thing happenes here. It is interesting to point out that, if you

> > > > run it the first time, thefunctionwizardis in default status

> > > > without showing a formula; the second time, if range("A1") is selected/

> > > > highlighted, thefunctionwizardpops up the correct way with formula
> > > > displayed, no matter thefunction"MyUDF()" exists or not. But if the
> > > > second time the range("A1") not selected, thefunctionwizardwill not

> > > > recognize "MyUDF()", same thing will happen as if the first time.
>
> > > > Further more, The code below works as desired, if you manually run
> > > > test() then test2().
>
> > > > Sub test()
> > > >    Dim R As Range
> > > >    Set R = Range("B1")
> > > >    R.formulaR1C1 = "=MyUDF()"
> > > >    R.Select
> > > > End Sub
>
> > > > Sub test2()
> > > >    Range("B1").FunctionWizard
> > > > End Sub
>
> > > > Thanks for any suggestions.
>
> > > > Terahertz
>
> > > > --
> > > > You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
> > > > To post to this group, send email to exce...@googlegroups.com.
> > > > To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.

> > > > For more options, visit this group athttp://groups.google.com/group/exceldna?hl=en.-Hidequoted text -
>
> > > - Show quoted text -- Hide quoted text -

[Govert van Drimmelen]

Hi,

Were you able in any way to generate any kind of error with the
Application.OnTime(Now) method?
This would seem to me like a good way to get code to run on the Excel
thread as soon as it is idle.

----

Your suggested implementation might work, but violates the COM
apartment threading rules - you are passing the Range object from the
Single-Threaded Apartment (STA) main thread to another thread without
any cross-apartment marshaling.

An example you can try where this might cause problems is if the user
is clicking on the sheet or editing a cell between Select and the
call to Range.FunctionWizard from the other thread (insert a Sleep
before the Range.FunctionWizard call to simulate a thread-switch, and
then see whether you can cause trouble by interacting with Excel. The
last call should fail since Excel suspends object model processing in
such cases, which should cause the cross-threaded call to fail.

-Govert

> > > > > For more options, visit this group athttp://groups.google.com/group/exceldna?hl=en.-Hidequotedtext -

[terahertz]

Hi Govert, thanks for your comments. I agree with what you said.

I've tested my code according to your suggestions. Here is the
results:

I put Thread.Sleep(5000) in front of range_.FunctionWizard(). when I
highlight another cell during the 5sec, the function wizard will pop
up but shows no function. Then when I try to edit a cell, the code
will break at range_.FunctionWizard().

As you said passing range between threads in our way is not safe. So
what about
try something like
ActiveRange.FunctionWizard() in the new thread? Effectively it will be
similiarly to Application.OnTime().

For application.OnTime(), I agree it is also a good alternative. But
the concern is that I don't have a way to pass the range to
Application.OnTime so that I may have to use ActiveCell for cell
reference.

Regards,
Terahertz

[Govert van Drimmelen]

Hi Terahertz,

Surely ActiveCell can also change in the time you're worried about.

Anything you do on another thread can be delayed or could fail if
Excel is busy. I always suggest doing nothing from the other thread
but call Application.Run to get back onto the main thread. I can't see
how another thread can help you.

Have you tried:
* Call Application.DoEvents before the FunctionWizard() call?

Or else, what about this:
* You make a static variable, say "rangeToShow" that will hold the
range you want to show function wizard for
* You make a (hidden) macro, say "showIt" that will call
rangeToShow.FunctionWizard()
* You set Application.OnTime(Now, "showIt")

-Govert

[terahertz]

That sounds a good solution. I will give it a try.

Thanks,
Terahertz

[Govert van Drimmelen]

Sorry - Ignore that first suggestion: there is no Application.DoEvents
- it is part of the VBA environment.
But the second suggestion might work.

-Govert