0

I am receiving a System.MissingMemberException: 'Public member 'Controls' on type 'Worksheet' not found.' error on the WS.Control line of the code below. The code below is the only solution I have been able to come up with to access the active running instance of Excel.

    Dim NativeWorksheet As Microsoft.Office.Interop.Excel.Worksheet = Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets(1)
    Dim WB As Microsoft.Office.Interop.Excel.Workbook = Globals.ThisAddIn.Application.ActiveWorkbook
    Dim worksheet As Microsoft.Office.Tools.Excel.Worksheet = Globals.Factory.GetVstoObject(NativeWorksheet)
    Dim rangeName As String = "Scope"
    Dim selection As Excel.Range = Globals.ThisAddIn.Application.Selection

    WB.Worksheets.Add()
    Dim WS As Excel.Worksheet = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet
    WS.Name = "Scope"
    WS.Activate


    selection = WS.Range("A1:C30")
    selection.Activate
    If Not (selection Is Nothing) Then
        Dim namedRange As NamedRange = WS.Controls.AddNamedRange(selection, rangeName)
    End If

The code works fine if I use worksheet in place of WS however, it adds the named range to the initial worksheet and I am looking to add the named range to the newly added worksheet. Thank you very much in advance for any much needed support on this!!

Grackel
  • 21
  • 6
  • Am I missing something? Doesn't WB.Worksheets.Add() return a Worksheet? Why go through the gyrations with ActiveWorkbook.ActiveWorksheet if the Worksheet you want to manipulate is returned by the WB.Worksheets.Add() method? – Joseph Willcoxson Apr 09 '17 at 04:15
  • The code is using two distinct WorkSheet types, one from Microsoft.Office.Interop.Excel (which does not have a Controls member) and one from Microsoft.Office.Tools.Excel (which does). One too many, mixing just isn't useful, stick with the VSTO types when you write an add-in. And do favor `Option Strict On` so the compiler can tell you about mistakes like this before they turn into undebuggable runtime exceptions. – Hans Passant Apr 09 '17 at 11:36
  • @Hans, thanks, makes sense what you are saying, I will definitely put Option Strict on. I found the code on Microsoft's web page [link](https://msdn.microsoft.com/en-us/library/cc442817.aspx) so I figured it was needed. I will try your suggestion here. Joe, normally it does, however, if you are trying to reference the active instance of Excel it fails, have to do it this way (only way I have found so far) to make it work with the COM object. – Grackel Apr 09 '17 at 18:16

0 Answers0