Excel constants in VB.NET

[Andrzejek]

When I create a macro in Excel 2010, I have this code:

With Range("A1:B2").Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With

So I transfer it to my VB.NET app so it looks like this:

Imports MyExcel = Microsoft.Office.Interop.Excel

Dim xlApp As New MyExcel.Application
Dim xlBook As MyExcel.Workbook = xlApp.Workbooks.Add(System.Reflection.Missing.Value)
Dim xlSheet As MyExcel.Worksheet = Nothing

With xlApp
  With .Range("A1:B2").Borders([red]xlEdgeLeft[/red])
    .LineStyle = [red]xlContinuous[/red]
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = [red]xlThin[/red]
  End With
End With

All red Excel constants give me errors - Public member 'xlContinous' on type 'ApplicationClass' not found. Even if I try:
[tt]
.LineStyle = [blue]xlApp.[/blue]xlContinuous
[/tt]

How can I retrieve Excel constants from Excel?

Have fun.

---- Andy

 

[DaveInIowa]

Andy = Try this ...

 With xlApp
    With .Range("A1:B2").Borders([red]myExcel.XlBordersIndex.[/red]xlEdgeLeft)
        .LineStyle = [red]myExcel.XlLineStyle.[/red]xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = [red]myExcel.XlBorderWeight.[/red]xlThin
    End With
End With

 

[Andrzejek]

Error
---------------------------
Public member 'XlLineStyle' on type 'ApplicationClass' not found.

:-(

This is my actual code:

[green]
' 7 - xlEdgeLeft
' 8 - xlEdgeTop
' 9 - xlEdgeBottom
'10 - xlEdgeRight
'11 - xlInsideVertical
'12 - xlInsideHorizontal[/green]
For iLine As Integer = 7 To 12
    With .Range("A2:F" & R - 1).Borders(iLine)
        .LineStyle = xlApp.XlLineStyle.xlContinuous[green]
        '.LineStyle = 1[/green]
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = 2 [green]'xlApp.xlLineStyle.xlThin[/green]
    End With
Next

As long as use hard coded values for Excel constants, I am fine.
But I hate to do that :-(


Have fun.

---- Andy

 

[DaveInIowa]

I'm not able to duplicate the error message you're seeing. I'm trying your code in Visual Studio 2010 and added a reference to Microsoft.Office.Interop.Excel, version 12.0.0.0. The path to this assembly is 'C:\Program Files (x86)\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office12\Microsoft.Office.Interop.Excel.dll'. I could have added a reference to version 14.0.0.0 but chose the older version as I thought that may have been your issue.

When you right-click on Excel in the Imports line and then 'Go To Definition', can you see any of the enums (XlBordersIndex, XlBorderWeight or XlLineStyle) in the Object Browser?

 

[Andrzejek]

I started with early binding (and had the same problem with Excel's constants), but since I cannot be sure what version of Excel my users will have, I moved to late binding:

Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object

xlApp = CreateObject("Excel.Application")
xlBook = xlApp.Workbooks.Add()
xlSheet = xlApp.Worksheets(1)

xlApp.visible = True[green]
'Code from above post[/green]

But that should not make a difference, right?

Have fun.

---- Andy

 

[Andrzejek]

I just tried early binding again, and it works OK (with the reference you had)

Imports MyExcel = Microsoft.Office.Interop.Excel

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim xlApp As New MyExcel.Application
        Dim xlBook As MyExcel.Workbook = xlApp.Workbooks.Add(System.Reflection.Missing.Value)
        Dim xlSheet As MyExcel.Worksheet = Nothing

        With xlApp
            .Visible = True
            With .Range("D5:H16").Borders(MyExcel.XlBordersIndex.xlEdgeLeft)
                .LineStyle = MyExcel.XlLineStyle.xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = MyExcel.XlBorderWeight.xlThin
            End With
        End With
    End Sub
End Class

So, how can I made it work with late binding....?

Have fun.

---- Andy

 

[softhemc]

All the Excel constants have numeric values. If you have a look in the MSDN library you should be able to find the values you need. You can then (if you like) declare them as named constants within your application and then you can use your named constants in a late bound scenario.

 

[softhemc]

Not tested, but values found in MSDN

For example:

[tt]
With xlApp

' With .Range("A1:B2").Borders(xlEdgeLeft)
' .LineStyle = xlContinuous
' .ColorIndex = 0
' .TintAndShade = 0
' .Weight = xlThin
' End With

With .Range("A1:B2").Borders(7)
.LineStyle = 1
.ColorIndex = 0
.TintAndShade = 0
.Weight = 2
End With


End With
[/tt]


I haven't used early binding for a long time, so I'm not sure, but you may also find that .Range, .Borders etc. aren't liked, so you may need to change your Option Settings for Strict and/or Explicit.

 

[Andrzejek]

I know I can find the numeric values for all those Excel constants and use them in my code. Have done that already. It is just like using ‘magic numbers’ in the code. I would hope there is a better way: to retrieve those values from Excel and not have to declare them separately with late Excel binding.

Well, the search goes on…


Have fun.

---- Andy

 

[DaveInIowa]

Whether or not you use early or late binding, the constants are still there in the Microsoft.Office.Interop.Excel namespace. Just because you import the namespace doesn't mean you need to early binding.

 

[Andrzejek]

I was wondering about it, Dave…
I don’t have a machine to test it on, but I was wondering if I can have a reference to Excel object, use early binding while coding, and then use late binding when creating EXE for users and keep the reference in the application. I would guess this reference is not checked if nowhere in code I would use it, so it should not create any problems / errors / crashes even if it is left in the application. I would guess reference to a dll in C:\Program Files (x86)\ in XP machine should be fine, too, if not used.

But I am not clear on “import the namespace” in late binding if I would reference a dll in C:\Program Files (x86)\ on XP machine, and (x86) is Win7 location.


Have fun.

---- Andy