locked
32 TO 64-BIT VBA EXCEL ADD-IN MIGRATION : VB SCRIPT REPLACEMEMT FOR PROPERTY VALUE EVALUATION RRS feed

  • Question

  • Need to replace 32-bit code which essentially uses reflection to get a property value using msscript.ocx and VBScript (not avail in 64-bit):

    ----

    Dim scriptCtrl As New ScriptControl

    scriptCtrl.Language = "VBScript"

    scriptCtrl.addObject "anyObjRefName", anyObj, True

    cell.Value = CStr(scriptCtrl.Eval("anyObjRefName.anyPropertyName"))

    ----

    How can I perform this very simple operation without the magic of VBScript and the DLL that allows evaluation of such?

    Many thanks,

    Joe



    Wednesday, April 9, 2014 8:48 PM

Answers

  • If you're using VBA and and if your object is available to VBA (probably) you can use VBA's CallByName, eg this in Excel

    ' normal module
    Sub test()
    ' with a ref to "Microsoft Script Control 1.0"
    Dim scriptCtrl As New ScriptControl
    Dim C As New Class1
    C.pName = "hello"
    
    scriptCtrl.Language = "VBScript"
    scriptCtrl.AddObject "Class1", C, True
    
    Debug.Print CStr(scriptCtrl.Eval("pName"))
    Debug.Print CallByName(C, "pName", VbGet)
    
    Sheet1.Name = "mySheet1Name"
    scriptCtrl.AddObject "Sheet1", Sheet1, True
    Debug.Print CStr(scriptCtrl.Eval("Name"))
    Debug.Print CallByName(Sheet1, "Name", VbGet)
    End Sub
    
    ' Class1
    Private msName As String
    Public Property Let pName(s As String)
    msName = s
    End Property
    Public Property Get pName() As String
    pName = msName
    End Property

    Wednesday, April 9, 2014 10:39 PM

All replies

  • If you're using VBA and and if your object is available to VBA (probably) you can use VBA's CallByName, eg this in Excel

    ' normal module
    Sub test()
    ' with a ref to "Microsoft Script Control 1.0"
    Dim scriptCtrl As New ScriptControl
    Dim C As New Class1
    C.pName = "hello"
    
    scriptCtrl.Language = "VBScript"
    scriptCtrl.AddObject "Class1", C, True
    
    Debug.Print CStr(scriptCtrl.Eval("pName"))
    Debug.Print CallByName(C, "pName", VbGet)
    
    Sheet1.Name = "mySheet1Name"
    scriptCtrl.AddObject "Sheet1", Sheet1, True
    Debug.Print CStr(scriptCtrl.Eval("Name"))
    Debug.Print CallByName(Sheet1, "Name", VbGet)
    End Sub
    
    ' Class1
    Private msName As String
    Public Property Let pName(s As String)
    msName = s
    End Property
    Public Property Get pName() As String
    pName = msName
    End Property

    Wednesday, April 9, 2014 10:39 PM
  • Perfect, many thanks!
    Thursday, April 10, 2014 1:20 PM