executing a sub within a function

[moosemaster23]

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)).

Function f_1(n, x)

f_1 = x ^ n

End Function

Sub making_f()

x = Worksheets(2).Cells(3, 2).Value
'this is the input for x
n = Worksheets(2).Cells(1, 2).Value
'this is the degree of the polynomial

Worksheets(2).Cells(8, 2) = Worksheets(2).Cells(7, 2).Value
'this is dropping down the constant term to the next row
For m = 1 To n

Worksheets(2).Cells(8, 2 + m) = Worksheets(2).Cells(7, m + 2) * f_1(m, x)
Next
'this is inputting the values of constant*x^m in the next row

Worksheets(2).Cells(9, 2) = Worksheets(2).Cells(7, 2).Value

'this is dropping the constant term down again as I don't know how to sum efficiently in vba I do it iteratively.
For m = 1 To n

Worksheets(2).Cells(9, m + 2) = Worksheets(2).Cells(9, m + 1).Value + Worksheets(2).Cells(8, m + 2).Value
'this iterates the summation
Next

Worksheets(2).Cells(9, 1) = Worksheets(2).Cells(9, n + 2).Value
'this gives me the value of f(x)
End Sub

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

I tried the following to no avail:

Function f(x)

Worksheets(2).Cells(3, 2).Value = x
making_f
f = Worksheets(2).Cells(9, 1).Value


End Function

I just get a value error.

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

[JosephP]

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)

[arlu1201]

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.

[moosemaster23]

Ok. Thanks. So is there a way to get a function which does what I require?

[MrShorty]

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.

[moosemaster23]

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.

Hi Shorty,

Thanks for your reply. I'm using vba as it's much more convenient to work with when you've got a variable like degree of polynomial. The purpose of this is so that I can do the following:

a.) want to know about a polynomial of degree n (whatever I choose, there is no largest)
b.) get a function (or something like a function) which will take an input of x and give f(x)
c.) then to plot the function on a graph (I've already got the x-axis set up so I can specify the range and accuracy of my graph I just need the function now).

Hence using vba is better than manually using formulae (for instance in some cases I have 1000 points to plot on my x-axis)...

=seriessum function looks really good. I'll have a play with it.

[Norie]

Can't making_f be converted to a function?

[moosemaster23]

Can't making_f be converted to a function?

I don't thinks so, as functions can't change the value of cells, only output based on their prior value.

[MrShorty]

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

 function making_f(x,n,a)
'code for desired calculations
making_f=result
end function

function f(x,n,a)
'code
'the key in the code for a UDF is to have no direct references to the spreadsheet.
temp=making_f(x,n,a)
'code
f=temp
end function

[moosemaster23]

Thanks for all your replies. I've now fixed the issues and made f (code is v simple).

Function f_(x, n)

f_ = x ^ n

End Function

Function f(x)

m = Worksheets(1).Cells(1, 2).Value


ReDim test1(0 To m)

test1(0) = Worksheets(1).Cells(4, 3).Value

For n = 1 To m

test1(n) = Worksheets(1).Cells(4 + n, 3).Value * f_(x, n) + test1(n - 1)

Next

f = test1(m)

End Function

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

[moosemaster23]

New related problem.

I've got a variable axis set up (say from -200 to 200 with 0.025 increments). I want to populate the column to the right of this axis column with f(values). When I do it in excel (i.e. click on the cell type "=f(D4)" then drag it down) it works immediately. 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?

the code I'm using to populate the f column is below:

Sub PopulateF()

x = Cells(1, 2).Value
y = Cells(2, 2).Value

For n = 0 To 2 * x * y

Cells(4 + n, 5) = f(Cells(4 + n, 4).Value)
Next

End Sub

[MrShorty]

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

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.