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

 

[johnnyL]

@Eric W, I can only digest a little bit at a time. Now I am throwing up, thank you so much! LOL

 

[JackDanIce]

@JackDanIce, I owe you an apology since I was terribly wrong

I should have looked into this further but didn't. The moment I would post my contribution I saw there were already enough possible solutions, so I didn't post it. When I read the OP had discovered a difference in the end result, while the end result of my solution (also a UDF) matched his, I noticed the difference in argument declaration. My premature conclusion was that this had to be the cause (indeed on account of type conversion), but nothing could be further from the truth!!

If I put your post #2 code and my code together (and use the same variable names for better comparison) I think I don't need to explain anything further. Cheers.

Public Function MyFunction(my_input As Double, my_power As Long) As Double
   
    ' JackDanIce
    Dim x As Long
    MyFunction = my_input
    For x = 2 To my_power
        MyFunction = MyFunction - MyFunction ^ x
    Next x
End Function


Public Function MyFunction(my_input As Range, my_power As Range) As Double
   
    ' GWteB
    Dim x As Long
    MyFunction = my_input.Value
    For x = 2 To my_power.Value
        MyFunction = MyFunction - my_input.Value ^ x
    Next x
End Function

Yeah I noticed that too and made change afterwards lol The construct/intention of both was same, sloppy mistake on my part