Can I Evaluate An Excel VB Constant That Is In Str

Question

Is it possible to Evaluate a String which contains a valid Excel VB Constant's Name to return that Constant's Value?

eg

    Dim ConstantName as String
    Dim ConstantValue as Long

    ConstantName="xlValues"

    ConstantValue= UnknownFunction(ConstantName)

    'would set ConstantValue=-4163

Answer 1

Is using the string value necessary?

Dim anyConstant as Long
anyConstant = xlValues

msgbox anyConstant

Set anyConstant to any xl constant you please, they are all enumerated Long values.

The first solution offered is indeed much more fun however.

Answer 2

Instead of using constants, you could use a dictionary

Dim dict As Object

Sub InitialiseDict()
    Set dict = CreateObject(Scripting.Dictionary)
    dict("xlValues") = -4163
    dict("const1") = value1
    ... 
    dict("constN") = valueN
End Sub

ConstValue = dict("xlValues")

Answer 3

Fun!

Option Explicit

Function getConstantValue(constStr As String) As Variant

    Dim oMod As VBIDE.CodeModule
    Dim i As Long, _
        num As Long

    Set oMod = ThisWorkbook.VBProject.VBComponents("Module1").CodeModule

    For i = 1 To oMod.CountOfLines
        If oMod.Lines(i, 1) = "Function tempGetConstValue() As Variant" Then
            num = i + 1
            Exit For
        End If
    Next i

    oMod.InsertLines num, "tempGetConstValue = " & constStr

    getConstantValue = Application.Run("tempGetConstValue")

    oMod.DeleteLines num

End Function

Function tempGetConstValue() As Variant
End Function

All code must be in a module called Module1. That can be changed pretty simply by changing the text "Module1" in the routine.

You'll need to add a reference to Microsoft Visual Basic for Applications Extensibility x.x

There are a number of ways this could fail. Let me know if you have any problems with it :)