It seems like Application.Evaluate always evaluates the command twice.
I have a custom addin and want to write a macro to evaluate an addin
command. By setting a break point at my addin function, I can see that when
Application.Evaluate is evoked, it called the addin function twice. Is there
any way to disable this behaviour or is there any alternative to
application.evaluate that would only run the command once?

I am using excel 2002 SP3.

Thanks.

(PS. I cannot put the command in a cell and calculate because the
addin-function returns a variable-size array and I want to get the returned
array and do some additional processing on it.)

Why not just set the array equal to the return of the function? You may need
to set a reference to the project, but this should work:

Sub TestArrayReturn()
Dim myArr As Variant
Dim i As Integer
myArr = TestFunction
For i = LBound(myArr) To UBound(myArr)
MsgBox myArr(i)
Next i
End Sub

Function TestFunction() As Variant
TestFunction = Array(1, 2, 3)
End Function


HTH,
Bernie
MS Excel MVP


"lcsma" <(E-Mail Removed)> wrote in message
news:C474FBB4-F6F3-4C3A-B1B4-(E-Mail Removed)...
> It seems like Application.Evaluate always evaluates the command twice.
> I have a custom addin and want to write a macro to evaluate an addin
> command. By setting a break point at my addin function, I can see that
> when
> Application.Evaluate is evoked, it called the addin function twice. Is
> there
> any way to disable this behaviour or is there any alternative to
> application.evaluate that would only run the command once?
>
> I am using excel 2002 SP3.
>
> Thanks.
>
> (PS. I cannot put the command in a cell and calculate because the
> addin-function returns a variable-size array and I want to get the
> returned
> array and do some additional processing on it.)
>

Thanks. I will try this approach. I had difficulty setting reference to the
add-in as it didn't show up in the Tools->References list, but I guess that's
a separate problem.

Out of curiosity, Is this behavior a well known feature of
Application.Evaluate? What's the reason that it needs to evaluate twice?

TIA

"Bernie Deitrick" wrote:

> Why not just set the array equal to the return of the function? You may need
> to set a reference to the project, but this should work:
>
> Sub TestArrayReturn()
> Dim myArr As Variant
> Dim i As Integer
> myArr = TestFunction
> For i = LBound(myArr) To UBound(myArr)
> MsgBox myArr(i)
> Next i
> End Sub
>
> Function TestFunction() As Variant
> TestFunction = Array(1, 2, 3)
> End Function
>
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "lcsma" <(E-Mail Removed)> wrote in message
> news:C474FBB4-F6F3-4C3A-B1B4-(E-Mail Removed)...
> > It seems like Application.Evaluate always evaluates the command twice.
> > I have a custom addin and want to write a macro to evaluate an addin
> > command. By setting a break point at my addin function, I can see that
> > when
> > Application.Evaluate is evoked, it called the addin function twice. Is
> > there
> > any way to disable this behaviour or is there any alternative to
> > application.evaluate that would only run the command once?
> >
> > I am using excel 2002 SP3.
> >
> > Thanks.
> >
> > (PS. I cannot put the command in a cell and calculate because the
> > addin-function returns a variable-size array and I want to get the
> > returned
> > array and do some additional processing on it.)
> >
>
>
>