XL2003 Macro Error with Square Bracket Range Reference

[MeGustaXL]

Hi All,

I've recently started re-using a VBA application I developed long ago, when I was using Office 97. It has a lot of "speeding up" features from "Skip's Tips", in particular, ranges are referred to within square brackets, e.g.

If [C14]<54 then [Total]=[C20]

Now it stops with a Compile error "Can't find project or library" EVERY TIME it encounters one of these bracket-delimited range statements!
Here's an example. This stops with the first line highlighted yellow, and the first occurrence of [G12] highlighted blue:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo 21
If Target = [G12] Then
[G15].Value = 1 / [G12] * 1000000
End If
If Target = [G15] Then
[G12].Value = 1 / ([G15] / 1000000)
End If
21
End Sub

Does this mean I'll have to go through ALL the code and change these references?

I really hope not, because there are 12 sheets and 16 Modules, each with an average of 130 lines, most of which use this method.

I'm using Office 2003, with VBA Version 6.5

Chris

Someday I'll know what I'm donig...damn!

 

[combo]

This syntax in excel 2003 is ok.
I would check for missing references first. Maybe you have a userform/sheet with a control from one of old common controls (check version). In Tools>Options>grneral try to set "break on all errors" errors trapping level.

combo

 

[SkipVought]

Hi,

I'd fully qualify the [total] range to the sheet

If [C14]<54 then sheets("YourSheetName").[Total]=[C20]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MeGustaXL]

Skip, I'd do that, but there are about a hundred named ranges throughout the workbook, and furthermore, it never tripped up until now.
It seems to be objecting to the [C14] type cell references in particular anyway, within the code embedded in the sheet(s)

Chris

Someday I'll know what I'm donig...damn!

 

[Andrzejek]

Did you try to replace [tt][C14][/tt] with [tt]Range("C14").Value[/tt] and see if that would be a good fix?
I know you have a lot of them, but if that would work, you may try to replace all:

[pre][highlight #FCE94F] [[/highlight]C14[highlight #8AE234]] [/highlight][/pre] with [tt][highlight #FCE94F]Range("[/highlight][/tt] and the ] with [tt][highlight #8AE234]").Value[/highlight][/tt]

Notice the spaces before [ and after ]

Then you may address [tt][G12][highlight #F57900].Value[/highlight][/tt] portion because you use both ways.

Have fun.

---- Andy

 

[SkipVought]

Are ALL your range names GLOBAL - Workbook level names, rather than LOCAL - Worksheet level names?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}