RSS

Proces- og massebalance i Excel med VBA

Hvem har ikke prøvet at lave en cirkulær reference i et regneark? Altså fx B1 = A1 og A1 = B1 + 2. Det kan hverken Excel eller andre regneark finde ud af.

Og det er lidt en skam, for i virkelighedens verden er der masser af "cirkulære referencer," hvor en hændelse virker tilbage på det, som udløste hændelsen.

Hvis du fx vil modellere et industrielt procesforløb, hvor en delstrøm returneres til et af de tidligere procesafsnit, render du ind i problemer, som er helt analoge med indledningens A1 = B1 + 2.

Matrixformler kan være løsningen, men matricer indgik ikke i mit pensum, så jeg bruger VBA-makroer.

Der er masser af processer, hvor noget af materialestrømmen returneres til et tidligere procesafsnit "for at få en tur mere," eller hvor stoffer går på gasfase, hvor der er varmt, for at udkondensere igen, hvor der er koldere.

Her et meget forsimplet eksempel på hvordan klor cirkulerer i en cementovn:

Forenklet model af klorstrømme

Jeg laver en model af processen (fx materialestrømme på kryds og tværs), og med en løkke gennemløbes processen X gange - fx til der er balance mellem input og output, og processen har fundet sin ligevægtstilstand.

Hvis man kender gennemløbstiden i anlægget, kan man så også beregne, hvor lang tid der går fra et anlæg starter, til der efter X gennemløb er opnået nogenlunde balance.

Det kan man fx bruge til at vurdere, hvor lang tid der går, fra man laver en ændring, til ændringen er slået helt igennem på slutproduktet eller koncentrationen i procesafsnit X.

Da disse sider handler om netop VBA-makroer, har jeg selvfølgelig lavet et regneark, som illustrerer, hvordan man kan gøre. Regnearket kan du downloade her. Det er zip-komprimeret og pakkes ud ved at højreklikke og vælge "Pak alle ud," eller hvad Windows nu foreslår.

Selvom det bare er en demo, kan det bruges på mange processer, hvor løbende input og output når en balance over tid, og så er det ligegyldigt, om man producerer flormelis, mineraluld eller cement.

Klasser og objektorienteret programmering

VBA-koden benytter sig af klasser (Class Modules), og selvom dette for mange er grænseoverskridende langhåret, tror jeg faktisk, at eksemplet formår at vise, hvordan netop klasser og klassesamlinger (class collections) kan forvandle noget ellers meget indviklet til et relativt enkelt og struktureret program.

Ved at "klone" procesafsnit som eksemplarer af en klasse undgår man at holde styr på et utal og ukendt antal variable. Alle procesafsnit ligger så som klasser i en klassesamling.

I regnearket, som du kan downloade, er inputtabellen sat op til en klorbalance på en cementovn, da det nu er noget af det, jeg arbejder med til daglig, men det kan nemt bruges til andre processer uden at ændre i koden, og koden kan nemt modificeres, hvis det skal være.

En klasse er som en småkageform, og man kan lave lige så mange småkager eller procesafsnit, som man vil. Hver klasse, som repræsenterer et procesafsnit, har nogle egenskaber, som er defineret i klassens properties.

I mit eksempel er det:

  • Løbende input/fødning udefra til det pågældende procesafsnit
  • Løbende bleed i % fra procesafsnittet
  • Returnering af materiale (eller whatever) i % til/fra et tidligere procesafsnit
  • Hvilket andet procesafsnit der (evt.) returneres til
  • Materialestrøm til næste procesafsnit (eller slutprodukt hvis det er sidste procesafsnit)

I procesafsnit-klassens kode deklareres klassens variabler således:

Option Explicit
Public Key As String            'Nøgle
Public sKey As String           'Sekundær nøgle
Private mvarAlias As String     'Klassens navn/alias
Private mvarBack As Double      'Retur modtaget fra senere procestrin
Private mvarBleed As Double     'Evt. bleed ud af processen
Private mvarBleedPct As Double  'Evt. bleed ud af processen i %
Private mvarMass As Double      'Hvor meget der er i afsnittet
Private mvarInput As Double     'Evt. løbende input udefra
Private mvarReturn As Double    'Evt. Retur til tidligere procestrin
Private mvarReturnPct As Double 'Evt. Retur til tidligere trin i %
Private mvarFeed As Double      'Fra forrige afsnit
Private mvarToNext As Double    'Videre til næste trin eller produkt
Private mvarReturnTo As Long    'Hvis noget retur, så til afsnit ??

Bemærk at variablerne her er defineret som "Private". Lige i dette program betyder det ikke noget, men i andre sammenhænge kan det fx give datasikkerhed, at de kun kan tilgås af klassen selv og således er "indkapslede".

