Calculate power

[Pawel]

Hi guys ,
I'm trying to write a macro that will calculate sum of powers(Y) of values from 1 to X
for example :
for X = 5 and Y=2 the value should be 1^2 + 2^2+3^2+4^2+5^2

any idea how to do that?

[GarysStudent]

Perhaps the UDF:

Public Function SumPowers(X As Long, Y As Long) As Long
 
 Dim i As Long
 
 SumPowers = 0
 For i = 1 To X
  SumPowers = SumPowers + i ^ Y
 Next i
End Function

[SamT]

Just an FYI.
SumPowers = 0 is redundant in that code.

[Pawel]

This code would be great but I need this to be a simple macro to display the result in MsgBox

[SamT]

This code would be great but I need this to be a simple macro to display the result in MsgBox

Shirley, you can figure that out.

BTW, this sounds like a homework assignment.

[Paul_Hossler]

This code would be great but I need this to be a simple macro to display the result in MsgBox

MsgBox SumPowers (5, 2)

[SamT]

Heh heh.

[snb]

Sub M_snb()
   MsgBox [sumsq(1,2,3,4,5)]
' or
   MsgBox [sum({1,2,3,4,5}^2)]
End Sub

[SamT]

Slick. I like the second line.

Now, with variables, please.

[snb]

I waited for you to ask for it:

Sub M_snb()
    x = 8
    y = 3
    
    MsgBox Evaluate("sum(row(1:" & x & ")^" & y & ")")
End Sub

[SamT]

Yowzer!

[Pawel]

I know it sounded funny-simple to you guys but I'm jus a newbie trying to learn something
Thanks for your help

[SamT]

You're welcome.

Yeah, sometimes our humour bleeds over. Sorry 'bout that.

[snb]

Your question learned me that Excel contains the formula sumsq().

Thank you