excel function to call solver (VBA Macro)

[felkin]

I want to know how to have a function call solver. Without any user
prompts or "alt + key". I just want a cell to contain [
=Solver(A1,3,0,B2) ] and be able to copy and paste this so the next
row is [ =Solver(A2,3,0,B2) ] I don't really care what the return
value is for the function. I just want it to set A1 to 0 by changing
B1. I have seen a bunch of people ask this question but have found no
answers? Can you even do this with excel?

Thanks,

Rob

 

[Niek Otten]

Hi Rob,

If I understand your question well, you want to change a cell from within a
function.
That is not possible. A function can only return a value, which takes the
place of its call.

A macro ("sub" in VBA terms) can change cells. But that is probably an
approach which is totally different from what you had in mind.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[A.W.J. Ales]

Felkin,

As far as I know this indeed can't be done.
The reason for that a function never can change the value of any cell.
It can only - as any "normal" Excel function give a value "back to the
formula" from which it is called. Off course the calling cell can then be
changed by the result of the formula.

You can however write a macro by which you invoke the solver (or Goalseek)
to change B1 in such a way that A1 becomes 0 ( provided that B1 is a direct
or indirect sourcecell from A1), but you probably knew that allready.
But just in case :

Sub GoalSeekMacro()
Source = "B1"
ResultCell = "A1"
Result = 0
Range(ResultCell).GoalSeek Goal:=Result, ChangingCell:=Range(Source)
End Sub

In this macro you can - if you want replace Source = "B1" by Source =
Cell( Rownr,Columnnr).value whereby Cthat cell contains B1 (without " ")
The same is true for ResultCell = "A1".

By applying a loop you could arrange that your whole column A1:A? becomes 0
by changing B1:B?

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *