I would like to be able to evaluate the value of the named constant
sSPEC_NAME in the active workbook by using application.evaluate, as
follows:

Dim vTest as Variant

vTest = Application.Evaluate("'" & Application.ActiveWorkbook.Name &
"'!" & sSPEC_NAME)

This works fine, except where ActiveWorkbook.Name contains a single
quote, which causes the string being evaluated to become nonsense. Can
anyone suggest an alternative approach?

The most obvious to me would be

vTest =
Application.Evaluate(Application.ActiveWorkbook.Na mes(sSPEC_NAME).RefersTo)

but the issue then is that this will raise a runtime error where there
is no constant named sSPEC_NAME in the active workbook. Ideally I
would be to escape the single quote somehow in the first approach - is
there a way to do this?

TIA

This worked ok for me:

vTest = Application.Evaluate("'" _
& Replace(Application.ActiveWorkbook.Name, "'", "''") _
& "'!" & sSPEC_Name)



geoff_ness wrote:

I would like to be able to evaluate the value of the named constant
sSPEC_NAME in the active workbook by using application.evaluate, as
follows:

Dim vTest as Variant

vTest = Application.Evaluate("'" & Application.ActiveWorkbook.Name &
"'!" & sSPEC_NAME)

This works fine, except where ActiveWorkbook.Name contains a single
quote, which causes the string being evaluated to become nonsense. Can
anyone suggest an alternative approach?

The most obvious to me would be

vTest =
Application.Evaluate(Application.ActiveWorkbook.Na mes(sSPEC_NAME).RefersTo)

but the issue then is that this will raise a runtime error where there
is no constant named sSPEC_NAME in the active workbook. Ideally I
would be to escape the single quote somehow in the first approach - is
there a way to do this?

TIA

--

Dave Peterson

It's hard to understand what you have, what you want to do, and why.

Eg, is sSPEC_NAME something like this

Public sSPEC_NAME as String = "some_name"

if so, why not ensure the constant is included in the project before writing
the code

Why are you trying to evaluate the Name, do you expect it to refer to a
range, if so why not simply

set r = Range(sSPEC_NAME) ' etc

Regards,
Peter T


"geoff_ness" wrote in message
...
I would like to be able to evaluate the value of the named constant
sSPEC_NAME in the active workbook by using application.evaluate, as
follows:

Dim vTest as Variant

vTest = Application.Evaluate("'" & Application.ActiveWorkbook.Name &
"'!" & sSPEC_NAME)

This works fine, except where ActiveWorkbook.Name contains a single
quote, which causes the string being evaluated to become nonsense. Can
anyone suggest an alternative approach?

The most obvious to me would be

vTest =
Application.Evaluate(Application.ActiveWorkbook.Na mes(sSPEC_NAME).RefersTo)

but the issue then is that this will raise a runtime error where there
is no constant named sSPEC_NAME in the active workbook. Ideally I
would be to escape the single quote somehow in the first approach - is
there a way to do this?

TIA

Dave, you must be psychic <g
I didn't follow at all !

Regards,
Peter T

"Dave Peterson" wrote in message
...
This worked ok for me:

vTest = Application.Evaluate("'" _
& Replace(Application.ActiveWorkbook.Name, "'", "''") _
& "'!" & sSPEC_Name)



geoff_ness wrote:

I would like to be able to evaluate the value of the named constant
sSPEC_NAME in the active workbook by using application.evaluate, as
follows:

Dim vTest as Variant

vTest = Application.Evaluate("'" & Application.ActiveWorkbook.Name &
"'!" & sSPEC_NAME)

This works fine, except where ActiveWorkbook.Name contains a single
quote, which causes the string being evaluated to become nonsense. Can
anyone suggest an alternative approach?

The most obvious to me would be

vTest =
Application.Evaluate(Application.ActiveWorkbook.Na mes(sSPEC_NAME).RefersTo)

but the issue then is that this will raise a runtime error where there
is no constant named sSPEC_NAME in the active workbook. Ideally I
would be to escape the single quote somehow in the first approach - is
there a way to do this?

TIA

--

Dave Peterson

Thanks Dave, works fine for me also :)


On Oct 30, 12:16*pm, Dave Peterson wrote:
This worked ok for me:

vTest = Application.Evaluate("'" _
* * * * * * & Replace(Application.ActiveWorkbook.Name, "'", "''") _
* * * * * * & "'!" & sSPEC_Name)



geoff_ness wrote:

I would like to be able to evaluate the value of the named constant
sSPEC_NAME in the active workbook by using application.evaluate, as
follows:

Dim vTest as Variant

vTest = Application.Evaluate("'" & Application.ActiveWorkbook.Name &
"'!" & sSPEC_NAME)

This works fine, except where ActiveWorkbook.Name contains a single
quote, which causes the string being evaluated to become nonsense. Can
anyone suggest an alternative approach?

The most obvious to me would be

vTest =
Application.Evaluate(Application.ActiveWorkbook.Na mes(sSPEC_NAME).RefersTo)

but the issue then is that this will raise a runtime error where there
is no constant named sSPEC_NAME in the active workbook. Ideally I
would be to escape the single quote somehow in the first approach - is
there a way to do this?

TIA

--

Dave Peterson

Thanks Peter, sorry I didn't make myself clearer - I was under some
time pressure to get it sorted. The code constant sSPEC_NAME is one of
a number of such constants holding a string value, which are workbook-
level names(in the case of sSPEC_NAME, string constants rather than
ranges) in a group of workbooks related to a single application. The
vTest variable is used to let an event-handler procedure know which
part of the application the active workbook belongs to and which
procedures to call, and it can hold a string, boolean or range
depending on which of the code constants is being evaluated. You're
quite right though, if it was always a range your suggestion would
work.

On Oct 30, 12:24*pm, "Peter T" <peter_t@discussions wrote:
It's hard to understand what you have, what you want to do, and why.

Eg, is sSPEC_NAME something like this

Public sSPEC_NAME as String = "some_name"

if so, why not ensure the constant is included in the project before writing
the code

Why are you trying to evaluate the Name, do you expect it to refer to a
range, if so why not simply

set r = Range(sSPEC_NAME) ' etc

Regards,
Peter T

"geoff_ness" wrote in message

...

I would like to be able to evaluate the value of the named constant
sSPEC_NAME in the active workbook by using application.evaluate, as
follows:

Dim vTest as Variant

vTest = Application.Evaluate("'" & Application.ActiveWorkbook.Name &
"'!" & sSPEC_NAME)

This works fine, except where ActiveWorkbook.Name contains a single
quote, which causes the string being evaluated to become nonsense. Can
anyone suggest an alternative approach?

The most obvious to me would be

vTest =
Application.Evaluate(Application.ActiveWorkbook.Na mes(sSPEC_NAME).RefersTo)

but the issue then is that this will raise a runtime error where there
is no constant named sSPEC_NAME in the active workbook. Ideally I
would be to escape the single quote somehow in the first approach - is
there a way to do this?

TIA