Brug af Excels Solver (Problemløser) med VBA makroer

På denne side viser jeg, hvordan man kan styre Excels Solver (Problemløser) med VBA makroer, og du kan downloade et eksempel på en VBA-styret applikation, som man kan bruge uden synderligt kendskab til Solver.

Excels Solver er et stærkt værktøj til at beregne og/eller optimere komplekse modeller. Det kan være hvilke håndtag, man skal dreje på for at maksimere et overskud, hvor mange chokoladeis man skal producere som funktion af vejrudsigten, eller hvordan man blander forskellige råmaterialer for at få en ønsket kemisk sammensætning til den billigste pris.

Der er rigtig mange muligheder, og er det en tilbagevendende opgave, kan det være en stor fordel at lave en applikation med VBA makroer. En sådan applikation kan også bruges af andre, som kun kender lidt til Solver.

Jeg arbejder på en cementfabrik og har lavet en applikation, som kan beregne, hvor meget der skal bruges af forskellige råmaterialer og brændsler for at få en givet produktkvalitet, og du kan downloade en til ukendelighed forsimplet udgave her.

Regnearket er zip-komprimeret og pakkes ud ved at højreklikke og vælge "Pak alle ud," eller hvad Windows nu foreslår.

Regnearket er lavet i Excel 2003, så du skal formentlig ændre referencerne til Solver/Problemløser til din version. Se lidt længere nede under Installation af Solver/Problemløser.

Udover makroer til styring af Solver indeholder det også eksempler på brug af Ranges, Arrays og UserForms med ListBoxe, ComboBoxe, TextBoxe, OptionButtons og CommandButtons.

Til at sikre, at der kun indtastes numeriske værdier i TextBoxe, er der en modificeret udgave af det klassemodul, som beskrives på denne side. Til brugerstyring af begivenhederne er der en "skræddersyet" popup-menu.

Installation af Solver/Problemløser

Solver/Problemløser installeres ikke automatisk, når man installerer Excel - det er et tilvalg. For at bruge Solver/Problemløser skal det vælges som tilføjelsesprogram i "regnearksdelen" af Excel, og skal det bruges af VBA, skal det også vælges her.

Det gøres under "References" i VBA-editorens Tools-menu. Sådan ser det ud i Excel 2003:

Står Solver ikke på listen, skal du klikke på knappen "Browse" og bladre dig frem til kataloget "Solver", der ligger begravet som et underkatalog til "Microsoft Office" i mappen med programmer.

Klik på "File types" og vælg Excel-filer (xls + xla eller xlsm + xlam). Klik på "solver.xla", eller "solver.xlam" hvis du bruger Excel 2007 eller nyere.

Lav en model

Før man bruger Solver/Problemløser, skal man have lavet en model med en eller flere målceller og en eller flere justerbare celler, hvis værdi indvirker på målcellerne.

Det helt simple eksempel følger her. I celle A2 taster du 2, i celle A3 skriver du "= A1 + A2", og så starter du Problemløser.

Som målcelle angiver du A3, Lig med værdi af 4 ved redigering af celle A1. I den danske version af Excel 2003 ser det således ud:

Klik på "Løs", og Solver finder skarpsindigt ud af at indsætte et 2-tal i A1. Man behøver ikke nødvendigvis have en målcelle, som betyder noget. Man kunne også sætte som betingelse, at celle A3 skal have værdien 4.

Ikke overraskende er det normalt mere komplekse problemer, man bruger Solver til, og når det begynder at lugte af ligninger med mange ubekendte, er det umagen værd at sætte sig ind i, hvordan Solver fungerer.

Man skal passe lidt på Hvis-funktioner i sine celler - kommer der for mange Hvis'er, kan Solver få forstoppelse. Prøv dig frem.

Jeg anbefaler kraftigt, at du laver sin model først og tester den ved at bruge Solver manuelt. Kan du ikke få den til at virke manuelt, kan du godt glemme at få den til at virke med VBA.

Kald af Solver med VBA

Når man kalder Solver med en VBA-makro, skal man gøre flere ting, ganske som når man bruger Solver manuelt. Man skal angive en målcelle, de justerbare celler, områderne med betingelser og endelig kan man fortælle Solver noget om løsningsmetode, præcisionskrav m.v.

Vi tager lige stumperne først, og til sidst føjer vi det hele sammen med et fuldstændigt eksempel.

Betingelser

Man kan sætte tre slags betingelser til værdier, når man bruger Solver, nemlig mindre end/lig med, lig med og større end/lig med ("< =", "=", "> =").

Når man kalder Solver fra VBA, er det smart at have grupperet disse betingelser i sæt af 2 kolonner. Fx kan celle A1 til A8 indeholde referencer til de celler, der skal være mindre end eller lig med nogle værdier (eller cellereferencer), som man så indsætter i kolonnen til højre, i dette tilfælde B1 til B8.

