Member Avatar for Shefali

Hello all
I need to solve a equation using excel solver from visual basic 6. i did it manually in excel and recorded tha macro. however when i trid to automate excel using vb, an error message is displayed. it highlights the solverok function and says function not valid.
can someone please tell me whats the problem or how do i automate excel solver using vb6.
any help will be appreciated.
thanks.
shefali.

  • 2 Contributors
  • 2 Replies
  • 280 Views
  • 23 Hours Discussion Span
  • Latest Post Latest Post by Shefali

Dani AI

Generated

Short answer: the Solver functions (SolverOk, SolverSolve, etc.) live inside Excel’s Solver add-in, so calling them directly from a VB6 process fails unless that Excel instance has the add-in loaded and the calls are routed into Excel. (learn.microsoft.com)

Fix checklist (what to do in your VB6 automation)

  • Make sure the Solver add-in is installed on the machine and available to Excel. If it isn’t in the Add‑Ins list you’ll need to load the add‑in file for your Excel version (older Excel used Solver.xla, newer uses Solver.xlam). (learn.microsoft.com)
  • When you create Excel via automation, explicitly load the add‑in into that Excel instance. Use the AddIns collection / Installed property and ensure the add‑in workbook is open (Workbooks.Open) — add-ins are not always auto‑loaded in automation instances. (learn.microsoft.com)

How to call Solver from VB6 (two safe options)

  • Open the Solver add‑in in the automated instance and call the Solver functions using Excel’s Run method (do not call SolverOk directly from VB6).
  • Or put the Solver calls into a small VBA macro inside the workbook, then invoke that macro from VB6 with Application.Run.

Short VB6 sketch (illustrative — adapt paths to your Excel version):

Set xl = CreateObject("Excel.Application")
xl.Visible = True
' Ensure Solver add-in installed / open
xl.AddIns.Add xl.LibraryPath & "\SOLVER\Solver.xlam"
xl.AddIns("Solver Add-In").Installed = True
xl.Workbooks.Open xl.AddIns("Solver Add-In").FullName
' Call Solver via Run
xl.Run "Solver.xlam!SolverOk", "$C$8", 3, "0", "$B$1:$B$3"
xl.Run "Solver.xlam!SolverSolve", True

Examples of calling Solver via Application.Run and the recommended AddIns pattern are documented in Microsoft and community guidance. (learn.microsoft.com)

Troubleshooting tips

  • If you still get "not defined", confirm the exact add‑in filename/location for your Office build and open that file in the automated Excel.
  • As a robust fallback, wrap all Solver logic in a VBA macro inside the workbook and call that macro from VB6 — that avoids namespace and reference issues across Excel versions.

Recommended Answers

All 2 Replies

Member Avatar for Comatose

Hmn, can you post the code?

Member Avatar for Shefali

thanks for ur response..
here is the code

Dim appExcel As Excel.Application
Dim appBook As Excel.Workbook
Dim appSheet As Excel.Worksheet

Private Sub cmdExcel_Click()
Set appExcel = New Excel.Application
appExcel.Visible = True
Set appBook = appExcel.Workbooks.Add()
ActiveCell.FormulaR1C1 = "a"
Range("A2").Select
ActiveCell.FormulaR1C1 = "b"
Range("A3").Select
ActiveCell.FormulaR1C1 = "c"
Range("A4").Select
ActiveCell.FormulaR1C1 = "u(a,b,c)"
Range("A5").Select
ActiveCell.FormulaR1C1 = "v(a,b,c)"
Range("A6").Select
ActiveCell.FormulaR1C1 = "w(a,b,c)"
Range("B1").Select
ActiveCell.FormulaR1C1 = "1"
Range("B2").Select
ActiveCell.FormulaR1C1 = "1"
Range("B3").Select
ActiveCell.FormulaR1C1 = "1"
Range("B4").Select
ActiveCell.FormulaR1C1 = _
"=18*R[-3]C+14*R[-2]C+16*R[-1]C-(R[-3]C^2+R[-2]C^2+R[-1]C^2)-120"
Range("B5").Select
ActiveCell.FormulaR1C1 = _
"=12*R[-4]C+10*R[-3]C+8*R[-2]C-(R[-4]C^2+R[-3]C^2+R[-2]C^2)-56"
Range("B6").Select
ActiveCell.FormulaR1C1 = _
"=14*R[-5]C+8*R[-4]C+12*R[-3]C-(R[-5]C^2+R[-4]C^2+R[-3]C^2)-74"
Range("A8").Select
ActiveCell.FormulaR1C1 = "sum of squares"
Range("C8").Select
ActiveCell.FormulaR1C1 = "=R[-4]C[-1]^2+R[-3]C[-1]^2+R[-2]C[-1]^2"
SolverOK SetCell:="$C$8", MaxMinVal:=3, ValueOf:="0", ByChange:="$B$1:$B$3"
SolverSolve
End Sub


the solverok and solversolve is creating a problem. it works fine with excel vba but not with vb6. it says function or sub not defined.

kindly help.
thanks.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.