3

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
Community
  • 1
  • 1
mikebinz
  • 370
  • 1
  • 4
  • 17

3 Answers3

2

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 :)

mkingston
  • 2,678
  • 16
  • 26
  • On my PC trying to run this code causes a RunTime Error '1004' = Programmatic access to VB Project is not trusted W – mikebinz Jan 24 '13 at 06:24
  • Thanks for your reply On my PC trying to run this code causes a RunTime Error '1004' = Programmatic access to VB Project is not trusted Security strikes again This approach seems too much like cracking a walnut with a sledgehammer for me, so please don't waste any time on pursuing it. Still very interested if anyone can supply a simpler solution Thanks again – mikebinz Jan 24 '13 at 06:38
  • I think this is the **ONLY** solution, if you are not doing a table lookup – Larry Jan 24 '13 at 10:58
  • @mikebinz unfortunately, like a sledgehammer, vba is a rather blunt tool. The only other methods I can think of are considerably more complex than this. I think you'll just have to accept this is a derivative of basic, not lisp. Good luck. – mkingston Jan 24 '13 at 12:52
  • 2
    @mikebinz: the runtime error about programmatic access to VB Project is normal if your macro security settings forbid accessing the VBProject object (the default setting). You can run code that injects code into other workbooks' VBA code with this security barrier down. It's a SERIOUS think-about-it. OTOH if you want to play with the VBA object model (bit like System.Reflection), then hey, go ahead. Just bear in mind this open door when opening an email-attached macro-enabled workbook and okaying the warnings. And if you're the one writing the macro virus then... careful with F5! – Mathieu Guindon Jan 25 '13 at 02:39
0

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")
kb_sou
  • 1,059
  • 1
  • 16
  • 26
-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.

Eric O'Brien
  • 9
  • 1
  • I am afraid I have to download this one, we can even just msgbox xlValues... The point is From String --> enumed values. – Larry Jan 28 '13 at 03:55