Celle A1 kan fx indeholde formlen "= M27", og så skal celle M27 være mindre end eller lig med det, som står i celle B1 (fx "7" eller "= X313" eller en formel).

Det er altså celle M27, der skal opfylde betingelsen - vi henviser blot til den i A1 - og cellen skal af indlysende grunde have en formel, så værdien direkte eller indirekte påvirkes af en eller flere justerbare celler.


'< = betingelser
If bRel1 Then
   solveradd cellref:=rA1.Address, _
             relation:=1, formulatext:=rB1.Address
End If

'Lig med (=) betingelser
If bRel2 Then
   solveradd cellref:=rA2.Address, _
             relation:=2, formulatext:=rB2.Address
End If

'> = betingelser
If bRel3 Then
   solveradd cellref:=rA3.Address, _
             relation:=3, formulatext:=rB3.Address
End If

Det centrale er "relation:=1" (og 2 og 3) som fortæller Solver, at det er hhv. "< =", "=" og "> =" betingelser.

"bRel1", "bRel2" og "bRel3" er flag af datatypen Boolean, som jeg har sat = True, hvis der er sat betingelser af typen.

rA1, rB1 osv. er Range-variabler og kunne fx være A1:A8 og B1:B8. Ved at bruge Ranges og ".Address", kan mine betingelseskolonner have et dynamisk antal rækker. Man kan også skrive celleadresser adskilt af komma, men så ryger dynamikken.

Der er ikke spor i vejen for at have flere kolonnesæt med samme relation (1, 2 eller 3), der skal bare være en linje som ovenfor for hvert sæt.

Umiddelbart før jeg kalder Solver, sletter jeg evt. gamle betingelser og indsætter dem, som gælder aktuelt. De betingelser, som sættes under vejs, opbevarer jeg typisk i Arrays, og så bliver de sat ind på flg. måde, hvor rA1 er et Range og arARel1 er mit array:

Set rA1 = Range("A1")
'Udvid rA1 nedad med samme antal rækker som i Arrayet
Set rA1 = rA1.Resize(Ubound(arARel1))
'Kopiér indholdet af arARel1 til rA1
rA1.Formula = arARel1

Justerbare celler

Solver skal også vide, hvilke celler den må ændre på for at finde en løsning.

I kommandoen til Solver angives dette med celleadresser adskilt af komma, men hvis det kan være forskellige celler fra gang til gang, kan man bruge en streng-variabel, som indeholder celleadresserne (fx: "A27,A28,A29,F30").

I kaldet til Solver kan det se ud som følger, hvor "sAdjust" er strengvariablen med adresserne på de justerbare celler, og "sTarget" indeholder adressen på målcellen. "dTargetValue" er her en variabel for den eksakte værdi, vi ønsker for målcellen.

I stedet for en variabel (her dTargetValue) kan man selvfølgelig også skrive et tal, hvis det aldrig ændrer sig.

"MaxMinVal:=3" er standard for at finde en bestemt værdi. Ændrer man fra 3 til 1, vil Solver maksimere værdien, og ved "2" vil Solver minimere målcellens værdi. I stedet for at skrive tallet 1, 2 eller 3, kan det være en variabel, som er sat et andet sted i programmet.

SolverOk SetCell:=sTarget, MaxMinVal:=3, _
         ValueOf:=dTargetValue, ByChange:=sAdjust

Hvis man maksimerer eller minimerer værdien af en målcelle, skal "ValueOf:=xxxx" naturligvis ikke stå der.

Det er jo ikke givet, at man har brug for, at en målcelle får en bestemt værdi eller optimeres, men man kan ikke undlade linjen, fordi den indeholder adresserne på de justerbare celler.

I så fald kan man bruge en "dummy-målcelle". Skriv fx tallet 1 i celle X65000, angiv X65000 som målcelle og skriv "ValueOf:=1". Så er du ude over det.

Der er ikke spor i vejen for at stille betingelser til justerbare celler. Fx kan man angive et interval ved at bruge både en større end/lig med-betingelse og en mindre end/lig med-betingelse.

Man kan også have brug for, at en justerbar celle skal have en bestemt værdi, og så "justerer" Solver værdien til betingelsesværdien.

Solver-indstillinger

Ganske som når man bruger Solver manuelt, kan man med VBA angive indstillinger for problemløsningen. Det er sådan noget som max antal iterationer, tidsbegrænsning, præcisionskrav m.m.

Undlader man en parameter, vil Solver bruge default-værdien. Det kan fx se således ud:

