Drawing Roofs

There are many options for drawing roofs in the Tas3D modeller. This short guide explains when each option may be the best choice and clears up some common questions.

Option 1: Set Wall Height
Pros: A very quick solution for simple sloped roofs with a level ridge.
Cons: Unsuitable for any other type of roof.

Option 2: Set Space Height
Pros: A very quick solution for stepped flat roofs.
Cons: Unsuitable for sloped roofs.

Option 3: Set Point Height (Select Join)
Pros: Allows quick modelling of curved roofs and intersecting slopes.
Cons: Can be time-consuming with roofs that cannot be triangulated easily. Not suitable when there is an abrupt change in roof level.

Option 4: Use 3D Planes
Pros: Can be applied to any sloping roof situation. Plane can be used for multiple roof areas at once. Intersection line between two planes can be calculated automatically. Reduces risk of errors arising from incorrect wall and point heights.
Cons: Creating the planes can be more time-consuming than the other methods.

Examples

How would three different roofs be modelled most effectively on this simple building?

With this roof there is a sudden change in roof level, and the “Set Point Height” option cannot be used. We can see why if we consider one of the points used by both sloping roofs (highlighted in lower image) which would need to have two different roof heights at the same time; in this example it would need to be 4.5m for the left-hand roof and 5.5m for the right-hand roof.

We need to use 3D Planes here.

With this example we have a sudden change in roof level, meaning that once again we have points which would need to have two different heights at once; we cannot use the “Set Point Height” option.

In this case we would need to use 3D Planes for the left-hand roof. For the right-hand roof we can simply use the “Set Space Height” option.

This roof rises to a single point and there is no step or sudden change in roof level. The roof can be achieved easily by using the “Set Point Height” option.

What about a situation where the roof itself is very simple, but there are several internal walls underneath it?

The answer depends on whether or not the roof is on a separate storey. In the case where the internal walls extend upwards to meet the underside of the sloping roof, the best option is to use 3D Planes. But if there is a separate roof space and the internal walls only extend to the underside of a flat ceiling, we should model the roof on a separate storey and the “Set Point Height Option” can be used.

What about “gaps” in the roof where internal walls or null lines are exposed?

When you create the analysis model, Tas3D creates a new building element for the exposed parts of internal walls, null walls, etc. These new elements, which will have a name ending in “-exposed” can be changed by the user to represent external walls (or, depending on your building, you may want to set these up to represent, e.g., glazing). When you refresh the analysis model you will see that your roof “gaps” have been filled. Be sure to assign an appropriate construction to these building elements in the TBD.

Overview of a 90.1 Appendix G Project in Tas

This blog post gives a brief overview of the methods recommended in Tas to carry out an ASHRAE 90.1 Appendix G analysis. Please note that the emphasis here is on the Tas methods, rather than interpretation of the regulations; as Tas files allow editing, a wide range of different interpretations can be allowed for. Moreover, the exact requirements for results or methods can vary from project to project. The steps explained here are designed to make carrying out a 90.1 project as quick and accurate as possible.

First step: Create the Proposed Tas3D file

When zoning the model, consider space use and conditioning so that each area can receive the correct internal condition and HVAC. Also consider perimeter zoning rules for the version of 90.1 you are using.

Create the Proposed TBD file

The Proposed TBD file represents the building fabric and space use of the designed building (with some important exceptions), and is used as the basis for the Baseline buildings.
Depending on the requirements of your project, may have to consider the following:
Blinds or shades that are controlled automatically or which are permanent features may be included in the Proposed building. Manually controlled blinds or shades cannot be included in the Proposed building.
The building is required to be mechanically ventilated for the purposes of the 90.1 project, so apertures for naturally ventilated areas should be removed even if they are part of the design.
The solar reflectance of roof constructions should be set as 0.30.
Automatic lighting controls may be included, but manual lighting controls may not.
If the lighting system has been designed then you should use these values. If not then the Proposed building should be set based on the building area method for the appropriate building type (more on this later).
EDSL recommend that the fresh air ventilation rate is set correctly in the internal conditions (or at least close to the correct rate) in order to ensure accurate results in TPD.

Add Proposed files to the NPO Studio

The 90.1 Studio allows organisation of the files associated with the project, and has built-in tools to speed up the project work.

Create the Baseline buildings

The 90.1 Studio creates the geometry for the four Baseline buildings; this includes rotating the building and applying new constructions in line with 90.1 versions 2007, 2010, 2013, and 2016. Constructions will be applied to the Baseline according to the surface type which the user assigns to the building elements from the proposed TBD file, according to the space use types (also assigned by the user), and according to the climate zone.

Adjust Baseline lighting

The 90.1 Studio adjusts the lighting level in the Baseline TBD files to comply with 90.1 2007, 2010, 2013, or 2016. If the Proposed building uses the building area method (because the lighting system has not been fully designed) then so should the Baseline building. In other cases, use the space-by-space method to assign the lighting gains to the Baseline building.
Lighting controls are not allowed in the Baseline building, and are automatically removed by the lighting wizard.

Simulate the TBD files

The TSD files created by these simulations will be automatically stored within the 90.1 project structure.

Create the Proposed systems file

