How to write equation in VBA

Question

I am new to VBA and I am stuck on how to enter the following equation in to VBA so that Cell A1 will can change the value of X. X=0.5

Here is my code in VBA:

Sub Equation()

Dim x As Double
x = Range("A1").Value
Answer = Sqr(1 + x ^ 2) & (1 + (1) / Sqr(1 + x ^ 2))

MsgBox ("The Answer to the equation is " & Answer)
End Sub

So the problem is the equation? Can you let us know what the correct result would be with some examples? In this example, would the answer be 2.12? — JvdV, Oct 23 '19 at 07:05
Wow, I totally misinterpreted that question. Wrote an answer saying that you can't write the equation in Excel without referencing Word. Still with noting here for those who think like my....https://learn.microsoft.com/en-us/office/vba/api/Word.OMath — ProfoundlyOblivious, Oct 23 '19 at 07:15

score 1 · Answer 1 · answered Oct 23 '19 at 07:08

1

You are missing a few parts of your expression. First, e is not defined, and it is also not part of your expression. Have a look at this:

e = 2.7182818284
Answer = (x * e ^ Sqr(1 + x ^ 2)) * (1 + 1 / Sqr(1 + x ^ 2))

answered Oct 23 '19 at 07:08

Sam

5,424
1
18
33

Thanks for all the help when i do the calculation on the calculator and math way 0.5 * 2.7182818284 √1+0.5^2 * ( 1+ 1\√1+0.5^2 ) the answer i get is 2.878706652. Should the equation in excel be (x * e * Sqr(1 + x ^ 2)) * (1 + 1 / Sqr(1 + x ^ 2)) i replaced (x * e ^Sqr to (x * e * Sqr – Cognex Oct 23 '19 at 09:06

Michał Turczyn · Accepted Answer · 2019-10-23T07:22:11.593

1

You can insert a module into you VBA project:

write below function there:

Function func(x As Double) As Double
    Dim result As Double

    result = x * Exp(Sqr(1 + x * x))
    result = result * (1 + 1 / (Sqr(1 + x * x)))
    func = result
End Function

And then use it in worksheet like: =func(A1)

NOTE: ^ operator doesn't work everywhere

edited Oct 23 '19 at 07:22

answered Oct 23 '19 at 07:16

Michał Turczyn

32,028
14
47
69

score 1 · Answer 3 · answered Oct 23 '19 at 07:19

If you want it to be an actual function do this ( @sam has the equation for you ).

Function Equation(x as Double) as Double

Dim e = As Double 

e = 2.7182818284

Equation = (x * e ^ Sqr(1 + x ^ 2)) * (1 + 1 / Sqr(1 + x ^ 2))

End Function

Now in B1 you could do =Equation(A1)

FunThomas · Answer 4 · 2019-10-23T07:33:37.260

a) To enter your math formula, you need to know the syntax:

To get the square root, use Sqr (you did already)
The power function is done with the ^ (caret) character
There is no build in constant e, you can define it either by defining a constant or use Exp(1)

b) If you want to use your equation available in your Excel, create a UDF (User Defined Function).

Have a look at the following piece of code. I like to split complicated statements into pieces, so I have used 2 intermediate variables.

If your x is in A1, you can for example write the Formula =Equation(A1) into cell B1.

Public Function Equation(x As Double) As Double    
    Dim term1 As Double, term2 As Double, answer As Double
    term1 = Sqr(1 + x ^ 2)
    term2 = 1 + (1 / term1)
    Equation = x * (Exp(1) ^ term1) * term2
End Function

How to write equation in VBA

4 Answers4