2

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.

  • In case you haven't figured this much out already: it looks like Evaluate() can handle only VBA expressions, and not Excel worksheet functions like INDEX(). If you're expecting a specific pattern (e.g., INDEX(something)/INDEX(something)), you could write code to parse it and emulate the worksheet functions with VBA functions. But I have no idea how to do a general solution. – G-Man Says 'Reinstate Monica' Aug 29 '14 at 23:23
  • Cheers. Guess that explains why I couldn't find any examples. I got the spreadsheet working by converting the formulas into just cell references, but I might just give up on this function and go with one that copy pastes as values and then activates the cells. – LonelyKnight Aug 30 '14 at 15:42
  • Oh yay.. that can't be done either. Somehow the act of opening and closing the cell is read as writing a completely new formula. Original contents be damned. – LonelyKnight Aug 30 '14 at 19:24
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.