Refer To Named Constant In VBA

Re: Named Constants In Vba

ThisWorkbook.Names("NameToUse").RefersTo

Code tags for VBA are a cardinal rule of the forum; I've added them for you above, please be mindful in the future.

Re: Refer To Named Constant In VBA

Dim s As String
    s = ThisWorkbook.Names("NameToUse").RefersTo
    Debug.Print Mid(s, InStr(s, """") + 1, Len(s) - InStr(s, """") - 1)

... or just strip them out, as you say.

Re: Refer To Named Constant In VBA

I have a related question: can a CONST refer to a cell value or named range?

I realize this violates the idea of a CONST, because the values could change, but could it be done and would there be any value in doing so as opposed to refering to a named range within a sub() procedure?

Re: Refer To Named Constant In VBA

Try hacking around with this:

Sub ShowValueOfNamedConstant()
    'Example how to use named formulas as named constants that can be changed without editing any VBA code
    Dim ValueOfNamedConstant As String
    Dim xNumber As Double
    
    ValueOfNamedConstant = Evaluate(ThisWorkbook.Names("myNamedConstant").RefersToR1C1)
    'But do your own data type checking and conversion if necessary
    xNumber = ValueOfNamedConstant + 1.5 'must be a number to do this
    MsgBox ValueOfNamedConstant
    MsgBox xNumber
    MsgBox ValueOfNamedConstant & xNumber
End Sub

Example values for the RefersTo in the name manager:
="String value"
=100.5