In the Proposed building, if an area of the building has a fully designed mechanical ventilation system which provides both heating and cooling, then you should model the designed system. In all other cases (for example if the area is intended only to be heated, or it is intended to have heating and cooling but the system has not been designed) you must use the appropriate Baseline system.
When modelling the designed system, you should try to replicate the design as closely as possible. In most cases a close equivalent can be found in the wizard and edited later if necessary. Baseline systems can be created in the wizard.
See the Tas documentation for more information on Tas Systems:
https://docs.edsl.net/tpd/
If Baseline systems are used, the user should follow the guidelines below, and ensure that they run the 90.1 airside and plant efficiency tools in the Proposed TPD:
https://docs.edsl.net/NPOTPDx/
When the Proposed TPD is complete, save and add it to the 90.1 Studio.

Complete a sizing run for the Proposed TPD

This is needed so that the zone fresh air rates can be sized (if applicable) and copied to the Baseline TPD files. Save the Proposed TPD after the sizing run.

Create the Baseline 000 TPD file

We only need to create systems for one Baseline building. The TPD files for the three remaining orientations will be generated from this file.
See the guide “Tas Systems Project Wizard and 90.1 Baseline Systems” for details of Baseline system selection and setup:
https://docs.edsl.net/NPOTPDx/
Tas can generate Baseline systems for 90.1 versions 2007, 2010, 2013, 2016, or 2019.
When the Baseline TPD is complete, save and add it to the 90.1 Studio.

Create the other Baseline TPD files

Select the Baseline 000 TPD file. Select the “Use for Baseline Systems” option.

Copy fresh air rate values

Located in the 90.1 Studio under Tools -> Copy Fresh Air Rate Values.

Simulate TPD files

Final Step: Re-runs and reports

The “Generate Documentation” button in the 90.1 Studio creates several reports, including a summary of unmet hours in the Proposed and Baseline buildings. Depending on the 90.1 version and the requirements of your project, a large number of unmet hours may require you to make changes to your system sizing.

A large number of unmet hours could mean zones have been grouped together incorrectly, e.g. zones with very different schedules or gains. It could also indicate airside schedule issues, for example a radiator which is turned off during unoccupied hours and cannot provide out-of-hours heating.

Many 90.1 project submissions are assisted by providing supporting documentation to explain the project inputs. A great deal of this information can be extracted from the systems files, using the “Create Report” button in TPD and by copying data from the 90.1 efficiency tools.

Approved Document O

Overheating: Approved Document O

Approved Document O, commonly referred to as “Part O”,  relates to assessing overheating in domestic properties and came into force on the 15th June 2022.

Its purpose is to reduce the occurence of high indoor temperatures to protect the health and welfare of occupants.

TM59 vs Approved Document O?

When it comes to dynamic thermal modelling, the methodology is based on TM59 – the main differences relate to openings. The following opening controls should be familiar:

  • During the day (8am to 11pm), openings should start to open at 22°C and be fully open at 26°C
  • The openings should start to close when the temperature falls below 26°C and be fully closed at 22°C
In addition to the above, Approved Document O states that, at night (11pm to 8am), inaccessible openings should:
  • Be fully open if the internal temperature exceeds 23°C at 11pm and stay open until 8am

This does not apply to ground floor windows or openings which pose a security risk.

Demonstrating Complaince in Tas

In Tas version 9.5.4 we have introduced a new aperture function specifically for Approved Document O.

Simply set up your models as you would for TM59 and apply the above aperture function to openings which are safe to open at night.

The day operation of the function will reflect the input settings, and at night the apertures will automatically stay open if the temperature exceeds 23°C at 11pm.

Then run through the TM59 wizard as normal.

Part O Aperture Function in Tas.

What else is new?

In addition to the new aperture function for part O, Tas v9.5.4 also allows you to model the effect of ceiling fans and other means of reliably generating air movement:

TM59 wizard screenshot showing wind speed column

Increased air movement can help reduce the temperature a person experiences when there are warm radiant surfaces present.

This functionality has also been added to our TM52 Adaptive Overheating report.

Where can I find more information about TM59 and Approved Document O?

In addition to the official TM59 document and the offical Approved Document O document, please see our TM59 documentation for more information about Approved Document O and TM59 in Tas.

Tas and EnergyPlus

EDSL Tas can both import and export EnergyPlus IDF files.

EnergyPlus IDF files generally contain good quality data and a comprehensive set of inputs, making IDF import a reliable and convenient way to get data into EDSL Tas quickly.

IDF import to Tas3D:

Create a new Tas 3D model from an IDF file. In Tas3D you can create feature shading systems and edit windows. The file can be used for daylighting calculations and Climate Based Daylighting Modelling using Tas3D’s built-in daylighting engine.

 

IDF import to TBD:

Create a new, ready-to-simulate model from an IDF file. Geometry, room assignments, opaque and glazed constructions, gains, thermostats, and humidistats are all imported. Surface shading can optionally be imported.

 

IDF import to Tas3D and TBD:

Combines all the advantages of the import types mentioned above, simultaneously creating new Tas3D and TBD files. Enables the user to, e.g., import the IDF and immediately carry out daylighting calculations on the geometry, then export to TBD and find all the construction and internal condition data already assigned and the TBD ready to simulate or to import into a Tas Studio.

 

IDF export from TBD:

Any TBD that is ready to simulate can be used to create an IDF file which is ready to simulate in EnergyPlus (for room loads only).

 

The IDF Import and IDF Export features make it easy for current EnergyPlus users to get started with EDSL Tas.

 

Validation of Tas against BS EN ISO Standards

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

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. 

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

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

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 FacebookTwitter or LinkedIn.

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 to make it 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.