Solver: Can you use it with Arrays in VBA?
 

Hello,

I was just wondering whether it was possible to use the Solver add-in
but by referencing data in arrays in VBA code rather than cells in a
worksheet?
Obviously, I know the latter is possible but I'm interested in saving
time by defining my design functions, variables, etc. and related data
in array form in memory rather than having to physically write
everything to a worksheet.

If possible, would anyone know how to reference specific elements in
these arrays for Solver?

For example rather than doing this in a worksheet:

SolverAdd CellRef:="$A$8:$C$8", Relation:=3, FormulaText:="$C$9:$E$9"

I'd rather go for something like:

SolverAdd Ref:=myarray(first row, etc), Relation:=3,
FormulaText:="4xy"
[Yes, this line is littered with non-VBA BS, but you get the idea]

Thanks,

-Pete


--
Peter Bernadyne
------------------------------------------------------------------------
Peter Bernadyne's Profile: http://www.excelforum.com/member.php...fo&userid=7017
View this thread: http://www.excelforum.com/showthread...hreadid=572795

Solver: Can you use it with Arrays in VBA?
 

I'm not sure if this is what you're after but you can use the Scenario tool
to store various Solver problems (and Goal Seek problems as well.)

"Peter Bernadyne" wrote:


Hello,

I was just wondering whether it was possible to use the Solver add-in
but by referencing data in arrays in VBA code rather than cells in a
worksheet?
Obviously, I know the latter is possible but I'm interested in saving
time by defining my design functions, variables, etc. and related data
in array form in memory rather than having to physically write
everything to a worksheet.

If possible, would anyone know how to reference specific elements in
these arrays for Solver?

For example rather than doing this in a worksheet:

SolverAdd CellRef:="$A$8:$C$8", Relation:=3, FormulaText:="$C$9:$E$9"

I'd rather go for something like:

SolverAdd Ref:=myarray(first row, etc), Relation:=3,
FormulaText:="4xy"
[Yes, this line is littered with non-VBA BS, but you get the idea]

Thanks,

-Pete


--
Peter Bernadyne
------------------------------------------------------------------------
Peter Bernadyne's Profile: http://www.excelforum.com/member.php...fo&userid=7017
View this thread: http://www.excelforum.com/showthread...hreadid=572795

Solver: Can you use it with Arrays in VBA?
 

No. The version of Solver that comes with XL works only with worksheet
cells. It *may* be that there is a (commercial?) version that works the way
you want. Visit solver.com.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <Peter.Bernadyne.2cp8k5_1155843008.2244@excelfor um-nospam.com,
says...

Hello,

I was just wondering whether it was possible to use the Solver add-in
but by referencing data in arrays in VBA code rather than cells in a
worksheet?
Obviously, I know the latter is possible but I'm interested in saving
time by defining my design functions, variables, etc. and related data
in array form in memory rather than having to physically write
everything to a worksheet.

If possible, would anyone know how to reference specific elements in
these arrays for Solver?

For example rather than doing this in a worksheet:

SolverAdd CellRef:="$A$8:$C$8", Relation:=3, FormulaText:="$C$9:$E$9"

I'd rather go for something like:

SolverAdd Ref:=myarray(first row, etc), Relation:=3,
FormulaText:="4xy"
[Yes, this line is littered with non-VBA BS, but you get the idea]

Thanks,

-Pete


--
Peter Bernadyne
------------------------------------------------------------------------
Peter Bernadyne's Profile: http://www.excelforum.com/member.php...fo&userid=7017
View this thread: http://www.excelforum.com/showthread...hreadid=572795