Category Archives: Uncategorized

Validation of Tas against BS EN ISO Standards

Posted on

The BS EN ISO Standards 13791, 13792, 15255, and 15265 are used to validate a building simulation software’s results against expected results; compliance with these standards demonstrates the integrity of the simulation engine as the results are concerned with the solar, heat flow, and room load calculations which underpin the annual simulation.

Models were set up in Tas to the requirements specified in the standards. In each case Tas results fell within the standards’ specified margin of the expected results.

BS EN ISO 13791 tests heat conduction, long-wave radiation, surface sunlit factors, and operative temperatures resulting from different geometry, constructions, and ventilation methods.

BS EN ISO 13792 tests surface sunlit factors, and operative temperatures resulting from different geometry, constructions, and ventilation methods.

BS EN ISO 15255 tests operative temperatures and cooling loads resulting from different constructions, gains, ventilation methods, and cooling systems.

BS EN ISO 15265 tests heating and cooling loads resulting from different geometry, constructions, gains, and system schedules.

The Tas software’s compliance with these standards means users can have confidence in the accuracy of the TBD simulation engine.

To read the full compliance reports or download the Tas models, click here: https://www.edsl.net/validation/

Migrating from Hevacomp to Tas

Posted on

Migrating from Hevacomp to Tas

Are you a Hevacomp user in need of a new tool for your building load and energy calculations? If so, in this post, we’ll look at how you can perform heat sizing and cooling sizing load calculations in Tas. 

You can also see an example heat sizing report and an example cooling sizing report from Tas. 

New Projects: Create the geometry

Creating new projects in Tas is a lot like Hevacomp. Start with the geometry, generate a building simulator file and provide constructions and weather details. 

You can create geometry by importing a DWG file and tracing around it. Label the spaces by assigning a zone, then export to the Building Simulator to assign constructions, weather & internal conditions. 

For existing projects, you can import IDF files from Hevacomp or use our gbXML import. 

New Projects: Select Weather & Assign Constructions

You can import an EPW weather file directly into the building simulator, use CIBSE weather or create your own weather file. 

You can assign constructions to your Building Simulator file from one of our databases bundled with the software, or you can create your own constructions by building up the material layers.

Calculating Loads using the Design Day Wizard

Once you’ve created a building simulator file with appropriate weather and constructions, you can launch the Design Day Wizard via: Tools > Design Day Wizard.

This wizard will guide you through performing heating & cooling load calculations. For heating loads, enter the heating setpoint and infiltration rate and the wizard will generate the heat loss report. 

The heat loss report is generated for each of the zones selected in the wizard showing the breakdown through the building fabric. You can easily re-run the wizard to make amendments and generate a new report. 

Full Samples: Heating Sizing Report & Cooling Sizing Report. 

Admittance Calculations & Pipe/Duct Sizing

For pipe & duct sizing, Tas integrates with MEPWorx, (formally Cymap). Data is transferred from your detailed HVAC simulation models. 

Importing Hevacomp files into Tas

Tas can import both gbXML and IDF files; the best way to import existing Hevacomp projects into Tas is with our IDF import wizard:

Using the IDF Import wizard, you can automatically create a Tas3D file to perform daylight calculations on. The wizard will also create a building simulator file with internal gains, construction information and create a weather file for you either using an EPW or a native Tas TWD file.

You can find the IDF wizard in the Utilities folder of the Tas Manager. 

Improve Efficiency with Dynamic Simulation

So far we’ve examined how you can calculate heating and cooling loads using Tas using the steady state method. As Tas is a dynamic simulation package, you can also simulate a full year and determine peak loads that are more representative of the actual demand for heating and cooling, therefore preventing oversizing and undersizing, and leading to far more efficient building operation.

This can save energy, money and reduces the carbon footprint of the building. 

Ready to try it?

If you’re an existing Hevacomp user and wish to try Tas Engineering, you can download a free trial. To get started quickly, try using the IDF wizard to import an existing project of yours so you can explore the Design Day Wizard. If you have lots of users who would like a trial or have any specific questions, contact us. 

