Learn to Code with Tas and Excel Lesson 3 – Tas3D Zone Writer pt 2

Learn to Code with Tas and Excel Lesson 3- Tas3D Zone Writer pt 2

In the last lesson, we created a macro that added zone names from an Excel spreadsheet to a 3D modeller file. Specifically, it:

  • Opened the 3D modeller
  • Opened an existing Tas3D file
  • Added a new zone group
  • Read the names of zones from the spreadsheet and added them as new zones to the new zone group
  • Saved and closed the file

In this lesson, we’ll continue where we left off and add some extra functionality to our macro. We’d like to extend our macro to:

  • Check for duplicate zone names
  • Sort the new zones into zone groups
  • Read the existing zones in the file

 So lets dive in.

Before we can start modifying our macro from the previous lesson to deal with duplicate zones and zone sets, we need to add a couple of columns to our spreadsheet so we’re on the same page:

We need to add a heading to column B so we can use it for zone sets, another heading for column C so the macro can report whether it added the zone, and some sample zone names and zone set names.

Checking for duplicate zone names

To check for duplicate zone names, we’ll write a function that returns a Boolean (True/False) depending on whether a Tas3D file already contains a zone with that name. 

Lets take a look at the script for our function:

				
					Function ZoneExists(name As String, doc As TAS3D.T3DDocument) As Boolean
    
    'declare variables for our loop
    Dim curZoneIndex As Integer
    Dim curZone As TAS3D.Zone
    
    'set the initial zone index to 1
    curZoneIndex = 1
    
    'go through each zone in the building
    While Not doc.Building.GetZone(curZoneIndex) Is Nothing
    
        'set the curZone reference variable to the current zone
        Set curZone = doc.Building.GetZone(curZoneIndex)
        
        'if the zone has the name we are looking for, return true
        If curZone.name = name Then
            ZoneExists = True
            Exit Function
        End If
        
        'increment the zone index so we look at the next one in the file
        curZoneIndex = curZoneIndex + 1
    Wend
    
    'if we have checked all the zones and none match the name we are looking for, return false
    ZoneExists = False

End Function
				
			

This function a while loop to iterate over every zone in a Tas3D file, checking to see if the name of each zone matches the name passed into the function as a parameter. 

If the function finds a zone with the name it is looking for, it immediately returns True.

If the function searches through all the zones and does not find the name it is looking for, it returns False.

For more information about Functions, see the section at the end of lesson 1

Function breakdown: how does it work?

We’ll start by looking at the definition of our function:

				
					Function ZoneExists(name As String, doc As TAS3D.T3DDocument) As Boolean
    
    'Code goes here
    
End Function
				
			

Here, we have created a function called ZoneExists. This function has two parameters – the name of the zone we are looking for which is of type String, and the doc which is a reference variable to a Tas3D Document. The ‘As Boolean’ after the parameters is the return type of the function. This means when we call (use) the function, the function can return True or False. 

				
					    'declare variables for our loop
    Dim curZoneIndex As Integer
    Dim curZone As TAS3D.Zone
    
    'set the initial zone index to 1
    curZoneIndex = 1
				
			

Next, we declare some primitive and reference variables we will use when we are looking through each of the zones in the Tas3D document, inside our while loop. We have an integer called curZoneIndex to track the number of the zone we are currently examining, and we have a reference variable of type Tas3D.Zone called curZone, so we can more easily refer to the zone we are currently checking the name of. 

We also have to initialise the curZoneIndex variable to 1 (line 6), because in Tas3D, the first zone is assigned the number 1. 

				
					'go through each zone in the building
    While Not doc.Building.GetZone(curZoneIndex) Is Nothing
    
        'set the curZone reference variable to the current zone
            'TODO
        
        'if the zone has the name we are looking for, return true
            'TODO
        
        'increment the zone index so we look at the next one in the file
        curZoneIndex = curZoneIndex + 1
    Wend
				
			

Next comes the basic structure of our while loop; on line 1, we check to see whether the zone in the building with index = curZoneIndex exists. Remember, the condition of the while loop and the code within it run multiple times when the condition is true. Before we enter the loop for the first time, we are checking to see whether the first zone in the building exists. If it does not, the body of the while loop (lines 3-11) doesn’t get executed. 

Assuming we do enter the while loop, on line 11 we increment our curZoneIndex by 1 so that when the loop repeats, we check the next zone in the file. We’re now ready to fill in the rest of the functionality of the while loop, which have TODO comments on lines 5 and 8. 

				
					'go through each zone in the building
    While Not doc.Building.GetZone(curZoneIndex) Is Nothing
    
        'set the curZone reference variable to the current zone
        Set curZone = doc.Building.GetZone(curZoneIndex)
        
        'if the zone has the name we are looking for, return true
        If curZone.name = name Then
            ZoneExists = True
            Exit Function
        End If
        
        'increment the zone index so we look at the next one in the file
        curZoneIndex = curZoneIndex + 1
    Wend
				
			

On line 5, we set our curZone reference variable to the zone in the document with the index equal to curZoneIndex.

On line 8, we check to see if the name of that zone matches the name we passed into our function. If it does, we set the return value of the function to true (line 9), and exit the function early as we no longer need to examine any more zones. 

				
					    'if we have checked all the zones and none match the name we are looking for, return false
    ZoneExists = False
				
			

Finally, if we complete our while loop without finding a zone with a name that matches the one we’re looking for, we set the return value of the function to False to indicate a zone with that name does not exist in the file.

Finding existing Zone Sets

Lets quickly review the flow of our macro as it stands. Currently, it adds every new zone to a single zone set, which it creates before looking through the list of new zone names on the worksheet. 

If we were to modify our macro so it added a new zone set for each new zone, we have a potential problem. If we had multiple zones that were supposed to belong to the same zone set we’d end up making multiple zone sets with the same name! 

To overcome this problem, we need to create a function that looks through the zone sets currently in the file to see if there’s already one with the name we need. If there is, we need a reference to it so we can add zones to it. If there isn’t, we know we need to add a new zone set to the file. 

Lets see what such a function could look like:

				
					Function GetZoneSet(name As String, doc As TAS3D.T3DDocument) As TAS3D.zoneSet
    Dim curZoneSetIndex As Integer
    Dim curZoneSet As TAS3D.zoneSet
    curZoneSetIndex = 1
    
    While Not doc.Building.GetZoneSet(curZoneSetIndex) Is Nothing
    
        'set the current zone set reference to the next zone set
        Set curZoneSet = doc.Building.GetZoneSet(curZoneSetIndex)
        
        'check to see if the name matches what we're looking for. If it does, return the zone set.
        If curZoneSet.name = name Then
            Set GetZoneSet = curZoneSet
            Exit Function
        End If
    
        'increment the index so we look at the next zone set when the loop repeats
        curZoneSetIndex = curZoneSetIndex + 1
    Wend
    
    'If we finish the loop without finding the zone set, return nothing
    Set GetZoneSet = Nothing
    
End Function
				
			

This function works in a similar way to the ZoneExists function. The return type is different, and rather than iterating through every zone in the building we iterate over every zone set. 

Function breakdown: How does it work?

We start by declaring our functions name, parameters and return type:

				
					Function GetZoneSet(name As String, doc As TAS3D.T3DDocument) As TAS3D.zoneSet

    'function code goes here
    
End Function
				
			

This function is called GetZoneSet, its parameters are a name as a string and doc which is a reference to the Tas3D.T3DDocument we wish to search through. The function returns a reference variable to a Tas3D.zoneSet; if it doesnt find one, it returns nothing. 

				
					Dim curZoneSetIndex As Integer
Dim curZoneSet As TAS3D.zoneSet
curZoneSetIndex = 1
    
				
			

We start by defining some variables  we’ll use while we’re looking through all the zoneSets in the file. We use the curZoneSetIndex integer variable to keep track of the index of the zoneSet we are currently working with. The first zoneSet in a Tas3D document is given an index of 1, so we must set the initial value of our curZoneSetIndex to 1. 

We use the curZoneSet reference variable to refer to the current zoneSet.

				
					While Not doc.Building.GetZoneSet(curZoneSetIndex) Is Nothing

    'set the current zone set reference to the next zone set
    'TODO
    
    'check to see if the name matches what we're looking for. If it does, return the zone set.
    'TODO

    'increment the index so we look at the next zone set when the loop repeats
    curZoneSetIndex = curZoneSetIndex + 1
Wend

				
			

On line 1, we check to see if there is a zone set in the Tas3D document we passed in as a parameter with an index equal to curZoneSetIndex. If there is a zone set, GetZoneSet will return something (rather than nothing), and we will enter the while loop.

Before we reach the Wend of the while loop and the condition on line 1 is re-evaluated, we increment the curZoneSetIndex by 1 so we look at the next zone set in the file. If there is no zone set with that index, GetZoneSet returns nothing so we do not enter the while loop again. 

				
					While Not doc.Building.GetZoneSet(curZoneSetIndex) Is Nothing

    'set the current zone set reference to the next zone set
    Set curZoneSet = doc.Building.GetZoneSet(curZoneSetIndex)
    
    'check to see if the name matches what we're looking for. If it does, return the zone set.
    If curZoneSet.name = name Then
        Set GetZoneSet = curZoneSet
        Exit Function
    End If

    'increment the index so we look at the next zone set when the loop repeats
    curZoneSetIndex = curZoneSetIndex + 1
Wend

				
			

