Excel VBA - .xlam (AddIn) throwing Subscript Out of Range Error 9 but everything worked as xlsm?

Question

Basically what the label on the tin says.

I have multiple userforms, multiple modules, class modules, etc. I have worksheets where data is stored and used by the code. I have added a tab and buttons to the ribbon.

I ran and tested everything from "Come Here" to "Sick 'Em" as a .xlsm file. Every field of every form, every botton, every line of code runs perfectly.

I saved the file as an .xlam and the thing hangs on this line of code in the very first module:

Worksheets("All Welders Data").Activate

As I said, I get Subscript Out of Range Error 9.

This is my first time trying to take a VBA project and save it as an add-in. I must be missing something. I have read (John Walkenbach "Excell 2013...") that the .xlam file is still a workbook with worksheets and everything should work just like it was a .xlsm.

Is it the reference to the sheet that is wrong? Is it the activate? Do i need to prefix Something.Worksheets("blah").Activate ?

I'm tired and I'm totally stumped. I hope someone has some kind of input for me. Thanks in advance!

Update

I want to tank everyone for their responses and comments. I do believe that "ThisWorkbook" will probably solve the issue. I am close now to being able to test it and i will update my question again with my results.

However, since there is so much discussion on this post about my use of activate I want to pose a follow-up question that is related to all of this.

Here is a sample from one of my modules.

Private Sub UserForm_Initialize()
    Dim lastRow As Long
    Dim nameCell As Range
    Dim box As control

    'Set Window size and position
    With Application
        .WindowState = xlMaximized
        Me.Top = .Top * 0.5
        Me.Left = .Left * 1.0015
        Zoom = Int((.Width * 0.85) / (Width * 0.85) * 40)
        Width = .Width * 0.995
        Height = .Height * 0.992
    End With

    'Turn off everything except the radio Buttons and thier labels
    Me.submitButton.Visible = False
    Me.submitButton.Enabled = False
    Me.chooseWelderLabel.Visible = False
    Me.chooseWelderNameComboBox.Visible = False
    Me.chooseWelderNameComboBox.Enabled = False
    Me.welderNameLabel.Visible = False
    Me.welderNameText.Visible = False
    Me.welderNameText.Enabled = False
    Me.checkNameButton.Visible = False
    Me.checkNameButton.Enabled = False

    Application.ScreenUpdating = False

    'Activate the worksheet
    Application.ThisWorkbook.Worksheets("All Welders Data").Activate
    Application.ThisWorkbook.Worksheets("All Welders Data").Range("A1").Activate

    'sort the data in the active sheet by the welder's last name.

    With Application.ThisWorkbook.ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("B3"), Order:=xlAscending
        .SetRange ActiveCell.CurrentRegion.Offset(1)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    Application.ScreenUpdating = True

    'populate the combox from the active sheet (welder name in the
    'first column, welder ID number in the second column.
    With Application.ThisWorkbook.ActiveSheet
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For Each nameCell In .Range("E3:E" & lastRow)
            If nameCell.Value <> "" Then
                With Me.chooseWelderNameComboBox
                    .ColumnCount = 2
                    .AddItem nameCell.Value
                    .List(.ListCount - 1, 1) = nameCell.Offset(, -1).Value
                    'ComboBox now shows the values in column "E" and the values
                    'in coulmn "D" - in that order, as in  "Name" - "ID Number".
                    '(the reverse order of the columns in the worksheet.)
                End With
            End If
        Next
    End With
End Sub

You will see about halfway down that I do activate the sheet. Then on the next line I am activating a range. I need to activate the range because I am then sorting in the With block that follows. Sorting is done because the ComboBox needs to be in alphabetical order.

If I want to activate the Range ("A1") I have to make sure the worksheet is activated. If not, then the Range("A1").Activate will fail with a 1004 error because you can't activate a cell unless the worksheet where the cell lives is the active sheet.

So if I am going to be hard core about never ever using activate what are the suggestions for refactoring this code so that I can use excel's built in sorting. I am not interested in doing any sort of looping routine to sort.

Final Word

I just want to thank Robin MacKenzie for her answer. She was on the right track and gave a good answer that solved the issue.

Also, I just want to say that I still think there are times when using activate is not a sin!

Answer 1

Further to this comment from MSDN on the ThisWorkbook property:

Use this property to refer to the workbook that contains your macro code. ThisWorkbook is the only way to refer to an add-in workbook from inside the add-in itself. The ActiveWorkbook property doesn't return the add-in workbook; it returns the workbook that's calling the add-in.The Workbooks property may fail, as the workbook name probably changed when you created the add-in.

(my emphasis)

You should do this instead (and avoid Activate):

Dim wsData As Worksheet

Set wsData = ThisWorkbook.Worksheets("All Welders Data") '<-- use ThisWorkbook

' no need to Activate - just use the data
Dim rngStuff As Range

Set rngStuff = wsData.Range("A1:G5") '<-- change hardcoded range to whatever you need
' now work with the range

Edit

Regarding the updated question, I'd suggest code similar to below to populate the ComboBox on the UserForm without using Activate. It is tested apart from the section that deals with loading up the ComboBox which is mainly your working code anyway.

Option Explicit

Sub UserForm_Initialize()

    Dim wsData As Worksheet
    Dim rngToSortFirstCell As Range
    Dim lngLastRow As Long
    Dim rngNameCell As Range

    ' set a reference to the sheet
    Set wsData = ThisWorkbook.Worksheets("All Welders Data")

    ' clear existing sort fields
    wsData.Sort.SortFields.Clear

    ' set a reference to the header of range to sort
    Set rngToSortFirstCell = wsData.Range("B3")

    ' sort it by CurrentRegion of first cell
    rngToSortFirstCell.Sort Key1:=Range("B3").CurrentRegion, _
        Order1:=xlAscending, _
        Header:=xlYes, _
        MatchCase:=False, _
        SortMethod:=xlPinYin

    ' put welder names to combo box
    With wsData
        lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For Each rngNameCell In .Range("E3:E" & lngLastRow)
            If rngNameCell.Value <> "" Then
                With Me.chooseWelderNameComboBox
                    .ColumnCount = 2
                    .AddItem rngNameCell.Value
                    .List(.ListCount - 1, 1) = rngNameCell.Offset(, -1).Value
                    'ComboBox now shows the values in column "E" and the values
                    'in coulmn "D" - in that order, as in  "Name" - "ID Number".
                    '(the reverse order of the columns in the worksheet.)
                End With
            End If
        Next
    End With

End Sub