named ranges question

[beckyinDC]

I have a named range that has a value of 4.

It is used for some looping in a procedure that works in the background, so really doesnt need to be seen by the end user/viewer of the spreadsheet. I wanted to hide the columns where I had stored a few of these values just for aesthetics.

When I do the VBA function that uses it no longer gets a value for that named range. I can toggle whether it works or not by hidiing or unhiding and rerunning the procedure- if hidden, the code stops when there is no value to be used and that causes a problem. when not hidden it works and I can walk through the code and see it working.

the value of 4 was a calculation based upon another named range, but for grins I switched it to a simple cell value of 4. same behavior occurs for the procedure.

I can think of other ways to make the range less notable- change font color to background color or use scroll area- or unhide/process/and then hide the worker-bee cells...

- but is there anywhere that explains limitations of referencing a named range like that? I am researching but finding more info regarding how the syntax would work...and was my diagnosis right that its a matter of the named range being hidden that is a problem- sure seems like it, but I also wonder that I havent hit this before.

 

[DauntingGecko]

I don't fully understand your problem, but I'm able to create a named range called "myNamedRange" and sum it up in VBA regardless of if it's hidden or not.

Private Sub example()

MsgBox WorksheetFunction.Sum(Range(ActiveSheet.Name & "!" & [myNamedRange].Address))

End Sub

Should give you some clues...

 

[RoryA]

If you are just using it for calculation, why put it on a sheet at all? Just use the name to store either the formula required, or the literal value you want. For example you can define a name as referring to =4

 

[pgc01]

I have a named range that has a value of 4.

It is used for some looping in a procedure that works in the background, so really doesnt need to be seen by the end user/viewer of the spreadsheet....

I don't understand exactly how you are using these values and so I don't know if this will be adequate, but if the user does not need to know about them, you can explore the possibility of simply defining them as constants.

 

[beckyinDC]

I am hearing what you guys are saying...and realizing I didnt explain why it was on the spreadsheet in the first place- here is how it got there

the calculation I want is for "Quarter"...so I had that as named range:

=IF(OfficialDSRReportDate>=TestingStartDate, (IF(OfficialDSRReportDate>Milestone_1,(IF(OfficialDSRReportDate>Milestone_2,(IF(OfficialDSRReportDate>Milestone_3,(IF(OfficialDSRReportDate>Milestone_4,5,4)),3)),2)),1)),0)

...in that there are references to named ranges-
-TestingStartDate- references cell data entered by user

-Milestone_1 through Milestone_4...each calculated based upon Testing Start and End dates and potential override for each milestone by user...and it uses a user-defined function for calculating time from start to end in business days

-OfficialDSRReportDate- this gets value set by the VBA upon kickoff of processing, and is based upon today function and optional override . here is that code-

   officialDate = CDate(ActiveWorkbook.Names("DSRReportDate").RefersToRange.Value)
    ActiveWorkbook.Names("OfficialDSRReportDate").RefersTo = "= " & officialDate

...then working with that named range in VBA, I found I couldnt get the value although I could see it listed in Names collection and see it had values to grab. taht is how I got to my admittedly kludgey workaround-

-put calculation in cell: =Quarter
-create named range for that location called QuarterTemp and use that instead.

(and I see I have done similar bandaids in 10 other places)

but that worked until hiding the cell referenced by quartertemp became a problem for me

any takers on giving me advice where I went astray?

 

[beckyinDC]

this may help- here is the line of code that was not getting the value of Quarter until I switched to using QuarterTemp


q = Application.Range("QuarterTemp").Text

q gets used for some looping after that.

...I used .text value because that seemed to be necessary.

and I am using excel 2007

 

[RoryA]

Your Name is not actually a range, it's a value. Try using:

q = [QuarterTemp]

 

[beckyinDC]

Re: named ranges question-hallelujah!

THANK YOU SOOO MUCH! That worked and not only that, but now I can get rid of my extraneous named ranges...yahoo!

q = [Quarter]

Have a great weekend!