1

Our object model contains a class called Unit and a collection of these called Units (which is stored in a Dictionary). These objects have unique Names and Keys (they originally came from a SQL db that enforced this) so I have added:

Public Units(N as String) As Unit ...
Public Units(K as Integer) As Unit...

which return a Unit object from the Units collection.

In Excel VBA, one can refer to most objects using similar methods; Worksheets(1) returns the first sheet, while Worksheets("Bob") returns the named sheet. But they have one additional method, Worksheets, which returns the entire collection. It's as if they have this method...

Public Worksheets() As List(Of Worksheet)

But you can't use List in interop (right?) so it's more like...

Public Worksheets() As ArrayList

So how would I do the same basic API in .net with interop? That is, have three methods...

Public Units(N as String) As Unit ...
Public Units(K as Integer) As Unit...
Public Units() As ArrayList...

As I understand it only the first method of a given name is exported (is this correct?). So how does Excel do it, and can I fake that in .net?

Community
  • 1
  • 1
Maury Markowitz
  • 9,082
  • 11
  • 46
  • 98

1 Answers1

3

VBA's Worksheets is not a method. It is a class, Worksheets, that has a default property Item that accepts a parameter of type Variant. There is no overloading (COM does not support it), it's just that Variant can hold both a number or a string.

If you want a similar structure in VB.NET, you can have a collection class that implements a default property as VB.NET understands it, and this time you can overload it.

Public Class UnitsCollection

    Default Public ReadOnly Property Item(ByVal i As Integer) As Unit
        Get
            Return ...
        End Get
    End Property

    Default Public ReadOnly Property Item(ByVal i As String) As Unit
        Get
            Return ...
        End Get
    End Property

End Class
Community
  • 1
  • 1
GSerg
  • 76,472
  • 17
  • 159
  • 346
  • Ok, so if I'm parsing this correctly, a call to `Worksheets("Bob")` is really a call to `Worksheets.Item("Bob")`, and additionally, the method `Item` takes a variant, figures out what was passed in, and then does the lookup based on that. Right? Ok, so what does the straight-up `Worksheets` call? Is this calling a method on the class? Is it a collection on some other object like, I don't know, `Workbook`? When I say `Workbooks`, who am I calling? – Maury Markowitz May 25 '15 at 19:54
  • @MauryMarkowitz Yes, that's right on the `Item` method. When you call `Worksheets`, you're accessing the `Worksheets` property of the `Application` class, the global singleton class representing the Excel instance itself. This property happens to be named `Worksheets` (just a property name) and it returns an object of type `Worksheets` (now that's a type name). – GSerg May 25 '15 at 20:32
  • *You answered, I didn't refresh* – Maury Markowitz May 25 '15 at 21:13
  • ++ Nicely Explained :) – Siddharth Rout May 26 '15 at 02:34