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.
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?
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!
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:
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.
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.