Convert imported text to a usable function in VBA

Hey guys,

I would like to be able to import a function as text (i.e. "x+2") and then convert that to a command in VBA so that I can use it as part of a calculation:

if A1 contains "x+2" I would have the code be

x=3
.
.
.
y=range("A1")
.
.
.

and have y be equal to 5. I dont know if this is even possible, but I would appreciate any input.

Hi

Bit contrived, but

Sub aaa()
  x = 3
  Application.Names.Add Name:="x", RefersTo:=x
  y = Evaluate("=" & Range("A1"))
  MsgBox y
  ActiveWorkbook.Names("x").Delete
  
End Sub

rylo

That worked, I think I can work with that for the time being. The only issue (minor) is that the cell with aaa doesnt automatically update when a new function is typed in cell A1. I think that is an issue with vba reiterating at the function at a certain time though. Its not a big deal really, thanks for your help!

Hi

Put

application.volatile

into the function. This means that it will recalculate every time there is a change in the workbook. ALternatively, put in a second variable that will take the data from A1. If this cell is in the function, then it will update every time there is a change in that cell.

rylo

the other problem you are going to run into as written is that the "range("a1") object is unqualified, so it will always reference the active sheet. So if you have two tabs in a sheet or have multiple workbooks open, it will pull the wrong information when those other sheets are active.

Better than an unqualified object and application.volatile is to pass the necessary information to the function through the argument list:

Function aa(varx As Variant,express as string) As Double 'varx is input as 3
  x = varx
  y = Evaluate(WorksheetFunction.Substitute(express, "x", x)) 'should return 5
  aa = y
End Function

I did both those things and now it works perfectly. Thanks again for all your help!