If you wish to create new projects in Tas, sign up for our free online e-trianing. 

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

Posted on

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

In the last lesson, we modified our zone writer macro to:

  • Check for duplicate zone names
  • Check for existing zone groups and add zones to groups if they already exist

In this lesson, we’ll take a look at performance. So far, our macro works very well for adding a small number of zones to a model but as our model and the number of zones gets bigger, it can take a very long time to run!

Do I really need to worry about performance?

Usually, the time to start worrying about performance is when we try and use our macro and it takes a long time to run. After all, the whole point of writing these macros is to save time! 

Getting a feel for the difference between fast code and slow code is quite useful though, as it you’ll intuitively write fast code from the start and develop an intuition for how to keep things snappy. 

Lets time our macro

Lets modify our existing macro so we can time how long it takes to run. First, lets add a new subroutine called TimeAddZoneNames. We’ll use this function to call our existing function, AddZoneNames, and time how long it took to run:

				
					Sub TimeAddZoneNames()
    
    'Declare some variables to keep track of when we started timing and how much time has elapsed
    Dim StartTime As Double
    Dim SecondsElapsed As Double
    
    'Use the built in 'Timer' function to get the current number of seconds since midnight
    StartTime = Timer
    
    'Call our macro that adds zones to our model
    AddZoneNames
    
    'Call timer again, and calculate the difference in seconds
    SecondsElapsed = Round(Timer - StartTime, 2)
    
    'Display a message showing the time it took to run
    MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
End Sub

We’ll also need to modify our AddZoneNames subroutine to remove the ‘finished’ messagebox at the end. 

Think about it

Why do we need to remove the finished message box from AddZoneNames?

Answer

If we didnt remove the message box, our timer would time how long it takes us to press OK to the Finished message box. We're interested in timing how long it takes to add zones to the 3D modeller file, not how quickly we can press Finished!

Last but not least, we’ll need to change the button we use to run our macro to call our TimeAddZoneNames subroutine:

To do this, right click on the button and press Assign Macro. 

How long does it take to run?

Using the above modifications, i’ve timed how long it takes to run our macro when we’re adding a variable number of zones to the model. Results below. 

When we’re only adding 30 zones, the macro takes 3.5 seconds to run. Pretty good.

When we want to add 200 zones, it takes over a minute. Maybe that’s ok, we can usually spare a minute.

When we want to add 500 zones, it takes over 12 minutes!! This is not good. What if we make a mistake and need to re-run it? that’s almost half an hour of wasted time!

These times are from a fast 4GHz processor – take a moment to see how long it takes to add 200 zones on your machine and see how the times compare. 

Why does it take so long to run?

You might be wondering why this macro takes so long to run when computers can perform billions of calculations every second. Lets time how long a simple operation such as an addition takes:

				
					Sub addMillionTimes()
    Dim i As Long
    i = 0
    While i < 1000000
        i = i + 1
    Wend
End Sub

I timed calling this function, which adds 1 to a variable 1 million times, to see how long it would take to execute. Even with declaring the variable and assigning space for it in the computer RAM, this macro took 0.01 seconds to run!

Now lets compare it to a function which retrieves the building name in a file:

				
					Sub getBuildingNameMillionTimes(doc As TAS3D.T3DDocument)
    Dim i As Long
    Dim name As String
    i = 0
    While i < 1000000
        name = doc.Building.name
        i = i + 1
    Wend
End Sub

This subroutine takes a jaw dropping 46 minutes to run! Why? Because when we use a type library to control another application, the Windows operating system has to perform many security checks each time we call a function belonging to that library. Therefore, if we want to write fast macros, we need to reduce any unnecessary type library function calls.

In the case of our existing macro, every time we add a zone to the file we need to check every single zone in the file to see if it already exists. If we have 10 zones in the file and we want to add another, we have to check 10 zones before we can add a new one. A checking operation involves getting a reference to a Zone object then reading its name (3 operations). 

If we have 499 zones in our file, we have to check 499 zones names before we can add another one. 

But what if we could check each of the zone names once and remember the result, so that next time we want to add one we can save a lot of time? 

