0

a valueI've inherited a large VBA project and whilst I have lots of dev expereince I have a small amount of VBA. The code reads data off a sheet in the form:

Intersect(Range("colName"), .Rows(intCurrentRow)).Value

Where colName is a named range, or so I thought. I have searched all of the project code and the excel sheets and cannot find where colName is defined ?

So far I have searched the code, looked in Name Manager on the sheet and have googled furiously but hit a total blank. As I now need to read in another value from the Sheet I would really prefer to use the code that is currently used with another value instead of colName to reference my new data field.

Is there anything obvious I'm missing ?

Edits:

activesheet.range("colName").address gives a value of "$L:$l"

SteveB
  • 1,474
  • 1
  • 13
  • 21
  • colName in "" would mean that its a sheet level range name, it could be a dynamic range, offset(a1,0,0,counta(a:a),1) for example for an expanding range, that may not appear in the CTRL G list of names, check in the Define Names box see if its there. – Nathan_Sav Jan 07 '16 at 14:30
  • It's not listed on Define Names ... – SteveB Jan 07 '16 at 14:36
  • in the immediate pane, try activesheet.range("colName").address does the code open another sheet, or define the range? – Nathan_Sav Jan 07 '16 at 14:45
  • 1
    Is there an `On Error ...` statement above the intersect? Perhaps it never worked properly? What happens when you put a breakpoint on that line? What is the value/address of `Range("colName")` when it arrives there? Stepping into and past that line, is anything processed? You have not provided enough code/data/behavior examples to determine anything. –  Jan 07 '16 at 14:56
  • @Jeeped I appreciate that. If I break on that line and hover over Value I get no value given by the IDE - adding a Watch gives an invalid property error, however, the value is part of a set of parameters passed to another function - breaking in the function and reading the value passed in shows it si reading a value from the sheet. I just cannot understand where colName is defined. – SteveB Jan 07 '16 at 15:37
  • 2
    probably a hidden name: try unhiding it to see – Charles Williams Jan 07 '16 at 16:03
  • @Nathan_Sav range("colName").address gives a value of "$L:$l" – SteveB Jan 07 '16 at 16:10
  • I second what @CharlesWilliams suggested. Try ActiveWorkbook.Names("colName").Visible=True. – Doug Glancy Jan 07 '16 at 16:15
  • @CharlesWilliams thank you for that - I really thought that was it - there is a function to HideNames that I commented out - unfortunately no further ranges have appeared. – SteveB Jan 07 '16 at 16:26
  • 1
    @DougGlancy - ok we're getting somehwere - typing that in the immediate window caused the named range to appear, so I guess I can add another range with the HideRange function enabled and it will be defined then be hidden with the rest. – SteveB Jan 07 '16 at 16:32
  • OK that worked fine - if someone would like to add it as the answer I'll very happily accept it as being correct. Many thanks to all who contributed. – SteveB Jan 07 '16 at 16:48

2 Answers2

3

Its probably a hidden name.As Doug Glancy said, you can unhide it using VBA

Activeworkbook.Names("colName").Visible=True

If you are working with defined names you may find it useful to get My & Jan Karel Pieterse's Name Manager addin which (amongst many other things) handles hidden names. download from
http://www.decisionmodels.com/downloads.htm

Charles Williams
  • 23,121
  • 5
  • 38
  • 38
2

It could be a hidden Name. Try: ActiveWorkbook.Names("colName").Visible=True

Doug Glancy
  • 27,214
  • 6
  • 67
  • 115