469,951 Members | 2,763 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,951 developers. It's quick & easy.

IntellSense in VBA and Arrays from/to .NET COM DLL

Hi there,

I have to use a C#-Library from Excel VBA.
This __works__ fine over COM (Reference to the .tlb, that is created by .NET, out of VBA).
But only __without__ [ClassInterface(ClassInterfaceType.AutoDual)] in C#1

__With__, for providing IntelliSense, I get - when compiling the VBA code:
--> __Compile Error__: Function or interface marked as restricted, or the function uses an
automation type not supported in Visual Basic
(but just for the methods that use SAFEARRAY (TestSum, ...) not for DATE (KW) - see .tlb below)

How can I get both? Full functionality AND IntelliSense.

any idea(s)/solution(s)?
TIA, Martin@Vienna@Austria@Europe

smallest code that shows my problem in detail:
VBA Code:
Expand|Select|Wrap|Line Numbers
  1. Private Function range2array(rng As Range) As Double()
  2. Dim m, n, i, j As Long
  3. Dim a() As Double
  4. m = rng.Rows.Count - 1
  5. n = rng.Columns.Count - 1
  6. ReDim a(m, n)
  7. For i = 0 To m
  8. For j = 0 To n
  9. a(i, j) = rng.Cells(i + 1, j + 1).Value
  10. Next
  11. Next
  12. range2array = a
  13. End Function
  14.  
  15. Public Function TestSum(rngArray As Range) As Double
  16. Dim o As New WorkingTime.Class1
  17. // COMPILE ERROR (on .TestSum):
  18. TestSum = o.TestSum(range2array(rngArray))
  19. End Function
  20.  
C# Code:
Expand|Select|Wrap|Line Numbers
  1. using System;
  2. using System.Runtime.InteropServices;
  3.  
  4. namespace WorkingTime
  5. {
  6. //  THE SINGLE DIFFERENCE:
  7. //    [ClassInterface(ClassInterfaceType.AutoDual)]
  8. public class Class1
  9. {
  10. public Class1(){}
  11.  
  12. public double TestSum(System.Double[,] arr)
  13. {
  14. double sum = 0.0;
  15. for(int r=0;r<arr.GetLength(0);r++)
  16. for(int c=0;c<arr.GetLength(1);c++)
  17. sum += arr[r,c];
  18. return sum;
  19. }
  20. }
  21. }
  22.  
..tlb __without__ [ClassInterface(ClassInterfaceType.AutoDual)]: full functionality no IntelliSense
// Generated .IDL file (by the OLE/COM Object Viewer)
//
// typelib filename: WorkingTime.tlb
Expand|Select|Wrap|Line Numbers
  1. [
  2. uuid(3A9C4D8F-E805-3BFE-8FD8-0686A34BE8B2),
  3. version(1.0)
  4. ]
  5. library WorkingTime
  6. {
  7. // TLib :     // TLib : Common Language Runtime Library : {BED7F4EA-1A96-11D2-8F08-00A0C9A6186D}
  8. importlib("mscorlib.tlb");
  9. // TLib : OLE Automation : {00020430-0000-0000-C000-000000000046}
  10. importlib("stdole2.tlb");
  11.  
  12. // Forward declare all types defined in this typelib
  13. interface _Class1;
  14.  
  15. [
  16. uuid(ECA63A36-9C29-3BB6-8C23-1DB74F653CEE),
  17. version(1.0),
  18. custom({0F21F359-AB84-41E8-9A78-36D110E6D2F9}, "WorkingTime.Class1")
  19. ]
  20. coclass Class1 {
  21. [default] interface _Class1;
  22. interface _Object;
  23. };
  24.  
  25. [
  26. odl,
  27. uuid(4C3E9A8D-E900-3384-8160-CD308CFF555C),
  28. hidden,
  29. dual,
  30. oleautomation,
  31. custom({0F21F359-AB84-41E8-9A78-36D110E6D2F9}, "WorkingTime.Class1")
  32.  
  33. ]
  34. interface _Class1 : IDispatch {
  35. };
  36. };
  37.  
..tlb __with__ [ClassInterface(ClassInterfaceType.AutoDual)]: compile error, but IntelliSense
// Generated .IDL file (by the OLE/COM Object Viewer)
//
// typelib filename: WorkingTime.tlb
Expand|Select|Wrap|Line Numbers
  1. [
  2. uuid(3AE2692B-065C-37E6-B581-6A34B9913359),
  3. version(1.0)
  4. ]
  5. library WorkingTime
  6. {
  7. // TLib :     // TLib : Common Language Runtime Library : {BED....6186D}
  8. importlib("mscorlib.tlb");
  9. // TLib : OLE Automation : {00020430-0000-0000-C000-000000000046}
  10. importlib("stdole2.tlb");
  11.  
  12. // Forward declare all types defined in this typelib
  13. interface _Class1;
  14.  
  15. [
  16. uuid(3E3FBD07-3144-3F66-9621-50B0AF53833B),
  17. version(1.0),
  18. custom({0F21F359-AB84-41E8-9A78-36D110E6D2F9}, "WorkingTime.Class1")
  19. ]
  20. coclass Class1 {
  21. [default] interface _Class1;
  22. interface _Object;
  23. };
  24.  
  25. [
  26. odl,
  27. uuid(4B2E15B2-F4A4-3E39-A8CC-DAEEA58E019F),
  28. hidden,
  29. dual,
  30. nonextensible,
  31. oleautomation,
  32. custom({0F21F359-AB84-41E8-9A78-36D110E6D2F9}, "WorkingTime.Class1")
  33.  
  34. ]
  35. interface _Class1 : IDispatch {
  36. [id(00000000), propget,
  37. custom({54FC8F55-38DE-4703-9C4E-250351302B1C}, "1")]
  38. HRESULT ToString([out, retval] BSTR* pRetVal);
  39. [id(0x60020001)]
  40. HRESULT Equals(
  41. [in] VARIANT obj,
  42. [out, retval] VARIANT_BOOL* pRetVal);
  43. [id(0x60020002)]
  44. HRESULT GetHashCode([out, retval] long* pRetVal);
  45. [id(0x60020003)]
  46. HRESULT GetType([out, retval] _Type** pRetVal);
  47. [id(0x60020004)]
  48. HRESULT TestSum(
  49. [in] SAFEARRAY(double) arr,
  50. [out, retval] double* pRetVal);
  51. [id(0x60020005)]
  52. HRESULT TestXimesDataSum(
  53. [in] SAFEARRAY(double) arr,
  54. [out, retval] double* pRetVal);
  55. [id(0x60020006)]
  56. HRESULT KW(
  57. [in] DATE dateTime,
  58. [out, retval] long* pRetVal);
  59. };
  60. };
  61.  
-------------------------------------------------------------------------
to get my real email address replace 'see@signature' with 'brunner@ximes'
Nov 17 '05 #1
0 1047

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

34 posts views Thread by Christopher Benson-Manica | last post: by
1 post views Thread by Rob Griffiths | last post: by
39 posts views Thread by Martin Jørgensen | last post: by
41 posts views Thread by Rene Nyffenegger | last post: by
weaknessforcats
29 posts views Thread by weaknessforcats | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.