VBA compatibility - Enums vs. Constants

Jan 9, 2012 at 11:38 AM

Hi Sebastian,

More about (Excel) VBA compatibility.

These are just observations, I'm not sure I have a serious suggestion for something to change yet.

In VBA I write:

 

Sub Test()
    Dim i
    Dim j
    Dim x As String
    
    i = xlAboveAverage
    j = XlAboveBelow.xlAboveAverage
    
    x = Format(5.1, "0")
End Sub

 

Now I want to port this to Excel-DNA / NetOffice code with VB.NET. I reference LateBinding.Core.dll and ExcelApi.dll (and probably OfficeApi.dll).

I create a module with the following code:

 

Imports NetOffice.ExcelApi.Enums

Public Module TestEnums

    Sub Test()
        Dim i
        Dim j
        Dim x As String

        i = xlAboveAverage ' <<<<< COMPILER ERROR 
        j = XlAboveBelow.xlAboveAverage

        x = Format(5.1, "0")
    End Sub

End Module

The compiler error is understandable - there is nothing called xlAboveAverage in the namespace.

Now if I Import the XlAboveAverage type, I get another problem:

Imports NetOffice.ExcelApi.Enums
Imports NetOffice.ExcelApi.Enums.XlAboveBelow

Public Module TestEnums

    Sub Test()
        Dim i
        Dim j
        Dim x As String

        i = xlAboveAverage
        j = XlAboveBelow.xlAboveAverage

        x = Format(5.1, "0") ' <<<<< COMPILER ERROR
    End Sub

End Module

Now the first xlAboveAverage looks OK, but because the type xlAboveAverage inherits from System.Enum, we have also imported the method System.Enum.Format(...), which now hides the previous Format method in Microsoft.VisualBasic.Strings.

So here's the thought:

If might be useful if ExcelApi had a sealed Constants type with static members (each of the right enum type) for all the constants. That way we could have the above code work directly.

The danger would be that it might increase the size of the assemblies a lot. If so, maybe the VBA compatibility helpers should be in a separate assembly?

 

In the Microsoft.VisualBasic assembly there is a type called Microsoft.VisualBasic.Constants, which have the various enum values as constants. Somehow these are all available globally (maybe because to the StandardModule attribute?) Certainly something makes the type Microsoft.VisualBasic.Constants special, so you only need to say "Imports Microsoft.VisualBasic" and Constant is imported too.

Another example which makes me think the Microsoft.VisualBasic.CompilerServices.StanrdardModuleAttribute is important is the DateTime.Now member. One does not have to import the type for "Now" to be available everywhere. This makes me think if you can add such "Global" classes added to NetOffice, they should be marked with the Microsoft.VisualBasic.CompilerServices.StanrdardModuleAttribute - at least it's worth investigating.

Right - I'm rambling a bit, but these are some issues I found while exploring how to use NetOffice for VBA -> VB.NET compatibility. None of this is urgent or serious, maybe nice to have features to implement if easy and if it does no harm otherwise.

-Govert