Excel VBA - User Defined Function to turn string into formula

Question

I'm looking for something similar to the solution posted here:

Excel function that evaluates a string as if it were a formula?

Problem is this does not seem to work with a function in there.

This is the function in that thread as submitted by chris neilsen:

Function ev(r As Range) As Variant
    ev = Evaluate(r.Value)
End Function

Here is an example of a string I would like to turn into a formula:

=(INDEX(DataV,1,4)/INDEX(DataV,2,4))*100

DataV is a name within the spreadsheet.

I'm not sure how to do it, maybe I'll figure it out, right now it's looking like I probably won't unless I read a lot more about VBA.

Any help is appreciated.

Answer 1

Just thought I'd share the solution I eventually went with since it does the job. Couldn't make a user defined function that does this, doesn't seem to be possible so I went with a basic macro.

Sub Formulas()
Dim rng As Range, cell As Range, y As Integer, o As Integer
    y = Range("B1").Value
    o = (y - 3) * 2
Set rng = Range(Cells(3, 4), Cells(3, y))

On Error GoTo ErrHandler
For Each cell In rng
    cell.Activate
    cell.Formula = ActiveCell.Offset(0, o).Value
Next cell
Exit Sub

ErrHandler:
    Msg = "Invalid formula for this Indicator, please re-check in sheet IndDef"
    MsgBox Msg, , "Error!", Err.HelpFile, Err.HelpContext

End Sub

This one was written to be used with a button, if you wish for an automatic running macro look around this site for tips on using intersect.

The important part for the problem is question is:

cell.Activate
cell.Formula = ActiveCell.Offset(0, o).Value

Just define the formula of the cell as the value of the one with the string

Range1.Formula = Range2.Value

In my case I wanted to have it run through a bunch of cells, hence the need to activate the equivalent to Range1 and using ActiveCell.Offset to define the Range2 equivalent

Works with any string, functions do need to be written in English though. Hope this helps.