On line 4, we set our zoneSet reference variable to the zone set with index curZoneSetIndex.

On line 7, we check to see if the name of that zone set matches the one we’re looking for that we passed in as a parameter. If it does, on line 8 we set the return value of the function to the currentZoneSet and on line 9 we exit the function early so we don’t consider any more zone sets. 

				
					'If we finish the loop without finding the zone set, return nothing
Set GetZoneSet = Nothing

				
			

Finally, if we have passed the end of the while loop without finding the zone set with the name we are looking for, we set the return value of the function to nothing. 

Putting it all together

Now that we’ve written our new functions to check for existing zone names and zone sets, we are ready to modify our existing macro to sort the new zones into zone sets. I’ve highlighted some of the main changes. 

				
					Sub AddZoneNames()
    'Read the file path from the spreadsheet
    Dim filePath As String
    filePath = Cells(1, 5)

    'Open the 3D modeller
    Dim t3dApp As TAS3D.T3DDocument
    Set t3dApp = New TAS3D.T3DDocument

    'Declare a variable for checking if operations were successful
    Dim ok As Boolean

    'Try to open the existing file
    ok = t3dApp.Open(filePath)

    'Check if it did open successfully
    If Not ok Then
        MsgBox "Couldnt open the file; is it in use?"
        Exit Sub
    End If
    

    'Define our loop variables
    Dim rowIndex As Integer
    Dim zoneName As String
    Dim zoneSetName As String
    Dim zoneSet As TAS3D.zoneSet
    Dim newZone As TAS3D.Zone

    'Set the starting rowIndex
    rowIndex = 2

    'Check each zone name cell to see if it contains something
    While Not IsEmpty(Cells(rowIndex, 1))

        'Read the zone name from excel
        zoneName = Cells(rowIndex, 1)
        
        'Read the zone set name from excel
        zoneSetName = Cells(rowIndex, 2)
        
        'Check that zone name isnt already in use
        If ZoneExists(zoneName, t3dApp) Then
            Cells(rowIndex, 3) = "Skipped"
        Else
        
            'Get the zone set if it exists
            Set zoneSet = GetZoneSet(zoneSetName, t3dApp)
            
            'If it doesnt exist, make a zone set with that name
            If zoneSet Is Nothing Then
                Set zoneSet = t3dApp.Building.AddZoneSet(zoneSetName, "", 0)
            End If
             
    
            'Add a zone to the zone set
            Set newZone = zoneSet.AddZone()
    
            'Change the name of the zone we just added
            newZone.name = zoneName
            
            'Note that the addition was a success
            Cells(rowIndex, 3) = "Added"
    
        End If

        'Increment the row index (so we read the next cell down)
        rowIndex = rowIndex + 1
    Wend

    'Save the file and check the save was successful
    ok = t3dApp.Save(filePath)

    If Not ok Then
        MsgBox "Couldn't save the file!"
        Exit Sub
    End If

    'Close the file
    t3dApp.Close

    'Close the 3D modeller
    Set t3dAppp = Nothing

    'Message box, so we know when it's done running
    MsgBox "Finished!"

End Sub
				
			

Rather than use a fixed name for the new zone set, we read the zone set name from the worksheet:

				
					    'Read the zone set name from excel
    zoneSetName = Cells(rowIndex, 2)
				
			

Before we check to see whether a zone set exists with this name, we should check to see if a zone already exists with this name. Why? Because if we check after we’ve already created a new zone set, we might end up making an empty zone set and then realising we have no zone to put in it as a zone with that name already exists in a different zone set!

				
					'Check that zone name isnt already in use
If ZoneExists(zoneName, t3dApp) Then
    Cells(rowIndex, 3) = "Skipped"
Else

  ' ... code to add the new zone & zone set ...
  
End If

				
			

On line 2, we are calling our ZoneExists function as the condition to an if statement with the zoneName we read from the spreadsheet and a reference to our Tas3D document. If the function returns true, this means a zone with that name already exists in the 3D file. We therefore write the message “Skipped” next to that row in the spreadsheet so the user is notified that there was a problem.

If ZoneExists returns false, this means there is no zone with that name in the file already so we can create it and add it.

				
					        'Check that zone name isnt already in use
        If ZoneExists(zoneName, t3dApp) Then
            Cells(rowIndex, 3) = "Skipped"
        Else
        
            'Get the zone set if it exists
            Set zoneSet = GetZoneSet(zoneSetName, t3dApp)
            
            'If it doesnt exist, make a zone set with that name
            If zoneSet Is Nothing Then
                Set zoneSet = t3dApp.Building.AddZoneSet(zoneSetName, "", 0)
            End If
             
    
            'Add a zone to the zone set
            Set newZone = zoneSet.AddZone()
    
            'Change the name of the zone we just added
            newZone.name = zoneName
            
            'Note that the addition was a success
            Cells(rowIndex, 3) = "Added"
    
        End If
				
			

On line 7, we use our GetZoneSet function to search through the Tas3D document and look for a zone set with the name we desire. If it finds one, our zoneSet reference variable is set to a valid reference to that zone set. 

If GetZoneSet cannot find one, the zoneSet variable will reference Nothing, so we check for this on line 10. If it is equal to nothing, we know we need to add a new zone set and name it, so this is what we do on line 11. 

By the time we get to line 16, the zoneSet variable should refer to a valid zone set. We can therefore add a new zone to that zone set, and set the name. 

On line 22, we leave a comment that the addition was successful, so the user knows that zone was added to the file. 

Details: "Nothing"

In previous lessons, we discussed Primitive and Reference variables. Primitive variables must always have a value; if we define a new primitive variable and do not set its value, it will have a default value:

				
					Dim number as integer  'The default value is 0
				
			

Reference variables may not always point to a valid object, and may point to Nothing. This is the default value for reference variables:

				
					Dim someText as String 'Default value is Nothing
Dim doc as Tas3D.T3DDocument 'Default value is Nothing
				
			

We should therefore be aware that functions that return reference variables may sometimes have values of Nothing. We must check for this, because if we try to call a function that belongs to the type the reference variable controls and the reference variable controls Nothing, we’ll get an error and our program will break. 

When we test for nothing, we cannot use the equals operator – we have to use the is keyword

				
					Dim doc as Tas3D.T3DDocument() 'default value is Nothing

'Check for nothing
If doc Is Nothing Then
    'doc is an invalid reference
End If

'Check for something
If Not doc Is Nothing Then
    'doc is a valid reference
End If
				
			

For more information about the Nothing value, see this page

Next Lesson

We haven’t really introduced anything new in this lesson, we’ve just been using the concepts introduced in previous lessons. This is great news, as it means we have already covered a lot of the programming basics. 

In the next lesson, we’ll discuss how we can modify our macro so we can use it to rename zones in a Tas3D file, and ways in which we can make our macro faster for large files. 

Learn to Code with Tas and Excel Lesson 2 – Tas3D Zone Writer

Learn to Code with Tas and Excel Lesson 2 - Tas3D Zone Writer

In the last lesson of this series, we learned how to setup our machine to write macros, and we wrote our first simple Excel macro. Our first macro:

  • Opened an instance of the 3D modeller
  • Created a new 3D modeller document
  • Changed the building name
  • Saved & closed the file

In this lesson, we’ll write a macro that can read zone names from an excel spreadsheet and add them to an existing 3D modeller file. Specifically, we’ll:

  • Open an instance of the 3D modeller
  • Open an existing 3D modeller document
  • Add new zones to the file with names read from the spreadsheet
  • Save & close the file. 

We’ll then modify our macro to create zone groups for the new zones, and we’ll also add the option to read out the existing zones list from the 3D modeller file. 

As promised in the last post, we’re building on the things we learned already. So lets dive in. 

Part 1: Adding zone names to Tas3D files

Before you begin...

To begin, we’ll need a new 3D modeller file. Just open the 3D modeller and save the blank file to your desktop

We’ll also need a list of zone names in excel:

 

These zone names can be whatever you like, but I’ve added a column header of ‘Zone Names’ in cell A1 so I can identify which data is in that column. I have also added the file path to the Tas3D file we wish to operate on in cell E1. 

The script

As with last time, we’ll look at the script as a whole before breaking down each part. Have a read of it and see if you can understand what’s going on before we go into detail:

				
					Sub AddZoneNames()
    'Read the file path from the spreadsheet
    Dim filePath As String
    filePath = Cells(1, 5)
    
    'Open the 3D modeller
    Dim t3dApp As TAS3D.T3DDocument
    Set t3dApp = New TAS3D.T3DDocument
    
    'Declare a variable for checking if operations were successful
    Dim ok As Boolean
    
    'Try to open the existing file
    ok = t3dApp.Open(filePath)
    
    'Check if it did open successfully
    If Not ok Then
        MsgBox "Couldnt open the file; is it in use?"
        Exit Sub
    End If
    
    'Define our loop variables
    Dim rowIndex As Integer
    Dim zoneName As String
    Dim newZone As TAS3D.Zone
    
    'Set the starting rowIndex
    rowIndex = 2
    
    'Check each zone name cell to see if it contains something
    While Not IsEmpty(Cells(rowIndex, 1))
    
        'Read the zone name from excel
        zoneName = Cells(rowIndex, 1)
        
        'Add a zone to the default zone group
        Set newZone = t3dApp.Building.GetZoneSet(1).AddZone()
        
        'Change the name of the zone we just added
        newZone.Name = zoneName
        
        'Increment the row index (so we read the next cell down)
        rowIndex = rowIndex + 1
    
    Wend
    
    'Save the file and check the save was successful
    ok = t3dApp.Save(filePath)
    
    If Not ok Then
        MsgBox "Couldn't save the file!"
        Exit Sub
    End If
    
    'Close the file
    t3dApp.Close
    
    'Close the 3D modeller
    Set t3dAppp = Nothing
        
    'Message box, so we know when it's done running
    MsgBox "Finished!"
    
