VBA - Solver use on arrays
I have developed an excel sheet which can calculate a certain number of data
via the solver-macro. Now I need to improve my sheet by only running all calculations and data in the "background", i.e. show no data in any sheets, unless it is the final result. Basically, the problem is to get the FXW out of the following: Minimize FunctionX s.t. 1) (Min)imum < (W)eighting of variable 2) (W)eighting of variable < (Max)imum 3) (Sum) of FXW = 1 4) Max Weight of varible 1 to y (W_1y) = Predefined by Cell reference (MaxY) 5) Max Weight of varible y+1 to n (W_yn) = Predefined by Cell reference (MaxN) Min and Max are arrays defined in functions. 4 and 5 is summation of variable weight in the FXW, see below. FunctionX is given as an FXW * matrix * FXW^-1, where FXW will be the weight of each variable. Input: a) Max b) Min c) MaxY d) MaxN e) Matrix Arrays and Matrix is n Code is like this (in generel terms): Function MaxY() MaxY = Sheets("Inputs").Cells(5, 31).Value End Function Function W_1y() Dim i as integer for i=1 to y x = x + FXW(1,i) next i W_1y = x End Function Function MinFunctionX() ReDim FXW(n) FunctionX = FXW * matrix * FXW^-1 SolverReset Call solveradd(Sum, 2, "1") Call solveradd(FXW, 3, Min) Call solveradd(FXW, 1, Max) Call solveradd(W_1y, 1, MaxY) Call solveradd(W_yn, 1, MaxN) Call solverok(FunctionX, 2, 0, FXW) Call SolverSolve(True) SolverFinish MinFunctionX = FXW End Function What I don't know is 1) if I can use functions in the call solver... 2) the repeated calculation of W_1y and W_yn is done in another function, is this appropriate, even possible? 3) how to store the FXW for other use. I would like to be able to double check the values with the slow excel program i got. |
All times are GMT +1. The time now is 10:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com