I am successful in running the provided routine directly from a standard
module, and even from a worksheet module. When I attempt to call the same
code from ThisWorkbook.Workbook_SheetChange event, I get the application
error.

I'm using the version provided that specifies the worksheet.

"Tom Ogilvy" wrote:

Sub ABC()
Row = 3
col = 12
MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)
ActiveWorkbook.Names.Add Name:="WHData", _
RefersTo:=Range(Cells(Row, col), _
Cells(Row + UBound(MyArray) - 1, col + 3))
End Sub

In a general module worked fine for me. If it is in a sheet module then
you need to use something like

Sub ABC()
Row = 3
col = 12
MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)
With worksheets("Sheet1")
ActiveWorkbook.Names.Add Name:="WHData", _
RefersTo:=.Range(.Cells(Row, col), _
.Cells(Row + UBound(MyArray) - 1, col + 3))
End With
End Sub

so you are sure all range references are on the same page.

Otherwise, make sure your references form a legal address.

--
regards,
Tom Ogilvy

"BigJimmer" wrote:

I'm using Excel 2000, and trying to modify a range name within VBA. When I
try code such as this -

Dim row, col as integer

row = 1
col = 1

ActiveWorkbook.Names.Add Name:="WHData", _
RefersTo:= Range(Cells(row, col), Cells(row + UBound(MyArray) - 1, col +
3))

I get 1004 Application-defined or object-defined error. I have confirmed
that UBound(MyArray) is defined, and in my code I have logic that changes
the values of row and col.

To test, I used the following, and did not get this error -

ActiveWorkbook.Names.Add Name:="WHData", RefersTo:= Range("A1:C5")


What am I missing to be able to use cells(rwindex, colindex) to set the range?

Thanks!