executing a sub within a function

Hi guys, I'm new here.

I'm trying to write some code which will take a polynomial and output it's value for varying x.

For a given input "n" I contruct n+1 placeholders (1, x, x^2, ... , x^n)

Beneath them I put in values

Then for a given "x" (in cells (3,2)).

I run the following code to get a value for f(x) (which is put in cell (9,1)).

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Now the problem is that I want to make the function f.

I tried the following to no avail:

Please Login or Register  to view this content.

I just get a value error.

Any ideas? P.S. Sorry if my formatting is awful, I'm new to presenting code on forums.

a function called from a cell may not alter the value or other properties of another cell-it may only return a value as its result (in the cell that contains it)

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.

1st question: Is there a special reason you are doing this in VB rather than just using spreadsheet formulas? This looks like it should be so easy without VBA, that I have to wonder if there is a special reason for using VB.

For example, the =SERIESSUM() function makes short work of polynomials http://office.microsoft.com/en-us/ma...015.aspx?CTT=1 (in R1C1 notation) =seriessum(r3c2,0,1,r7c2:r7c10) for a 9th order polynomial. If you have less than a 9th order, enter 0's for the excess coefficients. If r3c2 can ever be 0, rewrite to take the constant term outside of the seriessum() function to avoid a 0^0 error. If you'll never have a 9th order polynomial, adjust the coefficients argument to reflect the largest polynomial you ever expect to see.

I think before I tried to tackle this in VB, I'd want to understand the rationale for doing this in VB rather than the spreadsheet.

Can't making_f be converted to a function?

I'm using vba as it's much more convenient to work with when you've got a variable like degree of polynomial.

I will have to take your word for it. I'd have to see all the requirements and specifications, and how the f(x) calculation fits into the overall project, but I can see doing this just fine without VBA. I do believe that a programmer should use what he's most comfortable with, and it seems that a lot of people are just more comfortable with symbolic languages like VBA.

a.) want to know about a polynomial of degree n (whatever I choose, there is no largest)

In theory, this is true -- there is no limit to the order of a polynomial. However, I expect there are practical limits. Do you really expect to tackle 50th order polynomials? 100th order? 1000th order? If nothing else, Excel is limited to numbers between -1E308 and 1E308. When you start dealing with 200 to 400th order polynomials, I would expect you to start running into overflow errors.

A couple of questions about converting making_f to a function:

1) Is it necessary to copy each term of the polynomial into the cell below the coefficient? One of the main difficulties I see in converting making_f to a UDF is that you are returning multiple values to the spreadsheet (1 value for each term, and then the overall sum). I find that UDF's are easiest to code if your UDF only needs to return one value to the spreadsheet. So the first suggestion is to identify which values must be returned to the spreadsheet and which of these intermediate values we can simply store inside the procedure (maybe in an array or maybe they don't need to be stored at all).

A function can return multiple values as an array, in which case you can either use the =INDEX() function to determine which value to return, or you can enter the function as an array function and return all the values to a consecutive block of cells.

2) Sub procedures tend not to take arguments -- they extract information directly from the spreadsheet. UDF's, on the other hand, work best if needed information is passed to the UDF through the argument list. If you want to keep the same structure where you call another function from within another function, you will need to pass information from one function to the other. For example

Please Login or Register  to view this content.

Originally Posted by moosemaster23

My new problem is that my function doesn't auto update it's values if the argument changes. How to fix?

Originally Posted by MrShorty

UDF's ... work best if needed information is passed to the UDF through the argument list.

I notice that, while you are passing x to the function as an argument, you are still accessing the order and the coefficients directly from the worksheet. As written, it looks like it should update correctly when x changes, but Excel would not know to update the function when m or any of the coefficients change. I would suggest passing these arguments to the function through the argument list as well.

When I try to program it in excel however, it does not work quite so fast. Excel starts not responding and I have to shut it down. Why is this?

In this last sub, what are x and y? How long have you waited before declaring it "hung?" My guess is that 2*x*y is a fairly large number, and that the loop is simply taking a long time to calculate.