End Sub

				
			

Lots going on here! We’ve started using primitive variables,  return values from functions, while loops, if statements and control statements. But as always, we’ll break it down slowly and go into more detail about each of these topics at the end of the post. 

We start by reading the file path from the spreadsheet:

				
					    'Read the file path from the spreadsheet
    Dim filePath As String
    filePath = Cells(1, 5)
				
			

On line 2, we dimension a variable of type string (a variable that can hold text), and on line 3 we set its value equal to whatever is in the cell with row index 1 and column index 5. This corresponds to cell E1. Note that we didn’t need to use the set keyword — this is because the filePath variable is a primitive type. We only use the set keyword when we’re working with objects. 

Why did we do this? We did this because if we give our macro to a colleague or are using it a lot, its inconvenient to keep opening the code editor and having to find and change the line of code that determines which file we’re adding zone names to. We didn’t have to, but it makes things easier! And less intimidating for our colleagues.

We also need the file path when we open the file and save the file, so by reading it from the spreadsheet and storing it in a variable, we only have one place to change the file path when we’re working with a different file. Very neat. 

				
					'Open the 3D modeller
    Dim t3dApp As TAS3D.T3DDocument
    Set t3dApp = New TAS3D.T3DDocument
				
			

Next we dimension a new reference variable to control an instance of the 3D modeller, and then on line 3 we create the new instance using the new keyword and assign it to the t3dApp reference variable using the = operator and the set keyword. 

We’re almost ready to use the TAS3D.T3Document.Open function to open an existing 3D modeller file, but before we do, if we look at the function in the object browser we can see that it returns a value of type boolean:

A boolean is a primitive variable type which can be one of two values: True or False. What then, does it mean if the Open function returns True or False? Tas3D returns True when the open function is successful and false when the function is not successful.

For example, if you’re trying to open a file and the file is missing, (the file path could be invalid), the Open function will return False. This will also happen if the file is in use by another application or another instance of the 3D modeller. If the operation is successful, the function returns True.

In order to check the return value of this function, we’ll dimension a boolean variable called ‘ok’ to store the value the Open function returns and we’ll check to see what that value is after we have tried to Open the file:

				
					    'Declare a variable for checking if operations were successful
    Dim ok As Boolean
				
			

Now we can call the Open function, storing the functions return value in the ok variable:

				
					    'Try to open the existing file
    ok = t3dApp.Open(filePath)
				
			

Here, we have passed in the filePath variable we declared earlier and set to contain the value of cell E5. If cell E5 was empty, the Open function would return False, so it would be useful to check the result and exit our macro early if we couldn’t open the file. After all, there’s no point reading the zone names from the spreadsheet if we cant open the file we want to save them in!

				
					    'Check if it did open successfully
    If Not ok Then
        MsgBox "Couldnt open the file; is it in use?"
        Exit Sub
    End If
				
			

To check the value of the ok variable, we are using an if statement. We are saying if the value of ok is Not True, display a message box and exit our subroutine early. 

If statements are extremely useful as they control the flow of our macro based on conditions; for more information see the details box at the end of this post.

				
					 'Define our loop variables
    Dim rowIndex As Integer
    Dim zoneName As String
    Dim newZone As TAS3D.Zone
    
    'Set the starting rowIndex
    rowIndex = 2
    
    'Check each zone name cell to see if it contains something
    While Not IsEmpty(Cells(rowIndex, 1))
    
        'Read the zone name from excel
        zoneName = Cells(rowIndex, 1)
        
        'Add a zone to the default zone group
        Set newZone = t3dApp.Building.GetZoneSet(1).AddZone()
        
        'Change the name of the zone we just added
        newZone.Name = zoneName
        
        'Increment the row index (so we read the next cell down)
        rowIndex = rowIndex + 1
    
    Wend
				
			

Now to the heart of our macro. Remember what we’re trying to do. We need to:

  • Loop through each row that could contain a zone name and see if its empty
  • If there’s a zone name in that cell, store it in a variable
  • Add a new zone to the Tas3D file
  • Set the name of the new zone to be the one we read from the non-empty cell

In order to keep track of which row we’re currently looking at, we define a primitive variable rowIndex. On line 7, we set its initial value to 2 as row 1 column 1 contains the header Zone Name, and we dont want to accidentally create a zone name called Zone Name!

We also want a reference variable to control our new Zone object that we’ll create; we do so on line 4, and we dimension a primitive string variable to store the zone name we’ve read from the sheet. 

Line 10 is a While Loop. While the condition is true, the code between line 10 and the Wend on line 24 will repeat in order. 

First the while loop condition is checked; there is text in Cells(2,1), so we move onto line 13. 

On line 13 we read the zone name in Cells(2,1) and store it in the variable zoneName.

On line 16 we add a new Zone object to the first zone set in the Building object. We access the first zone set by using the GetZoneSet() function belonging to Building. If you look in the object browser, you’ll see its return type is TAS3D.ZoneSet, which has the function AddZone

On line 19 we change the Name of the Zone object to be the zone name we read earlier.

Next, we increment the rowIndex by 1, so its value goes from 2 -> 3. The while loop then repeats and checks to see if Cell(3,1) is empty.

As I only have 8 zone names in my spreadsheet, when rowIndex = 10, cells(10,1) will be empty and the while loop will stop executing. 

It this seems a bit confusing, watch the video accompanying this post as its easier to walk through each line with an explanation. We can even use the debugger in the visual basic environment to walk through each line one at a time to understand what’s happening, which I’ll demonstrate in the video. Also don’t forget to checkout the additional details at the bottom of this post, regarding while loops!

				
					'Save the file and check the save was successful
    ok = t3dApp.Save(filePath)
    
    If Not ok Then
        MsgBox "Couldn't save the file!"
        Exit Sub
    End If
    
    'Close the file
    t3dApp.Close
    
    'Close the 3D modeller
    Set t3dAppp = Nothing
        
    'Message box, so we know when it's done running
    MsgBox "Finished!"
				
			

By now you should be able to follow whats happening here; we use the ok variable we declared earlier to check to see whether the Save function executed successfully, and if it doesnt, we display a message and exit our macro early.

On line 10 we close the file, and then on line 13 we disconnect the reference variable that controls the 3D modeller instance from the object itself so Excel closes the 3D modeller instance for us.

We then display a helpful message box so we can tell when our macro has finished running. Though our macros so far have ran very quickly, if we had thousands of zone names in our spreadsheet it could take a little while!

What about zone sets?

Ok so far we’ve relied on the fact that our existing Tas3D file has a default zone set, and we’ve just added our new zones to that existing zone set. 

If we wanted to add our zones to a brand new zone set, we could modify our Macro as follows:

				
					Sub AddZoneNamesNewZoneSet()
    'Read the file path from the spreadsheet
    Dim filePath As String
    filePath = Cells(1, 5)
    
    'Open the 3D modeller
    Dim t3dApp As TAS3D.T3DDocument
    Set t3dApp = New TAS3D.T3DDocument
    
    'Declare a variable for checking if operations were successful
    Dim ok As Boolean
    
    'Try to open the existing file
    ok = t3dApp.Open(filePath)
    
    'Check if it did open successfully
    If Not ok Then
        MsgBox "Couldnt open the file; is it in use?"
        Exit Sub
    End If
    
    'Create a new zone set to store our zones in
    Dim newZoneSet As zoneSet
    Set newZoneSet = t3dApp.Building.AddZoneSet
    newZoneSet.Name = "My new zone set"
    
    'Define our loop variables
    Dim rowIndex As Integer
    Dim zoneName As String
    Dim newZone As TAS3D.Zone
    
    'Set the starting rowIndex
    rowIndex = 1
    
    'Check each zone name cell to see if it contains something
    While Not IsEmpty(Cells(rowIndex, 1))
    
        'Read the zone name from excel
        zoneName = Cells(rowIndex, 1)
        
        'Add a zone to the default zone group
        Set newZone = newZoneSet.AddZone()
        
        'Change the name of the zone we just added
        newZone.Name = zoneName
        
        'Increment the row index (so we read the next cell down)
        rowIndex = rowIndex + 1
    
    Wend
    
    'Save the file and check the save was successful
    ok = t3dApp.Save(filePath)
    
    If Not ok Then
        MsgBox "Couldn't save the file!"
        Exit Sub
    End If
    
    'Close the file
    t3dApp.Close
    
    'Close the 3D modeller
    Set t3dAppp = Nothing
        
    'Message box, so we know when it's done running
    MsgBox "Finished!"
    
End Sub
				
			

If you look at lines 23-25, you’ll see we first dimension a new ZoneSet reference variable so we can refer to our new zone set. We then create one by using the AddZoneSet function that belongs to the Building object

We then use that newZoneSet on line 42 and add the new zones directly to it. 

Question

What do you think would happen if we kept running our macro on the same Tas3D file?

Answer

