Call Function At Each Iteration Of Solver
Mar 27, 2008
I have a written a small sub to run solver. However, I need to run a secondary sub at each solver iteration. The secondary sub I want to run is called “BypassGoal”.
My code currently looks like:
Sub OperatingPt()
Dim WrkBk As Workbook
Dim WrkSht1 As Worksheet
Set WrkBk = ThisWorkbook
Set WrkSht1 = WrkBk.Worksheets("Engine")
Dim Mode As String
Dim CellTarget As String
Dim CellRange As String
WrkSht1.Activate
WrkSht1. Cells(30, 2).Value = 2
CellTarget = "H14"
CellRange = "B30"
SolverReset
SolverOptions Iterations:=3000, Precision:=0.000001, AssumeNonNeg:=True, Derivatives:=2, StepThru:=True ....................
View 2 Replies
ADVERTISEMENT
Nov 1, 2008
I would like to start with a spending value, (SV), that would be the amount of money that I want to spend in a year, not counting taxes. The function (or functions) would then go through some sort of iterative calculation process that would compute the total spending level including taxes, (TS), the federal tax, (FT), and the state tax, (ST).
These values would have the relationship that TS=SV+FT+ST. When the iteration was finished, the federal tax and state tax amounts would be consistent with the taxes that would be due for this level of total spending as determined by the normal tax tables for both federal and state taxes.
I am not concerned that the tax figures be absolutely exact, so a lot of iteration is probably not necessary.
View 3 Replies
View Related
May 21, 2006
I have a work book with 3 sheets. Sheet 1 is the main sheet and sheets 2 and 3 will use (I hope vlookup) to update 3 columns from info in sheet 1. my attempt at a vlookup call in sheet 2 is: =VLOOKUP($b3,[master_AoJ_2.xls]Sheet1!$B$3;$B$65,false)
my understanding is that: - $b3 is the cell in sheet 2 that will be updated as a result of the vlookup call. - [master_AoJ_2.xls]Sheet1!$B$3;$B$65 refers to range $B$3:$B$65 on sheet1 of master_AoJ_2. it does not work... infact I get nothing at all. when I type the call into cell $b3 of sheet2 excel thinks it is stariaght text. this is how I coded the function call =VLOOKUP($b3,[master_AoJ_2.xls]Sheet1!$B$3;$B$65,false)
View 4 Replies
View Related
Mar 23, 2007
At work, we have a spreadsheet that uses SOLVER.XLA in a macro. It works fine in Excel '97 but in Excel 2003 it simply does nothing. The code is
s = Application.LibraryPath & "solversolver.XLA"
On Error Resume Next
Workbooks.Open filename:=s
Application.ExecuteExcel4Macro String:="SOLVER.XLA!SOLVER.RESET()"
Application.ExecuteExcel4Macro String:="SOLVER.XLA!SOLVER.OK( PLOT!R46C4,2,0,(PLOT!R13C12:R14C12))"
Application.ExecuteExcel4Macro String:="SOLVER.XLA!SOLVER.OPTIONS(100,100,0.000001,FALSE,FALSE,2,1,1,0.05,TRUE)"
Application.ExecuteExcel4Macro String:="SOLVER.XLA!SOLVER.OK(PLOT!R46C4,2,0,(PLOT!R13C12:R14C12))"
Application.ExecuteExcel4Macro String:="SOLVER.XLA!SOLVER.SOLVE()"
ActiveSheet. Protect DrawingObjects:=False, Contents:=True, Scenarios _
:=True, password:="sigma"
It gives no error message, it simply does not do the calculations. (I took out the error handling line to make sure that there really were no errors being generated!)
View 3 Replies
View Related
Jan 11, 2010
See my file. I want to calcuate a % needed (20%) to add to a another material. Should be easy but I can not figure it out.
View 3 Replies
View Related
Jul 31, 2006
In the attached file I have an example. I've used the Frequency array function to get the distribution of results from some sample result data (50 results). I want to use Solver to change the Bin values to what they would need to be to provide a desired distribution, such as in the example file. I've tried setting the target cell at either the sum of the frequency results or sum of their percentage distribution (100%), and set contraints either frequency results or percentage distribution respectively, along with Bin contraints being consectutively larger (requirement of the Frequency function). Alas, I can't get a solution, only the response of "Solver could not find a feasible solution".
View 4 Replies
View Related
Mar 23, 2013
I have written this macro below to run solver multiple times. It saves the solver results on a line and then inserts a new line for the next solver run.
I would like to save the final Objective Function Value from solver in each line as well as its result. Is there any way to do this?
Sub run()
'
' run Macro
' run solver
'
' Keyboard Shortcut: Ctrl+n
'
Application.ScreenUpdating = False
Range("B4:T13").Select
[Code] .........
View 2 Replies
View Related
Mar 3, 2007
how can I generate a user defined function(UDF) that triggers excel's solver? I have a set of 5 constraints and one objective that is to be maximized. I want to perform a senstivity analysis for my objective changing these 5 constraints. So I want the UDF to take all the 5 constraints and return me the optimized value (obtained from excel's solver) for the objective cell.
View 8 Replies
View Related
May 15, 2014
I get this error : Compile error : Sub or Function not defined. I found out that this might be caused by the solver not being referenced. But, this is fixed now but I still get the same error message.
I'm using Excel 2007 - Windows.
Here is the code :
Sub Macro1()
Dim i As Integer
Dim j As Integer
j = 4
For i = 1 To 4000
If ActiveSheet.Cell(i, 12).Value = 1 Then
ActiveSheet.Cell(j, 15).Value = ActiveSheet.Cell(i, 2)
[code].....
View 2 Replies
View Related
Aug 30, 2007
Just wonder how to call the function if I have it's name in the variable String
for example something like that:
View 11 Replies
View Related
May 10, 2007
I wrote a function procedure in VBA. Pasted it into a 'VBAProject' sheet in my workbook. But when I try to call the function (by entering the name and arguments into a cell), the sheet displays "#NAME?".
What have I forgotten?
example:
Public Function TotDays( _EndDate As Date, _StartDate As Date _) As Integer
TotDays = EndDate - StartDate
End Function
cell
a5: =totdays(b2,A2)
b2: 4/1/2007
a2: 3/1/2007
View 9 Replies
View Related
Jul 12, 2007
I created a file with one sub and one function. Saved it as an add-in. and followed the promts to insert the Add-in.
The button I assigned to the macro works fine, but when I call the function, I am told "Sub or Function not defined".
Is there some special way to call an add-in function from VB?
View 9 Replies
View Related
Sep 24, 2008
How is it possible to take the choice from the Combo Box and send that into a Stored Procedure statement in Excel VBA
I have 3 Combo Boxes, Months, Years, Date.
I'm trying to fillout these into this statement to run my Stored Procedure.
View 10 Replies
View Related
Mar 2, 2012
I have found some code that does what I want- but i do not understand how to use - call it. The programmer says ...
GetNetworkIPAddress()
' can be called from a worksheet cell using the formula:
' =GetNetworkIPAddress()
I have put this in a cell but i ger an error #Name?
what should i do ?
View 9 Replies
View Related
Aug 12, 2008
I'm playing around with the Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) function. I have a list of people in Column D that I'd like to move data around if the user double clicks on a cell in Column D. The list is about 158 rows long, and could grow beyond that. I've gotten lazy typing:
If Intersect(Target, Range("D4") Is Nothing Then
Exit Sub
Else
{My Code Here}
End If
If Intersect(Target, Range("D5") Is Nothing Then
Exit Sub
Else
{My Code Here}
End If
Etc, Etc, Etc
What I was hoping to do is use a variable (t) to cycle through the rows and call the various functions. So what I came up with is:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Application.ScreenUpdating = False
On Error Resume Next
Dim t As Integer
Dim Subtest As String
For t = 4 To 158
Subtest = "D" & t
I've created Sub D4(), Sub D5(), and Sub D6() as a test. This didn't work at all as it's looking for a Subtest(). Also, clicking on D5, D6, didn't do much either. Is there any way to make this cycle work, or should I keep typing away?
View 9 Replies
View Related
Aug 23, 2006
I wanted to have that one appear whenever a certain cell in a certain sheet gets a specific value, and depending on the answer of the user the Form should set the value of some other cell. So maybe there´s a much simpler solution to this task than what I tried - and I´ll be grateful to learn about it.
Anyway I tried the code which follows below - and it works just fine when I call it from the VBE environment - but inside the UserForm it just stops in between (without any error message) when I operate it from the Excel sheet (but still succeeds with the function). I´ll be very happy if someone could solve this "miracle" for me (even IF there´s a better solution altogether). So I just provided a simple Function which does nothing but call the UserForm and is executed whenever the condition is met (some calls to MessageBoxes in between just have been added to see how far the code gets executed): Function:
Public Function Call_my_form()
Call Option_Form.Show
MsgBox "Call executed"
End Function
Call of the function: =If(E11="Go";Call_my_form();"Nothing")
The UserForm just uses two OptionButtons with code as follows (two versions to check whether one of these would be the reason - both do behave the same way):
Private Sub OptionButton1_Click()...........................
View 5 Replies
View Related
Apr 3, 2008
I have created an Excel add-in (.xla) which contains many custom functions. This add-in is stored as read-only on a network drive. I reference it from various spreadsheets and calls to it work perfectly unless I put a call to it in the Workbook_Open event. When I make a call to a function in the add-in from a spreadsheet's Workbook_Open event, Excel crashes completely and I get the dialog that offers to recover the file and report the problem (i.e. Excel shuts down). I have no doubt that this is being caused by the call to the add-in specifically from the Workbook_Open event as removing this call solves the problem. Also, it is not related to a particular function as I have tried various ones; it is ANY call to the add-in from Workbook_Open that causes it. Is this a bug in Excel or is there something that I need to do before referencing the add-in in Workbook_Open? I have tried Application.CalculateFull and Doevents prior to making the call to the add-in and neither approach worked.
View 2 Replies
View Related
Sep 10, 2006
I have a cell, D5, which is the sum of three other cells, A5 B5 and C5. (all currently empty). Cells A1 through C4 are filled with various numbers.
What I've been trying to do is use solver to say: Make D5 equal 200, do it by manipulating only A5 B5 and C5, and make it subject to the constraint that A5 must equal a value selected from A1:A4, and B5 must equal a value from B1:B4, and C5 ...etc. I have deliberately set it up so that there is only one solution.
I was doing fine until trying to create the constraints. How can I make a constraint that says "this cell" must equal "one of the following cells"? And if I can't do that, is there an alternate method of achieving the same result?
View 9 Replies
View Related
Dec 22, 2009
I'm using an activex combobox in excel. It display 4 posible choices, and one of them is "Other". When someone select "other" there is a textbox named "tb" that should be filled. Otherwise tb should be invisible. Combobox change() function looks like that:
View 14 Replies
View Related
Jan 9, 2006
I have a spreadsheet with 5 worksheets representing the working days of the week. Each worksheet contains data pertaining to every occurance of that day for as long as it has been recorded. Each day is broken up into hourly data.
So for example, for Monday's worksheet, you would have
Col A..............Col B........Col C
9/jan/06........5am.........data
9/jan/06........6am.........data
9/jan/06........7am.........data
...etc....then...
2/jan/06........5am.........data
2/jan/06........6am.........data
2/jan/06........7am.........data
...etc....
We are trying to create a function, on another worksheet called Query. The only way I can describe what we want it to do is to do it in psuedocode.
Psuedo........................................
View 10 Replies
View Related
Apr 23, 2007
I found a function to set the password a an excel file:
=========================================
Function SetPwd(strNoPwdFile As String, _
strPwdFile As String, _
Optional strOpenPwd As String, _
Optional strModPwd As String) ' As Boolean
' This function requires the following arguments:
' strNoPwdFile - The path to a document without a password.
' strPwdFile - The path and name to save the password-
' protected document.
'
But the compiler said that it is syntax error.
How can i rectify the error?
View 9 Replies
View Related
May 23, 2006
I want to call on a function with a macro/button in the same way as when you choose "insert-function" but in a way that the user himself is able to put in values on the spredsheet.
View 7 Replies
View Related
Jun 25, 2013
When I step-through my code below, it always opens the first file in the directory "C:Pyramid Files", but when it comes back to the Pyramid Files sub after fully processing the first file via various other subs, the VB Editor apparently doesn't like something about this line: StrFile = Dir(), since it quits after "snapping-back" to the previous sub Initialize(). I have also tried StrFile = Dir, but that doesn't work either. I did Dim Strfile in the General Declarations. When I set Watches for Dir and Dir(), I get the value "Invalid procedure call or argument" for both, as if the directory function lost the value. I can't determine why this is happening.
VB:
Dim WSM As Worksheet, WSB As Worksheet, WS1 As Worksheet, [U]StrFile As String[/U], StrDirectory As String, ClientCode As String
Dim Filename As String, LastRowb As Long, LastColB As Integer, LastRow1 As Integer, NextRowC As Integer, x As Integer, y As Integer
[Code] .......
View 4 Replies
View Related
Aug 30, 2012
This error message in line vpp:
Invalid procedure call or argument
Code:
Function fn1(ByVal a, ByVal i, ByVal e, ByVal N, ByVal w, ByVal ta)
Pi = Application.WorksheetFunction.Pi
mhu = 398600
vpp = (mhu / Math.Sqr(mhu * a * (1 - e ^ 2))) * (-Math.Sin(ta * Pi / 180))
fn1 = 2 * vpp
End Function
View 9 Replies
View Related
Jan 25, 2008
I have a spreadsheet that uses a function defined in VBA code that is housed in an add-in which is stored in the default location (C:Documents And Settings{user_name}Application DataMicrosoftAdd-Ins). To share this spreadsheet, I first install the add-in on the other person's computer. Then, when the other person opens the spreadsheet, the function doesn't work because the function call on the worksheet contains the whole path of the add-in (and the path contains my {user_name} rather than the other person's). So, the immediate solution is to just delete the path of the add-in from the function call (so that only the name of the function remains, plus the arguments of course). But this is quite inconvenient given the number of times the function appears in the worksheet, and it's not practical as we continue to share files like this.
My question is: is there a way to make sure that the 'C:Documents...' never gets added to the function call in the first place? I mean, why doesn't Excel just try to run the function locally before it adds that path that refers to a separate computer? I do realize that there are other methods of sharing VBA code. For example, I know that I could include the code in the specific spreadsheet that I'm trying to share, and this would work. But, that's not the answer I'm looking for (for one, the function refers to a very large amount of data stored in the add-in, and there are other reasons that make the aforementioned method impractical). I'm wondering if I'm missing something, and if there is a way to stop Excel from tacking on the path string to the function call, so that the function would just work on any computer as long as they have the add-in installed?
View 9 Replies
View Related
May 25, 2006
I have written a public function inside a class module. Is there anyway I could call the public function in Excel? What is the exact syntax to do it?
View 5 Replies
View Related
Aug 2, 2006
I am working with several columns of data, and each column's range is stored in a range array. My program needs to use a specififc combination of the columns as the second argument sent to the regress function. In certain cases, the columns I am referencing are not contiguous, so the function won't work. Here's an example:
Suppose we're working with 3 independent variables (a simple case) whose data is stored in three contiguous columns named x1, x2, and x3. The program I've written will call the regress function 6 times: once using x1, once using x2, once using x3, once using the union of x1 and x2, once using the union of x1 and x3, once using the union of x2 and x3, and once using the union of x1, x2, and x3. Suppose the range for x1 is d5:d18, the range for x2 is e5:e18, and the range for x3 is f5:f18. Also, each union of ranges is stored as one element in an array called Combos, which is sent to the regress function.
The regress function returns an error when prompted to calculate regression stats for columns x1 and x3 because they aren't contiguous. I need to know if there is some sort of way around this. write a procedure that will detect if the ranges being input to the regress function are contiguous, and if they are not, copy them to a new worksheet so they will be contiguous and then send the new range references to the regress function for calculation purposes? Is there an easier way around this issue? If I can't get this to work, my entire program is wasted.
View 3 Replies
View Related
Dec 18, 2006
i'm trying to call a function from another one, i'm getting this error 'Compiler error: = expected' but i don't know the reason, the functions simply take some values an store them in an here is the
Dim productos(19, 3) As String
Sub agregarProducto(ByVal descripcion As String, ByVal modelo As String, _
ByVal precio As String, ByVal unidad As String)
Dim r As Integer
For r = 0 To 19
If productos(r, 0) = "" Then
productos(r, 0) = descripcion
productos(r, 1) = modelo
productos(r, 2) = precio
productos(r, 3) = unidad
End If
Next
End Sub
Sub agregarProductoTelas()
Dim descripcion, modelo, precio, unidad As String
If Selection.Column = 1 Then
descripcion = Selection. Offset(0, 0).Value
modelo = Selection.Offset(0, 0).Value
precio = Selection.Offset(0, 3).Value
unidad = Selection.Offset(0, 2).Value
agregarProducto(descripcion, modelo, precio, unidad) 'error happens right here
MsgBox (descripcion)...
View 3 Replies
View Related
Sep 7, 2013
In an Excel sheet, in A1 I have written B1 and in A2 I have written D20.
I have a data panel in B1:D20.
I would like to call a vba function my_Fun that acts on my data panel and returns a double in cell A3.
Instead of writing my_Fun(B1:D20) in A3 I would like to be able to write my_Fun(A1:A2), where the entries in A1 and A2 are called, rather than A1 and A2 being used to define the range themselves.
View 5 Replies
View Related
Jan 10, 2013
I try to call a function/procedur in an Excel2010-AddIn (.xlam file) from another Excel2010-AddIn (.xlam file):
Code:
Application.Run (filename.xlam!functionname)
Both AddIn-files are in the same directory (the default AddIn directory).
This call causes the error message "runtime error 91: object variable or with block variable not set".
This problems came up with providing an existing Excel2003-AddIn for Excel 2010.
The same code runs in following cases:
1. call a function in an Excel2003-AddIn (.xla) from another Excel2003-AddIn (.xla)
2. call a function in an Excel2003-AddIn (.xla) from a Excel2010-AddIn (.xlam)
View 1 Replies
View Related