error while running VBA add-in

Question

I'm writing a VBA macro in Excel 2010 in an .xlam file.

When I try to run it I get this error:

object variable or with block variable not set

It is supposed to swap columns in specific table, and when I run it as just a macro (not in the add-in) it works perfectly. This is my macro:

Sub SwapTable(ByVal control As IRibbonControl)
Dim LastCol As Long
Dim LastRow As Long
Dim Swaps As Long
Dim i As Integer
Dim DocumentTitle As String
Dim SearchDetails As String

LastRow = LastRowInOneColumn()
LastCol = LastColumnInOneRow(LastRow)
StartTitlesRow = Find_TitlesRow()
'copy title rows
With ActiveSheet
    DocumentTitle = .Cells(StartTitlesRow - 3, 1).Value
    SearchDetails = .Cells(StartTitlesRow - 2, 1).Value
End With

'check how many swaps needed
If LastCol Mod 2 = 0 Then
    Swaps = LastCol / 2
Else
    Swaps = (LastCol - 1) / 2
End If

'run swap
For i = 1 To Swaps
   Call Swap(i, LastCol - i + 1, LastRow, StartTitlesRow - 1)
Next i

'past title rows
With ActiveSheet
    .Cells(StartTitlesRow - 3, 1) = DocumentTitle
    .Cells(StartTitlesRow - 2, 1) = SearchDetails
End With
Worksheets(1).Columns("A:EE").AutoFit
End Sub



Function LastColumnInOneRow(LastRow As Long) As Long
'Find the last used row in a Column: column A in this example
Dim LastCol As Long
With ActiveSheet
        LastCol = .Cells(LastRow, .Columns.Count).End(xlToLeft).Column
End With
LastColumnInOneRow = LastCol
End Function

Function LastRowInOneColumn() As Long
'Find the last used row in a Column: column A in this example
Dim LastRow As Long
With ActiveSheet
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
LastRowInOneColumn = LastRow
End Function


Function Find_TitlesRow() As Long

Dim SearchString As String
Dim StartTitlesRow As Long

SearchString = "ùåøä"

With ActiveSheet
    Set cl = .Cells.Find(What:=SearchString, _
        After:=.Cells(1, 1), _
        LookIn:=xlValues, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=False)

        If Not cl Is Nothing Then
            StartTitlesRow = cl.Row
        Else
            MsgBox "Could'nt find start row"
        End If
End With

Find_TitlesRow = StartTitlesRow
End Function


Function Swap(Col1 As Integer, Col2 As Integer, LastRow As Long,     StartTableRow As Variant)
Dim FirstCol As Variant
Dim SecondCol As Variant
Dim temp As Variant

    temp = Sheets(1).Range(Cells(StartTableRow, Col1), Cells(LastRow, Col1)).Value
    Sheets(1).Range(Cells(StartTableRow, Col1), Cells(LastRow, Col1)).Value = Sheets(1).Range(Cells(StartTableRow, Col2), Cells(LastRow, Col2)).Value
    Sheets(1).Range(Cells(StartTableRow, Col2), Cells(LastRow, Col2)).Value = temp

End Function

What line gives the error? What is ActiveSheet when you run it as an add-in? — Ron Rosenfeld
– Ron Rosenfeld, Commented Jul 5, 2016 at 12:47
That's odd. Usually the cursor location when the macro breaks will give you an idea as to which line. Repeating: What is ActiveSheet when you run it as an add-in? — Ron Rosenfeld
– Ron Rosenfeld, Commented Jul 5, 2016 at 12:52
That does not necessarily mean that ActiveSheet in your macro is pointing to that location. When you get the error message, the VB Editor should open. If it is behind other windows, select it. Then, note the location of the cursor in your macro. Then in the immediate window, type ?Activesheet.Range("A1") (or some other address) and ensure that the return is the same as the content of what you expect to be at that location. — Ron Rosenfeld
– Ron Rosenfeld, Commented Jul 5, 2016 at 13:18
Oh, and be sure when you get that error message, to select Debug and not Cancel — Ron Rosenfeld
– Ron Rosenfeld, Commented Jul 5, 2016 at 13:22
i found the issue, but i dont know how to fix it: when im running the macro he dosnt recognize the work sheet. i tryied to activate the woorkbook using: Workbooks(1).Activate MsgBox Workbooks(1).Name and it does show the right workbook name. but when i tryeid to check wich sheet is active and he shows me "sheet1" insted of the sheet in the active work book. also, the macro doent recognize the whith AcitveSheet() so i changed it to with sheets(1) — ALEXM
– ALEXM, Commented Jul 6, 2016 at 5:58

Olle Sjögren · Accepted Answer · 2016-07-06 06:35:48Z

Avoid using ActiveSheet! It will only give you problems like the one you're having, where you are not sure which sheet it is referencing. Avoid ActiveWorkbook while you're at it, for the same reason.

Instead, get a reference to the sheet you want to work with:

Dim oWb As Workbook
Dim oSheet As Worksheet

Set oWb = Workbooks("[WORKBOOKNAME]")
'***** or use index like Workbooks(1)

If Not oWb Is Nothing Then
    Set oSheet = oWb.Sheets("[WORKSHEETNAME]")
    '***** or use index like Sheets(1)
End If

If Not oSheet Is Nothing Then
    '***** Do your stuff

    'past title rows
    With oSheet
        .Cells(StartTitlesRow - 3, 1) = DocumentTitle
        .Cells(StartTitlesRow - 2, 1) = SearchDetails
    End With

    '***** etc

End If

Or you could use an index like you already do in some places, but you need to specify a workbook as well, to avoid using ActiveWorkbook:

oWb.Worksheets(1).Columns("A:EE").AutoFit

Collectives™ on Stack Overflow

error while running VBA add-in

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related