Excel VB "Type 'X' is not defined"

May 27, 2013 at 11:05 AM
Hi,

I've been trying to convert some existing VB to Excel-DNA, and am stuck converting the following code.
Sub SheetCheck()
Dim sh As Worksheet, flg As Boolean
For Each sh In Worksheets
If sh.Name Like "Results" Then flg = True: Exit For
Next
If flg = False Then
Sheets.Add.Name = "Results"
End If

End Sub
It worked fine in Excel before adding the ExcelDnaUtil references. Now, in Excel-DNA it gives the error that "Type 'Worksheet' is not defined"

I've tried to work around that, creating the following code instead. However, in this new code, The line app.Worksheets(i).Name doesn't work. The message box will display once, then the code exits silently and the new sheet isn't made.

Any help would be appreciated.
Sub SheetCheck()
 Dim app as Object = ExcelDnaUtil.Application 
Dim flg As Boolean
Dim i As Integer

i=0
flg=FALSE
Do While i<app.Sheets.Count
        MsgBox("Hi")
    If app.Worksheets(i).Name Like "Results" Then flg = True: Exit Do
    i=i+1
Loop
    
If flg = False Then
    app.Sheets.Add.Name = "Results"
End If


End Sub
Coordinator
May 27, 2013 at 11:29 AM
Hi,

When you are talking to the "Worksheets" collection, you are using COM Automation to talk to the Excel object model. The Excel collections are 1-based (even though arrays in VB.NET are 0-based). So you need to start your index at 1, and the last valid index will be "app.Sheets.Count".
        i = 1
        flg = False
        Do While i <= app.Sheets.Count
To get the "For Each" working on the Excel collections can be done with a little trick - you need to change the type of your object to "IEnumebrable" explicitly, so something like this should work too, and is a bit closer to your original code:
    Sub SheetCheck2()

        Dim app As Object = ExcelDnaUtil.Application

        Dim sh As Object
        Dim flg As Boolean

        For Each sh In CType(app.Worksheets, IEnumerable)
            If sh.Name Like "Results" Then flg = True : Exit For
        Next
        If flg = False Then
            app.Sheets.Add.Name = "Results"
        End If

    End Sub
End Module
You might also find the guide by Patrick O'Beirne here to be useful: http://sysmod.wordpress.com/2012/11/06/migrating-an-excel-vba-add-in-to-a-vb-net-xll-with-excel-dna-update/ He discusses some options for referencing the "interop" assemblies (either the office "Primary Interop Assemblies" or the NetOffice alternative ones) that allow you to refer to the Office and Excel types like Worksheet in your code, and get IntelliSense when dealing with these.

I hope that gets you on the right track.

The best place for these kind of questions is probably the Google group at http://groups.google.com/group/exceldna , though I'm also happy to help here.

Regards,
Govert
May 28, 2013 at 10:28 AM
Thanks Govert, my code is now working.

Patrick's site also looks really useful, thanks for pointing it out. I see that you linked it on the Excel-DNA front page, but sifting through the various tutorials and forums I hadn't read that one.
Coordinator
May 28, 2013 at 10:35 AM
Cool - Good luck with your venture into the fabulous .NET world!

-Govert