Dynamically named ranges

[sunkai]

Any help appreciated on this one.

I'm dynamically creating named ranges in Excel VBA code:

index.rangeName = idxRngPrefix & Replace(index.mdSymbol, ".", "_")

ActiveWorkbook.Names.Add index.rangeName, Range(Worksheets("index compositions").Range("A8").Offset(0, i * indexColumnOffset).Address, Worksheets("index compositions").Range("M8").Offset(j + 5, i * indexColumnOffset).Address).Address

I can see that these are being generated correctly by checking the defined names list in Excel.

However, when I try and refer to the ranges I get an error:

Run-time error '1004'
Method 'Range' of object '_Global' failed

The code that I am referring to the ranges in is:

For Each index In idxs
For i = 1 To index.components.count
For Each component In index.components
If Range(index.rangeName).Item(6 + i, 1) = component.id Then
component.price = Range(index.rangeName).Item(6 + i, 1)
Exit For
End If
Next
Next
Next

I have tried prefixing "Range" with "Activeworkbook", but this does not help.

Thanks for looking.

Sunkai

 

[Richard Schollar]

Hi Sunkai

Does it make any difference if you use the Value property of the range ie:

If Range(index.rangeName).Item(6 + i, 1).Range = component.id Then

 

[sunkai]

Afraid the error is the same. It seems to be the referal to the range name itself that is the problem.

After you have added a named range in excel in VBA code, do you have to refresh some sort of cache such that they are recognised in subsequent code?

The error message I get is the same as if I had typed Range("foo"), where there is certainly no range named foo.

 

[Richard Schollar]

Is the Name actually present in the active workbook - ie you haven't got another workbook open at the same time that may be active instead do you?

 

[sunkai]

They are present in the active workbook.

I have tested the code by manually defining a range "foo", and then overriding the range name in the code from index.rangeName to "foo". It does not error out in this case.

 

[Richard Schollar]

What value does index.rangeName hold when the code bugs out? You should be able to tell by hovering the mouse over it in the VBE.

 

[sunkai]

idx_sxke_zrhr

 

[Richard Schollar]

And if you type that over the index.rangeName within the Range() method, does the code then work?

 

[sunkai]

I've got it Richard. The way that I was defining the ranges in the first place were no good.

If you use the Range.Address function to generate the range definition, the format of the string is ="cellA:cellB", and is not valid.

I would have thought that there would be some validation when adding a named range, but I guess not.

I've chopped off the first and last characters, and thrown in the sheet name for good measure (i.e. the range is now ='sheetname'!cellA:cellB)

Thanks for helping me work through it.