How To Develop My Own Excel Add-in in VB.Net

Question

I am trying to create an Excel Add-in using Vb.Net. I've started an Excel 2007 Add-in Project in VS2010. Sadly, I am not good with vb.net; I am more a VB6 developer in this regard, and my ThisAddin.vb code is:

Public Class ThisAddin

    Private Sub ThisAddIn_Startup() Handles Me.Startup

    End Sub

    Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown

    End Sub

    ' test function; simple
    Public Function getRowCount() As Long
        Dim thisWB As Workbook = Me.Application.ThisWorkbook
        Dim activWS As Worksheet
        activWS = thisWB.ActiveSheet
        Return activWS.UsedRange.Rows.Count
    End Function
End Class

I've also added a Ribbon item (via Add New Item... menu option) in designer mode (not xml) - and then add a button. Then I go to code and try to call the function and I get this error when using:

MsgBox(Globals.ThisAddIn.getRowCount())

Which I got from this link: Calling a procedure within another class

To be honest, I've been trying a myriad things and I've been getting so many errors. I've been looking online as well for a tutorial on creating my own Excel Addin from scratch with no real luck. I would like not to use Add-In-Express since that's a third party app and I have to create an Excel add-in for my company from scratch.

Does anyone have an idea on how I can create a vb.net coded Excel Addin (2007) that I can use as a template or guide? I've tried several and many rely on Add-In-express and I really cannot go that way. I have a lot of VBA code (natural VBA so it's in a module in an my excel files' VBA/Developer section) and I think I can translate those from VBA/VB6 to VB.Net format so that's not my concern. It is really about getting to code my own Excel Addin in VB.Net. Any help would really be great. Thank you.

*note: I would also like not to have to ask coworkers (or do myself) to just add to the quick access toolbar the functions and subs I've created since that's really not a solution, considering that those buttons will be there when they create or open another workbook. Essentially, I've got to create my own excel addin in vb.net. Thank you once again.

Answer 1

The issue has to do with the definitions in Microsoft.Office.Tools.Excel and Microsoft.Office.Interop.Excel. To code an "Interop" version you could use this:

   Public Function getRowCount() As Long

        Dim thisWB As Excel.Workbook = Application.ActiveWorkbook
        Dim activWS As Excel.Worksheet = CType(thisWB.ActiveSheet, Excel.Worksheet)

        Return activWS.UsedRange.Rows.Count

    End Function

To extend the functionality of the Native objects and use VSTO, you could do it like this:

    Public Function getRowCount() As Long

        Dim NativeWorkbook As Excel.Workbook = Application.ActiveWorkbook
        Dim NativeWorksheet As Excel.Worksheet = CType(NativeWorkbook.ActiveSheet, Excel.Worksheet)

        Dim thisWB As Workbook = Nothing
        Dim activWS As Worksheet = Nothing

        If NativeWorkbook IsNot Nothing Then
            thisWB = Globals.Factory.GetVstoObject(NativeWorkbook)
        End If

        If NativeWorksheet IsNot Nothing Then
            activWS = Globals.Factory.GetVstoObject(NativeWorksheet)
        End If

        Return activWS.UsedRange.Rows.Count

End Function

This is a function you can put in ThisAddin.vb that will create a new Worksheet. Note that this function names the Worksheet and adds it to the end.

Public Function AddWorkSheet(sheetName As String) As Worksheet

    Dim wk = Application.ActiveWorkbook
    Dim ws As Worksheet = Nothing

    Try
        ws = CType(wk.Sheets.Add(, wk.Sheets(wk.Sheets.Count)), Worksheet)
        ws.Name = sheetName
    Catch ex As Exception
        Throw
    Finally
        AddWorkSheet = ws
    End Try

End Function

To use this outside of ThisAddin.vb you could do something like this:

 Dim ws As Excel.Worksheet
 Dim newSheetName As String
     .
     '
 ws = Globals.ThisAddIn.AddWorkSheet(newSheetName)