Using excel solver in VB.NET

[Holger Fitschen]

Hi to all,
I want to use the Excel solver in a VB.Net project.

The macro

Sub Makro1Solver()
Application.Run "Solver.xla!Auto_Open"
SolverReset
Worksheets(1).Select
Worksheets(1).Range("B9").Select
SolverOk SetCell:="R9C2", MaxMinVal:=3, ValueOf:="0", ByChange:="R8C2"
SolverSolve
End Sub

works fine when starting manually in Excel.

But a message like (sorry it is in german) "Zielzelle muß eine einzelne
Zelle im aktiven Blatt sein." and "Zielzellen müssen sich in der aktiven
Tabelle befinden." appears when I start the macro from my VB.NET
project. My translation for the messages "Goalcell must be a single cell
in an active sheet" and "Goalcells must locate in the active table".

The function in the cell R9C2 is
"=Z(-8)S-Z(-7)S/(Z(-6)S+(Z(-1)S+273.15))-LN(Z(-2)S*(760/1013.25))"

My VB.NET code is

Dim ExApp As New Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
Dim ExSheet As Microsoft.Office.Interop.Excel.Worksheet

Try
wb = ExApp.Workbooks.Open("E:\Daten\DD-Rechnung.xls")
ExApp.Run("SOLVER.XLA!Auto_Open")

ExApp.DisplayAlerts = True
ExApp.Visible = True
wb.Activate()

ExSheet = ExApp.Worksheets("Tabelle1")
ExSheet.Activate()

ExApp.Run("Tabelle1.Makro1Solver()")

Catch ex As COMException
MessageBox.Show("Error accessing Excel: " + ex.ToString())
Catch ex As Exception
MessageBox.Show("Error: " + ex.ToString())
End Try

And I import in vb.net

Imports Microsoft.Office.Interop
Imports System.Runtime.InteropServices
Imports System.Math

A simple macro which wrote some words in the excelsheet/cell works fine,
only the solver makes problems. Also the goalseek macro in excel works
when starting manually but not when starting from vb.net and has no
error messages. It only does nothing...

I searched for several days/weeks but I couldn't find the solution.

Could someone help me please or give me a hint or has an idea how to solve.

Thanks in advance
Holger

 

[Richard T. Edwards]

What its telling you is that you need to have a reference to worksheet .
You're doing your program outside of VB.Net so, you need to have a reference
to the ei ther running excel application or create a new instance of excel
and then use that reference to run your macro.

 

[Holger Fitschen]

I think I don't really understand what you mean.
I made a new instance of Excel with:

Dim ExApp As New Microsoft.Office.Interop.Excel.Application

and then I use a reference to excel, which starts excel and runs simple
macros, but not the solver and not the GoalSeek funktion.

btw I use Visual Studio 1.1, VSTO 2003 and Excel 2003

Could it be, that the solver couldn't read a function in a cell when it
started from external??

Thanks in advance
Holger

 

[Dana DeLouis]

btw I use ...Excel 2003

SolverOk SetCell:="R9C2", ...

Hi. Not sure on the German version, but in US versions of Excel, Solver
references need to be in A1 notation. (Starting with Excel 97 I believe)

SolverOk SetCell:="B9", ...

As a side note, I am a little curious on your equation.
Is it working ok?
=Z(-8)S-Z(-7)S/(Z(-6)S ...

It looks to me like it should be:
"=Z*(-8)*S-Z*(-7)*S ...

 

[Holger Fitschen]

Hi Dana,
yes, the function works in both notations (A1 and Z$S$)
But how I said, only when I start it manually, not out of vb.net.

I very distressed not to find a solution.....

Hi. Not sure on the German version, but in US versions of Excel, Solver
references need to be in A1 notation. (Starting with Excel 97 I believe)

SolverOk SetCell:="B9", ...

As a side note, I am a little curious on your equation.
Is it working ok?
=Z(-8)S-Z(-7)S/(Z(-6)S ...

That means a relativ position to the dependent cell
R9C2 = B9 and Z(-8) = B1 .
This happens automatically when I switched from A1 to Z$S$ notation for
testing several capabilities to solve my problem.

 

[Dana DeLouis]

I've learned that =Z(-8)S is the German equivalent to RC notation =R[-8]C.
I would experiment and switch everything (Worksheet & code) to the
equivalent A1 notation.

 

[John J Fors]

In my version of Excel 2003 the application addins don't load when Excel is
run as a COM server. But this can be over come by explicitly loading in the
startup routine or by COM reference method. My example uses an addin called
dataline follows:

AddIns("PI-DataLink").Installed = True

This could be your problem also.

John Fors

 

[Holger Fitschen]

Thanks to all,
I solved it by using a VBA subroutine "Worksheet_Change" which monitored
a cell. In this cell I wrote with vb.net a "1" and the sub
"Worksheet_Change" starts the solver.
It not a beautiful code, but it works.

Holger

In my version of Excel 2003 the application addins don't load when Excel is
run as a COM server. But this can be over come by explicitly loading in the
startup routine or by COM reference method. My example uses an addin called
dataline follows:

AddIns("PI-DataLink").Installed = True

This could be your problem also.

John Fors

I've learned that =Z(-8)S is the German equivalent to RC notation =R[-8]C.
I would experiment and switch everything (Worksheet & code) to the
equivalent A1 notation.