Macro excel 2007 voor simulaties

[Verwijderd]

hallo iedereen,

Voor mijn masterproef heb ik een simulatie van een productie systeem in excel gemaakt. Nu moet ik de werkmap zeer veel herberekenen om resultaten te verzamelen en deze dan onderwerpen aan statistische testen, om zo uiteindelijk conclusies te kunnen formuleren.

Nu wil ik het herberekenen en wegschrijven van de informatie automatiseren door middel van een macro, omdat ik anders ettelijke uren gewoon op F9 zit te drukken en copy/paste zit te doen...

Dit is de code van mijn macro op heden:

Sub Macropush()
'
' Macropush Macro
'

'
Calculate
Range("I2:K2").Select
Selection.Copy
Sheets("test").Select
Range("B2").Select
ActiveSheet.Paste

End Sub

dit is gewoon één maal berekenen, cellen I2 tot K2 van sheet x wegschrijven naar sheet y.
nu zou ik graag hebben dat via een betere macro de werkmap 15 maal wordt herberekend en bij elke berekening de info in cellen I2:K2 op sheet x worden weggeschreven naar sheet y. Het wegschrijven begint bij cel B2, maar deze mag natuurlijk niet overschreven worden bij een herberekening, dus er moet altijd een rij opgeschoven worden zodat ik na 15 herberekeningen 15 rijen heb met informatie.

Ik heb zelf geen kaas gegeten van vba en heb geen idee hoe dit moet, er is me echter wel gezegd dat het zou moeten lukken...

alle ideeën zijn welkom!!

Alvast bedankt^^!!

[pedorus]

Dit kan zelfs zonder macro's, maar met calculatie op manual, en iteraties op 1. In 1 cel maak je de mogelijkheid om te resetten, genaamd reset. In een andere cel, genaamd iteration, hou je de iteratie bij met =IF(reset,0,iteration+1). Stel dat je dan de resultaten van een cel genaamd simresult wil bijhouden, dan kun je in cel D1 een doortrekbare formule als "=IF(iteration=ROW(D1),simresult,D1)" gebruiken. Als je geen zin hebt om F9 even in te houden, dan kun je daar natuurlijk een macro'tje voor gebruiken (met 1 for-loop).

offtopic:
NB: gebruik [code=vb] voor code

[ Voor 3% gewijzigd door pedorus op 26-04-2010 22:03 ]

[Verwijderd]

mja een 1 simulatie duurt +- 2 minuten en ik moet er ongeveer 4000 doen, dus even f9 indrukken is zachtjes uitgedrukt haha.
bedankt wel voor de tips, hoe zou zo een 1 for-loop er uitzien? Ik ben er zeker van dat het niet meer dan 4 regeltjes code kan zijn om het voor mekaar te krijgen, maar weet niet hoe ik de code moet schrijven

[pedorus]

Gewoon [google=vba for-loop] Komop, die drie regels code moet je voor je eindwerk toch wel kunnen verzinnen.. En anders leg je toch gewoon een gewichtje op je F9-toets?

Overigens zou dat dus 6 dagen werk zijn?! Misschien dat 4000 iteraties wat veel is... Het kan helpen om Excel onzichtbaar te maken of geen schermupdates te doen, zodat er geen tijd verloren gaat aan het hertekenen van zaken (in een macro kun je het rekenwerk tussen Application.ScreenUpdating = False en Application.ScreenUpdating = True zetten).

[ Voor 10% gewijzigd door pedorus op 26-04-2010 23:22 ]

[Verwijderd]

het zijn gewoon zoveel simulaties omdat ik met verschillende parameters moet werken, dus elke keer 15 simulaties en dan parameters aanpassen en dan terug. Komt er bij dat ik voor een bepaald productiesysteem bij het aanpassen van een bepaalde parameter ik nog een klein ding moet aanpassen in cellen (dit moet ik 10 keer doen omdat ik deze parameter 10 keer moet aanpassen). het zal dus uiteindelijk ook niet één macro worden, maar een hoop verschillende. Reden dat ik het hier vraag is dat ik met enorm veel tijds te kort zit. examens beginnen over een week en er gaat nog weinig tijd over schieten om alles te kunnen simuleren. Heb er veel problemen mee gehad (ben de eerste op school die iets zoals dit doet), anders had ik zeker en vast al vba code zitten opzoeken haha. Nuja ik weet nu ongeveer in welke richting ik moet zoeken, bedankt vr de moeite!!

[F_J_K]

