Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
solver setcell= to an argument name rather than a cell in the worksheet
I would like to use solver in VBA and setcell= to an argument name
rather than a cell in the worksheet so I can keep the overhead in VBA. I am writing a least squares routine that will make a function for polynomial fits, similar to how trend or forcast work Here is my code, it does not work in VBA because I am mixing ranges with variables and such. ' Function created by Travis Beek Function Poly2(Known_Xs, Known_Ys, New_X, Optional Statistics) If Known_Xs.Rows.Count < Known_Ys.Rows.Count Then MsgBox "Known_Xs does not have the same number of rows as Known_Ys." & Chr(13) _ & "The answer in " & ActiveCell.Address & _ " will not be correct until they match.", vbExclamation, "Warning" End If For n = 1 To Known_Xs.Rows.Count X = Known_Xs.Item(n, 1) 'ref(r,c) is relative to the range, not the sheet y = Known_Ys.Item(n, 1) a = 1 'should the constants be outside the loop? b = 1 c = 1 Sumsqs = (y - (a * X ^ 2 + b * X + c)) ^ 2 + Sumsqs Next n SolverOK SetCell:=Sumsqs, MaxMinVal:=2, ByChange:="a,b,c" SolverSolve Poly2 = Sumsqs ' For Each blah In Known_Xs.Cells 'also works, loop repeats at the "Next" operator ' total = total + blah.Value ' Next End Function --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
solver setcell= to an argument name rather than a cell in the worksheet
I'm not exactly sure of the math, but here is a look at part of your code...
a = 1 'should the constants be outside the loop? b = 1 c = 1 Sumsqs = (y - (a * X ^ 2 + b * X + c)) ^ 2 + Sumsqs Next n SolverOK SetCell:=Sumsqs, MaxMinVal:=2, ByChange:="a,b,c" The "SetCell" and "ByChange" are references to Cells on the worksheet. Solver can not work on vba variables. The reference to "SetCell" would be a function on your worksheet. The "ByChange" reference would be to three cells on your worksheet that can be changed by Solver. You can give the three cells a Name like "a", "b", and "c_" to make your code a little easier to read. HTH. -- Dana DeLouis Using Windows XP & Office XP = = = = = = = = = = = = = = = = = "Tbeek " wrote in message ... I would like to use solver in VBA and setcell= to an argument name rather than a cell in the worksheet so I can keep the overhead in VBA. I am writing a least squares routine that will make a function for polynomial fits, similar to how trend or forcast work Here is my code, it does not work in VBA because I am mixing ranges with variables and such. ' Function created by Travis Beek Function Poly2(Known_Xs, Known_Ys, New_X, Optional Statistics) If Known_Xs.Rows.Count < Known_Ys.Rows.Count Then MsgBox "Known_Xs does not have the same number of rows as Known_Ys." & Chr(13) _ & "The answer in " & ActiveCell.Address & _ " will not be correct until they match.", vbExclamation, "Warning" End If For n = 1 To Known_Xs.Rows.Count X = Known_Xs.Item(n, 1) 'ref(r,c) is relative to the range, not the sheet y = Known_Ys.Item(n, 1) a = 1 'should the constants be outside the loop? b = 1 c = 1 Sumsqs = (y - (a * X ^ 2 + b * X + c)) ^ 2 + Sumsqs Next n SolverOK SetCell:=Sumsqs, MaxMinVal:=2, ByChange:="a,b,c" SolverSolve Poly2 = Sumsqs ' For Each blah In Known_Xs.Cells 'also works, loop repeats at the "Next" operator ' total = total + blah.Value ' Next End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
solver setcell= to an argument name rather than a cell in the worksheet
I would like to use VBA variables not cells on the worksheet, but if
can't, so be it. If anyone knows how to please post. So lets say this is my code then: Function Poly2(Known_Xs, Known_Ys, New_X, Cell_for_SumSqs As Range, a b, c, Optional Statistics) 'Cell_for_Sumsqs, a, b, and c are each a cell on the worksheet 'I need to paste an elaborate formula that depends on the number o data pairs, into the range Cell_for_Sumsqs, but the following does no work Range(Cell_for_SumSqs).Activate ActiveCell.FormulaR1C1 = "=25" ' 25 is just to see if it works 'the least squares formula is as follows: ' Sumation from i=1 to n of (yi-(a*xi^2+b*xi+c))^2 ' how would you write that into a cell when the number of xi,yi pair depends on the langth of the argument range Known_Xs (or Known_Ys) Thanks for any help Travi -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Which argument can be replaced by a Tag rather than a cell range? | Excel Discussion (Misc queries) | |||
Average: 2nd argument as a variable in another cell av(c6:c(a3)) | Excel Discussion (Misc queries) | |||
TIMEVALUE - can I use cell reference as argument? | Excel Worksheet Functions | |||
using the name of the worksheet as argument | Excel Worksheet Functions | |||
Worksheet Name as argument in Custom Function | Excel Programming |