User input on worksheet to directly edit or amend code in VBA

Attachment 357212

Hi Guys

I’m after some direction on this one.

I want the user to be able to amend code by changing a cell in the worksheet without the need to edit the code.

Background
I have the attached file which creates a 100 x 100 grid of x and y coordinates and using a hardcoded function determines the z coordinate for each x and y and then plots a 3 dimensional image representing that function. At present it just makes pretty images (try it for yourself) and one day I may actually find a practical use for it.

For the example attached I use a 2 dimensional array for z and calculate as follows:

z(x, y) = (x - 50) ^ 3 + 50000 * Sin((y - 50) / 4)

The (x-50) and (y-50) are simply to ensure the 100 x 100 grid is centred around x=0 and y= 0 axis.

If I want to plot a different function I need to edit this line of the code.

I would like to be able to enter in a cell (say y3)in the worksheet a similar function say x^2 + y^2 and have the code adjust accordingly, but attempts to set z(x,y) = range(“y3”).value simply sets z(x,y) to a string value equal to “x^2 + y^2” rather than amending the line of code. Once I work this out I can use simple substitution formula’s to change the x’s and y’s back to (x-50) and (y-50) so I’m not too concerned about that at present.

Is there a way to take the text string from a cell and use this in the code? If so how do I do it

WARNING
I’m self taught in VBA so my code might be a bit messy

Please Login or Register  to view this content.

You can do this without VBA.

Lets say that you have the string " (x - 50) ^ 3 + 50000 * Sin((y - 50) / 4)" in A1
And you have your x values in C2:C100 and your Y values in D1:AC1 (or how ever far down and out you want to go)
Select cell D2 and define a name

Name: zValue
RefersTo: =EVALUATE(SUBSTITUTE(SUBSTITUTE(Sheet1!$A$1,"x", Sheet1!$C2),"y", Sheet1!D$1))

(Note the absolute/relative addressing in the last two cell references.)

Then put the formula =zValue in D2 and drag down and right.
The values in your matrix will change when you change the formula in A1.

In the attached, A1 allows you to either choose from the list or to enter your own formula (lower case x and y)
(Note that the attached is a .xlsx file, no macros)

Mikerickson

That wasn't what I was looking for because I'm trying to steer clear of the excel charts but thanks anyway becauue I learnt something new today. I think I'll use that somewhere else.

Hi Guys,

About to close this one as I've worked it out myself but with a very big hint from Mickrickson (thanks).

For those of you who are interested or for those in the future searching the code is below. Evalute certainly got me heading in the right direction it's just that it appears you can't assign an array value to an evaluate formula so I had to do this in two steps.

Please Login or Register  to view this content.