offtopic:
Te laat om zelfs nog iets mee te doen, maar dan tenminste iets om aan je school mee te geven: Excel is niet het juiste tool voor dergelijke simulaties. Kijk eens wat andere faculteiten (exacte wetenschappen, maar ook bijv. logistiek) gebruiken.

Ik wil je ook even waarschuwen tegen het gebruiken van de resultaten als je geen kennis hebt van het tool: weet zeker dat de code doet wat je in je hoofd hebt. Lees alsjeblieft eerst enkele tutorials. Dan zal je merken dat dingen als loopjes basisbeginselen zijn van VBA, en zo ongeveer elke programmeertaal.

Maar inderdaad, een paar FOR loopjes nadat je screenupdating uit hebt gezet en je bent een eind op weg. Maar zoals gezegd, doe jezelf een lol en zorg dat je zeker weet dat je de logica er achter snapt. Nog wat optimalisatie: gebruik geen copy ... paste, maar bijvoorbeeld Cells(1, 2).Formula = Cells(1, 1).Formula

Succes!

[Verwijderd]

@F_J_K

mja geld speelt een enorme rol... simulatie software kost een bom geld en zal wss niet aanwezig zijn in de meeste bedrijven, excel echter wel. Het is daarom dat me gevraagd is dit te proberen in excel (ook omdat er niets anders voorhanden was haha). Tevens kloppen mijn test resultaten, want heb papers zitten nabootsen en mijn data komt steeds mooi uit tussen een bepaald betrouwbaarheidsinterval.

Nu denk ik dat je bedoelt dat ik de logica en mechanismen moet snappen 8er het vba gebeuren ivm de loops? Als ik een geschreven stuk code zie kan ik er meestal wel aan uit wat het doet, maar het zelf formuleren heb ik last mee. Heb nooit les gehad in vba of andere talen en om het nu snel snel te doen ontbeert me de tijd . Heb hier al wel enkele mooie suggesties gehad en zal op het net wat surfen zodat ik het (hopelijk) in een werkend stukje code kan gieten. het zal echter geen mooi geoptimaliseerd gebeuren zijn, maar tamelijk omslachtig voor een doorwintert (of zelfs beginnende) programmeur ^^.

bedankt nog voor de info!

[Verwijderd]

Dit heb ik ervan gemaakt, lijkt te werken haha. Wss een vrij pijnlijk zicht voor de meeste hier, tips zijn welkom

code:

