Henri Pellemans RBA      

VB.NET Add-in express^TM examples

I have developed a lot of Excel add-ins using Visual Studio 2010 and Add-in Express. I will provide you with an example. While developing an Excel sheet it maybe useful to have all range names visible. However you might not want the end user to see the names you've used. With just one ribbon button click you can hide all Excel range names. The VB.NET code is as follows:

Private Sub hideNames()
    Dim wb As Excel.Workbook = TryCast(Me.ExcelApp.ActiveWorkbook, Excel.Workbook)
    Try
        For Each myName As Excel.Name In wb.Names
            myName.Visible = False
        Next
    Catch ex As Exception
        MessageBox.Show("something went wrong")
    Finally
        If Not IsNothing(wb) Then
            Marshal.ReleaseComObject(wb)
            wb = Nothing
        End if
    End Try
End Sub

Private Sub showNames()
    Dim wb As Excel.Workbook = TryCast(Me.ExcelApp.ActiveWorkbook, Excel.Workbook)
    Try
        For Each myName As Excel.Name In wb.Names
            myName.Visible = True
        Next
    Catch ex As Exception
        MessageBox.Show("something went wrong")
    Finally
        If Not IsNothing(wb) Then
            Marshal.ReleaseComObject(wb)
            wb = Nothing
        End if
    End Try
End Sub

---

In order to measure the speed of your Excel workbook I have made a ribbon button available that recalculates the Excel workbook with a full rebuild. Please note if you have more than one book open, all workbooks will be recalculated. The VB.NET code is as follows:

Private Sub recalculateFullRebuildButton_OnClick(sender As System.Object,
                              control As AddinExpress.MSO.IRibbonControl,
                              pressed As System.Boolean) Handles recalculateFullRebuildButton.OnClick
    Dim sWatch As New Stopwatch
    sWatch.Start()
    ExcelApp.CalculateFullRebuild()
    sWatch.Stop()
    Dim elapsedTime As Long = sWatch.ElapsedMilliseconds
    Dim message As String = String.Format("recalculation took {0} milli-seconds", elapsedTime.ToString)
    MessageBox.Show(message)
End Sub