How to get evaluated value of worksheet names that do not refer to worksheet ranges?

D

Dick Watson

I have workbook named "ranges" that refer to things that aren't ranges of
cells.

E.g., MinWidthToSet refers to "=8" and RowsToTest refers to
"=COUNTA(MyRange)" (and these are simplified examples for discussion).

The problem comes in when trying to get a value for these from VBA since
they can't be found as default values of Range("MinWidthToSet") or
Range("RowsToTest").

I tried using Names("MinWidthToSet") which returns the string "=8"--I can
strip the "=" and CLng the remainder. Ugly but functional.

Bet there's no good way to do anything with the string "=COUNTA(MyRange)".

I've also tried Application.Evaluate "=RowsToTest" but this gets into all
kinds of ugly issues of the context in which the evaluation is done since
these are all scoped to specific sheets and multiple workbooks may be open
when this needs to be evaluated.

Is there a workaround to get the evaluated value of these names that aren't
cell ranges?

How does one define the complete context for an Application.Evaluate when
running code that belongs to a specific worksheet?

Thanks in advance!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Embedding excel formula in MS Query that will be evaluated in excelnot in the SQL server 0
Worksheet level names 5
VBA CreateNames (ignores last 5 rows?) 6
Copy worksheet with named ranges to new workbook and keep names in 1
Use Names of Named Ranges in ComboBox 2
Macro question: Using named ranges 6
trying to resolve range names to variable that vba can act on 6
How to modigy the reference in a named range? 2

Top