Solver: Can you use it with Arrays in VBA?

[Peter Bernadyne]

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

[Dave F]

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

[Tushar Mehta]

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 <[email protected]>,
Peter.Bernadyne.2cp8k5_1155843008.22...rum-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
>
>