loop of this formula

[kingman29]

Hello
I have a Monster function,
is that possible to make this function inVBA ?
A1 = 15%
B1 = +POWER(A1;1)-POWER(A1;2)-POWER(A1;3)-POWER(A1;4)-POWER(A1;5)
witch mean every time we subtract A1 with the increase of power

So My Vba code is :

Sub Calculate()
Range("B1") = Range("A1") ^ 1 - Range("A1") ^ 2 - Range("A1") ^ 3 - Range("A1") ^ 4 - Range("A1") ^ 5 - Range("A1") ^ 6

End Sub

Is that anyway to make this Code more professional ?
what If I have Power of 56 times ?
Thank you *
note : My windows and Office in French Language

 

[JackDanIce]

Try:

Public Function myFunction(my_input As Double, my_power As Long) As Double

    Dim x As Long
 
    myFunction = my_input
 
    For x = 2 To my_power
        myFunction = myFunction - myFunction ^ x
    Next x

End Function

Other.xlsm
	A	B	C
1	0.15	56	0.125144703
Main

Cell Formulas
Range	Formula
C1	C1	=myfunction(A1,B1)

Note, in either normal Excel formula or VBA, you do not need

=+

as you show for B1, if the next number already is positive.
i.e. no point writing

=+myFunction(A1,B1)

in above, when

=myFunction(A1,B1)

suffices

Positive numbers are implied, negative numbers are explicit

 

[kingman29]

Try:

Public Function myFunction(my_input As Double, my_power As Long) As Double

    Dim x As Long
 
    myFunction = my_input
 
    For x = 2 To my_power
        myFunction = myFunction - myFunction ^ x
    Next x

End Function

Other.xlsm
	A	B	C
1	0.15	56	0.125144703
Main

Cell Formulas
Range	Formula
C1	C1	=myfunction(A1,B1)

Note, in either normal Excel formula or VBA, you do not need

=+

as you show for B1, if the next number already is positive.
i.e. no point writing

=+myFunction(A1,B1)

in above, when

=myFunction(A1,B1)

suffices

Positive numbers are implied, negative numbers are explicit

you understand my desire
but my only question is
when I tried the power of 6 the result is 0.125145252029647
and in this vba code

Range("B1") = Range("A1") ^ 1 - Range("A1") ^ 2 - Range("A1") ^ 3 - Range("A1") ^ 4 - Range("A1") ^ 5 - Range("A1") ^ 6

I get 0.123531421875
why it's not same result ?
Thank you verymuch

 

[johnnyL]

How About:

Sub Calculate()
'
    MaxPower = 6                                                                ' <--- Set this value to the highest power you want to use
    OurNumber = Range("A1")
    Result = Application.WorksheetFunction.Power(OurNumber, 1)
'
    For ThisPower = 2 To MaxPower
        Result = Result - Application.WorksheetFunction.Power(OurNumber, ThisPower)
    Next
'
    Range("B1") = Result
End Sub

 

[kingman29]

How About:

Sub Calculate()
'
    MaxPower = 6                                                                ' <--- Set this value to the highest power you want to use
    OurNumber = Range("A1")
    Result = Application.WorksheetFunction.Power(OurNumber, 1)
'
    For ThisPower = 2 To MaxPower
        Result = Result - Application.WorksheetFunction.Power(OurNumber, ThisPower)
    Next
'
    Range("B1") = Result
End Sub

function is very well
why we have not same result ?
please

 

[johnnyL]

function is very well
why we have not same result ?
please

We? as in you and I? we do have same result.

 

[kingman29]

We? as in you and I? we do have same result.

we, My vba code is not the same you Vba code, for that I said WE
My vba code result is =

0.123531422​

and your Vba code (function code) result is =

0.125145252​

 

[JackDanIce]

Error in my suggestion, try:

Public Function myFunction(my_input As Double, my_power As Long) As Double

    Dim x As Double
   
    myFunction = my_input
   
    For x = 2 To my_power
        myFunction = myFunction - Application.Power(my_input, x)
    Next x

End Function

 

[johnnyL]

@kingman29 I think your confused. @JackDanIce offered a function code, not me.

My suggestion was a regular subroutine like in your original post.

 

[kingman29]

@kingman29 I think your confused. @JackDanIce offered a function code, not me.

My suggestion was a regular subroutine like in your original post.

Thank both of you
the function and a regular subroutine as working verywell