solverOptions MaxTime:=32760, Iterations:=32760, _
Precision:=0.0000001, AssumeLinear:=False, _
StepThru:=False, Estimates:=1, Derivatives:=1, _
SearchOption:=1, IntTolerance:=2, Scaling:=True, _
Convergence:=0.0001, AssumeNonNeg:=False
'Endelig langt om længe kaldes Solver:
SolverSolve(False)

Den sidste linje, "SolverSolve(False)", vil få Solver til at poppe op med samme dialogboks, som kommer, hvis man bruger Solver manuelt.

iSolution = SolverSolve(True)

Alt afhængigt af udfaldet, vil Solver returnere en talværdi (integer), som man så "fanger" i sin variabel - her kaldet "iSolution". Værdien kan man så bruge til tilpasset aktion, som i eksemplet nedenunder.

Der er en del flere parametre, end vist ovenover, men det vil jeg springe over, og der er også visse forskelle fra version til version. Dog skal det med, at Solver som default går ud fra, at problemet er ikke-lineært.

Hele proceduren med Solver-kaldet

Nu har vi været igennem de væsentligste elementer af et Solver-kald, og det er på sin plads at vise en hel procedure, hvor elementerne er stykket sammen. Nogle af variablerne i flg. procedure er deklareret på modulniveau og har fået deres indhold tidligere.

Det gælder fx nogle af flagene (Booleanværdier), ranges og strengvariablen med justerbare celler.

Private Sub MixSolve()
'Definerer problemet og kalder Solver
Dim dPrecision As Double  'Variabel for Solver-præcision
Dim iSolution As Integer  'Solvers returværdi
Dim bSolved As Boolean    'Flag for løsning eller ej
Dim lTimes As Long        'Tæller
Dim sMsg As String        'Strengvariabel
Dim sTarget As String     'Strengvariabel for målcelleadresse

On Error GoTo ErrorHandle

'Skærmopdatering slås fra
Application.ScreenUpdating = False
Worksheets(2).Activate

'Det er vigtigt at resette Solver, så den
'ikke kører videre med gamle parametre.
SolverReset

'lRelation er her en Public variabel sat andet steds.
'Værdien afgør, om målcellen skal maksimeres,
'minimeres eller have en bestemt værdi.
'1 = max, 2 = min, 3 = bestemt værdi.
If lRelation = 0 Then lRelation = 3
If lRelation = 3 Then
   sTarget = Range("pctsum").Address
Else
   sTarget = Range("price").Address
End If

'=< betingelser
'bRel1, bRel2 og bRel3 er flag for, om der er
'sat relation 1, 2 eller 3-betingelser.
'Flagene er sat True/False tidligere.
'rA1, rB1, rA2, rB2 osv. er rangevariabler,
'for de betingelseskolonner, vi har lavet.
'(beskrevet højere oppe på siden).
If bRel1 Then
    solveradd cellref:=rA1.Address, _
              relation:=1, formulatext:=rB1.Address
End If

'Lig med (=) betingelser
If bRel2 Then
    solveradd cellref:=rA2.Address, _
              relation:=2, formulatext:=rB2.Address
End If

'>= betingelser
If bRel3 Then
    solveradd cellref:=rA3.Address, _
              relation:=3, formulatext:=rB3.Address
End If

'Kemiske betingelser
solveradd cellref:=rA4.Address, _
          relation:=2, formulatext:=rB4.Address

'lRelation 3 er standard
If lRelation = 3 Then
   'Målcelle, målværdi og justerbare celler
   SolverOk SetCell:=sTarget, MaxMinVal:=3, _
         ValueOf:=dTargetValue, ByChange:=sAdjust
Else  'I dette tilfælde: Minimér prisen (relation 2)
   SolverOk SetCell:=sTarget, MaxMinVal:=lRelation, ByChange:=sAdjust
End If

'Kravet til præcision. Hvis Solver ikke finder en løsning,
'slækkes kravet med en faktor 10, og vi prøver igen op
'til 6 gange.
dPrecision = 0.000000001

