The functions start the application-defined error when called from the Excel Addin module, but work well when used in the scope of the workbook

advertisements

I have the following example:

Sub TestArray()
On Error GoTo errCatch

Dim resultArray(2, 2) As Variant
resultArray(0, 0) = "00"
resultArray(1, 0) = "10"
resultArray(2, 0) = "20"
resultArray(0, 1) = "01"
resultArray(1, 1) = "11"
resultArray(2, 1) = "21"
resultArray(0, 2) = "02"
resultArray(1, 2) = "12"
resultArray(2, 2) = "22"

Dim myRange As Range
Set myRange = ActiveWorkbook.Worksheets("Sheet4").Range("A1").Resize(3, 3) 'row,col
myRange.Value = resultArray

errCatch:
   If Err Then
   MsgBox Err.Description
   End If
End Sub

When called from within the scope of the active workbook, for example a button on the worksheet, it works great.

When called as from a module in an addin (.xlam), I get the "Application-Defined or Object-defined" error thrown on this line:

Set myRange = ActiveWorkbook.Worksheets("Sheet4").Range("A1").Resize(3, 3) 'row,col

I see this same behavior when i work with functions/properties on range objects like .value= and .select.

The only thing i can think of is scope, but i can set other range properties just fine line border styles and colors.

Any help would be great...

Thank you in advance, Mark


I would add some code to check whether the sheet "Sheet4" exists in the workbook where you want to run the add-in.

Sub TestArray()
'On Error GoTo errCatch

Dim resultArray(2, 2) As Variant
resultArray(0, 0) = "00"
resultArray(1, 0) = "10"
resultArray(2, 0) = "20"
resultArray(0, 1) = "01"
resultArray(1, 1) = "11"
resultArray(2, 1) = "21"
resultArray(0, 2) = "02"
resultArray(1, 2) = "12"
resultArray(2, 2) = "22"

' Testing if sheet exists
Dim wsTest As Worksheet
Const strSheetName As String = "Sheet4"
Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo 0
If wsTest Is Nothing Then
    Worksheets.Add.Name = strSheetName
End If

Dim myRange As Range
Set myRange = ActiveWorkbook.Worksheets("Sheet4").Range("A1").Resize(3, 3) 'row,col
myRange.Value = resultArray

errCatch:
   If Err Then
   MsgBox Err.Description
   End If
End Sub