Dictionaries

Fortunately, we can use an object called a Dictionary in order to create a lookup table in the computers memory of zone names and zone references. 

Before we start adding zones to our file, we can read all the zones in the 3D modeller file once and add them to our lookup table. 

				
					Function CreateZoneDictionary(doc As TAS3D.T3DDocument) As Scripting.Dictionary
    Dim lookup As Dictionary
    Set lookup = New Scripting.Dictionary
    Dim curZone As TAS3D.Zone
     
     
    Dim curZoneIndex As Integer
    curZoneIndex = 0
    
    While Not doc.Building.GetZone(curZoneIndex) Is Nothing
        
        'Get a reference variable for the current zone
        Set curZone = doc.Building.GetZone(curZoneIndex)
        
        lookup.Add curZone.name, curZone
            
        
        'incremenet the zone index so we look at the next one in the file when the loop repeats
        curZoneIndex = curZoneIndex + 1
    Wend
    
    Set CreateZoneDictionary = lookup
    
End Function

Note that in order to use Dictionaries, you need to reference the Microsoft Scripting Runtime via Tools > References.

This function creates a dictionary on line 3, and then iterates over every zone in the file. On line 15, it stores the zone name as the dictionary key and a reference to the zone as the value in the dictionary. 

We can therefore use the dictionary to very quickly retrieve a reference to a zone using its name. Using this dictionary, we can re-write our ZoneExists function:

				
					Function ZoneExists(name As String, lookup As Scripting.Dictionary) As Boolean
    
  ZoneExists = lookup.Exists(name)
   
End Function

Looking up a value in a dictionary based on its key is extremely fast, as no type library function calls are required. 

Putting it all together

The complete macro, with the changes highlighted, can be seen below. I have also created a dictionary for zoneSets in order to speed up checking of zone sets exist already, and adding zones to them. 

				
					Sub TimeAddZoneNames()
    Dim StartTime As Double
    Dim SecondsElapsed As Double
    
    StartTime = Timer
    
    AddZoneNames

    SecondsElapsed = Round(Timer - StartTime, 2)
    
    MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
End Sub




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 newZone As TAS3D.Zone
    Dim zoneSet As TAS3D.zoneSet

    'Set the starting rowIndex
    rowIndex = 2
    
    'lookups
    Dim zoneLookup As Dictionary
    Dim zoneSetLookup As Dictionary
    Set zoneLookup = CreateZoneDictionary(t3dApp)
    Set zoneSetLookup = CreateZoneSetDictionary(t3dApp)
    

    '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 the zone name isnt already in use
        If ZoneExists(zoneName, zoneLookup) Then
            Cells(rowIndex, 3) = "Skipped"
        Else
            'Check to see if there is a zone set already in the file with the right name
            Set zoneSet = GetZoneSet(zoneSetName, zoneSetLookup)
            
            'If it doesnt exist, make a zone set wtih that name
            If zoneSet Is Nothing Then
                Set zoneSet = t3dApp.Building.AddZoneSet(zoneSetName, "", 0)
                
                'Add it to the dictionary
                zoneSetLookup.Add zoneSetName, zoneSet
            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
            
            'Add it to the dictionary
            zoneLookup.Add zoneName, newZone
            
            'Note 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


End Sub

Function CreateZoneDictionary(doc As TAS3D.T3DDocument) As Scripting.Dictionary
    Dim lookup As Dictionary
    Set lookup = New Scripting.Dictionary
    Dim curZone As TAS3D.Zone
     
     
    Dim curZoneIndex As Integer
    curZoneIndex = 0
    
    While Not doc.Building.GetZone(curZoneIndex) Is Nothing
        
        'Get a reference variable for the current zone
        Set curZone = doc.Building.GetZone(curZoneIndex)
        
        'Add the zone to the dictionary, using its name as the key
        lookup.Add curZone.name, curZone
            
        
        'incremenet the zone index so we look at the next one in the file when the loop repeats
        curZoneIndex = curZoneIndex + 1
    Wend
    
    Set CreateZoneDictionary = lookup
    
End Function