'Start løkken. Løkken kører, til der er fundet en løsning,
'eller til der har været prøvet 7 gange.
Do Until bSolved
   lTimes = lTimes + 1
   If lTimes = 7 Then
      MsgBox "Solver fandt ingen løsning " & vbNewLine & _
      "trods 6 forsøg med nedsat krav til præcision."
      Exit Do
   End If
   'Parametre til Solver
   solverOptions MaxTime:=32760, Iterations:=32760, _
   Precision:=dPrecision, AssumeLinear:=False, _
   StepThru:=False, Estimates:=1, Derivatives:=1, _
   SearchOption:=1, IntTolerance:=2, Scaling:=True, _
   Convergence:=0.0001, AssumeNonNeg:=False
   'Solver kaldes.   
   'Den følgende linje returnerer Solvers svar som
   'en værdi (integer), og vi tager tilpasset
   'aktion. Alternativet er at skrive:
   'SolverSolve(False), og så vil Solvers
   'dialog poppe op, som hvis man kørte Solver
   'Manuelt.
   iSolution = SolverSolve(True)
      
   Select Case iSolution
      Case 0
         bSolved = True
         If lTimes = 1 Then
            MsgBox "Solver fandt en løsning."
         Else
            If lTimes = 2 Then
               sMsg = " gang"
            Else
               sMsg = " gange"
            End If
            MsgBox "Solver fandt en løsning, da kravet" & vbNewLine & _
            "til præcision var reduceret " & lTimes - 1 & sMsg & "."
         End If
         Exit Do
      Case 1
         bSolved = True
         MsgBox "Solver har tilnærmet en løsning." & vbNewLine & _
         "Vær opmærksom på, at når løsningen" & vbNewLine & _
         "er tilnærmet, er det ikke altid den optimale løsning."
         Exit Do
      Case 2
         bSolved = True
         MsgBox "Solver kan ikke forbedre løsningen." & vbNewLine & _
         "Alle betingelser er opfyldt."
         Exit Do
      Case 3
         MsgBox "Solver stoppede, da grænsen for max"  & vbNewLine & _
         "antal iterationer blev nået."
         Exit Do
      Case 4
         MsgBox "Målcellerne konvergerer ikke. " & vbNewLine & _
         "Prøv eventuelt at sætte en (anden)" & vbNewLine & _
         "betingelse for flow eller totalproduktion."
         Exit Do
      Case 5
         'Solver fandt ikke en løsning.
         'Vi slækker kravet til præcision og prøver igen.
         dPrecision = dPrecision * 10
      Case 6
         'Følgende burde ikke kunne forekomme, medmindre
         'brugeren på en eller anden måde afbryder VBA
         MsgBox "Solver stoppet af brugeren"
         Exit Do
      Case 7
         'Det følgende burde ikke kunne lade sig gøre,
         'da en ikke-lineær model bruges ...
         MsgBox "Betingelserne for at bruge"  & vbNewLine & _
         "en lineær model er ikke opfyldt."
         Exit Do
      Case 8
         MsgBox "Opgaven er for stor."
         Exit Do
      Case 9
         MsgBox "Solver stødte på en fejlværdi" & vbnewline _
         "i en målcelle eller en betingelsescelle." & vbNewLine & _
         "Undertiden er det nødvendigt at lukke" & vbNewLine & _
         "Excel og starte forfra, men det kan" & vbNewLine & _
         "også være en kemisk betingelse, som ikke" & vbNewLine & _
         "kan opfyldes og derfor giver division med nul."
         Exit Do
      Case 10
         MsgBox "Solver stoppet, da tidsgrænsen blev nået."
         Exit Do
      Case 11
         MsgBox "Der er ikke hukommelse nok" & vbNewLine & _
         "til at løse problemet." & vbNewLine & _
         "Luk evt. nogle andre programmer og prøv igen."
         Exit Do
      Case 12
         MsgBox "Solver.dll bruges af et andet Excel-program."
         Exit Do
      Case 13
         MsgBox "Fejl i modellen."
         Exit Do
   End Select
Loop

BeforeExit:
Application.ScreenUpdating = True

Exit Sub
ErrorHandle:
MsgBox Err.Description & " Procedure MixSolve, module SolverCode"
Resume BeforeExit
End Sub

Det var så om at bruge Solver (Problemløser) med VBA. Hvis du downloader dette zip-komprimerede regneark, kan du studere en simpel applikation, som bruger VBA og Solver.

Eksemplet går ud på at blande råmaterialer til cement til en ønsket kemisk sammensætning. Det kan også lave den billigste blanding, som opfylder de givne betingelser.

(Priserne i regnearket er fuldstændig tilfældige tal, som du kan ændre, som du lyster!)

Matematisk er det sådan set bare vægtet forholdstalsregning og en ligning med X ubekendte.

Udover at bruge Solver, er der også eksempler på, hvordan man bruger Ranges, Arrays og UserForms til at få input fra brugeren. Programmet styres af brugeren via en skræddersyet popup-menu (højrekliksmenu).

Programmet er blot en smagsprøve på, hvordan man kan lave blandingsberegninger og bruge Solver med VBA makroer, men kernefunktionaliteten er OK og kan måske give inspiration til at løse andre opgaver.

Af hensyn til disse siders udenlandske læsere er alle kommentarer til koden skrevet på engelsk, og jeg har ikke orket også at skrive dem på dansk. Det må du leve med!

Vil du have mere detaljeret information, kan du finde den på solver.com eller hos Microsoft (links herunder).