The macro would keep adding zones with the same name to the 3D modeller file!

Details: If Statements

If statements control the flow of our macros and allow us to react differently to different situations. They take the form:

				
					If [condition] then
    'code for if its true
End if
				
			

This if statement executes the code between lines 1 and 3 if the condition is true. We can also execute different code if the condition is false:

				
					If [condition] Then
    'code if true
Else 
    'code if false
End If
				
			

We can also check multiple conditions in a single If Statement:

				
					If [condition 1] Then
    'code
Else If [condition 2] Then
    'code
Else If [condition 3] Then
    'code
End if
				
			

We can have as many ‘Else If’ statements as we like. 

The [condition] placeholders are statements that evaluate to a value of either  True or False. For example:

				
					'Example 1
Dim x as Integer
x = 4

If x = 4 Then
    'x is 4
Else
    'x is not 4
End If

'Example 2
If (1 + 5) = 6 Then
    'The condition is true
End If

'Example 3
Dim booleanValue as Boolean
booleanValue = True

If booleanValue Then
    'the booleanValue is true!
End If
				
			

We can also use the Not keyword to evaluate whether a condition evaluates to False (false is equivalent to ‘not true’):

				
					Dim x as Integer
Dim y as Integer
x = 4
y = 4

If Not x = y Then
    'The variables are not equal
End If
				
			

To learn more about If Statements, see ExcelEasy

Details: While Loops

While loops allow us to repeat a section of code while a condition is true. Remember, a condition is just a statement that evaluates to either True or False

				
					While [condition]

    'Some code

Wend
				
			

If the condition evaluates to False by the time we encounter the loop, the code in the loop will not be executed. 

If the condition is true, usually the code within the while loop has the ability to cause the condition to eventually evaluate to False, otherwise the loop would repeat forever!

				
					Dim x as Integer
x = 1

While x < 10            ' < is the less than operator
    
    'Write the value of x to the sheet
    Cells(x,1) = x
    
    'Increment the value of x
    x = x + 1
    
Wend
				
			

This snippet writes the numbers 1 to 9 to the current worksheet. The condition on line 4 gets evaluated every time the loop repeats, so as line 10 alters the value of x, eventually x will be equal to 10 and therefore the condition will be false and the code will stop repeating. 

There are a few different types of loops; there are For loops, While loops and Do While loops. For more information about loops, see ExcelEasy

Whats next?

Next time we’ll look at how we can modify our macro to dynamically add zones to different zone groups based on the contents of our excel spreadsheet, rather than just adding them all to the same spreadsheet. 

We’ll also keep an eye out for duplicate zone names. Click here to go to the next lesson.

Learn to Code with Tas and Excel Lesson 1 – Setup

Learn to Code with Tas and Excel Lesson 1 - Setup

In this post, I’ll run through how to get ready to write your first Excel macro. If you haven’t already, I suggest you read the intro post to find out more.

With each post in this series, there will be a written explanation and a video demonstrating what to do. 

1. License Tas

It might seem obvious, but please make sure your copy of Tas Engineering is licensed via the Register button in the Tas Manager. 

If your copy of Tas isn’t licensed properly, your Macro might behave strangely and it can lead to some very hard to track down bugs. 

2. Enable Excel developer Tab

When we’re writing macros in Excel, we do so in the Visual Basic for Applications window. The button to open this is hidden away in the Developer tab in the ribbon, which isn’t visible by default.

 

To enable it, click on File > Options > Customise Ribbon, and check the Developer checkbox.

 

The developer tab also allows us to run our macros, and add buttons to our spreadsheet to make running them quicker and easier.

 

Once you’ve enabled this tab, it’ll should always be visible in your ribbon.

And thats it! That’s pretty much all you need to do. Now you just need to open the VBA editor and write your first macro!

Writing your first macro

Add a new module

To open the Visual Basic editor, click on the Developer tab in the ribbon and click on the Visual Basic button. The VBA editor will pop up, and then you just need to add a module to start writing code:

Insert module into excel workbook

If all goes according to plan, you’ve created your first module and you’ll be presented with a white document in which you can type code. In case you’re wondering what a module is, it;s just a way to organise different bits of code and group them together

At this point you can start writing macros, but they wont be able to use Tas yet. To automate Tas, you’ll need to reference the appropriate type libraries.

 

Reference Tas Type Libraries

The Tas type libraries are files that tell the excel programming language what you can do when interacting with the Tas software.

To reference the Tas3D type library, click on Tools > References in the VBA editor, and select Tas3D:

 

If you are writing a macro that operates on TBD files or TCD files, you’ll need to pick those type libraries instead. The full list is:

  • 3D Modeller – Tas3D
  • Building Simulator – TBD
  • Constructions Database – TCD
  • Calendar Database- TCR
  • Internal Conditions Database – TIC
  • Weather Database – TWD
  • Systems – TPD
  • Ambiens – TAI
The Object Browser

After you’ve referenced the Tas type libraries, you can see what functions are available for a type library using the Object Browser. To open it, just press F2 on the keyboard or click on View > Object Browser:

 

Hello, Tas3D!

Lets write some code. Copy and paste the below code into your new module. You’ll need to change the file path to a folder on your computer.

 

				
					Sub HeloTas3D()
    
    'Open a new instance of the 3D modeller
    Dim t3dApp As TAS3D.T3DDocument
    Set t3dApp = New TAS3D.T3DDocument
    
    'Create a new document using the instance we just opened
    t3dApp.Create
    
        
    'Change the building name to "Hello, World!"
    t3dApp.Building.Name = "Hello, World!"
    
    'Save the 3D modeller file. YOU NEED TO CHANGE THIS LINE!
    t3dApp.Save ("C:\users\hilmya\desktop\MyNewFile.t3d")
    
    'Close the file
    t3dApp.Close
    
    'Close the 3D modeller instance
    Set t3dApp = Nothing
        
    'Message box, so we can tell when its finished
    MsgBox "Finished!"
End Sub

				
			

To run your macro, press F5 on your keyboard. The macro will run, and you should get a new Tas3D file created at the location you specified in line 15 of the previous code snippet and you should get a message box pop up saying “Finished” if everything ran OK.

 

If you open the Tas3D file that was created when you ran your Macro and open the Building dialog (ctrl + B), you should see the phrase ‘Hello, World!’ in the building name field.

 

Rememebr, if you want to re-run your macro, you’ll need to delete the 3D modeller file first.

I don't understand the code!

Now that we’ve written and ran our first macro, lets go through each part of the Macro to understand whats going on in a bit more detail. We’ll start with creating a new instance of the 3D modeller:

				
					'Open a new instance of the 3D modeller
    Dim t3dApp As TAS3D.T3DDocument
    Set t3dApp = New TAS3D.T3DDocument
				
			

Line 1 is a comment – comments start with the apostrophe character, and allow us to leave notes to ourselves and our colleagues to help them understand the purpose of that section of code. Comments are really useful, as they can help us understand our intentions when we come back and review the code later.

Line 2 ‘Dimensions’ a new object reference variable called t3dApp with type TAS3D.T3Document.  Variables represent data which can change throughout the execution of your program, and there are two broad types – primitive variables and object reference variables. We’ll discuss these more in the next section.

For now, you can think of a reference variable like a remote control – we can use the remote control to control an object; in this case, an instance of the 3D modeller is the object.

Line 3 actually creates a new instance of the 3D modeller, and links up this new instance to the ‘remote control’ reference variable we created on the previous line.

You said I opened a new instance of the 3D modeller, but where is it? I dont see it!

When we create instances of the Tas applications using programming languages, they remain hidden – they have no visible User Interface (UI). This doesn’t mean you can’t see them, though!

Run your macro again, but this time keep an eye on the ‘Processes’ list in the Windows Task Manager. You should see Tas3D appear when the macro starts, and dissappear when the macro finishes.

Too quick to see? We can add a message box to pause things so we can take a peek. Modify your macro like this:

 

				
					    'Open a new instance of the 3D modeller
    Dim t3dApp As TAS3D.T3DDocument
    Set t3dApp = New TAS3D.T3DDocument
    MsgBox "Quick, look in the Task Manager"
				
			

When you press OK, the macro will continue and the Tas3D instance will disappear.

				
					    'Create a new document using the instance we just opened
    t3dApp.Create
				
			

Next, as we’re creating a new 3D modeller document from scratch, we need to call the Create function.

This function tells the 3D modeller to make a new document – the 3D modeller creates default building elements, default zone lists and everything else you see when you create a new document using the user interface.

				
					    'Change the building name to "Hello, World!"
    t3dApp.Building.Name = "Hello, World!"
				
			

The 3D modeller file we just created has a Building property, and the Building property has a property called Name. In this step, we use the equals sign to change the value of that property to something else — in this case, the string “Hello, World!”. We used the equals sign earlier to ‘link up’ our Tas3D remote to our Tas3D object.

				
					'Save the 3D modeller file. YOU NEED TO CHANGE THIS LINE!
    t3dApp.Save ("C:\users\hilmya\desktop\MyNewFile.t3d")
				
			

Now we’re saving the file by calling the Save function — this is equivalent to pressing File > Save As in the 3D modeller. But as we don’t have a dialog to ask us where we want to save the file, we need to specify the file path using a string (the bit in the speech marks).

				
					    'Close the file
    t3dApp.Close
    
    'Close the 3D modeller instance
    Set t3dApp = Nothing
				
			

