Value fo Variables and MSScriptControl

[Jameo]

Good morning all,

At the moment I have a form with lots of textboxes, named in a coherent fashion. These relate both to a string variable containing the cell references on a sheet and a double variable conatining the values.

For instance.

the textbox me.txtSHCV relates to varSHCV and varSHCVt.

I have a caluculation that determines the name of the other variables based on the name of the text box.

i.e.

OptName = OptionBtn.Name
NName = Mid(OptName, 4, Len(OptName) - 2)
OptName = CStr("var" & NName & "")
OptName2 = CStr("var" & NName & "t")

Where OptName would be varSHCV and OptName2 would be varSHCVt.

OK, so why I hear you say. Well I am using MSScript control to change the values of these variables when ever a text box is updated.

So:

Dim Script_Control As MSScriptControl.ScriptControl, Script_Control2 As MSScriptControl.ScriptControl
 
Set Script_Control = New MSScriptControl.ScriptControl
Set Script_Control2 = New MSScriptControl.ScriptControl
 
With Script_Control
    .Language = "VBScript"
    .AddObject "WsVB", Workbooks(1).Sheets("VBA Data")
    .AddCode _
        "Sub ChangeVals" & vbCrLf & "" & varSHCVt & " = " & CoVal & "" & vbCrLf & "End Sub"
    .Run ("ChangeVals")
End With

The above (sucessfully) changes the value of the reference cell in the reference sheet. Alternitively, I have a similar peice of code changes the value of the highlighted variable (varSHCV).

My issue is that I am struggling to pass the caluclated name of the variable into the Script.

My question is, is there anyway you can think of doing this?

For instance, OptName = varSHCVt, varSHCVt = Cells(1,1).Value.

I would like a function that would calculate the above so that NewVar = Cells(1,1).Value.

Obviously there are other ways I could do this, but in an effort to learn about MSScriptControl (and to avoid making my work over the last day meaningless) I would liek to continue with this methodaology if possible.

Many thanks in advance

 

[Jameo]

I've tried to get around this issue by using a function:

With Script_Control2
.Language = "VBScript"
.AddCode "Function DetermineVals()" & vbCrLf & "DetermineVals = " & OptName2 & " " & vbCrLf & "End Function"
End With
rslt = Script_Control2.Eval("DetermineVals")

When you debug.print this statement, you get

Function DetermineVals()
DetermineVals =  varSHCVt
End Function

However, the function returbs rslt as empty. Any ideas?