Sub Macro6()
'
' Macro6 Macro
Application.ScreenUpdating = False
For a_counter = 1 To 15
   i = a_counter
    Calculate
    Sheets("results push").Select
    Range("I2:K2").Select
    Selection.Copy
    Sheets("Blad1").Select
    Cells(i, 1) = i
   Cells(i, 1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
      Next a_counter
    If a_counter = 16 Then Application.ScreenUpdating = True
        
End Sub

[F_J_K]

ieuw

Naah. Als het werkt dan werkt het, het kan veel mooier en sneller maar dat is iets voor later. Ik zal me beperken tot een 'zeur-'opmerking: de laatste If a_counter = 16... werkt wel, maar IMHO meer door een 'bug/denkfout' van VBA (niet jouw fout ). Immers hoort a_counter nooit meer dan 15 te worden. Hoe dan ook is het altijd 15+1, dus doe maar gewoon Application.ScreenUpdating = True

[Lustucru]

F_J_K schreef op dinsdag 27 april 2010 @ 22:09:
Immers hoort a_counter nooit meer dan 15 te worden.

Ieeuw

grenzeloos offtopic:
Zo vreemd is het toch niet? For next implementeren als While-Lus?

code:

For i = 1 to 10
Next
~~
i=1
While i <=10
i=i+1
Wend

C# maakt trouwens dezelfde denkfout

/grenzeloos offtopic.

[Verwijderd]

Een poging tot optimalinationalisatie dan:

Visual Basic .NET:

Sub Macro6()
    Dim I As Long
    Dim WSResults As Worksheet
    Dim WSBlad1 As Worksheet
    Dim ResultsRange As Range
    Dim Blad1Range As Range
    
    Application.ScreenUpdating = False

    Set WSResults = ThisWorkbook.Sheets("results push")
    Set WSBlad1 = ThisWorkbook.Sheets("Blad1")
    Set ResultsRange = WSResults.Range("I2:K2")
    
    For I = 1 To 15
        Calculate
        With WSBlad1
            Set Blad1Range = .Range(.Cells(I, 1), .Cells(I, 3))
        End With
        Blad1Range.Value = ResultsRange.Value
    Next I

    Application.ScreenUpdating = True
        
End Sub

Werkt iets sneller. Schiet u maar; ik ben volledig auto-didact in VBA

[pedorus]

Tsja, je kan het ook herschrijven naar iets als:

Visual Basic:

Sub Simulate()
    Dim results As Range, target As Range, i As Integer
    Set results = ['results push'!I2:K2]
    Set target = [blad1!a1:c1] 'first target

    Application.ScreenUpdating = False
    On Error GoTo rescreen

    For i = 0 To 14
        Calculate
        target.Offset(i, 0).Value2 = results.Value2
    Next

    Application.ScreenUpdating = True
    Exit Sub

rescreen:
    Application.ScreenUpdating = True
    Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext
End Sub

Maar dan nog valt de referentietechniek op (beter is gedefinieerde namen en sheet code names).

Voor de rest nog wat overige opmerkingen:

• De opmerking van F_J_K over for-loops die eindigen bij 15(+1) zou tot inefficiënte code leiden, dubbel checken op condities, inconsistentie met andere talen, enz. Het lijkt me trouwens dat je eigenlijk variabelen die binnen een loop horen sowieso niet buiten de loop moet gaan benaderen, en dan boeit het verschil niet eens. Wie maakt nu eigenlijk de denkfout hier?
• En ook de opmerking over simulatieprogramma's kan ik niet 100% plaatsen. Excel wordt volgens mij best vaak ingezet voor dit soort dingen (met add-ins zoals YASAI). Welke tools dacht je dan aan? Voor 'zelf schrijven' is wat meer programmeerkennis nodig dan TS heeft.
• Ik gebruik zelden meer With omdat dit onduidelijk kan zijn, en een desnoods eenletterige variabele net zo goed werkt. Als je al een variabele-naam ervoor hebt is het al helemaal gek.
• Ik vind naamgeving met kleine beginletters, zonder hongarische notatie, voor lokale variabelen goed werken.

[ Voor 7% gewijzigd door pedorus op 28-04-2010 02:11 . Reden: nu met foutafhandeling (zie onder), zonder verkeerde copy ]

[Lustucru]

^^^met stupid, enne
Als je ergens screenupdating uit zet, neem dan ajb een screenupdating=true op in je foutafhandeling.
Niks zo irritant als vastlopende code en dat je overblijft met een applicatie die zijn schermen niet meer bijwerkt
* Lustucru doet vrolijk mee aan het mierenmisbruik

[Verwijderd]

mijn code tov die optimalisaties die jullie voorstellen (waarvoor onmetelijk veel dank ) geeft dat echt veel snellere berekeningen? voor 15 iteraties hangt excel nu zo een 10min vast
Ik ga nu proberen of ik ook mijn parameters niet automatisch kan aanpassen in een andere sheet en de loop opnieuw kan beginnen.

en@pedorus: Ik ben ook van die mening dat excel zeer veel gebruikt wordt in ondernemingen. Voor zeer complexe simulaties is het wss niet krachtig genoeg, maar niet alles is zo ingewikkeld in het dagelijkse bedrijfsleven. Wat meer RAM geheugen zou precies wel geen kwaad kunnen, want vanaf 20 000 rijen verslikt mijn pc zich precies (waar is de tijd dat een 3 gieg ram pc nog niet eens bestond haha)
@Lustucru
Nooit van mijn leven al iets in vba geschreven, dus sta allemaal niet stil bij die dingen. Wist nog niet eens dat foutafhandeling erbij moest begin wel al het 1 en het ander te leren^^ dus snap nu wel wat je bedoelt

EDIT:

heb poging gedaan om de parameter van de interarrival rate van mijn jobs in het systeem automatisch te laten aanpassen. Overigens is blad1 veranderd naar results en de sheet waar de resultaten moeten worden uitgehaald is veranderd van results naar results_push.
het geeft nog een fout in de vorm van fout 91: object variabele of blockvariabele with nog niet ingesteld.
De bedoeling is dat een teller (bij mij j) met een 0.1 omhoog gaat beginnende bij 0.4 en eindigend bij 0.9. de formule die bij input moet komen is 1/ "de teller" (dus 1/0.4,1/0.5,...). Verder moeten de resultaten altijd in andere kolommen worden weggeschreven om per parametersetting de resultaten bij te houden, hier gebruik ik de teller k voor.

anyhows hier is de code, moet naar school nu straks verder werken

code:

Sub simulationPush()
    Dim I As Long
    Dim j As Single
    Dim k As Integer
    Dim WSresults_push As Worksheet
    Dim WSresults As Worksheet
    Dim WSinput As Worksheet
    Dim Results_pushRange As Range
    Dim resultsRange As Range
    Dim parameter_arrivalr As Range
    
    Application.ScreenUpdating = False

    Set WSresults_push = ThisWorkbook.Sheets("results push")
    Set Results_pushRange = WSresults_push.Range("I2:K2")
    Set WSinput = ThisWorkbook.Sheets("input")
    Set parameter_arrivalr = WSinput.Range("z11")
    For j = 0.4 To 0.9 Step 0.1
    For I = 1 To 15
    For k = 0 To 15 Step 3
    WSinput.Range("z11") = 1 / j 'POISSON arrivalrate parameter bepalen
        Calculate
        With WSresults
            Set resultsRange = .Range(.Cells(I, 1 + k), .Cells(I, 3 + k))
        End With
        resultsRange.Value = Results_pushRange.Value
    Next
    Next I
    Next
    Application.ScreenUpdating = True
        
End Sub

[ Voor 62% gewijzigd door Verwijderd op 28-04-2010 11:30 ]

[F_J_K]

FOR-loopje-tellers buiten de loop gebruiken in de wetenschap dat die max+1 is: ik zeg niet dat het niet in 'elke' taal werkt, ik zeg alleen dat het IMHO niet iets is dat je wilt aanleren te gebruiken. En bedoel dus hetzelfde als pedorus

Simulatietools: inderdaad wordt Excel er veel voor gebruikt. Lekker makkelijk, zowel voor simpele dingen als hele grote complexe. De licentie is er al en iedereen weet hoe de basics werken. Maar ik heb te veel tig-tabbladen-Excelsheets gezien waar toch ergens een denkfoutje wordt gemaakt in sheetversie 500. Excel heeft te weinig debugmogelijkheden om dat op te merken. Het kan, ik heb het ook gedaan@universiteit, maar ik houd elke keer mijn hart vast. Daarom zou ik bij regelmatig gebruik zelf voor een dedicated tool gaan, take your pick: SGPSS, Matlab, Arena, heel Wikipedia: List of discrete event simulation software. Maar dat is inderdaad allemaal niet relevant voor de topicstarter, die moet hier voor zijn eigen klus geen aandacht aan schenken. Ten eerste door extra kosten (ik ging er van uit dat er op de universiteit al wel ergens licenties lagen) en benodigde kennis, ten tweede omdat de opdracht juist was hier Excel voor te gebruiken, dus negeer mijn gezeur

Ontopic:
de VBA-code is hier niet wat het traag maakt als een handmatige run een minuut duurt, dat verandert er namelijk niet door. De VBA-code voor snelheid optimaliseren gaat niet veel helpen.

[ Voor 0% gewijzigd door F_J_K op 28-04-2010 11:00 . Reden: Freudiaanse typo :+ ]

[Verwijderd]

je hebt 100% gelijk F_J_K, was eerst begonnen met trail van ARENA, omdat me gezegd was dat ik een volledige licentie kon krijgen. Al zeer veel zitten testen en kreeg na 2 maanden te horen dat het toch niet ging lukken en ik op excel moest overgaan. Ik heb zitten vloeken check ook even update van me hierboven, als je even je hersenspan wil "breken" en niets te doen hebt^^
wel leuk dat mensen hier degelijk wel helpen, ik hoop in de toekomst iets terug te kunnen doen^^

[F_J_K]

Aha

Naast toevoegen van de eerder genoemde errorhandling alleen wat details: gebruik geen hoofdletter i (ik las bijvoorbeeld een L ;-) ), de volgorde van i,j,k is ook wat raar maar dat laat ik zo. Omdat i tot 15 gaat is integer ipv. long voldoende, scheelt weer een whopping 2 bytes. Voor de vorm en vast ook minieme optimalisatie .Value2 toegevoegd bij de Range (daar zou ik trouwens zelf Cells(11,26) gebruiken ipv Range("z26"), maar dat terzijde). Check alle aanpassingennatuurlijk voor je ze overneemt, garantie tot de deur webserver
Maar zoals gezegd zal optimalisatie van de code zelf IMHO niet echt helpen, behalve als het aantal iteraties omlaag kan. Nu doe je nu 6*15*6=540 keer Calculate, dat kan vast niet lager.

