running a programme

[paul]

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?

the vba programmes ok its just no running anything or doing anything
to my spreadsheet

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

 

[Guest]

Hi Paul:

in A3, I put 2.1
in B3, I put 3
in C3, I entered:
=myfunction(A3,B3) which displays:
1.5435


In another cell:
=2.1*2.1*2.1/6
which displays the same thing.


Make sure your function is in a standard module. It seems to calculate
correctly.

 

[Guest]

Do you have x and n declared public as equaling A3 and B3? There is nothing
in the function code to tell it where to find the value of X or N. If x and
n are empty when the code is run, then you will get no result.

 

[paul]

oh how would i declare that then?









Do you have x and n declared public as equaling A3 and B3? There is
nothing

 

[Dave Peterson]

Your function worked fine for me.

Are you sure you enabled macros when the workbook was opened? But that would
have returned a #Name? error.

Are you sure you don't have calculation set to manual?

What do you mean when you say "nothing happens"?

And you could use a function like:

=(A3^B3)/FACT(B3)
or with that check:
=IF(B3<0,"N must be an integer greater than zero",(A3^INT(B3)/FACT(INT(B3))))

 

[Rick Rothstein \(MVP - VB\)]

Are you trying to run your function from the spreadsheet? If so, you need to
put your function in a Module, not in a sheet or workbook code window. Click
Insert/Module from the VBA menu bar; then cut your function from the code
window it is now in and paste it into the Module's code window. You should
now be able to put

=MyFunction(A3,B3)

in a cell on the spreadsheet and it should work fine (well, fine within the
limitations of the FACT function's level of accuracy... after FACT(14), the
FACT function returns approximated floating point values).

Rick

 

[paul]

thanks fot that, really useful

which bit is the function i should cut then?

 

[Rick Rothstein \(MVP - VB\)]

I am not sure what you are asking here... can you clarify your question a
little more?

Rick

 

[paul]

when you said i need to cut my function fromt he code window and paste
it intot he module code window

i dont understand what the function code bit i need to copy is?

 

[Rick Rothstein \(MVP - VB\)]

Your "MyFunction" function is what you are trying to make use of on the
spreadsheet, right? That entire function is what you "cut" (by "cut" I mean
Edit/Cut from the VBA menu bar, or use the keyboard short cut Ctrl+X) from
whichever code module it is now in and "paste" (again, Edit/Paste from the
VBA menu bar, or Ctrl+V from the keyboard) it into the Module you added to
your project. In order to use a VBA function the you write in the
spreadsheet, that function must be located in a Module.

Rick