Using CallByName function?

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

Using CallByName function?
 
Josh W




Posts: 17
Joined: 2008-10-21
Is it possible to use CallByName in a VB2008 XLL, or something similar? When I use "Me" as the ObjectRef I get "Me is valid only within an instance method". My question is what do I use instead of "Me", or is there some other way to do this? For example, say I have the following function:

Public Shared Function TryCallByName(ByVal func As String) As Double
'for example, I want to call a function by name that takes no arguments

TryCallByName = CallByName(Me, func, vbMethod)

End Function


********

Any help is greatly appreciated!
Posted 29 Oct, 2008 15:31:02 Top
Eugene Astafiev


Add-in Express team


Posts: 8540
Joined: 2007-07-25
Hello Josh,

Please read more about shared methods here.
.NET, Extended MAPI, Windows Phone & RT
Posted 29 Oct, 2008 16:32:13 Top
Josh W




Posts: 17
Joined: 2008-10-21
Thanks Eugene, that makes sense. I guess what I should have asked is how do I declare the instance as an object variable appropriately? So I basically am asking:

Dim o As Object = ?
result= CallByName(o, "SomeFunction", vbMethod)


What do I set this to? This is where I am really stuck. Is it just not possible with shared methods? Is there some other way that you are aware of?

Sorry for the basic questioning. I really appreciate your help.

Thanks,

Josh
Posted 29 Oct, 2008 23:06:32 Top
Eugene Astafiev


Add-in Express team


Posts: 8540
Joined: 2007-07-25
Hello Josh,

Sorry, I don't understand your question. You can use the following declaration at a class level (when the class is not marked as static(shared in VB.NET):

Dim o As Object
.NET, Extended MAPI, Windows Phone & RT
Posted 30 Oct, 2008 04:18:12 Top
Josh W




Posts: 17
Joined: 2008-10-21
Yes, sorry for the confusion. I am only asking if it is possible to use CallByName() in ADX for VB2008 XLL UDF's. Is this possible? Or, is it that CallByName() is simply not supported in ADX for VB2008 XLL UDF's because all functions must be shared functions? After everything I've read, that would make sense actually.

I am able to figure out the use of ADXExcelWorkseetFunction.Evaluate, but what I want to end up being able to do is write XLL functions that can evaluate UDFs in written by the user in VBA directly. I tried the following, but it is exceedingly slow as I think it creates a new excel instance every time the function is called in Excel:

Public Shared Function TryCallByName(ByVal funcName As String) As Object

Dim oXL As Excel.Application = New Excel.Application
TryCallByName = oXL.ExecuteExcel4Macro(funcName)

End Function

****

Is there a more efficient way of doing this that anyone can think of?

Thanks.
Posted 30 Oct, 2008 13:00:30 Top
Eugene Astafiev


Add-in Express team


Posts: 8540
Joined: 2007-07-25
Hello Josh,

Thank you for clearing up this to me. You can try to use the following code:

Module.ExcelApp
.NET, Extended MAPI, Windows Phone & RT
Posted 30 Oct, 2008 14:16:34 Top
Josh W




Posts: 17
Joined: 2008-10-21
I'm not following you on the use of ExcelApp. I don't show ExcelApp under module. I have:

CallByName(AddinExpress.MSO.ADXXLLModule.ExcelApp, func, CallType.Method)

which doesn't work. What am I doing wrong here?
Posted 30 Oct, 2008 16:07:40 Top
Eugene Astafiev


Add-in Express team


Posts: 8540
Joined: 2007-07-25
Hello Josh,

You can use the InvokeMember function of the System.Type class.
.NET, Extended MAPI, Windows Phone & RT
Posted 30 Oct, 2008 16:33:41 Top
Josh W




Posts: 17
Joined: 2008-10-21
Ok, thanks again, Eugene, and sorry to keep bothering you with this. I tried the following (and several variants therof) but I still can't get it to work:
*************

Imports System.Reflection
.
.
.
Public Shared Function TryInvoke(ByVal func As String) As Object

'call a function 'func' from Excel/VBA with no arguments

Dim oXL As Object

oXL = GetObject(, "Excel.Application")

TryInvoke = oXL.GetType().InvokeMember(func, Reflection.BindingFlags.InvokeMethod, Nothing, Nothing)

End Function
**********************
A tend to get one of the following errors depending on how exactly I try it:
**********************
A first chance exception of type 'System.Runtime.InteropServices.COMException' occurred in MyXLLAddin3.DLL
A first chance exception of type 'System.MissingMemberException' occurred in Microsoft.VisualBasic.dll
A first chance exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll
A first chance exception of type 'System.Exception' occurred in Microsoft.VisualBasic.dll
A first chance exception of type 'System.MissingMemberException' occurred in Microsoft.VisualBasic.dll
A first chance exception of type 'System.MissingMethodException' occurred in mscorlib.dll
**********************

I'm guessing it is something very easily corrected that I am overlooking. What am I doign wrong?!

Please Help!
Posted 30 Oct, 2008 20:03:54 Top
Eugene Astafiev


Add-in Express team


Posts: 8540
Joined: 2007-07-25
Hello Josh,

Please make sure that the method exists in the specified object variable and don't accept any parameters.
.NET, Extended MAPI, Windows Phone & RT
Posted 31 Oct, 2008 06:24:58 Top