Process and mass balance in Excel with VBA macros

Anybody here who hasn't tried to make a cicular reference in a spreadsheet? You know, e.g. B1 = A1 and A1 = B1 + 2. Neither Excel nor any other spreadsheet can handle circular references.

And in a way that is too bad, because in real life there are lots of "circular references," where an event has an impact on the thing that triggered the event.

If for instance you want to model an industrial process flow, where a part of the flow is returned to an earlier stage in the process, you face a problem that is very similar to "A1 = B1 + 2".

Matrix or array formulas may be the answer, however these pages focus on VBA macros, so let us forget sophisticated formulas and use the brute force of VBA instead.

By using VBA iteration you can also get an indication of how much time it will take to achieve the balance - more about this later.

There are many processes, where a part of the material flow is returnes to an earlier stage in the process, or where compounds evaporate and are carried back to an earlier stage as gases, where they condense at lower temperature.

Here is a very simplified example of how chloride behaves in a cement kiln. I didn't bother to make an English version of the diagram - "Forvarmer" means preheater, "Ovn" means kiln and "Fordampning" is evaporation.

Simplified model of chloride behaviour in a cement kiln

I model the proces (e.g. material flows up and down) and loop through the process X times, until there is a reasonable balance between input and output.

If you know the residence time of the plant, you can get a pretty good estimate of how much time you need from start up to the point where the process is in balance - just multiply the residence time with the number of program loops.

For instance that can be used to estimate how long it takes from a change in the feed to full impact on the final product or the concentration in process stage X.

I have made a spreadsheet with VBA macros to exemplify how to do this. You can download it here. It is zipped (compressed), and you unzip by right-clicking and selecting "Unpack" or whatever Windows suggests.

It is just a demo, but it can be applied to many processes, where continuous input and output reach a balance over time, and it doesn't matter if you produce sugar, mineral wool or cement.

Classes and object oriented programming

The VBA code uses classes (Class Modules). To many this sounds terribly complicated, however I do believe that the example will show the opposite, namely that by using classes and class collections, you can turn something (otherwise) pretty complicated into a fairly simple and structured program.

By "cloning" process stages as instances of a class you avoid keeping track of many, many variables. All process stages are classes in a class collection, so it is easy to add or remove a stage.

In the spreadsheet for download the input table is set up to emulate chloride behaviour in a cement kiln, but it can easily be changed to model other processes, and if need be the code can also be changed.

A class is like a cookie cutter, and you can have as many cookies or process stages as you like. Each class representing a process stage has some properties that are defined in the class.

In my example they are:

  • Continuous input/feed from outside to the process stage
  • Continuous bleed in % from the process stage
  • Material (or whatever) in % returned from a later stage
  • If this stage returns something to an earlier stage then which one?
  • Material flow to the next stage (or end product if it is the final stage)

In the process stage class the variables are declared like this:

Option Explicit
Public Key As String            'Key
Public sKey As String           'Sekundary key
Private mvarAlias As String     'The class' name/alias
Private mvarBack As Double      'Return recieved from a later stage
Private mvarBleed As Double     'Bleed (or loss) out of the process
Private mvarBleedPct As Double  'Bleed in %
Private mvarMass As Double      'How much there is in the stage
Private mvarInput As Double     'Continuous feed/input from outside
Private mvarReturn As Double    'Returned to a previous stage
Private mvarReturnPct As Double 'Returned to a previous stage in %
Private mvarFeed As Double      'Feed from previous stage
Private mvarToNext As Double    'Feed next stage (or product)
Private mvarReturnTo As Long    'If any return then to which one?

Notice that the variables are declared as "Private". In this program it doesn't matter, but in other scenarios it can provide security if only the class itself can access the data, and the class can act like a vault.

Having declared the variables we make some properties that are all public. The properties get and write values from/to the variables declared above. It looks like this:

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


No matter "where we are," we can now address the class' bleed by writing:

All.Item(2).dBleed = something

Where "All" is the class representing the process as a whole with all the process stages in a class collection. Here we address "Item(2)", i.e. class or process stage nb. 2 in the collection.

The same class could also be addressed by its name (secondary key), e.g.:

All.Item("Stage2").dBleed = something

However it is easier to use the item numbers, when you loop through a collection.

A power of properties is that you can use them for automatic action. I don't use it in this process balance, but if for instance a property gets a new value, it can trigger something, e.g.:

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

'And now the action:
dReturn = dReturn + 1
End Property

You can have as many process stages as there are columns (-2) in a spreadsheet, and all process stages lie as classes in a class collection that represents the process as a whole, which - ahem - could be called "the upper class".

The continuous feed, bleed and return (sending back) in % for each stage is read from a table, and then the program loops through the process, until the difference between input and output is less than a user defined percentage (e.g. 0.0001 %).

If (the defined) balance isn't achieved after 2001 loops, the program aborts. Being iterative the program will never achive 100% balance between input and output - no matter how many loops there will always be a minute difference.

When the program stops, it inserts a table in the spreadsheet. Here you can see how many times the process was looped and concentration/mass in each stage.

Input to the program is keyed into a table:


To get a realistic model you normally need some measurements (or experience based values) for flows and/or concentrations in the different process stages, but the model can also be used to simulate what will happen, if you change something.

The spreadsheet shows the power of using classes (object oriented programming). To make the same thing without using classes, you would have to keep track of a lot more variables, and it would be much more difficult to add or remove process stages.

If you want to study the VBA code in the spreadsheet, things are executed in the following sequence:

  1. When the user clicks the button "Start" on the worksheet Setup, the macro "TakeOff" in Module1 will execute. It makes a new instance of the class "clAll" that represents the process as a whole.
  2. This automatically calls the class' initialize procedure: Private Sub Class_Initialize(), which calls the procedure FindStage. This and all of the following code lies in the class module clAll.
  3. The procedure FindStage reads the parameters from the table in the sheet Setup.
  4. The function CheckParameters checks if the parameters are ok.
  5. For each new process stage we call the function Add: Public Function Add(Key As String, Optional sKey As String) As clStage
  6. The function adds an instance of the clStage class to the class collection.
  7. When the class has been added, the properties' values are read from the table on Setup.
  8. Once all process stages have been added as classes the procedure StartProces is called.
  9. StartProces contains the loop that loops through the process until equilibrium has been achieved.
  10. When the loop stops, the procedure WriteTable is called. It writes a table with data for the process in equilibrium on the worksheet "Table".

When the code says "Me." followed by something, it just tells you that it is the class clAll addressing one of its own properties - hence the "Me".

There is no advanced math at all - it is simply adding, subtracting and percent calculation repeated X times.

Have fun!