vba coding

P

paul

im using the following code

Sub test()
Dim x As Single, n As Integer, i As Integer, fact As Integer
n = Range("b3")
x = Range("a3")
'calculate n factorial
'checking B3
If Range("B3") <= 0 Or Int(Range("B3")) < Range("B3") Then
MsgBox " integer in b3 must be greater then zero"
Exit Sub
End If
fact = 1
For i = 1 To n
fact = fact * 1
Next i
'calculate result x ^ n/fact n!
result = x ^ n / fact
Range("c3") = result

where n =5 and x = 2 but is running into cell c3 32, which is 2 to the
power 5 factorial

but i want to run 2 to the power 5 divided by n factorial, does anyone
no anything i can put into this vba code to make it return this.

also i want to add some error checking, does anyone know how to add
that to the vba code

thanks
 
J

Joel

Use the worksheetfunction from VBA. SeeCode below.

Sub test()
Dim x As Single, n As Integer, i As Integer, fact As Integer
n = Range("b3")
x = Range("a3")
'calculate n factorial
'checking B3
If Range("B3") <= 0 Or Int(Range("B3")) < Range("B3") Then
MsgBox " integer in b3 must be greater then zero"
Exit Sub
End If

'calculate result x ^ n/fact n!
result = x ^ n / WorksheetFunction.fact(n)
Range("c3") = result

End Sub
 
M

Mike H

Paul
Sub seenthisbefore()
Dim x As Single, n As Integer, i As Integer, fact As Integer
n = Range("b3")
x = Range("a3")
'calculate n factorial
'checking B3
If Range("B3") <= 0 Or Int(Range("B3")) < Range("B3") Then
MsgBox " integer in b3 must be greater then zero"
Exit Sub
End If
factorial = Application.WorksheetFunction.fact(n)
'calculate result x ^ n/fact n!
result = x ^ n / factorial
Range("c3") = result

End Sub

Mike
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

vba 1
vba 2
programme run help 2
coding and helping to launch vba 1
running varibiables into vba 1
running a programme 9
programme fucntioning problems errors 2
PROGRAMME HELP 2

Top