Function CreateZoneSetDictionary(doc As TAS3D.T3DDocument) As Scripting.Dictionary
    Dim lookup As Dictionary
    Set lookup = New Scripting.Dictionary
    Dim curSet As TAS3D.zoneSet
     
     
    Dim curZoneSetIndex As Integer
    curZoneSetIndex = 0
    
    While Not doc.Building.GetZone(curZoneSetIndex) Is Nothing
        
        'Get a reference variable for the current zone
        Set curSet = doc.Building.GetZoneSet(curZoneIndex)
        
        'Add the current zone set to the dictionary, using its name as the key
        lookup.Add curSet.name, curSet
            
        'incremenet the zone index so we look at the next one in the file when the loop repeats
        curZoneSetIndex = curZoneSetIndex + 1
    Wend
    
    Set CreateZoneSetDictionary = lookup
    
End Function

Function ZoneExists(name As String, lookup As Scripting.Dictionary) As Boolean
    
  ZoneExists = lookup.Exists(name)
    
End Function

Function GetZoneSet(name As String, lookup As Scripting.Dictionary) As TAS3D.zoneSet
    
    If lookup.Exists(name) Then
    
        Set GetZoneSet = lookup(name)
        
    Else
    
        Set GetZoneSet = Nothing
        
    End If
  
End Function

As we have already discussed how dictionaries work, we wont go through this macro line by line – hopefully the comments will be enough to explain what’s going on, along with your experience from the lessons so far. 

How much time did we save?

The amount of time we saved by using dictionaries might shock you. 

To add 1,000 new zones using dictionaries took only 2.16 seconds. With the old version, it took over 12 minutes to add half that number!

Details: Dictionaries

Dictionaries are common in most programming languages, but sometimes they go by other names such as ‘Hash Maps’. A dictionary is an object that can store a number of pairs of items. These pairs consist of a Key and a Value.

The Key can be of any type, as can the Value. 

Each key in a dictionary must be unique — that is, you cant have the same key in there multiple times!

If you’re still unsure what a dictionary is, you can think of it as a kind of lookup table. Imagine you had a table of council tax bands and the prices you’d pay for each band:

Band Price
A £20,000
B £30,000
C £43,000
D £51,000

Here, the band is the key and the price is the value. If you wanted to find out the price for being in a band, you’d look it up in the table. 

Dictionaries have functions that allow you to:

  • See if there is an entry in a dictionary for a key already (dictionary.exists)
  • Add items (dictionary.add)
  • Remove items (dictionary.remove)

For more information about dictionaries, see the Excel Macro Mastery topic on the subject. 

Next Lesson

In this lesson we’ve learned something very important – that we should try to call certain type libraries functions as infrequently as possible in order to write fast macros. We’ve also looked at one way in which we can achieve this – by calling these functions once and storing the result to use later, so we don’t have to check again.

In the next lesson, we’ll move on from our zone writer macro and write one to perform some daylight calculations in the 3D modeller. We’ll also learn how to read the lux values for each point and write them to our spreadsheet. 

Smart Glass

Posted on

What is Smart Glass and is it the future of glazing?

‘Smart Glass’ is glazing that has changeable light and solar transmission values, there are a number of different types of Smart Glass that use different types of technology to alter these properties such as electrochromic, thermochromic and photochromic glazing.

Electrochromic glazing has its light and solar transmission altered when a voltage is applied to the glazing.

Thermochromic glazing has its light and solar transmission altered when heat is applied to the glazing.

Photochromic glazing has its light and solar transmission altered when electro-magnetic radiation is applied to the glazing.

When used in building design, Smart Glass can adapt to the varying climate experienced throughout the year and can create a responsive building envelope. 

Diagram from Heliotrope Technologies Inc., showing how electrochromic glazing can allow different amounts of light and solar energy into a space depending on which ‘state’ the glazing is in, not only can light and solar be controlled but they can be so in an independent way.


