I am trying to make VBA calculate the values of a f(x) function at different x values and then display the values in excel.I have made the following program to do just that for f(x) = 2x:For x = 1 To 10 y = 2 * x Range("A1").Offset(x, 0) = y y = 0Next xHere's the problem: I want to be able to, by using an inputbox or similar, different functions into the program without having to mess with the code each time. It seems like a simple problem, but i haven't been able to make it work. My first thought was to add a variable z that would be holding the function. This did not work, but you can see the idea below:z = 2*xFor x = 1 To 10 y = z Range("A1").Offset(x, 0) = y y = 0Next xAny suggestions would be welcome!

European81,Functions aren't allowed to write to worksheets unfortunately.You could try writing a "Sub" instead and calling it through a Macro instead.Terror

You can also have a look at this thread: http://www.wilmott.com/messageview.cfm? ... adid=14663

I made it as a sub, just didnt include the sub part, sorry. The sub is below, and it works for the function f(x) = 2*x, but i need a way to change the given function without having to edit the sub everytime.Sub FuncVal()For x = 1 To 10 y = 2 * x Range("A1").Offset(x, 0) = y y = 0Next xEnd Sub

Why don't you have cell b1 being a value, and c1 being a function of b1. Then your sub can put each value from 1 to 10 into b1, and then copy all the values of c1 into a1 and below.This makes it easy to specify the function, and the extra time will be negligible. Alternatively, define a function f(x) (in vba), and then call that function.

I think you're very close to what you want.You can simply shove in a formula into the cells.Sub DCFC ()Dim x As Long, y As StringFor x = 1 To 10 y = "=2 * " & x Range("A1").Offset(x, 0) = y Next xEnd SubIf you have a series in A1:A10 then you can use the R1C1 notation thusfx = "Sqrt" and in the loopRange("B1").Offset(x, 0).FormulaR1C1 = "=" & fx & "(RC[-1])"That bit of code will give you roots of a series.

Thanks for your replies. Making use of the y = "=2 * " & x expression is interesting, but as far as I can see it does not solve my problem of substituting in different function. For instance, how about x^3? I could hard code this, but the idea is that the user will be asked something like "Type in a function" and then the user will type in a function in a way similar to 2*x or x^3 or (x-2)/x - you get the idea. As far as I can see, your solutions do not solve this. But maybe I am missing something.

That is the difficulty of trying to switch between mathematical notation and excel notation.If you're not insistent on them entering in the equation in mathematical notation, I'd get them to enter the function in the spreadsheet, as per my previous response.If you want them to enter in the equation in the form "=x^3 + 3 x", you could try and use VBA's string functions to parse the equation, replacing all instances of x with the number in question. The obvious difficulty is, for example, if x=5, you will get "=5^3 + 3 5" - not what you're after.I guess you could force the user to use the form "=(x)^3 + 3*(x)".When you've got the whole column of values, do you want them to be values, or the formula?

My idea is that once I have gotten the values in a column, then my next step would be to let excel draw a chart of the values. Kinda same thing as you can do on a graphical calculator. I was thinking that when a calculator can be programmed to do this, it should be pretty easy in VBA. I guess I was wrong! Even if I could get this to work, I would imagine that there would be problems with functions like f(x)=ln(x) or f(x) = e^x. My hope wsa to make a problem that could do the same as my calculator but at a much higher speed, viewability, and flexibility.Maybe was I am looking for has already been developed? Are there any programs out there that are good at drawing graphs (maybe even of functions of two variables)?

There are probably many more but I had fun with the old gnuplot.

I think you are using VBA when you shouldn't be. Why don't you use an excel data table. A one way data table, with a chart based on it, would be perfect for that.

This approach should work for simple functions, although I can't guarantee that it works for all cases:Sub CallThis()'This routine is here only to excercise the routine below... Call EvalMyFunction("x^2")End SubSub EvalMyFunction(Func As String)'Only works on functions of x For i = 0 To 20 toeval = Replace(Func, "x", "(" & i & ")") result = Application.Evaluate(toeval) Debug.Print result NextEnd Sub