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

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.

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

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

worked for me. Does that give you some ideas.

--
Regards,
Tom Ogilvy


"Awill" wrote:

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.