Square the values of individual cells in a range

In the range A1:E1 are the values 1,2,3,4,5

and in the range A3:E3 the array formula {=A1:E1^2} witch gives me the values 1,4,9,16,25

Is there a way in vba to do the same in a single statement.....?

 Try

Range("A3:E3").FormulaArray = "=R[-2]C:R[-2]C[4]^2"

Why are you using an array formula, why not just

Range("A3:E3").Formular1c1 = "=R[-2]C^2"

 thnx Roger

But I want to use it like this

Function TestFunction(counts As Range, values As Range)

       With WorksheetFunction

             dTemp = .SUM(.MMULT(values^2,.TRANSPOSE(counts)))

       End With

End Function

Function TestFunction(counts As Range, values As Range)

    With Application.Caller.Parent

        TestFunction = .Evaluate("MMult(" & values.Address & _ "^2,Transpose(" & counts.Address & "))")

    End With

End Function

 Thnx Bob,

But why do you use Application.Caller.Parent....?

In my code

Evaluate("SUM(MMULT(" & Values.Address & "^2,TRANSPOSE(" & Counts.Address & ")))")

will work fine

Jelle-Jeroen

It will if the active sheet is a worksheet when you run the code, but if it is a chart sheet ...

Using APplication.Caller.Parent ensures that the Evaluate works against the worksheet that the function is called from.

BTW, you do not need the SUM, as per your example. MMULT returns an array equal to the dimensions of your input arrays, which were only 1 row 1 column in your example.

Bob,

True but I simplyfied the example.

I will post the complete problem soon.

Jelle-Jeroen

I thought that was the case, but I decided to mention it anyway.

 Bob,

I posted the complete thing at http://xlns.lamkamp.nl/

I want to post it also on the Excel User Group...

Is that a good plan or is it not done....?

Jelle-Jeroen

Can't see a problem, but as you have the link, probably not necessary.

As an aside, the 'With WorksheetFunction' is superfluous in the 2nd solution (removing it might make it as fast

Bob,

Evaluate("SUM(MMULT(" & Values.Address & "^2,TRANSPOSE(" & Counts.Address & ")))")

doesn't seem to work if the data is on another sheet as the formula...

do you know a workaround for that...

Jelle-Jeroen 

Sure 

Function TestFunction(counts As Range, values As Range)

    With Application.Caller.Parent

        TestFunction = .Evaluate("MMult(" & values.Address(, , , True) & _
                                 "^2,Transpose(" & counts.Address(, , , True) & "))")
       
    End With

End Function