There are numerous potential advantages to utilising Smart Glass in building design such as:

  • Allowing more daylight into the building during winter months when there is less available, whilst also being able to reduce the amount of daylight entering the building during the summer months when excessive levels and glare are more likely to be experienced.
  • Prevent the need for external shade systems that may require higher maintenance costs, obscure occupant’s views or even be considered an eyesore.
  • Reduce heating demands in the winter and cooling demands in the summer by varying the solar energy allowed into the building.
  • Smart Glass can be used for privacy, reducing the light transmittance of the glazing of an occupied room so people can no longer see in.
  • Prevent the need for internal blinds that can lead to high glass and blind surface temperatures when there is not sufficient ventilation between the blind and the glass, leading to high radiant temperatures in the space and therefore poor occupant comfort.
  • The fact that the glazing parameters can alter means that architects have more flexibility, they can design buildings with a higher proportion of glazed façade with less risk of overheating, glare and high cooling demands. 

Photograph of public restrooms in Japan that utilise the adjustable light transmission of Smart Glass to become opaque when occupied.


So is Smart Glass the future of building design and will we see it replace conventional glazing systems? It definitely has its applications and can be of great use for designing buildings in areas that have highly variable climates but with a higher cost and the need for its benefits to be considered at early design stage it may not be for everyone.  As production costs start to drop however we could expect to see it installed more regularly, particularly in markets like high end domestic, hotels, and retrofits of buildings currently struggling with the increasing effects of climate change, experiencing high cooling demands and/or occupant discomfort.

Modelling Smart Glass with Dynamic Simulation Modelling (DSM)

Modelling the effects of Smart Glass is obviously much more complicated than modelling standard glazing as the glazing specifications are not static for the yearlong simulation. At EDSL we recently carried out an investigation into how Heliotrope’s electrochromic glazing performed in comparison to more conventional glazing options for a number of different climates. The electrochromic glazing has variable transmission, with extremes called the “bright” state and the “dark” state. Transitions between these two extreme states are changed by applying a voltage across the glass.

The “bright” state was modelled with the following parameters:

The “dark” state was modelled with the following parameters:

As you can see from the light transmittance and G values the dark state of the glazing allows significantly less light and solar energy into the space. 

Each EC glazing system has its own solar gain sensor that controls the hourly variation of the panel between ‘bright’ and ‘dark’ states. The optimal control curve (to continuously control to a target average lux level on the working plane) is non-linear and depends on both model geometry and location. Deriving these control curves requires multiple annual daylight simulations, similar to CBDM, which are non-trivial and can be time consuming. The shape of the control curve is obviously important, but the solar gain sensor value at which the EC unit starts to dim is also critical. This whole process of optimising EC control would be intractable without using the multiple cores/threads of modern processors.

Graph of the incident solar gain on the windows and the average lux in the space when using the calculated control curve to control the ‘state’ of the Smart Glass, as you can see the lux levels are held below a value of 2000 lux even when higher solar gain levels are incident. Without the control curve and with the glazing always in its ‘bright state’ lux levels reached an average of up to 14000 lux at some points through the year!


As new technologies become available for building design, thermal modelling software tools must be developed so that the effects of these can be investigated at the design stage. Modelling Smart Glass is no trivial task, at EDSL we are continuing to develop the software to make the modelling of Smart Glass a completely seamless experience for the user. 

If you’d like to find out more about Heliotrope’s electrochromic glazing check out their website Heliotrope Technologies – The Next Generation in IGU Evolution

If you have your own Smart Glass or innovative technology project you would like assistance with contact our consultancy department 

If you found this blog interesting and would like alerts when we publish blogs join our mailing list here or follow us on Facebook, Twitter or LinkedIn.

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

Posted on

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 you begin...

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 to make it faster for large files. 

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

Posted on

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

Posted on

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

Posted on

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

Posted on

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

Posted on

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)
 

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.

*BREEAM HEA04 Update – The latest version of BREEAM HEA04 now states that for the projected climate change assessment, the CIBSE 2050s medium emissions DSY2 and DSY3 future weather files should be used for naturally ventilated buildings and the CIBSE 2020s high emissions DSY2 and DSY3 future weather files should be used for mechanically ventilated or mixed mode buildings. It’s important to check what the BREEAM document you’re working towards states.

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.