CODEWITHSUNDEEP
excelvba
Vieri_Wijaya
Vieri_Wijaya

Reputation: 101

How to use a function written on a cell as function syntax (VBA)

So i have asked a similar question several days ago but i got no satisfying answer.

I want to ask about using a function that is written on a cell that is user defined, for example: 

log(x)+2x+exp(x)

that is written on cell A1.

So how to use this string as a function syntax in VBA editor?

Upvotes: 0

Views: 117

Answers (1)

A.S.H
A.S.H

Reputation: 29332

You can replace x with some value then use Application.Evaluate

Function EvaluateEquation(equation As String, variable As String, value As Double)
    EvaluateEquation = Application.Evaluate _
       (Replace(equation, variable, value))
End Function

Test and usage

Let's say you have entered the equation x^2+2 in sheet1, Cell A1. You can evaluate it for x=2 this way:

Sub TestIt()
    Dim result As Double
    result = EvaluateEquation(Sheet1.Range("A1").Text, "x", 2)
    Debug.Print result
End Sub

Output: 6

p.s.: since x is a letter that can appear inside the equation at any place (not only at the formal parameter), we should use a different (rare) combination for the formal parameter, such as xxx..

Upvotes: 1

Related Questions