Next we use the Close function to close the file we created, and we set the t3dApp object reference variable to point to Nothing — this means it no longer controls the 3D modeller instance we opened earlier, so Excel will automatically close it as it can see we’re not using it any more.

				
					    'Message box, so we can tell when its finished
    MsgBox "Finished!"
				
			

Finally, we want to display a message box so its really clear that the macro has finished running. We do this using the MsgBox function thats built into excel, and we call this function using the string “Finished!”.

We’ve covered a lot in this first lesson and learned a lot of new things. Don’t worry if you feel overwhelmed, we’ll keep coming back to the things we’ve introduced in this first lesson as they’ll form the building blocks to more ambitious Macros!

If you haven’t watched the video for this lesson, I demonstrate and discuss the content of this lesson in more detail, so definitely watch it!

Details - Subroutines, Functions, Methods and Properties

Subroutines, functions and methods are essentially the same things and the terms often get used interchangably. They’re just ‘organisational blocks of code’, which can be re-used.

In VBA, subroutines cannot return a value but functions can. For example:

 

				
					'This function adds two numbers, and gives the value back to the code that executed it
Function AddNumbers(number1 As Double, number2 As Double) As Double
     AddNumbers = number1 + number2
End Function
				
			
				
					'This subroutine displays a message box containing the file path it is executed/called with
Sub FilePathMessageBox(FilePath As String)
    MsgBox FilePath
End Sub
				
			

The above function and subroutine have arugments – in the function they are called number1 and number2 and represent variables we wish to operate on within our function. Sometimes arugments are called Parameters.

Properties belong to Objects, (see the Details- Variables section), and are used to access variables that belong to that object. We saw one earlier when we accessed the Building property of the Tas3D document we created.

For more indepth information check out excel-easy.

Details - Variables & Objects

When a computer program is running, the computer program stores data in the computers memory. As a programmer, if you want to store data in memory and access it when your program is running, you can do so with Variables.

Lets look at the following simple macro:

				
					Sub StringVariable()
    
    Dim FirstName As String
    
    FirstName = Cells(1, 1)
    
    MsgBox FirstName
End Sub
				
			

Here, we’re creating a String variable calle FirstName, and setting its value to whatever is in cell A1 in the current Excel spreadsheet. We then display a messagebox showing the value of that String:

 

This enables us to react dynamically to the data we have and make our macro operate accordingly. 

I mentioned earlier that there are two broad types of variable – primitive variables and object reference varables. Primitive variables store simple values – things like text and numbers. Object reference variables store a reference to an Object – an Object is just a group of reference variables and possibly some functions associated with them.

Primitive Variables

  • Integer  – stores whole numbers, example- 56, -2, 12
  • Single – stores small decimal numbers – 12.5, 8.0, 12.2545, -45.32
  • Double – stores decimal numbers with more accuracy – 34.32234543476767
  • Boolean – stores true/false values. 

Non Primitive Variables

  • String – stores text, example – “Hello, there!”
For more information about variables, see excel-easy.

If you’ve made it this far and you’re still with me, well done! In the next lesson, we’ll make a macro that writes zone names to an existing 3D modeller file!

Coding: Take your Tas modelling to the next level

Coding: Take your Tas Modelling to the next level with Excel

				
					Sub Hello()
    Dim model As TAS3D.T3DDocument
    Set model = New TAS3D.T3DDocument
    
    model.Building.Name = "Hello, World!"
End Sub
				
			
hello_world_tbd_cropped

When EDSL sponsored the CIBSE Building Simulation Awards 2020, I noticed that almost all of the candidates had one thing in common; the use of Python scripts, macros and other programming tools to supercharge their modelling capability.

There’s no doubt coding is going to form an important part of the energy modelling of the future, but many feel overwhelmed by the prospect and don’t know where to start. Sound familiar? If so, this blog post series is for you.

In this series of blog posts, i’ll explain how to get started with the Tas Application Programming Interface – no prior experience required. The tools you’ll need to get started? A copy of Tas Engineering and Microsoft Excel.

Which programming languages are supported?

