Excel Macro vs Excel Add-In and cell selection

Question

I am trying to determine why "Cells.Select" (which is used to select all the cells in a worksheet) will work in a macro but the exact same command will result in a Run-time error: 1004. Has the add-in been set up in correctly? Is there a difference in how the code is executed between running a macro vs running an add-in?

This also will apply to any command using "Cells" such as the following...

If Not IsEmpty(Cells(a, 1)) Then

    Cells(a, 3) = Num

End If

Note that the value of Num is a valid integer but the point I'm trying to make here is that the actual cell value is not set to Num when the condition is TRUE.

Thank you in advance

Are you running the add-in macro with a workbook open, or not? Are the cells you're trying to select in the open workbook, or a hidden sheet within your add-in book? — CLR, Feb 28 '19 at 14:58
There is an important difference in ActiveWorkbook and ThisWorkbook in this case. Simply using Cells isn't clear enough. — Sam, Feb 28 '19 at 15:01
yes, CLR, the workbook is open and the cells are within that open workbook and are not hidden...nothing is hidden. — ViggyTheBadMonkey, Feb 28 '19 at 15:01

score 2 · Answer 1 · answered Feb 28 '19 at 15:08

Using your example and the extra info you've provided in the comments section, your code of:

If Not IsEmpty(Cells(a, 1)) Then

    Cells(a, 3) = Num

End If

Would better be:

With Application.ActiveSheet

    If Not IsEmpty(.Cells(a, 1)) Then

        .Cells(a, 3) = Num

    End If

End With

Also, try to resist using .Select unless you really have to. Check out: this excellent article

score 0 · Answer 2 · answered Feb 28 '19 at 15:04

ThisWorkbook will directly reference the workbook that the code resides in. It is possible to have a hidden worksheet as part of an Addin having done it myself and that is the only reason to ever use ThisWorkbook in an Addin.

With an Addin you most always want to make reference to ActiveWorkbook to apply macros to whatever workbook the Addin needs to manipulate. Also use ActiveSheet where referencing sheets too.

Change your code to ActiveSheet.Cells.Select

Thank you xn1...I normally use ActiveSheets and will never go without it again! — ViggyTheBadMonkey, Feb 28 '19 at 17:05

Excel Macro vs Excel Add-In and cell selection

2 Answers2