range object error

[BigJimmer]

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!

 

[papou]

Hello
How about the sheet name?

HTH
Cordially
Pascal

 

[Tom Ogilvy]

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.

 

[BigJimmer]

I tried it before with and without the sheet name, and had gotten the same
error.

 

[Joel]

The 1004 error will occur if MyArray is a size of 1. UBound(MyArray) - 1
will equal zero which will produce this error.

 

[Tom Ogilvy]

More important would be the value of Ubound

in VBA, you can have

Sub abcd()
Dim myArray(5 To 5)
MsgBox UBound(myArray)
End Sub

which is of size 1, but doesn't cause a problem. But I know what you mean <g>

 

[BigJimmer]

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.

 

[Joel]

There is no sheet reference. Notice the periods in front of Range and the
two Cells.

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

 

[BigJimmer]

Yes, I saw those. I used the code that Tom provided without an issue until
I tried to reference it from ThisWorkbook.Workbook_SheetChange.

There is something about it being referenced from this module that is a
problem, or maybe it's just me, as even this simple hard coded logic withing
this module generates the same error -

ActiveWorkbook.Names.Add Name:="WHData", _
RefersTo:=ActiveWorkbook.Worksheets("Sheet1").Range("$B$5:$E$8")

 

[Joel]

Using excel 2003 I put the following code into this wrokbook and dcid not get
any error. I tested the code by putting c hanges into sheet 1, 2, & 3. Try
openning a new workbook (close all excel workbooks) and run this code. I bet
you don't get an error. Do you have any protections set in your workbook?

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Source As Range)
' runs when a sheet is changed

ActiveWorkbook.Names.Add Name:="WHData", _
RefersTo:=ActiveWorkbook.Worksheets("Sheet1").Range("$B$5:$E$8")

End Sub

 

[BigJimmer]

As you expected, when I copied the code to a new workbook, it worked fine.

There is no protection on the existing document yet, as that is the last
step I was going to do.

Any idea as to why this doesn't work in the existing workbook?

Thanks!