run programme error occuring

[harry buggy]

number,x, from cell A3 and a positive integer, n, from cell B3 then
returns into cell C3 the value of x raised to the power n divided by
n factorial.

where x in cell a3 = 2 and n in cell b3 = 5


im using this formula, but when i run it nothing happens, how do i
make this vba programme run and return the value?


this is the vba programme:


Public Function MyFunction(X As Double, N As Long) As Variant
If (N <= 0&) _
Then
MyFunction = "N must be an integer greater than zero"
Else
MyFunction = (X ^ N) / WorksheetFunction.Fact(N)
End If
End Function


or i can use this one, which one do you thinks better?


Sub test()
'checking B3:
If Range("B3") < 1 Or Int(Range("B3")) <> Range("B3") Then
MsgBox "B3 should be an integer greater than 0"
Exit Sub
End If
Range("C3") = Range("A3") ^ Range("B3")
Application.WorksheetFunction.Fact (Range("B3"))
End Sub


how do i delcare x and n variables into working vba code so that
the vba can run, im not sure how to decalre them, how you do it. at
the minute nothing happens the vba doesnt code it. do i need to put
it
into a macro.


the cell a3 and b3 are on a normal sheet 1 on excel and i need to use
vba to calculate into cell C3 the value of x raised to the power n
divided by n factorial.


I just want to no how to make the code run, im really struggling
please please please help!!!!!


if any one can help can u explain in great detail so i can get it
right thank you

 

[OssieMac]

Hi Harry,

Because you have the VBA function you use it as you would any other excel
function.

In cell C3 enter =MyFunction(A3,B3)

The two parameters are passed to the VBA function because that is how they
are in shown within the brackets in the function name.

Public Function MyFunction(X As Double, N As Long) As Variant

A3 is passed to X and B3 is passed to N

Regards,

OssieMac

 

[OssieMac]

Hi again Harry,

If you want to use the function within your VBA code then the following is
some example code assuming that range A3 and B3 are on the active sheet
otherwise you would need to qualify the sheet name:-

Sub Test_Funct_Code()

MsgBox MyFunction(Range("A3"), Range("B3"))

It is basically the same as entering the function directly on the worksheet
as per my previous post

End Sub

Regards,

OssieMac