Using user function (vba) with sqrt(array)-only first answer

A

Awill

Hello,

I have written an Excel spreadsheet that has a list of formulae. The
formulae perform array calculations on a 10 by 12 matrix. A formula is chosen
by the user. The spreadsheet calculates the formula using a vba function like

Function EVAL2(R)
EVAL2 = Application.Evaluate(R.Text)
End Function

I have a formula of the form: sqrt(x) where x is an array. I am expecting
the result to be the sqrt root of each number in x, but the result array is
full of the square of the first number in x. The above vba function works
fine for other formulae. I am confused why the sqrt root function would cause
problems. If I manually put the formula into a range of cells and press
ctrl-shift enter, the sqrt root formula works fine. It seems to fail with the
..evaluate function in vba.

Does anyone have any suggestions to get my evaluate vba function to properly
calculate the sqrt of an array (with the result being the square root of each
number in the array) or a workaround.

Thanks for the help everyone.
 
T

Tom Ogilvy

Range("F19").Resize(3,2).Value = Evaluate("(B4:C6)^.5")

worked for me. Does that give you some ideas.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top