muhammad taqi

muhammad taqi

  • NA
  • 3
  • 1.3k

Convert Excel Macro in to C#

Sep 18 2015 5:47 AM
Hello Everyone,
I have a Macro in Excel which calculates some values, Now I have to implement the technique which is used in that Macro in C# Application.

I tried to understand the macro but could get it as I haven't work much in Excel

Can any expert help me convert the following Macro in C#.

Sub SingleLN()
'
' SingleLN Macro
'

k = 8
j = 35

'Reset Trial number and time
Cells(4, j + 12).Value = ""
Cells(5, j + 12).Value = ""
Cells(6, j + 12).Value = ""


Cells(5, j + 12).Value = Now
Trials = Cells(4, j + 9).Value
For i = 1 To Trials
Cells(4, j + 12).Value = i

SolverReset


Cells(4, 5).Value = (18 - 0.5) * Rnd + 0.5
Cells(5, 5).Value = (4 - 0) * Rnd + 0

Cells(k + i, j + 1).Value = Cells(4, 5).Value
Cells(k + i, j + 2).Value = Cells(5, 5).Value

Cells(k + i, j + 9).Value = Cells(2, 21).Value
Cells(k + i, j + 10).Value = i


SolverOk SetCell:="$U$2", MaxMinVal:=1, ValueOf:="0", ByChange:= _
"$E$3:$E$5,$G$4:$G$5"
SolverAdd CellRef:="$E$3", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$G$3", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$E$4", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$G$4", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$E$5", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$G$5", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$E$3", Relation:=1, FormulaText:="1"
SolverAdd CellRef:="$G$3", Relation:=1, FormulaText:="1"
SolverOptions MaxTime:=100, Iterations:=1000, Precision:=0.000001, AssumeLinear _
:=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=True
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1

Result = Application.Run("Solver.xlam!SolverSolve", True)

' finish the analysis
Application.Run "Solver.xlam!SolverFinish"


Cells(k + i, j + 12).Value = Cells(4, 5).Value
Cells(k + i, j + 13).Value = Cells(5, 5).Value

Cells(k + i, j + 20).Value = Cells(2, 21).Value
Cells(k + i, j + 21).Value = Cells(5, 14).Value

' report on success of analysis
'If Result = 0 Then
'Cells(k + i, j + 22).Value = "SOLUTION FOUND, optimality and constraints satisfied"

'ElseIf Result = 1 Then
'Cells(k + i, j + 22).Value = "SOLUTION FOUND, converged, constraints satisfied"

'ElseIf Result = 2 Then
'Cells(k + i, j + 22).Value = "SOLUTION FOUND, Cannot improve, constraints satisfied"

'ElseIf Result = 3 Then
'Cells(k + i, j + 22).Value = "SOLUTION FOUND, Stopped at maximum iterations"

'Else
' Result = 4, Solver did not converge
' Result = 5, No feasible solution
'Cells(k + i, j + 22).Value = "NO SOLUTION"
'End If
Cells(k + i, j + 22).Value = Result
Next i
Cells(6, j + 12).Value = Now
End Sub


Thanks