Visual Basic .NET:

Sub simulationPush()
    Dim j as single
    Dim i, k as integer
    Dim WSresults_push As Worksheet
    Dim WSresults As Worksheet
    Dim WSinput As Worksheet
    Dim Results_pushRange As Range
    Dim resultsRange As Range
    Dim parameter_arrivalr As Range
    
    On Error GoTo rescreen

    Application.ScreenUpdating = False

    Set WSresults_push = ThisWorkbook.Sheets("results push")
    Set Results_pushRange = WSresults_push.Range("I2:K2")
    Set WSinput = ThisWorkbook.Sheets("input")
    Set parameter_arrivalr = WSinput.Range("z11").value
    For j = 0.4 To 0.9 Step 0.1
        For i = 1 To 15
            For k = 0 To 15 Step 3
                WSinput.Range("z11").value2 = 1 / j 'POISSON arrivalrate parameter bepalen
                Calculate
                With WSresults
                    Set resultsRange = .Range(.Cells(i, 1 + k), .Cells(i, 3 + k))
                End With
                resultsRange.value2 = Results_pushRange.value
            Next k
        Next i
    Next j
    Application.ScreenUpdating = True

    Exit Sub
    rescreen:
        Application.ScreenUpdating = True
        Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext
End Sub

[pedorus]