Efter deklarationen sætter vi nogle properties, som alle er Public, og som henter eller skriver deres værdier fra/til de variabler, vi deklarerede ovenfor. Det ser således ud:

Public Property Get sAlias() As String
sAlias = mvarAlias
End Property

Public Property Let sAlias(ByVal vData As String)
mvarAlias = vData
End Property

Public Property Get dInput() As Double
dInput = mvarInput
End Property

Public Property Let dInput(ByVal vData As Double)
mvarInput = vData
End Property

Public Property Get dBleed() As Double
dBleed = mvarBleed
End Property

Public Property Let dBleed(ByVal vData As Double)
mvarBleed = vData
End Property

Osv.

Nu kan vi hvor som helst adressere fx klassens bleed ved fx at skrive:

Alt.Item(2).dBleed = et eller andet

Hvor "Alt" er den klasse, som repræsenterer hele processen og som har alle procesafsnittene i en klassesamling (class collection). Her adresserer vi "Item(2)", dvs. klasse nr. 2 i samlingen.

Samme klasse kunne også adresseres med dens navn (sekundære nøgle), fx:

Alt.Item("Afsnit2").dBleed = et eller andet

Men det er selvfølgelig nemmere at bruge klassernes numre i samlingen, når man gennemløber dem med en løkke.

En styrke ved properties er, at man kan lægge aktivitet ind i dem. Jeg bruger det ikke i mit eksempel, men når der fx skrives en ny værdi til en property, kan det automatisk udløse en handling. Fx:

Public Property Let dBleed(ByVal vData As Double)
mvarBleed = vData

'Og nu handlingen:
dReturn = dReturn + 1
End Property

Man kan have lige så mange procesafsnit, som der er kolonner (-2) i et regneark. Alle procesafsnit ligger som klasser i en klassesamling (class collection) i en anden klasse, som repræsenterer hele processen, og som man vittigt kunne kalde "overklassen".

Den løbende fødning samt bleed og returnering i % for hvert procesafsnit indlæses fra en tabel, og så gennemløbes processen, indtil forskellen mellem input og output er mindre end en brugerdefineret procent (fx 0,0001 %).

Hvis balancen ikke er opnået efter 2001 gennemløb, stopper løkken.

Når programmet stopper, indsættes en tabel i regnearket, hvor man kan se, hvor mange gange løkken blev gennemløbet samt koncentration/mængde i hvert procesafsnit.

Input til programmet indtaster man i en tabel:

Tabel

For at få en realistisk model, skal man normalt have nogle målinger eller erfaringstal for strømme og/eller koncentrationer i de forskellige procesafsnit, men modellen kan selvfølgelig også bruges til at simulere, hvad der sker, hvis man ændrer noget.

Regnearket demonstrerer styrken ved at bruge klasser (objektorienteret programmering). Skulle man lave det samme uden brug af klasser, skulle man holde styr på et utal af variabler, og det ville være meget sværere at tilføje eller fjerne procesafsnit.

Hvis du vil studere VBA-koden i regnearket, eksekveres den som følger:

  1. Når brugeren trykker på knappen "Start" på fanebladet Setup, køres makroen "TakeOff" i Module1. Den laver et nyt eksemplar af klassen "clAlt," som repræsenterer hele processen.
  2. Det kalder automatisk klassens initialize procedure: Private Sub Class_Initialize(), som kalder proceduren FindAfsnit. Dette og alt det følgende er kode i klassemodulet clAlt.
  3. Proceduren FindAfsnit indlæser parametrene fra tabellen på fanebladet Setup.
  4. Funktionen CheckParameters tjekker, om parametrene er ok.
  5. For hvert nyt procesafsnit kaldes funktionen Add: Public Function Add(Key As String, Optional sKey As String) As clAfsnit
  6. Den tilføjer samlingen et eksemplar af klassen clAfsnit, som repræsenterer procesafsnittet.
  7. Når klassen er føjet til samlingen, indlæses tabellens værdier i klassens tilsvarende properties.
  8. Når alle procesafsnit er føjet til vores class collection, kaldes proceduren StartProces.
  9. StartProces indeholder løkken, som gennemløber processen, til den er i balance.
  10. Når løkken afbrydes, kaldes proceduren SkrivTabel, som indsætter en tabel med data for balancetilstanden på fanebladet Table.

Når der i koden står "Me." fulgt af et eller andet, er det bare udtryk for, at det er klassen clAlt, der adresserer en af sine egne properties - derfor "Me".

Der er ikke noget hokuspokus-matematik ved selve procesgennemløbet - det er bare at lægge til, trække fra og så lidt procentregning.

God fornøjelse!

Relateret