The Tas API has been part of Tas Engineering since the very beginning, which makes it extremely powerful. It uses features built into the Micorsoft Windows operating system which means it can be used with almost every programming language that will run on your PC, including:

  • Microsoft .net (c#, vb.net)
  • Python
  • Visual Basic for Applications (VBA; excel)
  • Java
  • C++, C
  • Matlab/Octave
You can even use it with Jupyter notebooks!

Why Excel?

With so much choice available and with Visual Basic for Applications (VBA) being fairly old, it’s a fair question! I chose Excel because:

  • Almost everyone has it
  • Excel is a great way to store data
  • The language is very similar to VB.net (very modern)

Almost everyone has it – you don’t need to waste time downloading, installing and setting up complicated development environments on every computer you’re using or want to run your program on.

Storing Data – When your clients send you data for internal conditions, how often does arrive as an Excel document? Wouldn’t it be great if you could press a button in excel and have the data go straight into your TBD file?

Language Similarity – As VBA is so similar to VB.net, when you’re ready to transition to a more modern and more powerful language, it’ll be a breeze. All the techniques I’ll teach you are transferrable, as most modern programming languages have a surprising amount in common!

What will I learn?

In this series of blog posts, I’ll teach you the basics of the Tas Programming Interface using practical examples of automating every day tasks.

Tas3D

We’ll start with the 3D modeller, and how to quickly create zones and zone groups in your Tas3D file directly from excel.

We’ll then run a daylight calculation, and write the lux levels to an Excel spreadsheet.

Next we’ll export our model and merge it with our TBD file.

TBD

When we get to the Building Designer, we’ll write a macro to quickly and effortlessly create Internal Conditions.

We’ll also write a macro to read/write heating and cooling loads from zones.

We’ll then simulate the TBD file to TSD.

TSD

We’ll write a script to extract results to a table, and process the data to look for certain values.

In order to teach you how to do these things, i’ll also need to teach you the basics of Object Oriented Programming — i’ll teach you the basics of variables, loops and conditions.

I’ll also introduce you to the Excel API!

I'm stuck! Help!

If, at any time along the way, you feel a bit lost and confused and run into any difficulty with the exercises in this tutorial series, don’t panic — I’m here to help. Just email the support team, mention this blog and your message will find its way to me and I’ll see what I can do.

If you do run into any difficulty and are able to resolve it, I’d still like to hear about it so I can improve the content for other learners such as yourself!

Whats Next?

In the next post, i’ll be showing you how to get started with Excel and Tas, and how to write your first macro! Click here to check it out.

Modelling Hybrid Ventilation Units for part L2 and EPC purposes

Modelling hybrid ventilation units for part L2 and EPC purposes

Hybrid ventilation units have become very popular in recent years, often being used in priority schools as a lower energy alternative to mechanical ventilation or air conditioning. The units are available from a variety of manufacturers and often have a range of different ‘ventilation modes’ depending on the current internal and external conditions, they’re sometimes installed to help pass the BB101 assessment.

They are usually used in conjunction with openable windows and allow for air to naturally flow through the space when this will result in a comfortable environment for the occupants. When natural ventilation would result in an uncomfortable environment, either by high internal temperatures being reached or cold draughts near the windows the hybrid ventilation units change to a mechanically assisted mode. 

A mechanically assisted mode can help alleviate high temperatures or CO₂ levels by providing a boost to the natural ventilation rate via running an integrated fan, meaning more fresh air is pulled into the space. Alternatively if the external temperature is very low the units often have an air mixing mode in which fresh air is mixed with air from the occupied space before being supplied, this means that the space can be provided with fresh air without the risk of occupants experiencing cold draughts.

Many of the units also utilise a ‘night mode’ in which the unit provides mechanical ventilation to reduce the temperature in the occupied space overnight. This can make use of the thermal mass within in the space while the outside air is cooler but it’s not possible to open the windows.

 

Image from Monodraught’s hybrid ventilation brochure showing some of the different modes of operation for their units

As the fans work in conjunction with natural ventilation and are not needed at all times these units often boast low Specific Fan Powers (SFPs) and can save on energy consumption in the building when compared to a full mechanical ventilation system. The difficulty with modelling these units for Part L2 and EPC assessments however comes from the regulations assessment methodology. 

The L2 and EPC assessments are a comparisons against similar ‘notional’ and ‘reference’ buildings, these buildings will have the same building envelope size and location but will use set HVAC efficiencies. To make this possible the regulations outline that a HVAC system type for each space in the building must be selected from a list of pre-set options, for these units there are two possible options, naturally ventilated and mechanically ventilated. There is currently no option to model the hybrid units as they perform in reality for this assessment.

CO₂ emissions comparison for an actual, notional and reference building produced by Tas.

Modelling the spaces served by these units as naturally ventilated results in all fresh air requirements in your ‘actual’ building and the ‘notional’ building spaces to be provided naturally, thus no fans are included in the assessment and the benefit of the low SFPs achieved are not accounted for.

Unfortunately choosing to model the spaces as mechanically ventilated is also not ideal as this will result in having all air provided to the spaces in both the ‘actual’ and ‘notional’ building be supplied mechanically. As the units are not designed to work in this manner it is unclear if the low SFPs would still be applicable, additionally the units will be compared against mechanical ventilation heat recovery (MVHR) units which will be sized to provide all of the required fresh air mechanically and so are likely to have higher SFPs. This is an unfair comparison as the hybrid units do not necessarily have to be sized to provide the whole fresh air requirement to the spaces they serve. The heat recovery adds additional complexity as many of the hybrid units do not have mechanical heat recovery and so in some cases being compared against a space with MVHR units can have a negative impact on the results due to higher heating loads vs the ‘notional’ building.

 

Two CO₂ emissions comparisons for the same priority school building with hybrid units, the one on the left has the spaces served by the units modelled as naturally ventilated and the one on the right has them modelled as mechanically ventilated.

So how are you meant to model hybrid ventilation units for the purpose of L2 and EPC assessments if neither option is ideal? We asked CIBSE for clarification on the matter as they’re the governing body for all EPCs lodged in-house at EDSL and their response was as follows:

“We had this question a few times in the past, for project that are mostly schools where these appear to be popular.

We don’t have a definitive answer to this, we appreciate the arguments on both sides and the two different approaches.

The NCM modelling guide in paragraph 46 states that if a mechanical ventilation system only works on peak conditions and it otherwise it generally is natural ventilation, then the space should be considered naturally ventilated and any mechanical ventilation aspects ignored. This is the approach that the people who opt for the natural ventilation option would follow.

On the other hand, there is no clear line of what would be considered only working at peak conditions etc., the line is not clearly drawn, and because the system does indeed include a fan, a lot of assessor would consider this a mechanical ventilation system with a very low SFP.

We have advised in the past that we tend to lean towards the guidance in the NCM modelling guide, but generally advised assessors to liaise with Building Control to agree an acceptable approach.

I hope this helps a bit, I am afraid there is no clear cut right/wrong answer, these system appear to be too complicated for the NCM as it is structured today.”

So to summarise there isn’t a clear cut answer as not all applications of hybrid units are the same. If the majority of the ventilation to the space throughout the year is via natural ventilation and the mechanically assisted modes kick in during peak conditions (high CO₂ or temperatures) then we would suggest these should be modelled as naturally ventilated spaces for the purpose of Part L2 and EPC assessments. However if the majority of the ventilation throughout the year is provided by a fan assisted mode there is no clear option and it is advised to liaise the Building Control Officer (BCO) for the building.

The problem with modelling these hybrid ventilation units doesn’t come from limitations within the software package but with the current methodology set out by the regulations, perhaps in the future the methodology will be updated and there will be a clearer cut method for modelling these units in L2 and EPC assessments.

 

If you have an L2 or EPC assessment either involving or not involving hybrid ventilation units you would like assistance with, feel free to contact our consultancy department.

We also have a guide on how to model Breathing Buildings NVHR units if you would like more in depth information on this.

EDSL UK Weather Data Guide

UK Weather data sets guide

There are so many different weather files available these days and with a large number of assessments, each specifying weather data files to use it can be hard to keep track of which weather files are required for which assessments. We’ve put together a guide outlining most of the applicable weather files for assessments that are commonly undertaken using our software to help keep track of when to use each weather set. Here are some of the commonly used weather data sets:

CIBSE TRY (Test Reference year) – This is designed to be a typical year of weather data, it is composed of 12 separate months of data which are not necessarily from the same year, each month chosen to be the most average month from the collected data. The 2006 TRY files are based on weather data from 1984-2004, the 2016 TRY files are based on weather data from 1984-2013.

 

CIBSE DSY (Design Summer Year) – This represents a warmer than typical year. For the 2006 DSY files the year with the third hottest summer from 1984-2004 was selected. For the 2016 DSY weather files the methodology was updated and three files for each location were produced from the weather data recorded from 1984-2013:

DSY1 – A moderately warm summer.

DSY2 – A summer with a short intense spell.

DSY3 – A summer with a longer less intense warm spell.

CIBSE future weather files – These are predicted future weather files based on the UKCP09 projections and are available for a number of years with different emissions scenarios and percentiles. The percentiles represent that likelihood that the mean air temperature will be less than predicted. The CIBSE TRYs and DSYs are available for the following scenarios:
2020s – High emissions scenario – 10th, 50th, 90th percentile
2050s – Medium – 10th, 50th, 90th percentile
2050s – High – 10th, 50th, 90th percentile
2080s – Low – 10th, 50th, 90th percentile
2080s – Medium – 10th, 50th, 90th percentile
2080s – High – 10th, 50th, 90th percentile
 
PROMETHEUS Exeter University future weather files – These are also future weather files created using UKCP09 and were created as a set of future weather data for free distribution and use by industry and academics. The files are available from University of Exeter’s website and are available for the following emissions scenarios for a number of locations:
2030s medium (A1B)
2030s high (A1FI)
2050s medium (A1B)
2050s high (A1FI)
2080s medium (A1B)
2080s high (A1FI)
 sets

Assessments and the current guidance on weather data files given in them

Compliance:

L2 & EPC (2013): The (November 2017 update to the) NCM modelling guide states that the 2006 CIBSE TRY must be used. Please note that CIBSE are seeking endorsement for use of the new weather sets in compliance calculations so this may change to the 2016 TRY files in the future. CIBSE have produced a weather locations look-up EXCEL sheet which states which weather file should be used depending on the postcode of the building, this can be downloaded here.


Overheating:

TM52 (2013): it is suggested that an appropriate DSY weather file is used in the simulation. We would suggest checking which location weather file would be most appropriate for the building and initially running with a DSY1 weather file.

TM59 (2017): Developments should refer to the latest CIBSE DSY weather files and it is required to pass the assessment with the DSY1 file most appropriate to the site location, for the 2020s, high emissions 50th percentile scenario. Other files including the more extreme DSY2 and DSY3 files, as well as future files (i.e. 2050s or 2080s) should be used to further test designs of particular concern, but a pass is not mandatory for the purposes of the simpler test presented in this document.

BB101 (August 2018): The CIBSE DSY1 50th percentile range 2020s weather file most appropriate to the location of the school building should be used for the thermal comfort assessment.


BREEAM:

BREEAM HEA04 thermal comfort – Adaptability for a projected climate change credit: PROMETHEUS project at Exeter University projected climate change weather files. For free running buildings the 2050s medium (A1B) emissions scenario DSY should be used. For mechanically ventilated or mixed mode buildings the 2030s medium (A1B) emissions scenario DSY should be used.

GN32 – Prediction of operational energy consumption: The current 2016 CIBSE TRY weather file may represent “typical” weather. For extreme weather DSY weather files should be used. The current DSY1 weather file is recommended to represent weather outside of London, whilst DSY data sets reflecting urban, semi-urban and rural locations are recommended for locations in greater London – guidance on this is given in TM49.


Other:

Heating & Cooling Loads: If not using cyclic or steady state design days for loads assessments, then a dynamic assessment could be used. If so then the modeller is free to make the most appropriate choice, taking into account the location of the building and if they want to allow for future changes in the weather. Often TRY files are used but DSY files could be used for cooling loads to account for warmer summers and worst case values.

Energy Models: There isn’t specific guidance on which weather file should be used, again the modeller should make the most appropriate choice for the building. A 2016 TRY file appropriate to the building location may give the best all round results but the modeller can use DSYs and future weather files to check the impact of warmer summers and future climate changes on the building performance.

 

ONC – A Calibrated Energy Model

What is a Calibrated Energy Model?

A Calibrated Energy Model (CEM) is a digital twin of a building created in a thermal modelling tool that is then fed inputs from actual metered data and BMS readings once the building is completed and occupied. This allows the thermal model to be calibrated with the actual installed values for efficiencies, energy use and HVAC component measurements, the model can then be used to predict the impacts of building control changes accurately. It can also highlight ‘problem’ areas in the building and BMS.

Although on more complex projects CEMs can take a lot of work and time to set up they can be extremely useful in testing how suggested renovations or HVAC control changes will perform before these are actually carried out. If we know that the CEM is predicting accurate results by comparing these to meter readings from the actual building, tweaking values in the CEM should give us a good indication of what effects these tweaks would have if carried out on the actual building. This could for example be used to test the effects of updating the VRF system on the CO2 emissions and costs of electricity in the building and thus be used to predict how long the payback period for the upgraded system would be. Another example could be to test how a number of different glazing specifications would perform for a building in need of an update to its façade; the most cost effective option for the long term could then be identified and installed

One New Change Project

EDSL worked in conjunction with a number of companies including Demand Logic, Landsec and NG Bailey to produce a CEM for the One New Change (ONC) building in London. To start with a 3D model of the building was created and the areas to be included in the analysis were then zoned. There were 2 major areas to be included in the analysis, the offices and the retails areas, with retail being the first two floors, offices above and some restaurant and hospitality on the roof.

EDSL worked in conjunction with a number of companies including Demand Logic, Landsec and NG Bailey to produce a CEM for the One New Change (ONC) building in London. To start with a 3D model of the building was created and the areas to be included in the analysis were then zoned. There were 2 major areas to be included in the analysis, the offices and the retails areas, with retail being the first two floors, offices above and some restaurant and hospitality on the roof.

As the building was so large and the HVAC systems utilised a number of different technologies the work on calibrating the systems was time consuming. The building uses 13 Ground Source Heat Pumps (GSHP) for heating and cooling, as shown in the geothermal ground loop pipework layout screenshot below. The water loops for these were set into the underground piles that support the building structurally.

 

The geothermal ground loop pipework layout schematic for ONC showing the location of all the ground source water loops:

Here is an annotated screenshot of the Tas systems file showing the waterside systems that were set up for the building:

The Retail units were calibrated using the metered data, this allowed for the internal gains to be tweaked over a number of iterations until the model was in good agreement with what was being observed in reality.

A phenomenon seen at ONC from the logged BMS data and meter readings was a substantial amount of simultaneous heating and cooling by the office FCUs in the building. As we were building a CEM this sub-optimal HVAC system behaviour had to be accounted for to compare old and new strategies, both before and after the Energy Conservation Measure (ECM) is implemented. Being able to model simultaneous FCU heating and cooling was an important part of the project.

Simultaneous heating and cooling can happen for a number of reasons. After extensive study of the FCU BMS logged data at ONC, there seemed to be two primary causes for this. Firstly, the control system in the FCUs was not sophisticated enough to control temperatures to the current settings, which were very exacting (effectively trying to control to within 1C, with only a 0.5C deadband). This was termed ‘hunting’ and had been confirmed and explained by the N G Bailey team, who put forward a proposal to cure this issue by widening the deadband.

Secondly, FCU setpoints were varying significantly within zones of the building. This means neighbouring spaces were at different temperatures and this leads to air mixing between these areas due to stack pressure effects. For example a typical FCU on level 4 had the following varying setpoint signal for the year (from 1st January to 11th June 2016).

The next chart shows the space/return temperature, so the unit is clearly attempting to follow this erratic setpoint control.

The approach to try and simulate the effect of ‘hunting’ was allowing the heating and cooling to have overlapping control bands. So, for example, heating and cooling control would be as follows.

This arrangement gives both heating and cooling between 22C and 22.25C and accounted for the hunting problem quite well. Once the model was calibrated the models simulated results were in agreement with the billing energy information to a fairly accurate degree.

The graph above shows the whole site monthly energy consumptions, the blue bars are the billing information for the building and the red bars are the simulated predictions from the CEM. (Two of the biggest differences can be explained – January billing data included part of December’s usage and June billing information did not include gas usage). The model was then also able to quantify the energy savings following an update on the humidity controls on the HVAC systems for ONC. The model was calibrated around July 2015 and so the July to December results are a better fit than the 2016 results. The building is always changing (so the results drift) which shows a calibrated model needs ‘maintenance’ to keep it up to date.

Implementing an Energy Conservation Measure (ECM) using the CEM

Below is a graphic showing the first ECM implemented at ONC using the CEM, the aim was to include for a change on humidity controls in the building and use the CEM to predict the energy savings from this change. The top left green box shows the original calibrated model performing close to the monitored BMS data. The red lozenge shows how the system would have carried on if the ECM hadn’t been implemented, notice that this includes for a lot of full cooling demand. The bottom right green box shows a Tas model with the ECM in place comparing well to the BMS data after the change, so it’s been implemented correctly and performing as expected. The vertical green line, where the two boxes touch at around April 19th, is when the ECM was introduced. So the lowest graph before the vertical line shows just how much could have been saved had the change been introduced earlier!

The ONC project was highly ambitious due to the sheer size of the building and the complexity of the HVAC systems, with a smaller less complex building the task would be much easier to carry out. A second project at Dashwood House was completed in less than a week. Although creating CEMs for buildings isn’t currently a very commonly undertaken task there may be an increasing need for these in the future, not only are there great benefits in the long run in terms of ensuring building efficiency and saving money on running costs, but as energy targets become more stringent and there is greater responsibility placed on building designers/managers to ensure/prove buildings are performing as efficiently as they’re designed to be. As developments in BMS monitoring and thermal modelling simulation software continue to be made, creating CEMs may become a simpler task to carry out.

If you found this blog interesting you may want to take a look at this somewhat similar article reviewing in use building performance from the November 2020 CIBSE Journal in which the thermal modelling was carried out by Hilson Moran using our software. Tas Systems allowed for the waterside systems to be modelled in great detail.

 

If you think that you would benefit from having a CEM created for a building contact our Consultancy department for help.

3D Visualisation

Using the 3D Visualisation to help sanity check your TBD file

Setting up the TBD correctly is a vital part of carrying out any thermal modelling in Tas, whether it’s for an L2 & EPC assessment, an overheating assessment or an energy model. It’s very important that the TBD file is set up appropriately for the analysis being carried out, as any errors or mistakes in the TBD could impact the results of the analysis and thus invalidate them. Ultimately it is the users responsibility to ensure that the correct constructions, internal conditions, apertures etc. are applied throughout the model, but Tas does have some great tools to assist with this.

The TBD file does give errors and warnings for a number of things when you run the pre-simulation checks under Tools in the toolbar, as shown in the image below.

 

The pre-simulation checks are a brilliant tool for highlighting common omissions and mistakes, it is a great place to start when performing a sanity check of the model but they are not able to decipher when the wrong information has been applied in the model. For example you would get an error message if no construction is applied to the windows but if you have applied the ground floor construction to your windows accidentally, the pre-simulation checks will not detect this.

The 3D Visualisation is a great tool to sanity check many components of your model and we strongly recommend using it to aid in checking your TBD is set up correctly. If you click on the 3D Visualisation icon in the toolbar (as shown in the image below) the 3D Visualisation window will appear with your building geometry displayed.

Only zoned areas of the building will be displayed in the 3D Visualisation. If there are areas of the building missing that you think should be included in the analysis you will to check these are zoned correctly in your 3D modeller file, then re-export and merge with your TBD file. Right clicking in the 3D Visualisation window will allow you to change the settings of the 3D Visualisation, you’re able to adjust the display colour of the visualisation to be based on a number of options including zone colour. 

Using the 3D Visualisation to check building elements

While the 3D Visualisation window is open you can expand the building elements folder in the tree-view and click one of the building elements, this will highlight where this building element is applied in the model. This is a quick way to check the correct building elements have been applied to the correct surfaces. If you have 2 external wall types, for example, with differing constructions and U values, you can highlight each of these separately and ensure that the model has these assigned in the correct areas.

By highlighting the “curtain wall N/E/W” building element I can quickly see that this element has been applied in the right locations and hasn’t been applied to the south facing curtain walling.

If you have any building elements for which you are unsure what construction to apply, you can use this feature to find where the building element is applied and then choose the appropriate construction.

Using the 3D Visualisation to check zones & internal conditions

Expanding the zones folder in the tree-view and selecting a zone will highlight this zone in the visualisation. You can quickly cycle through the zones using the arrow keys on your keyboard to check the zones in the model have been applied to the correct areas of the building. If you have any HVAC groups, Zone groups or Zone sets created in the model selecting these in the tree view will highlight all zones assigned to this group simultaneously. This feature can be useful to check the correct zones have been assigned to each group, for example if you have a HVAC group called “3F VRF” and see that there is GF zone applied to this in the visualisation you know this will need looking into.

Here I can see that there is a 1F zone assigned to the HVAC group “2F Office VRF group”, I can now look into ensuring the 1F zone is assigned to the correct HVAC group.

Checking the locations of the zones in the model using the visualisation is also a great way to find any non-contiguous zoning. You will likely get a warning about non-contiguous zoning in the pre-simulation checks if this is present in your model but highlighting the zone and its location in the 3D Visualisation is the quickest way of locating the issue.

Highlighting an internal condition in the tree view will highlight all areas where this is applied in the geometry, for larger models this may not be very useful as it will be hard to check the correct areas have this internal condition applied visually but it can be useful for smaller models as shown below.

By highlighting the toilet internal condition I can see there is a large space on the 3F that has incorrectly been assigned the toilet internal condition, this space should have the office internal condition applied.

Using the 3D Visualisation to check construction and aperture types

Selecting a construction in the tree view will highlight all surfaces this has been applied to. Cycling through the constructions using the arrow keys while the 3D Visualisation window is a fantastic way to sanity check the constructions assigned in the model. This feature is key to finding instances in which an incorrect construction has been assigned, for example if you have a different glazing specification for south facing glazing you can easily identify any cases of this being applied to the wrong façade(s). 

If you have a ceiling void modelled in the 3d modeller but have applied the default internal floor building element to this space and applied the default internal ceiling element to the space below this will likely come through as an internal floor/internal ceiling in the building simulator. It’s quite likely that the construction between a void and the space below will be different to the construction between one occupied floor and another and any cases of the incorrect construction being applied can easily be identified. 

Here I can see that the same internal floor construction has been applied between occupied floors and between the 4F and the void space above. I know that the construction between the 4F and the void is not a concrete floor and is plasterboard so I can amend this.

Selecting an aperture in the tree view will unsurprisingly show all instances where this aperture is applied in the 3D Visualisation, this is useful for checking you haven’t accidentally assigned the aperture to any incorrect building elements.

 “Top-tip”: if you change the colour of all openable windows to one colour in the 3D modeller file you can quickly check the correct windows have apertures assigned using this feature.

Because I know I made all of the openable windows green in the 3D modeller file I know that the blue window has incorrectly been assigned the aperture function in this model and it needs to be removed from this building element.


The 3D Visualisation is a great tool for quickly checking your TBD file for errors which could otherwise be easily over-looked. If you’re not currently a Tas user but are interested in trying out our software you can get a free trial from our website, because we are unable to offer our usual face to face software training our e-training  is currently all free!

CBDM Case Study

Using CBDM to improve daylight quality in my classroom design.

Climate Based Daylight Modelling (CBDM) has been around for a while now and it’s often a requirement that it’s carried out on buildings, particularly schools, to test the quality of the daylight that can be expected within the occupied spaces to help reduce electrical lighting energy and improve occupant well-being. Rather than simply taking a design, carrying out the CBDM assessment on it and tweaking the internal surface reflectance values and/or glazing transmission to achieve a “passing” result let’s look at using CBDM as an iterative design tool to find a daylight solution that provides desired results for our classroom. Starting with a very basic design let’s look at how we can alter this, investigate the effects on the daylight availability and compare the results obtained from 6 different classroom designs.

To start with a simple strip of six 1m x 1m windows was modelled along the façade for our fairly deep south facing classroom with a floor area of 70m2 as shown in the images below.

The CBDM report produces results in the form of Useful Daylight Illuminance (UDI) metrics which are as follows:

  • UDIs Supplementary Annual occurrence of illuminances less than the acceptable lux level.
  • UDIa Acceptable Annual occurrence of illuminances between the acceptable lux level and the excessive lux level.
  • UDIt Target Annual occurrence of illuminances between the target lux level and the excessive lux level.
  • UDIe Excessive Annual occurrence of illuminances greater than the excessive lux level.

UDIs corresponds to time where electric lighting will be required in the space. UDIa corresponds to time where the daylight is acceptable and electric lighting isnt required. UDIt corresponds to time where the desired target illuminance is met by daylight. UDIe corresponds to time where the daylight is excessive and may be too bright for occupants.

For this project the acceptable lux level was set to 100 lux, the target lux level was set to 300 lux and the excessive lux level was set to 3000 lux. These values may well change between projects and are set by the user in the CBDM tool before the calculation is carried out. The weather file used in this project was the 2005 CIBSE London TRY.

The CBDM report also produces results for the Spatial Daylight Autonomy (sDA); this is the percentage of area that is above a specified lux level for a specified percentage of the time or more (t/50% corresponds to the target illuminance for 50% of the occupied time) and the UDIa Min; this is the minimum result for the grid point with the lowest average UDIa result and so is useful in investigating the uniformity of the average UDIa result.

Classroom Design 1 Results:

Floor plans showing the average UDIs, UDIa and UDIe from left to right. As the UDIs reports areas that have lux levels below the acceptable level the light parts of the image correspond to areas that are not getting sufficient light and vise versa.

The average UDIt result of 25% from this assessment shows that the majority of the space is not achieving the target illuminance level of 300 lux for a significant portion of time, looking at the floor plans above it’s evident that not enough space is reaching the back of the classroom and you can also see a small patch by the windows that is occasionally getting too much daylight in the average UDIe floor plan on the right. The average UDIa floor plan in the middle shows a band around the middle of the classroom that is only achieving an acceptable lux level between 33-67% of the time.

 

One option to allow more daylight into the back and middle of the classroom is to increase the height of the windows, allowing a larger amount of daylight to enter the space and increasing the angle at which it can penetrate into the space. To investigate this the window heights were increased to 2m for design 2.

Classroom Design 2 Results:

Floor plans showing the average UDIs, UDIa and UDIe from left to right. As the UDIs reports areas that have lux levels below the acceptable level the light parts of the image correspond to areas that are not getting sufficient light and vise versa.

The change in window height resulted in an increased average UDIt and UDIa results of 36% and 65% respectively, this means that the space now achieves its target illuminance level and its acceptable illuminance level more often than in design 1. The average UDIe result has also increased to 11% however, this means that the excessive lighting level of 3000 Lux is now being reached more than before and there is an increased risk of glare and occupant discomfort.

If we look at the Minimum UDIa we can see that this is quite low when compared to the average UDIa, this signals that there are still areas in the space that are not achieving sufficient daylight and that the distribution of the light within the space could be improved upon. Inspecting the floor plans we can see we are still not achieving the desired daylight at the back of the classroom and the front of the classroom is often receiving excessive daylight levels.

To attempt to reduce the excessive daylight in the space an external shade system consisting of 6 fins at an angle of 165° was added in front of the south facing windows in the Tas 3D model, a screenshot of the 3D model showing the shades can be found below. An angle of 165° was chosen as this would block more daylight from entering the front of the classroom (where we are experiencing excessive lux levels) when the sun is positioned high in the sky, whilst still allowing light to penetrate into the back of the space when the sun is at a lower angle.

3D image from model showing shades included.

Classroom Design 3 Results:

Floor plans showing the average UDIs, UDIa and UDIe from left to right. As the UDIs reports areas that have lux levels below the acceptable level the light parts of the image correspond to areas that are not getting sufficient light and vise versa.

Introducing the shade reduced the average UDIe to 6%, it did however also reduce the average UDIa and UDIt results in doing so. Looking at the reduced UDIe figure and the UDIe floor plan informs us that the shade is successful in reducing daylight in the desired area of the classroom. If the UDIe had not come down this would inform us that the shade is not reducing the light entering the space at the times when excessive light levels are reached and that we need to look at refining its design, possibly by adding fins or adjusting the angle of the fins.

If the shade was not effective at reducing the average UDIe result we could use the CBDM results viewer to see at what time of the year the different parts of the space are too bright or dark, then use the display sun option in conjunction with shadows displayed to position the sun at the right location for that hour in the year and make an intelligent estimate for what sort of shade angle would rectify the problem we are trying to solve.

Our next step is to address the lack of daylight at the back of the classroom, one option to do this would be to add rooflights directly to the space, another possibility is to add windows into the top of the corridor and internal windows from the corridor to the classroom so that light can be shared between the corridor and the back of the classroom. To compare these two strategies the side-lit windows were reverted to the 1m tall windows and the external shades were removed so that the effect on the daylight levels reached at the back of the classroom by could be investigated. Both design options were run on identical classrooms at the same time to investigate which would be more appropriate.

 

Classroom design 4 includes two rooflights each 1.5m x 1m as shown in the model images below:

Classroom 5 includes high level windows into the corridor and internal windows between the corridor and classroom as shown in the 3D model image below:

*The roof constructions have been displayed as transparent to show the internal windows.

Classroom Design 4 & Classroom Design 5 Results:

Floor plans showing the average UDIs, UDIa and UDIe from left to right. As the UDIs reports areas that have lux levels below the acceptable level the light parts of the image correspond to areas that are not getting sufficient light and vise versa.

From the results it is clear that the rooflights are much more effective at introducing light into the back of the classroom with an average UDIa of 85% we know that the light in the space will be at acceptable levels without the use of additional lighting for a large portion of the year.

The floor plans clearly show the difference in the light distribution between the two methods if using shared light from the corridor was the desired approach the design would have to be refined, perhaps high level corridor windows aren’t sufficient or the internal windows need a larger area for the back of the classroom to receive sufficient light.

 

I was interested to see how the rooflights would perform in combination with the 2m tall windows and external shade system so this was set up as Classroom Design 6.

Classroom Design 6 Results:

Floor plans showing the average UDIs, UDIa and UDIe from left to right. As the UDIs reports areas that have lux levels below the acceptable level the light parts of the image correspond to areas that are not getting sufficient light and vise versa.

This provides the highest average UDIa and average UDIt of all of the design options modelled so far but the UDIe has also crept back up to 9%. It has to be considered if the small increase in average UDIa and average UDIt are worth the cost of installing the larger windows and shade system in addition to the increased risk of excess lighting. If it was decided that this is the desired design solution but the average UDIe of 9% was a concern then we could look at adjusting the external shade to further reduce excess light at the front of the classroom or add internal blinds onto the rooflights, these 2 options would address the areas of the classroom that are effected by excess daylight that can be seen in the floor plan on the right.

 

 

To summarize let’s look at all of the results together.

The initial design including 6 1m x 1m windows did not achieve acceptable daylight levels for a large proportion of the year, changing the height of the windows to 2min design 2 increased the acceptable daylight levels in the classroom but it also meant that excessive daylight levels were experienced more often near the windows. The shade that was added in design 3 reduced the excessive daylight along the south facing side of the classroom but also reduced the average UDIa and UDIt results we were trying to improve. Designs 4 and 5 were two options aimed at increasing the amount of daylight in the back of the classroom, design 4 utilized rooflights and design 5 aimed to share light with the corridor via high level windows. For designs 4 and 5 the windows were reverted to 1m x 1m windows and the shade system was removed, the rooflights proved a better option for our classroom and greatly increased the amount of time the classroom reached acceptable light levels with daylight alone. For Design 6 the rooflights were combined with the 2m tall windows and shade system, this did further increase the useful daylight in the space albeit slightly but it also increased the risk of excessive daylight and glare.

 

Using Tas CBDM tools distributed calculations makes is easy to quickly asses the daylight results for a number of different design options and find one that suits the space in question, being able to investigate the average UDI floor plans for the space allows a designer to clearly see which areas of the space are receiving the desired daylight and which areas could be improved on.

If you’re interested in carrying out CBDM assessments but don’t have a Tas licence you can contact us for a free trial of the software and learn how to carry them out or contact our consultancy department who can assist in carrying the assessment out on your building design.

Build2Perform 2020 Presentation

Our 2020 Build2Perform presentation on the future of building simulation

In-case you missed it at CIBSE’s Build2Perform conference last week here is Andrew Hilmy’s short presentation covering a variety of topics related to the future of building simulation. The video is only 6 minutes long but gives a quick insight into a wide range fast developing areas in the industry such as machine learning, more realistic modelling of sporadic occupant behaviour, smart glass, computational fluid dynamics (CFD) and calibrated energy models.