Ik snap het niet helemaal, aangezien je eigenlijk alleen de laatste waardes van j opslaat als ik het goed zie? Bedoel je niet meer zoiets?

Visual Basic:

    Set parameter_arrivalr = WSinput.Range("z11")
    For k = 1 To 6
        'Hier wil je waarschijnlijk eigenlijk een 'seed' zetten, zodat resultaten
        'beter vergelijkbaar zijn. Kan niet bij rand(), wel bv. met rnd(-1)/randomize 1
        For i = 1 To 15
            parameter_arrivalr = 1 / (k / 10 + 0.3) '1/(0.4 to 0.9 step 0.1)
            Calculate
            Set resultsRange = WSresults.Range(WSresults.Cells(i, k * 3 - 2), _
                                               WSresults.Cells(i, k * 3))
            resultsRange.value2 = Results_pushRange.value2
        Next i
    Next k

[Lustucru]

Verwijderd schreef op woensdag 28 april 2010 @ 08:42:[...]
Overigens is blad1 veranderd naar results en de sheet waar de resultaten moeten worden uitgehaald is veranderd van results naar results_push.het geeft nog een fout in de vorm van fout 91: object variabele of blockvariabele with nog niet ingesteld.[...]

code:

Sub simulationPush()
 [...]
    Set WSresults_push = ThisWorkbook.Sheets("results push")
    Set Results_pushRange = WSresults_push.Range("I2:K2")
    Set WSinput = ThisWorkbook.Sheets("input")
    Set parameter_arrivalr = WSinput.Range("z11")
  [...]
        With WSresults
            Set resultsRange = .Range(.Cells(I, 1 + k), .Cells(I, 3 + k))
        End With

Vba denkt niet, maakt geen denkfouten , maar is ook niet paranormaal begaafd.
Hoe moet de code weten wat jij bedoelt met WSresults?

aka: als je bij een foutmelding op foutopsporing klikt wordt keurig de regel gehighlight waar de fout optreedt. Door met de muis over variabelen te hooverne kun je die vars onderzoeken, of je zet verdachte variabelen in de watch.

[Verwijderd]

pedorus schreef op woensdag 28 april 2010 @ 14:05:
Ik snap het niet helemaal, aangezien je eigenlijk alleen de laatste waardes van j opslaat als ik het goed zie? Bedoel je niet meer zoiets?

Visual Basic:

    Set parameter_arrivalr = WSinput.Range("z11")
    For k = 1 To 6
        'Hier wil je waarschijnlijk eigenlijk een 'seed' zetten, zodat resultaten
        'beter vergelijkbaar zijn. Kan niet bij rand(), wel bv. met rnd(-1)/randomize 1
        For i = 1 To 15
            parameter_arrivalr = 1 / (k / 10 + 0.3) '1/(0.4 to 0.9 step 0.1)
            Calculate
            Set resultsRange = WSresults.Range(WSresults.Cells(i, k * 3 - 2), _
                                               WSresults.Cells(i, k * 3))
            resultsRange.value2 = Results_pushRange.value2
        Next i
    Next k

zie dat je gelijk hebt, had eerst niet goed door wat je deed, maar ga de aanpassingen eens doorvoeren. Op hoop van zegen^^

UPDATE

Het werkt Pedorus, bedankt voor de input (iedereen trouwens^^)!!! Ik heb net vernomen dat ik iets minder parameters moet gebruiken om mijn stelling te bewijzen, dus ga er dadelijk invliegen^^

[ Voor 132% gewijzigd door Verwijderd op 29-04-2010 20:26 ]