CODEWITHSUNDEEP
excelvba
Cognex
Cognex

Reputation: 43

How to write equation in VBA

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
Equation

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

Upvotes: 4

Views: 3296

Answers (4)

FunThomas
FunThomas

Reputation: 29171

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

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37347

You can insert a module into you VBA project:

enter image description here

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

Upvotes: 1

Kevin
Kevin

Reputation: 2631

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)

Upvotes: 1

Sam
Sam

Reputation: 5721

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))

Upvotes: 1

Related Questions