Passing Range vs. Object parameter

Oct 9, 2012 at 4:35 PM

Hi Sebastian,

I am primarily using NetOffice to give access to Excel's VBA to my .NET model. I was looking at this example that was given as a solution:

public double CalculateArea(object range)
{
  Excel.Range r = new Excel.Range(null, range);
  int result = Convert.ToDouble(r.Width) * Convert.ToDouble(r.Height);
   r.Dispose();
   return result;
}

The disadvantage of the previous function, compared to this:

public double CalculateArea(IRange range)
{
   return Convert.ToDouble(range.Width) * Convert.ToDouble(range.Height);
}

Is that the auto complete in the VBA editor will show the parameter "object" rather than "IRange" which is not ideal, is there a workaround and am I missing anything? I don't mind disposing the object or not, but my concern is the method signature.

Coordinator
Oct 12, 2012 at 3:11 AM

"to give access to Excel's VBA to my .NET model."

i dont understand you. you call NetOffice from Visual Basic for Applications???
can you explaine your situation with more details ?

Oct 12, 2012 at 9:39 AM

I have a .NET project that is exposed as COM and references NetOffice assemblies and I have the following code:

[ComVisible(true)]
[InterfaceType(ComInterfaceType.InterfaceIsDual)]
[Guid("01A33313-9364-51CC-B1F4-C6F1310A4B30")]
public interface ITest {
        int GetCount(object range);
}

[ComVisible(true)]
[ClassInterface(ClassInterfaceType.None)]
[Guid("A3F07ED4-AE73-5102-B35D-112DF2624C47")]
[ProgId("NAME.Test")]    
public class Test : ITest {       
    public int GetCount(object range) {
            int count;            
            using(var r = new Range(null, range)) { 
                count = r.Count();            
            }
            return count;
    }    
}

In VBA, I referenced the above assembly and I am using it as follows:

Dim t As New Test()
MsgBox t.GetCount()

Because the GetCount parameter is 'object' type, the auto complete (intellisense) in VBA is showing the parameter as variant type, which is not ideal.

I tried declaring it as Range (from the NetOffice) like:

int GetCount(Range range)

But that doesn't work as the intellisense still shows the parameter as variant, however, when using the 'Range' from the MS Office Interop, VBA's intellisense DOES display the parameter as a Range, which is the behaviour I am looking for.

Any ideas how to make the NetOffice's Range appear in the intellisense as a Range, not as variant?

Coordinator
Oct 12, 2012 at 10:22 AM

now i'm understand the problem! i need some days to find a solution. what you can do as hotfix is to combine the interop assemblies with netoffice.
include the interop assemblies as embedded reference( the interop assemblies are version independent in this way but no event support).

then use:

public int GetCount(MSInterop.Excel.Range range) 
{
Excel.
Range r = new Excel.Range(null, range);
  int result = Convert.ToDouble(r.Width) * Convert.ToDouble(r.Height);
  r.Dispose();
  return result;

}

looks not fine but works gladly. i hope i find a better way at the weekend.
for this little example you dont neet the NetOffice wrapper of course.
you may dont need NetOffice with the embedded interop assemblies at all for your situation.
but if you do more complex stuff then netoffice can be helpfull with the integrated com proxy lifetime management service.

Sebastian
Oct 12, 2012 at 5:18 PM

Sebastian,

Thank you for the reply and for the hotfix suggestion.

This was an over simplistic example, but I will be using events and later on automation. 

By the way, I am using the latest preview version.

Regards,
Adam