我可以评估字符串格式的 Excel VB 常量吗? [英] Can I Evaluate An Excel VB Constant That Is In String Format?

问题描述

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

解决方案

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

这篇关于我可以评估字符串格式的 Excel VB 常量吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！