I was writing .NET component that was COMVisible and aimed at working along with Excel Macros. Instead of creating a VSTO project, I just went with a .NET DLL with Com wrappers.
Actually this is a very viable option, if you just want to automate excel without too much of the support provided by VSTO (which in turn adds its own overhead). So you just reference the Excel Interop and you are set to go.
One of the things I wanted to do was to pass over the current Excel Application object to .NET so that I can interact and do some work from .NET.
So I initially wrote a function like below:
Public Function testParam(ByVal app As Application) As Boolean more here code Return True End Function
If you try to call this function from Excel VBA
x.testparam (Application)
will result in an error at the VBA level “Invalid procedure call or argument”
I have tried to find why this is happening but I don’t see any specific description of why this happens. I did see some pointers where, the InterOp actually uses Interfaces and 
behind the scenes map the objects to the actual classes. So if you see in Visual Studio you will see the Excel.Application is actually shown as an interface. I suspect this maybe the reason for the error.
The current workaround that works for me is to use a Property to capture the Application object. Though this is the cleanest way (I prefer the parameter in the object I was designing) the property seems to be the only way to get this to work.
So the code will change to
Public Property ExcelApplication As Application
And in the VBA you can just assign to the property as:
x.ExcelApplication = Application
This works fine….
Note: I am using VS2010 and .NET 4. So I have taken advantage of Automatic Properties
Comments